Database Tuning (MOSC)

MOSC Banner

Query not using index on a view, only when going directly towards the table

edited Jan 30, 2012 2:44AM in Database Tuning (MOSC) 11 commentsAnswered
Hi,
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

Howdy, Stranger!

Log In

To view full details, sign in to My Oracle Support Community.

Register

Don't have a My Oracle Support Community account? Click here to get started.

Category Leaderboard

Top contributors this month

New to My Oracle Support Community? Visit our Welcome Center

MOSC Help Center