| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> RE: Views and predicates.....
Yep, I've been there ;-) Good luck on the re-write.
I found my old notes on the analytic and view merging stuff, and it might be of interest to you. You know how you can use in-line views to reduce sorting, calls to a DB function, etc? Well, we had a case where the SQL would call a DB function, but we really didn't need to do it until all the rows were aggregated, thus drastically reducing the calls to the function. Having the function call at a higher level in the SQL with the main select in the in-line view, combined with the use of the NO_MERGE hint, worked well. But once we included an analytic, the NO_MERGE hint was being ignored, and the function call was being invoked for every row. This was against 8.1.7. But when testing against 9.2.0.1, the NO_MERGE hint was honored, all the rows were selected and aggregated, and then the function call was applied.
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 11:18 AM
> To: oracle-l_at_freelists.org
> Subject: RE: Views and predicates.....
>
>
> 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
> -----------------------------------------------------------------
>
-- 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:43:48 CST
 |
 |