Issue Details (XML | Word | Printable)

Key: CORE-4165
Type: Improvement Improvement
Status: Resolved Resolved
Resolution: Fixed
Priority: Major Major
Assignee: Dmitry Yemanov
Reporter: Dmitry Yemanov
Votes: 0
Watchers: 0
Operations

If you were logged in you would be able to see more operations.
Firebird Core

Replace the hierarchical union execution with the plain one

Created: 05/Aug/13 02:29 PM   Updated: 29/May/15 04:20 PM
Component/s: Engine
Affects Version/s: None
Fix Version/s: 3.0 Alpha 2

QA Status: Done successfully
Test Details: Test contains plan in EXPLAINED form, according to answer by dimitr, letter 22-mar-2015 16:07


 Description  « Hide
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


 All   Comments   Change History   Subversion Commits      Sort Order: Ascending order - Click to sort in descending order
There are no comments yet on this issue.