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: Bobak, Mark <Mark.Bobak_at_il.proquest.com>
Date: Fri, 30 Jan 2004 12:18:13 -0500
Message-ID: <4C9B6FDA0B06FE4DAF5918BBF0AD82CFBFD55F@bosmail00.bos.il.pqe> 





Larry,



You're right on.  I just came to the same independent conclusion.  If I
comment out the OLAP function from the select list, it performs exactly
as I'd expect.



Ok, I think I see a solution...I have some re-writing to do.



Thanks for the input!



-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




-----Original Message-----


From: Larry Elkins [mailto:elkinsl_at_flash.net]=20
Sent: Friday, January 30, 2004 12:19 PM


To: oracle-l_at_freelists.org


Subject: RE: Views and predicates.....




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 =3D 1;



Execution Plan




   0      SELECT STATEMENT Optimizer=3DCHOOSE (Cost=3D2 Card=3D1 =
Bytes=3D11)


   1    0   TABLE ACCESS (BY INDEX ROWID) OF 'CODE_MASTER' (Cost=3D2
Card=3D1


Bytes=3D11)


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



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 =3D 1;



Execution Plan




   0      SELECT STATEMENT Optimizer=3DCHOOSE (Cost=3D340 Card=3D100000
Bytes=3D3500000)


   1    0   VIEW OF 'V_CODE_MASTER' (Cost=3D340 Card=3D100000 =
Bytes=3D3500000)

   2    1     WINDOW (BUFFER)
   3    2       TABLE ACCESS (BY INDEX ROWID) OF 'CODE_MASTER' =


(Cost=3D340


Card=3D100000 Bytes=3D1100000)


   4    3         INDEX (FULL SCAN) OF 'CODE_MASTER_PK' (UNIQUE)

(Cost=3D188


Card=3D100000)




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) <=3D

>


>


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) <=3D

>


>


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 =3D 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=3DCHOOSE (Cost=3D496561 =

Card=3D1948518


>           Bytes=3D335145096)

>    1    0   VIEW OF 'XAN_COMP_VIEW' (Cost=3D496561 Card=3D1948518

> Bytes=3D335145096)

>    2    1     SORT (UNIQUE) (Cost=3D496561 Card=3D1948518 =

Bytes=3D378012492)


>    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=3D6171 Card=3D2609173 Bytes=3D258308127)

>    8    7                 INDEX (FULL SCAN) OF 'CPAG_CNST_UK01'


(UNIQUE)



> (Cost=3D6613 Card=3D2609173)

>    9    6               TABLE ACCESS (BY INDEX ROWID) OF


> 'COMPRESSED_AGREEMENTS' (Cost=3D1 Card=3D270 Bytes=3D25650)

>   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 =3D 1'

> When I do that, the plan looks like:

> SQL> select * from xan_mjb where doc_id  =3D1;


>




> Execution Plan

> ----------------------------------------------------------

>    0      SELECT STATEMENT Optimizer=3DCHOOSE (Cost=3D496561 =

Card=3D1948518


> Bytes=3D335145096)

>    1    0   VIEW OF 'XAN_MJB' (Cost=3D496561 Card=3D1948518

Bytes=3D335145096)


>    2    1     SORT (UNIQUE) (Cost=3D496561 Card=3D1948518 =

Bytes=3D378012492)


>    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=3D6171 Card=3D2609173 Bytes=3D258308127)

>    8    7                 INDEX (FULL SCAN) OF 'CPAG_CNST_UK01'


(UNIQUE)



> (Cost=3D6613 Card=3D2609173)

>    9    6               TABLE ACCESS (BY INDEX ROWID) OF


> 'COMPRESSED_AGREEMENTS' (Cost=3D1 Card=3D270 Bytes=3D25650)

>   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=3D1' 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=3D1' 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
-----------------------------------------------------------------
----------------------------------------------------------------
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:13 CST












Original text of this message













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