I have written a multi-step view using CTEs.
The first step is a union on the source table (used to turn two columns begin_date and end_date into separate rows); then there is a group by that includes an important id (obj_id); then some analytic functions; then a couple of left joins; and finally the last query adds some more computed values.
One important use case (not the only one) is to only perform this computation for a single obj_id (which corresponds to multiple records in the source_table)
I have tried using a simple where on my view:
select * from my_view where obj_id = ? This of course gives the correct results, but an Explain shows that it is not using the index for obj_id in source_table, instead performing a full table scan.
How can I tell Oracle to take the where condition on obj_id (added outside the view) and push it down to the innermost CTE, in order to use the index on the source table?
Is there an index hint that can help me with this?
The obj_id column is passed down the pipeline unchanged, being included by itself in all select, group by, and partition expressions, so it should be logically "evident" that the entire query can be performed for a single obj_id.
This application is currently locked on Oracle 11g, if this makes any difference.
create or replace view MY_VIEW as with step1 as ( select obj_id, begin_date as event_date, ... from SOURCE_TABLE where ... union all select obj_id, end_date as event_date, ... from SOURCE_TABLE where ... ), step2 as ( select obj_id, ..., sum(...) from step1 group by obj_id, event_date ), step3 as ( select step2.*, ..., sum(...) over (partition by obj_id ...) from step2 ), step4 as ( select obj_id, ... from step3 left join ... on ... left join ... on ... ) select step4.*, ... from step4 ;