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