2

In my database, there is a view named VAL_DATES_V which is used in many other views in the same schema. For example, the SQL code in the view SECTOR_V uses VAL_DATES_V.

CREATE OR REPLACE VIEW schema1.sector_v AS
SELECT s.*
FROM   schema1.sector      s,
       schema1.val_dates_v d
WHERE  s.date_id   = d.date_id
AND    d.open_flag = 1;

In some use cases, we want to allow a user to override the VAL_DATES_V view with a "with clause" of the same name:

WITH val_dates_v AS (
    SELECT 1234 AS date_id,
           1    AS open_flag
    FROM   dual
) SELECT * FROM schema1.sector_v;

In our Dev database this works perfectly fine, and SECTOR_V evaluates VAL_DATES_V as what is defined in the "with clause". However, in our QA database, the optimizer uses the schema defined view SCHEMA1.VAL_DATES_V instead of the "with clause" view of the same name.

Even more confusing, if I copy the SQL code for SECTOR_V in place of "sector_v" it works just fine in QA.

Let me know if anyone can explain why the view is taking precedent over the "with clause" view of the same name.

Real World Dev Explain Plan (partially formatted):

WITH val_dates AS
  (SELECT /*+ materialize */
          val_date,
          id,
          period_month,
          period_year,
          '1' AS open_flag,
          val_date_type
   FROM   client_rep.val_dates_table a
   WHERE  a.period_year= 2014
   AND    a.period_month = 9
  )
SELECT * FROM client_rep.xamin_sleeve_attribution_v

OPERATION    OBJECT_NAME     CARDINALITY     COST 

SELECT STATEMENT         14      42 
 **TEMP TABLE TRANSFORMATION                
  LOAD AS SELECT    SYS_TEMP_0FD9D66E3_A916354E
   TABLE ACCESS VAL_DATES_TABLE 2   3 
    INDEX   VAL_DATES_IDX2   2   1 
     Access Predicates 
      AND 
       A.PERIOD_MONTH=9 
       A.PERIOD_YEAR=2014**

 SORT   14   39
 HASH JOIN  14   38
 Access Predicates 
 XSA.VARIABLE_COMP_FUND_CODE=F.VARIABLE_COMP_FUND_CODE 
 VIEW 
 index$_join$_005    105     2
 HASH JOIN
 Access Predicates
 ROWID=ROWID
 INDEX 
 FUND_CONTROL_PK     105     1 
 INDEX 
 FUND_CONTROL_UK1    105     1
 HASH JOIN 14    36 
 Access Predicates 
 XSA.BATCH_ID=B.ID 
 JOIN FILTER 
 :BF0000     93      29 
 NESTED LOOPS   93   29 
 NESTED LOOPS   190      29 
 VIEW 
 VW_NSO_1    2   2 
 HASH 2        
 VIEW   2    2 
 Filter Predicates 
 AND
 TO_NUMBER(V.OPEN_FLAG)=1 
 VAL_DATE_TYPE='Calendar End'
 **TABLE ACCESS 
 SYS_TEMP_0FD9D66E3_A916354E     2   2**
 INDEX 
 BATCHES_VAL_DATES_FK    95      1 
 Access Predicates
 B.VAL_DATE_ID=ID
 TABLE ACCESS
 BATCHES     47      13 
 Filter Predicates
 B.ACTIVE=1
 JOIN FILTER
 :BF0000     548     7 
 TABLE ACCESS
 XAMIN_SLEEVE_ATTRIBUTION    548     7 
 Access Predicates
 SYS_OP_BLOOM_FILTER(:BF0000,XSA.BATCH_ID)
 Filter Predicates
 SYS_OP_BLOOM_FILTER(:BF0000,XSA.BATCH_ID)

Create view statement:

CREATE OR REPLACE FORCE VIEW "CLIENT_REP"."XAMIN_SLEEVE_ATTRIBUTION_V" AS 
SELECT XSA.*
     FROM CLIENT_REP.XAMIN_SLEEVE_ATTRIBUTION XSA,
          CLIENT_REP.BATCHES B,
          CLIENT_REP.FUND_CONTROL F
    WHERE     XSA.BATCH_ID = B.ID
          AND B.ACTIVE = 1
          AND XSA.VARIABLE_COMP_FUND_CODE = F.VARIABLE_COMP_FUND_CODE
          AND B.VAL_DATE_ID IN
                 (SELECT v.id
                    FROM client_rep.val_dates v
                   WHERE v.open_flag = 1 AND VAL_DATE_TYPE = 'Calendar End')
 ORDER BY VAL_DATE,
          FIXED_COMP_FUND_CODE,
          TIME_PERIOD,
          SORT_KEY;

Just trust me, the QA explain plan does not even materialize the "with clause". It ignores it and just uses the "val_dates_v"

9
  • I don't think what you say in your Dev database actually happens. The schema1.sector_v view should always use the schema1.val_dates_v view. Can you add an explain of the WITHstatement from your dev environment? Mar 16, 2015 at 17:56
  • 1
    Edit your question with examples executed on the databases in question, as proof. Include EXPLAIN PLANs.
    – Philᵀᴹ
    Mar 16, 2015 at 18:09
  • I added the explain plan to the original post.
    – Silas
    Mar 16, 2015 at 19:50
  • Show us the client_rep.xamin_sleeve_attribution_v definition as well (from the same (Dev) environment as the explain plan was taken). Mar 16, 2015 at 20:49
  • 2
    create view v1 as ( select 'view' as vvv from dual ); create view v2 as ( select * from v1 ); with v1 as ( select 'cte' as vvv from dual) select * from v2; -- this test case selects from the CTE on 11.2.0.4. Madness. I'd consider this a bug
    – Philᵀᴹ
    Mar 17, 2015 at 10:35

1 Answer 1

3

This is a bug, fixed in the July 2014 CPU - See Oracle Support document 1666884.1.

Easily reproduced with the following:

create view v1 as ( select 'view' as vvv from dual );

create view v2 as ( select * from v1 ); 

with v1 as ( select 'cte' as vvv from dual) select * from v2;

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.

Not the answer you're looking for? Browse other questions tagged or ask your own question.