Remove unnescesary sorting on fields different then first when do union [CORE5497] #5766
Comments
|
Commented by: @mrotteveel I guess (!) that unique sort is there because you use `UNION` instead of `UNION ALL`, when using UNION it needs to eliminate all duplicates, only after that has been done it will apply the sort for the order by. In the first case it just happens that the unique sort to eliminate duplicates also satisfied the order by. Are you suggesting to let the order by influence the key order for the unique sort of the UNION? |
|
Commented by: @livius2 >>I guess (!) that unique sort is there because you use `UNION` instead of `UNION ALL` yes, unique sort is because of use of UNION not UNION ALL >>Are you suggesting to let the order by influence the key order for the unique sort of the UNION? Yes it will be good - if unique sort use it for checking uniques in some part then it can also change order of items in key |
Submitted by: @livius2
For query like this Firebird remove unnescessary sorting but only if we order by first column
SELECT
R.RDB$RELATION_NAME, R.RDB$DBKEY_LENGTH
FROM
RDB$RELATIONS R
UNION
SELECT
R.RDB$RELATION_NAME, R.RDB$DBKEY_LENGTH
FROM
RDB$RELATIONS R
ORDER BY 1
Select Expression
-> Unique Sort (record length: 150, key length: 108)
-> Union
-> Table "RDB$RELATIONS" as "R" Full Scan
-> Table "RDB$RELATIONS" as "R" Full Scan
but when we change query to and sort on second field (same fields but swapped)
SELECT
R.RDB$DBKEY_LENGTH, R.RDB$RELATION_NAME
FROM
RDB$RELATIONS R
UNION
SELECT
R.RDB$DBKEY_LENGTH, R.RDB$RELATION_NAME
FROM
RDB$RELATIONS R
ORDER BY 2
Select Expression
-> Sort (record length: 142, key length: 100)
-> Unique Sort (record length: 150, key length: 108)
-> Union
-> Table "RDB$RELATIONS" as "R" Full Scan
-> Table "RDB$RELATIONS" as "R" Full Scan
we have 2 sorts - one is unnescessary
The text was updated successfully, but these errors were encountered: