Replace the hierarchical union execution with the plain one
-----------------------------------------------------------
Key: CORE-4165
URL: http://tracker.firebirdsql.org/browse/CORE-4165
Project: Firebird Core
Issue Type: Improvement
Components: Engine
Reporter: Dmitry Yemanov
Test case #1:
select 0 i from rdb$database
union all
select 1 from rdb$database
union all
select 2 from rdb$database
Current plan:
Select Expression
-> Union
-> Union
-> Table "RDB$DATABASE" Full Scan
-> Table "RDB$DATABASE" Full Scan
-> Table "RDB$DATABASE" Full Scan
Expected plan:
Select Expression
-> Union
-> Table "RDB$DATABASE" Full Scan
-> Table "RDB$DATABASE" Full Scan
-> Table "RDB$DATABASE" Full Scan
Test case #2:
select 0 i from rdb$database
union
select 1 from rdb$database
union
select 2 from rdb$database
Current plan:
Select Expression
-> Sort
-> Union
-> Sort
-> Union
-> Table "RDB$DATABASE" Full Scan
-> Table "RDB$DATABASE" Full Scan
-> Table "RDB$DATABASE" Full Scan
Expected plan:
Select Expression
-> Sort
-> Union
-> Table "RDB$DATABASE" Full Scan
-> Table "RDB$DATABASE" Full Scan
-> Table "RDB$DATABASE" Full Scan
The latter case is very important as it avoids redundant sorts. However, even
the former case could benefit from the "plain" execution, as it saves one union
context thus avoiding redundant record copying.
As a bonus, for the N streams union, we'll have only one context introduced
instead of N-1 contexts. Of course, mixed type (both ALL and DISTINCT used for
the streams) unions will behave like before:
select 0 i from rdb$database
union distinct
select 1 from rdb$database
union all
select 2 from rdb$database
Select Expression
-> Union
-> Sort
-> Union
-> Table "RDB$DATABASE" Full Scan
-> Table "RDB$DATABASE" Full Scan
-> Table "RDB$DATABASE" Full Scan
--
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
------------------------------------------------------------------------------
Get your SQL database under version control now!
Version control is standard for application code, but databases havent
caught up. So what steps can you take to put your SQL databases under
version control? Why should you start doing it? Read more to find out.
http://pubads.g.doubleclick.net/gampad/clk?id=49501711&iu=/4140/ostg.clktrk
Firebird-Devel mailing list, web interface at
https://lists.sourceforge.net/lists/listinfo/firebird-devel
