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"
schema1.sector_v
view should always use theschema1.val_dates_v
view. Can you add an explain of theWITH
statement from your dev environment?client_rep.xamin_sleeve_attribution_v
definition as well (from the same (Dev) environment as the explain plan was taken).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