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

From: "Duret, Kathy" <kduret@xxxxxxxxxxxxxxxxxxxx>
To: "'oracle-l@xxxxxxxxxxxxx'" <oracle-l@xxxxxxxxxxxxx>
Date: Thu, 24 Jun 2004 16:37:38 -0500
_complex_view_merging

worked for us.....

Good luck,

Kathy
-----Original Message-----
From: Bobak, Mark [mailto:Mark.Bobak@xxxxxxxxxxxxxxx]
Sent: Thursday, June 24, 2004 4:13 PM
To: oracle-l@xxxxxxxxxxxxx
Subject: RE: Help with view, pushing predicate, and 9i


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@xxxxxxxxxxxxx
[mailto:oracle-l-bounce@xxxxxxxxxxxxx]On Behalf Of Juan Carlos Reyes
Pacheco
Sent: Thursday, June 24, 2004 4:52 PM
To: oracle-l@xxxxxxxxxxxxx
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@xxxxxxxxxxxxx=3D0D
Date: 06/24/04 16:37:05=3D0D
To: oracle-l@xxxxxxxxxxxxx=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@xxxxxxxxxxxxx=3D0D
put 'unsubscribe' in the subject line.=3D0D
--=3D0D
Archives are at //www.freelists.org/archives/oracle-l/=3D0D
FAQ is at //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@xxxxxxxxxxxxx
put 'unsubscribe' in the subject line.
--
Archives are at //www.freelists.org/archives/oracle-l/
FAQ is at //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 //www.freelists.org/archives/oracle-l/
FAQ is at //www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------



This transmission contains information solely for intended recipient and may
be privileged, confidential and/or otherwise protect from disclosure.  If
you are not the intended recipient, please contact the sender and delete all
copies of this transmission.  This message and/or the materials contained
herein are not an offer to sell, or a solicitation of an offer to buy, any
securities or other instruments.  The information has been obtained or
derived from sources believed by us to be reliable, but we do not represent
that it is accurate or complete.  Any opinions or estimates contained in
this information constitute our judgment as of this date and are subject to
change without notice.  Any information you share with us will be used in
the operation of our business, and we do not request and do not want any
material, nonpublic information. Absent an express prior written agreement,
we are not agreeing to treat any information confidentially and will use any
and all information and reserve the right to publish or disclose any
information you share with us.
----------------------------------------------------------------
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 //www.freelists.org/archives/oracle-l/
FAQ is at //www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------
RE: Help with view, pushing predicate, and 9i

Other related posts:
