FirebirdSQL
UNION ALL optimalization with fix false condition
Hi *!
CREATE OR ALTER VIEW union_opt AS SELECT 'A' union_id FROM rdb$database UNION ALL SELECT 'B' FROM rdb$database
SELECT * FROM union_opt WHERE union_id = 'A'
The select does 2 non-index reads. I think it should be only 1 non-index read, as the second select expressions conditon is fix false, no row-by-row evaluation is needed.
Thank you!
It will need to get that row to be able to evaluate that it should be filtered out.
The filtered column is a constant, so the column value is known at prepare time. The condition on the constant column also constant, so the condition can be evaluated at prepare time without needing to fetch any record from the table at exec time.
Plan and statistics after the fix:
Select Expression
-> Filter
-> Union
-> Filter (preliminary)
-> Table "RDB$DATABASE" as "UNION_OPT RDB$DATABASE" Full Scan
-> Filter (preliminary)
-> Table "RDB$DATABASE" as "UNION_OPT RDB$DATABASE" Full Scan
UNION_ID
========
A
Per table statistics:
--------------------------------+---------+---------+---------+---------+---------+---------+---------+---------+
Table name | Natural | Index | Insert | Update | Delete | Backout | Purge | Expunge |
--------------------------------+---------+---------+---------+---------+---------+---------+---------+---------+
RDB$DATABASE | 1| | | | | | | |
--------------------------------+---------+---------+---------+---------+---------+---------+---------+---------+
