|
[
Permalink
| « Hide
]
Sean Leyne added a comment - 20/May/15 05:58 PM
It appears that USING is not SQL standard conformant -- it seems to be an MySQL'ism.
> USING is not SQL standard conforman
Quote 5WD-02-Foundation-2003-09.pdf (SQL-2003), page 312: 7.7 <joined table> Function Specify a table derived from a Cartesian product, inner join, or outer join. Format <joined table> ::= <cross join> | <qualified join> | <natural join> <cross join> ::= <table reference> CROSS JOIN <table factor> <qualified join> ::= <table reference>[ <join type>] JOIN <table reference> <join specification> <natural join> ::= <table reference> NATURAL[ <join type>] JOIN <table factor> ----------------------------------------- [ 1 ] <join specification> ::= <join condition> | <named columns join> <join condition> ::= ON <search condition> <named columns join> ::= USING <left paren> <join column list> <right paren> ---------------- [ 2 ] <join type> ::= What about this ? PS. I was sure that JOIN USING & NATURAL JOIN both are just 'syntax sugar' in FB... :-) Sorry, I had done some googling and could not find any usage aside from MySQL, hence the comment.
If key for join is changed from RDB$DB_KEY to common field than all become OK in 3.0: optimizer DOES make plans with HASH join in all cases (checked on WI-T3.0.0.31846):
recreate table tn(id_key int primary key using index tn_id_key); commit; insert into tn(id_key) with recursive r as (select 0 i from rdb$database union all select r.i+1 from r where r.i<99) select r1.i*100+r0.i from r r1, r r0; commit; set statistics index tn_id_key; commit; set planonly; ----------- test `traditional` join form ----------------- select count(*) from (select id_key||'' a from tn) r join (select id_key||'' a from tn) s on r.a = s.a; PLAN HASH (S TN NATURAL, R TN NATURAL) select count(*) from (select id_key||'' a from tn) r join (select id_key||'' a from tn) s on r.a = s.a join (select id_key||'' a from tn) t on s.a = t.a; PLAN HASH (HASH (T TN NATURAL, S TN NATURAL), R TN NATURAL) select count(*) from (select id_key||'' a from tn) r join (select id_key||'' a from tn) s on r.a = s.a join (select id_key||'' a from tn) t on s.a = t.a join (select id_key||'' a from tn) u on t.a = u.a; PLAN HASH (HASH (HASH (U TN NATURAL, T TN NATURAL), S TN NATURAL), R TN NATURAL) ----------- test join on named columns form ----------------- select count(*) from (select id_key||'' a from tn) r join (select id_key||'' a from tn) s using(a); PLAN HASH (S TN NATURAL, R TN NATURAL) select count(*) from (select id_key||'' a from tn) r join (select id_key||'' a from tn) s using(a) join (select id_key||'' a from tn) t using(a); PLAN HASH (T TN NATURAL, HASH (S TN NATURAL, R TN NATURAL)) select count(*) from (select id_key||'' a from tn) r join (select id_key||'' a from tn) s using(a) join (select id_key||'' a from tn) t using(a) join (select id_key||'' a from tn) u using(a); PLAN HASH (U TN NATURAL, HASH (T TN NATURAL, HASH (S TN NATURAL, R TN NATURAL))) ----------- test natural join form ----------------- select count(*) from (select id_key||'' a from tn) r natural join (select id_key||'' a from tn) s; PLAN HASH (S TN NATURAL, R TN NATURAL) select count(*) from (select id_key||'' a from tn) r natural join (select id_key||'' a from tn) s natural join (select id_key||'' a from tn) t; PLAN HASH (T TN NATURAL, HASH (S TN NATURAL, R TN NATURAL)) select count(*) from (select id_key||'' a from tn) r natural join (select id_key||'' a from tn) s natural join (select id_key||'' a from tn) t natural join (select id_key||'' a from tn) u; PLAN HASH (U TN NATURAL, HASH (T TN NATURAL, HASH (S TN NATURAL, R TN NATURAL))) |