Mark, this may be as simple as selecting the correct doc_id column. Is the CPAG_CNST_UK01 index in the COMPRESSED_AGREEMENTS table? If not, then change your select statement (within the view) to query the doc_id column from the correct table. Good Luck Tom Mercadante Oracle Certified Professional -----Original Message----- From: Bobak, Mark [mailto:Mark.Bobak@xxxxxxxxxxxxxxx] Sent: Friday, January 30, 2004 11:09 AM To: oracle-l@xxxxxxxxxxxxx Subject: Views and predicates..... Ok, so I have a view defined as: create or replace view xan_comp_view as select /*+ ordered use_nl(xcpag) */ distinct cpag.doc_id doc_id, decode(xcpag.doc_id,NULL,cpag.cpag_text,xcpag.cpag_text) cpag_text, decode(xcpag.doc_id,NULL,length(cpag.cpag_text),length(xcpag.cpag_text)) cpag_text_length, decode(xcpag.doc_id,NULL,max(cpag.cpag_day_effective) over (partition by cpag.doc_id), max(xcpag.cpag_day_effective) over (partition by xcpag.doc_id)) cpag_day_effective, decode(xcpag.doc_id,NULL,'PQ','XN') aud_type from compressed_agreements cpag, xan_compressed_agreements xcpag WHERE decode(xcpag.doc_id,NULL,cpag.CPAG_DAY_EFFECTIVE,1) <= decode(xcpag.doc_id,NULL,decode(sign(trunc(sysdate)-cpag.cpag_calc_date) ,-1,0, trunc(sysdate)-cpag.cpag_calc_date + NVL(cpag.C PAG_DAY_EFFECTIVE,0)),1) and decode(xcpag.doc_id,NULL,1,xcpag.CPAG_DAY_EFFECTIVE) <= decode(xcpag.doc_id,NULL,1,decode(sign(trunc(sysdate)-xcpag.cpag_calc_da te),-1,0, trunc(sysdate)-xcpag.cpag_calc_date + NVL(xcpag.CPAG_DAY_EFFECTIVE,0))) and cpag.doc_id = xcpag.doc_id(+) / If I do 'select * from xan_comp_view;' The plan is: SQL> select * from xan_comp_view 2 / Execution Plan ---------------------------------------------------------- 0 SELECT STATEMENT Optimizer=CHOOSE (Cost=496561 Card=1948518 Bytes=335145096) 1 0 VIEW OF 'XAN_COMP_VIEW' (Cost=496561 Card=1948518 Bytes=335145096) 2 1 SORT (UNIQUE) (Cost=496561 Card=1948518 Bytes=378012492) 3 2 WINDOW (SORT) 4 3 WINDOW (BUFFER) 5 4 FILTER 6 5 NESTED LOOPS (OUTER) 7 6 TABLE ACCESS (BY INDEX ROWID) OF 'COMPRESSED_AGREEMENTS' (Cost=6171 Card=2609173 Bytes=258308127) 8 7 INDEX (FULL SCAN) OF 'CPAG_CNST_UK01' (UNIQUE) (Cost=6613 Card=2609173) 9 6 TABLE ACCESS (BY INDEX ROWID) OF 'COMPRESSED_AGREEMENTS' (Cost=1 Card=270 Bytes=25650) 10 9 INDEX (RANGE SCAN) OF 'CPAG_INDX_FK01' (NON-UNIQUE) This is fine, and I'm ok w/ it. But, I don't plan on ever using the view this way. What I plan to do is something like: 'select * from xan_comp_view where doc_id = 1' When I do that, the plan looks like: SQL> select * from xan_mjb where doc_id =1; Execution Plan ---------------------------------------------------------- 0 SELECT STATEMENT Optimizer=CHOOSE (Cost=496561 Card=1948518 Bytes=335145096) 1 0 VIEW OF 'XAN_MJB' (Cost=496561 Card=1948518 Bytes=335145096) 2 1 SORT (UNIQUE) (Cost=496561 Card=1948518 Bytes=378012492) 3 2 WINDOW (SORT) 4 3 WINDOW (BUFFER) 5 4 FILTER 6 5 NESTED LOOPS (OUTER) 7 6 TABLE ACCESS (BY INDEX ROWID) OF 'COMPRESSED_AGREEMENTS' (Cost=6171 Card=2609173 Bytes=258308127) 8 7 INDEX (FULL SCAN) OF 'CPAG_CNST_UK01' (UNIQUE) (Cost=6613 Card=2609173) 9 6 TABLE ACCESS (BY INDEX ROWID) OF 'COMPRESSED_AGREEMENTS' (Cost=1 Card=270 Bytes=25650) 10 9 INDEX (RANGE SCAN) OF 'CPAG_INDX_FK01' (NON-UNIQUE) What I want to see here is an INDEX (UNIQUE SCAN) on CPAG_CNST_UK01', since I'm providing the 'doc_id=1' predicate. However, it insists on the FULL SCAN, which, of course, is killing my performance. Can anyone think of a way that I can convince the optimizer to do what I want? If I take the SQL that defines the view, and execute it as a SQL, with the 'DOC_ID=1' predicate, it does exactly what I want. As soon as I put the SQL into the view, it stops working. Help! Thanks in advance, -Mark Mark J. Bobak Oracle DBA ProQuest Company Ann Arbor, MI "Imagination was given to man to compensate him for what he is not, and a sense of humor was provided to console him for what he is." --Horace Walpole ---------------------------------------------------------------- Please see the official ORACLE-L FAQ: http://www.orafaq.com ---------------------------------------------------------------- To unsubscribe send email to: oracle-l-request@xxxxxxxxxxxxx put 'unsubscribe' in the subject line. -- Archives are at //www.freelists.org/archives/oracle-l/ FAQ is at //www.freelists.org/help/fom-serve/cache/1.html ----------------------------------------------------------------- ---------------------------------------------------------------- Please see the official ORACLE-L FAQ: http://www.orafaq.com ---------------------------------------------------------------- To unsubscribe send email to: oracle-l-request@xxxxxxxxxxxxx put 'unsubscribe' in the subject line. -- Archives are at //www.freelists.org/archives/oracle-l/ FAQ is at //www.freelists.org/help/fom-serve/cache/1.html -----------------------------------------------------------------