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@xxxxxxxxx
214.954.1781

> -----Original Message-----
> From: oracle-l-bounce@xxxxxxxxxxxxx
> [mailto:oracle-l-bounce@xxxxxxxxxxxxx]On Behalf Of Bobak, Mark
> Sent: Friday, January 30, 2004 11:18 AM
> To: oracle-l@xxxxxxxxxxxxx
> 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@xxxxxxxxx]=20
> Sent: Friday, January 30, 2004 12:19 PM
> To: oracle-l@xxxxxxxxxxxxx
> 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@xxxxxxxxx
> 214.954.1781
>
> > -----Original Message-----
> > From: oracle-l-bounce@xxxxxxxxxxxxx
> > [mailto:oracle-l-bounce@xxxxxxxxxxxxx]On Behalf Of Bobak, Mark
> > Sent: Friday, January 30, 2004 10:09 AM
> > To: oracle-l@xxxxxxxxxxxxx
> > 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@xxxxxxxxxxxxx
> > 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@xxxxxxxxxxxxx
> 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@xxxxxxxxxxxxx
> 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@xxxxxxxxxxxxx
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
-----------------------------------------------------------------

Other related posts: