Query not using index on a view, only when going directly towards the table
Hi,
11.2.0.2
The following two querys get different plans.
VIEW
TABLE
This is the VIEW:
CREATE OR REPLACE FORCE VIEW LDW.MDV_CUST_CUID
(
CUID_CUST_KEY,
EFV_DATE,
END_DATE,
CUR_FLAG,
CUID_ID,
CUID_TYPE,
CUST_CUST_KEY,
CUST_ID,
MRKT_ID,
ZIP_CODE,
CITY_NAME,
GEND_CODE,
BD_DATE,
BD_YEAR,
ASSN_STOR_ID,
EMPL_FLAG,
ECOM_CUST_FLAG,
LOAD_TSTM
11.2.0.2
The following two querys get different plans.
VIEW
select * from ldw.mdv_cust_cuid where cust_id = 4095714
TABLE
select * from ldw.mdt_cust_cuid where cust_id = 4095714
The one against the table uses the index on cust_id. The query towards the view do a FTS.This is the VIEW:
CREATE OR REPLACE FORCE VIEW LDW.MDV_CUST_CUID
(
CUID_CUST_KEY,
EFV_DATE,
END_DATE,
CUR_FLAG,
CUID_ID,
CUID_TYPE,
CUST_CUST_KEY,
CUST_ID,
MRKT_ID,
ZIP_CODE,
CITY_NAME,
GEND_CODE,
BD_DATE,
BD_YEAR,
ASSN_STOR_ID,
EMPL_FLAG,
ECOM_CUST_FLAG,
LOAD_TSTM
0