RE: Help with view, pushing predicate, and 9i

From: Bobak, Mark <Mark.Bobak_at_il.proquest.com>
Date: Thu, 24 Jun 2004 17:13:20 -0400
Message-ID: <4C9B6FDA0B06FE4DAF5918BBF0AD82CF09660B2E@bosmail00.bos.il.pqe>

Tried both of those, no luck......

Also, discovered that if we take the sql before the 'UNION ALL' and = create a view v1 and then take the sql from afte the 'UNION ALL' and = create a view v2 and finally, create view v3 as select * from v1 union = all select * from v2; then select * from v3 where doc_id =3D .... works = correctly!

Seems to be something with the union all....

-----Original Message-----
From: oracle-l-bounce_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org]On Behalf Of Juan Carlos Reyes Pacheco
Sent: Thursday, June 24, 2004 4:52 PM
To: oracle-l_at_freelists.org
Subject: Re: Help with view, pushing predicate, and 9i

Content-Type: Text/Plain;
  charset=3D"iso-8859-1"
Content-Transfer-Encoding: quoted-printable Two ideas, try disablyg complex view, sugin the hidden parameter, try = usi=3D
ng
/*+ NO_MERGE */ =3D0D
=3D0D

-------Original Message-------=3D0D
=3D0D

From: oracle-l_at_freelists.org=3D0D
Date: 06/24/04 16:37:05=3D0D
To: oracle-l_at_freelists.org=3D0D
Subject: Help with view, pushing predicate, and 9i=3D0D
=3D0D

Hi,=3D0D
=3D0D

We have a view which is usually accessed like:=3D0D select * from vu_xan_comp_agrmt_single where doc_id =3D3D3D = :some_doc_id;=3D0D
=3D0D

In 8i (8.1.7.4 on Solaris 8), the 'doc_id=3D3D3D ' predicate is pushed = into=3D
=3D0D
the view, and it works fine.=3D0D
=3D0D

We are in the process of converting this app to 9i (9.2.0.5 on = Solaris=3D0D
8), and in 9i, the predicate is not pushed. This results in a full=3D0D table scan on a large table, and, abysmal performance.=3D0D
=3D0D

This should NOT be a case where the predicate can't be pushed, due = to=3D0D
rownum or analytical or aggregate function, etc. It works in 8i, so = why=3D0D
not in 9i? Is this an optimizer bug? (_push_join_union_view and=3D0D _push_join_predicate are both TRUE.)=3D0D
=3D0D

Any thoughts or suggestions are welcome.=3D0D
=3D0D

Thanks,=3D0D
=3D0D

-Mark=3D0D
=3D0D

PS Here's the beastie:=3D0D
CREATE OR REPLACE FORCE VIEW ADDS.VU_XAN_COMP_AGRMT_SINGLE=3D0D (DOC_ID, VLAD_ID, CPAG_TEXT, CPAG_TEXT_LENGTH, = CPAG_DAY_EFFECTIVE,=3D3D20=3D0D
  CPAG_CALC_DATE, CPAG_FORMAT_MASK)=3D0D AS=3D3D20=3D0D
select /*+ index(cpag cpag_indx_pr02) */ distinct doc_id, 0 = vlad_id,=3D0D
  first_value(cpag_text) over (partition by doc_id=3D0D   order by cpag_day_effective desc) cpag_text,=3D0D   first_value(length(cpag_text)) over (partition by doc_id=3D0D

                order by cpag_day_effective desc)  =

cpag_text_length,=3D0D
first_value(cpag_day_effective) over (partition by doc_id=3D0D order by cpag_day_effective desc) cpag_day_effective,=3D0D first_value(cpag_calc_date) over (partition by doc_id=3D0D order by cpag_day_effective desc) cpag_calc_date,=3D0D first_value(cpag_format_mask) over (partition by doc_id=3D0D

                order by cpag_day_effective desc)  cpag_format_mask=3D0D

  from compressed_agreements cpag=3D0D
  WHERE cpag.CPAG_DAY_EFFECTIVE <=3D3D3D=3D0D   DECODE(SIGN(TRUNC(SYSDATE)-cpag.cpag_calc_date),-1,0,=3D0D   TRUNC(SYSDATE)-cpag.cpag_calc_date + = NVL(cpag.CPAG_DAY_EFFECTIVE,0))=3D0D
  UNION=3D0D
  select /*+ index(xpag cpag_indx_pr02) */ distinct doc_id,vlad_id,=3D0D   first_value(cpag_text) over (partition by doc_id,vlad_id=3D0D   order by vlad_id,cpag_day_effective desc) cpag_text,=3D0D   first_value(length(cpag_text)) over (partition by doc_id,vlad_id=3D0D

                order by vlad_id,cpag_day_effective desc)=3D0D

cpag_text_length,=3D0D
first_value(cpag_day_effective) over (partition by doc_id,vlad_id=3D0D

  order by vlad_id,cpag_day_effective desc)  cpag_day_effective,=3D0D
  first_value(cpag_calc_date) over (partition by doc_id,vlad_id=3D0D
  order by vlad_id,cpag_day_effective desc)  cpag_calc_date,=3D0D
  first_value(cpag_format_mask) over (partition by doc_id,vlad_id=3D0D
                order by vlad_id,cpag_day_effective desc)=3D0D

cpag_format_mask=3D0D
from xan_compressed_agreements xcpag=3D0D WHERE xcpag.CPAG_DAY_EFFECTIVE <=3D3D3D=3D0D DECODE(SIGN(TRUNC(SYSDATE)-xcpag.cpag_calc_date),-1,0,=3D0D TRUNC(SYSDATE)-xcpag.cpag_calc_date + = NVL(xcpag.CPAG_DAY_EFFECTIVE,0));=3D
=3D0D
=3D0D
=3D0D
=3D0D
=3D0D

Mark J. Bobak=3D0D
Oracle DBA=3D0D
ProQuest Company=3D0D
Ann Arbor, MI=3D0D
"Post Hoc Ergo Propter Hoc"=3D0D

----------------------------------------------------------------=3D0D

Please see the official ORACLE-L FAQ: http://www.orafaq.com=3D0D

----------------------------------------------------------------=3D0D

To unsubscribe send email to: oracle-l-request_at_freelists.org=3D0D put 'unsubscribe' in the subject line.=3D0D --=3D0D
Archives are at http://www.freelists.org/archives/oracle-l/=3D0D FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html=3D0D

Binary/unsupported file stripped by Ecartis --
Type: image/gif
File: IMSTP.gif

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 Thu Jun 24 2004 - 16:10:42 CDT