Hi folks,
I'm interested in understanding when and if Oracle will push predicates from a query into a view constructed from multiple UNION ALLs.
(This may be related to this recent discussion but it doesn't answer my question directly,
449931
I understand from the documentation on technet that Oracle
will push top-level predicates down into the multiple branches of a UNION view. However, anecdotal tests on our development box do not confirm this.
So, can anyone tell me what version of Oracle introduced the pushing of predicates into UNION views? Or if there are any restrictions as to the circumstances when this will happen? Is there a limit to what can go on inside the UNIONed statements?
Our database version is 8i (8.1.7).
The view is constructed of a series of (presently 3 but in future more) SELECTs connected by UNION ALLs. Each of the component SELECTs is actually a further VIEW outer joining c. 10 base tables (all suitably analysed and indexed).
The top-level statement joins the view and a number of other tables.
Each component statement, when executed in isolation, uses the relevant (analysed) indexes and returns sub-second if I manually push the predicate.
If I leave Oracle to do the job, querying the top-level statement, it appears to prefer to materialise the view before joining it.
This is okayish and I know I could use a pukka materialised view to boost performace but if I could get the optimiser to do the work so much the better.
Many thanks,
denty.