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

  • From: "Juan Carlos Reyes Pacheco" <jreyes@xxxxxxxxxxxxxxxx>
  • To: <oracle-l@xxxxxxxxxxxxx>
  • Date: Thu, 24 Jun 2004 16:52:02 -0400

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


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

Other related posts:

  1. Home
  2. oracle-l
  3. Archive
  4. 06-2004