Force optimizer to consider usage of MERGE JOIN when data sources are joined on
USING(<col>) or by NATURAL clauses (related to 2.5 only)
----------------------------------------------------------------------------------------------------------------------------------------
Key: CORE-4822
URL: http://tracker.firebirdsql.org/browse/CORE-4822
Project: Firebird Core
Issue Type: Improvement
Components: Engine
Affects Versions: 2.5.4, 2.5.3 Update 1, 2.5.5
Reporter: Pavel Zotov
Priority: Minor
I've opened this ticket on request by dimitr, in order to separate issues about
2.5 and 3.0.
AFAIU, ticket 4809 is relevant only to FB 3.0.
Following samples use only NESTED LOOPS:
recreate table tn(x int primary key using index tn_x);
commit;
insert into tn(x)
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_x;
commit;
set planonly;
----------- test `traditional` join form -----------------
select count(*)
from (select rdb$db_key||'' a from tn) r
join (select rdb$db_key||'' a from tn) s on r.a = s.a;
PLAN JOIN (R TN NATURAL, S TN NATURAL)
select count(*)
from (select rdb$db_key||'' a from tn) r
join (select rdb$db_key||'' a from tn) s on r.a = s.a
join (select rdb$db_key||'' a from tn) t on s.a = t.a;
PLAN JOIN (R TN NATURAL, S TN NATURAL, T TN NATURAL)
select count(*)
from (select rdb$db_key||'' a from tn) r
join (select rdb$db_key||'' a from tn) s on r.a = s.a
join (select rdb$db_key||'' a from tn) t on s.a = t.a
join (select rdb$db_key||'' a from tn) u on t.a = u.a;
PLAN JOIN (R TN NATURAL, S TN NATURAL, T TN NATURAL, U TN NATURAL)
----------- test join on named columns form -----------------
select count(*)
from (select rdb$db_key||'' a from tn) r
join (select rdb$db_key||'' a from tn) s using(a);
PLAN JOIN (R TN NATURAL, S TN NATURAL)
select count(*)
from (select rdb$db_key||'' a from tn) r
join (select rdb$db_key||'' a from tn) s using(a)
join (select rdb$db_key||'' a from tn) t using(a);
PLAN JOIN (R TN NATURAL, S TN NATURAL, T TN NATURAL)
select count(*)
from (select rdb$db_key||'' a from tn) r
join (select rdb$db_key||'' a from tn) s using(a)
join (select rdb$db_key||'' a from tn) t using(a)
join (select rdb$db_key||'' a from tn) u using(a);
PLAN JOIN (R TN NATURAL, S TN NATURAL, T TN NATURAL, U TN NATURAL)
----------- test natural join form -----------------
select count(*)
from (select rdb$db_key||'' a from tn) r
natural join (select rdb$db_key||'' a from tn) s;
PLAN JOIN (R TN NATURAL, S TN NATURAL)
select count(*)
from (select rdb$db_key||'' a from tn) r
natural join (select rdb$db_key||'' a from tn) s
natural join (select rdb$db_key||'' a from tn) t;
PLAN JOIN (R TN NATURAL, S TN NATURAL, T TN NATURAL)
select count(*)
from (select rdb$db_key||'' a from tn) r
natural join (select rdb$db_key||'' a from tn) s
natural join (select rdb$db_key||'' a from tn) t
natural join (select rdb$db_key||'' a from tn) u;
PLAN JOIN (R TN NATURAL, S TN NATURAL, T TN NATURAL, U TN NATURAL)
--
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators:
http://tracker.firebirdsql.org/secure/Administrators.jspa
-
For more information on JIRA, see: http://www.atlassian.com/software/jira
------------------------------------------------------------------------------
Firebird-Devel mailing list, web interface at
https://lists.sourceforge.net/lists/listinfo/firebird-devel
