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

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

RE: Views and predicates.....

From: Larry Elkins <elkinsl_at_flash.net>
Date: Fri, 30 Jan 2004 11:18:38 -0600
Message-ID: <MPBBKDBLJAGDLMINJNKBAEJJANAB.elkinsl@flash.net>


Mark,

It may have to do with the analytic functions. I've run into similar things where the predicate doesn't get pushed and is applied after the view is virtualized (?). Here's a real simple test, a two column table, CODE_MASTER, the CODE column is a PK. Unique scan is used when the view doesn't have the analytic, but a full scan on the index is used when an analytic exists. In this case, we partition by the predicate column in the analytic, but even if the analytic doesn't reference the predicate column in any way, the test is repeatable. The test case below behaves the same on 8.1.7 and 9.2.0.1.

FWIW, I've also found some strange behaviors dealing with view merging and the existence of analytics, and how it differed between 8.1.7 and 9.2.0.1. I'll see if I can dig up my test cases and examples.

SQL> set autotrace trace explain
SQL> create or replace view v_code_master   2 as select code, foo_date from code_master;

View created.

SQL>
SQL> select *
  2 from v_code_master
  3 where code = 1;

Execution Plan


   0 SELECT STATEMENT Optimizer=CHOOSE (Cost=2 Card=1 Bytes=11)    1 0 TABLE ACCESS (BY INDEX ROWID) OF 'CODE_MASTER' (Cost=2 Card=1 Bytes=11)

   2 1 INDEX (UNIQUE SCAN) OF 'CODE_MASTER_PK' (UNIQUE) (Cost=1 Card=1)

SQL>
SQL> create or replace view v_code_master   2 as select code, foo_date, row_number () over (partition by code order by code) foo

     from code_master;

View created.

SQL>
SQL> select *
  2 from v_code_master
  3 where code = 1;

Execution Plan


   0 SELECT STATEMENT Optimizer=CHOOSE (Cost=340 Card=100000 Bytes=3500000)

   1 0 VIEW OF 'V_CODE_MASTER' (Cost=340 Card=100000 Bytes=3500000)

   2    1     WINDOW (BUFFER)
   3    2       TABLE ACCESS (BY INDEX ROWID) OF 'CODE_MASTER' (Cost=340
Card=100000 Bytes=1100000)
   4    3         INDEX (FULL SCAN) OF 'CODE_MASTER_PK' (UNIQUE) (Cost=188
Card=100000)

Regards,

Larry G. Elkins
elkinsl_at_flash.net
214.954.1781

> -----Original Message-----
> From: oracle-l-bounce_at_freelists.org
> [mailto:oracle-l-bounce_at_freelists.org]On Behalf Of Bobak, Mark
> Sent: Friday, January 30, 2004 10:09 AM
> To: oracle-l_at_freelists.org
> 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_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
> -----------------------------------------------------------------



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 - 11:18:38 CST

Original text of this message

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