You signed in with another tab or window. Reload to refresh your session.You signed out in another tab or window. Reload to refresh your session.You switched accounts on another tab or window. Reload to refresh your session.Dismiss alert
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
Submitted by: @dyemanov
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
Commits: 0a98281 FirebirdSQL/fbt-repository@b782566
====== Test Details ======
Test contains plan in EXPLAINED form, according to answer by dimitr, letter 22-mar-2015 16:07
The text was updated successfully, but these errors were encountered: