Correlated Sub-queries: Predicate Promotion in CSQs
1) ps_jrnl_ln: INDEX PATH
Filters: NOT EXISTS <subquery>
(1) Index Keys: process_instance business_unit
Lower Index Filter: (ps_jrnl_ln.business_unit = 'ABC' AND ps_jrnl_ln.process_instance = 5960 )
1) ps_bus_unit_tbl_gl: INDEX PATH
(1) Index Keys: business_unit (Key-Only)
ps_bus_unit_tbl_gl.business_unit = 'ABC'
2) ps_bus_unit_tbl_fs: INDEX PATH
(1) Index Keys: business_unit descr (Key-Only)
Lower Index Filter: ps_bus_unit_tbl_fs.business_unit = ps_bus_unit_tbl_gl.business_unit
Constant Subquery Optimization
When this filter is checked for the first row, the query can stop immediately, if:
- it’s a NOT EXISTS and a row is found
- it’s an EXISTS and no rows are found