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
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
  • It is a task I believe most optimizers will have problems with. Have you considered a table function instead of a view? The arguments of the function can be pushed into the cte and make life much easier for the DBMS. Apr 29, 2020 at 4:27


Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Browse other questions tagged or ask your own question.