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

From: "Bobak, Mark" <Mark.Bobak@xxxxxxxxxxxxxxx>
To: <oracle-l@xxxxxxxxxxxxx>
Date: Thu, 24 Jun 2004 17:50:39 -0400
Just double checked.  No luck w/ _complex_view_merging....

-----Original Message-----
From: oracle-l-bounce@xxxxxxxxxxxxx
[mailto:oracle-l-bounce@xxxxxxxxxxxxx]On Behalf Of Duret, Kathy
Sent: Thursday, June 24, 2004 5:38 PM
To: 'oracle-l@xxxxxxxxxxxxx'
Subject: RE: Help with view, pushing predicate, and 9i



_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 =3D
create a view v1 and then take the sql from afte the 'UNION ALL' and =3D
create a view v2 and finally, create view v3 as select * from v1 union =
=3D
all select * from v2; then select * from v3 where doc_id =3D3D .... =
works =3D
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=3D3D"iso-8859-1"
Content-Transfer-Encoding: quoted-printable
Two ideas, try disablyg complex view, sugin the hidden parameter, try =
=3D
usi=3D3D
ng
/*+ NO_MERGE */ =3D3D0D
 =3D3D0D
-------Original Message-------=3D3D0D
 =3D3D0D
From: oracle-l@xxxxxxxxxxxxx=3D3D0D
Date: 06/24/04 16:37:05=3D3D0D
To: oracle-l@xxxxxxxxxxxxx=3D3D0D
Subject: Help with view, pushing predicate, and 9i=3D3D0D
 =3D3D0D
Hi,=3D3D0D
 =3D3D0D
We have a view which is usually accessed like:=3D3D0D
select * from vu_xan_comp_agrmt_single where doc_id =3D3D3D3D =3D
:some_doc_id;=3D3D0D
 =3D3D0D
In 8i (8.1.7.4 on Solaris 8), the 'doc_id=3D3D3D3D ' predicate is pushed =
=3D
into=3D3D
=3D3D0D
the view, and it works fine.=3D3D0D
 =3D3D0D
We are in the process of converting this app to 9i (9.2.0.5 on =3D
Solaris=3D3D0D
8), and in 9i, the predicate is not pushed.  This results in a =
full=3D3D0D
table scan on a large table, and, abysmal performance.=3D3D0D
 =3D3D0D
This should NOT be a case where the predicate can't be pushed, due =3D
to=3D3D0D
rownum or analytical or aggregate function, etc.  It works in 8i, so =3D
why=3D3D0D
not in 9i?  Is this an optimizer bug?  (_push_join_union_view and=3D3D0D
_push_join_predicate are both TRUE.)=3D3D0D
 =3D3D0D
Any thoughts or suggestions are welcome.=3D3D0D
 =3D3D0D
Thanks,=3D3D0D
 =3D3D0D
-Mark=3D3D0D
 =3D3D0D
PS  Here's the beastie:=3D3D0D
CREATE OR REPLACE FORCE VIEW ADDS.VU_XAN_COMP_AGRMT_SINGLE=3D3D0D
(DOC_ID, VLAD_ID, CPAG_TEXT, CPAG_TEXT_LENGTH, =3D
CPAG_DAY_EFFECTIVE,=3D3D3D20=3D3D0D
  CPAG_CALC_DATE, CPAG_FORMAT_MASK)=3D3D0D
AS=3D3D3D20=3D3D0D
select /*+ index(cpag cpag_indx_pr02) */ distinct doc_id, 0 =3D
vlad_id,=3D3D0D
  first_value(cpag_text) over (partition by doc_id=3D3D0D
  order by cpag_day_effective desc)  cpag_text,=3D3D0D
  first_value(length(cpag_text)) over (partition by doc_id=3D3D0D
                order by cpag_day_effective desc)  =3D
cpag_text_length,=3D3D0D
  first_value(cpag_day_effective) over (partition by doc_id=3D3D0D
  order by cpag_day_effective desc)  cpag_day_effective,=3D3D0D
  first_value(cpag_calc_date) over (partition by doc_id=3D3D0D
  order by cpag_day_effective desc)  cpag_calc_date,=3D3D0D
  first_value(cpag_format_mask) over (partition by doc_id=3D3D0D
                order by cpag_day_effective desc)  =
cpag_format_mask=3D3D0D
  from compressed_agreements cpag=3D3D0D
  WHERE cpag.CPAG_DAY_EFFECTIVE <=3D3D3D3D=3D3D0D
  DECODE(SIGN(TRUNC(SYSDATE)-cpag.cpag_calc_date),-1,0,=3D3D0D
  TRUNC(SYSDATE)-cpag.cpag_calc_date + =3D
NVL(cpag.CPAG_DAY_EFFECTIVE,0))=3D3D0D
  UNION=3D3D0D
  select /*+ index(xpag cpag_indx_pr02) */ distinct =
doc_id,vlad_id,=3D3D0D
  first_value(cpag_text) over (partition by doc_id,vlad_id=3D3D0D
  order by vlad_id,cpag_day_effective desc)  cpag_text,=3D3D0D
  first_value(length(cpag_text)) over (partition by =
doc_id,vlad_id=3D3D0D
                order by vlad_id,cpag_day_effective desc)=3D3D0D
cpag_text_length,=3D3D0D
  first_value(cpag_day_effective) over (partition by =
doc_id,vlad_id=3D3D0D
  order by vlad_id,cpag_day_effective desc)  cpag_day_effective,=3D3D0D
  first_value(cpag_calc_date) over (partition by doc_id,vlad_id=3D3D0D
  order by vlad_id,cpag_day_effective desc)  cpag_calc_date,=3D3D0D
  first_value(cpag_format_mask) over (partition by doc_id,vlad_id=3D3D0D
                order by vlad_id,cpag_day_effective desc)=3D3D0D
cpag_format_mask=3D3D0D
  from xan_compressed_agreements xcpag=3D3D0D
  WHERE xcpag.CPAG_DAY_EFFECTIVE <=3D3D3D3D=3D3D0D
  DECODE(SIGN(TRUNC(SYSDATE)-xcpag.cpag_calc_date),-1,0,=3D3D0D
  TRUNC(SYSDATE)-xcpag.cpag_calc_date + =3D
NVL(xcpag.CPAG_DAY_EFFECTIVE,0));=3D3D
=3D3D0D
 =3D3D0D
 =3D3D0D
 =3D3D0D
 =3D3D0D
Mark J. Bobak=3D3D0D
Oracle DBA=3D3D0D
ProQuest Company=3D3D0D
Ann Arbor, MI=3D3D0D
"Post Hoc Ergo Propter Hoc"=3D3D0D
----------------------------------------------------------------=3D3D0D
Please see the official ORACLE-L FAQ: http://www.orafaq.com=3D3D0D
----------------------------------------------------------------=3D3D0D
To unsubscribe send email to:  oracle-l-request@xxxxxxxxxxxxx=3D3D0D
put 'unsubscribe' in the subject line.=3D3D0D
--=3D3D0D
Archives are at //www.freelists.org/archives/oracle-l/=3D3D0D
FAQ is at //www.freelists.org/help/fom-serve/cache/1.html=3D3D0D
-----------------------------------------------------------------


-- 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
-----------------------------------------------------------------
----------------------------------------------------------------
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:
