Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> Views and predicates.....

Views and predicates.....

From: Bobak, Mark <Mark.Bobak_at_il.proquest.com>
Date: Fri, 30 Jan 2004 11:09:07 -0500
Message-ID: <4C9B6FDA0B06FE4DAF5918BBF0AD82CFECFBD9@bosmail00.bos.il.pqe>


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_at_freelists.org put 'unsubscribe' in the subject line.
--
Archives are at http://www.freelists.org/archives/oracle-l/
FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------
Received on Fri Jan 30 2004 - 10:09:07 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US