Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> RE: Help with view, pushing predicate, and 9i

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

From: Khedr, Waleed <Waleed.Khedr_at_FMR.COM>
Date: Thu, 24 Jun 2004 18:13:46 -0400
Message-ID: <42BBD772AC30EA428B057864E203C99911596D@MSGBOSCLF2WIN.DMN1.FMR.COM> 





Probably a 10053 trace can help!



Waleed



-----Original Message-----


From: Bobak, Mark [mailto:Mark.Bobak_at_il.proquest.com]=20
Sent: Thursday, June 24, 2004 5:43 PM


To: oracle-l_at_freelists.org


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




Yes, in fact, the original code is UNION ALL, change to UNION was one of


=3D



the iterations we tried.



-----Original Message-----


From: oracle-l-bounce_at_freelists.org


[mailto:oracle-l-bounce_at_freelists.org]On Behalf Of Khedr, Waleed
Sent: Thursday, June 24, 2004 5:40 PM


To: oracle-l_at_freelists.org


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




Did you try "UNION ALL" instead of UNION?



Waleed



-----Original Message-----


From: Bobak, Mark [mailto:Mark.Bobak_at_il.proquest.com]=3D3D20
Sent: Thursday, June 24, 2004 5:14 PM


To: oracle-l_at_freelists.org


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




If we take the SQL that defines the view, and add the predicate and =3D


=3D3D3D



execute that, then yes, we get the right plan.



-Mark



-----Original Message-----


From: oracle-l-bounce_at_freelists.org


[mailto:oracle-l-bounce_at_freelists.org]On Behalf Of Khedr, Waleed
Sent: Thursday, June 24, 2004 5:11 PM


To: oracle-l_at_freelists.org


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




The predicate may have been pushed already but the optimizer is choosing
a different plan anyway.



Do you get the right exec plan if you run sql that defines the view?



Waleed




-----Original Message-----


From: Bobak, Mark [mailto:Mark.Bobak_at_il.proquest.com]=3D3D3D3D20
Sent: Thursday, June 24, 2004 4:39 PM


To: oracle-l_at_freelists.org


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




Hi,



We have a view which is usually accessed like:
select * from vu_xan_comp_agrmt_single where doc_id =3D3D3D3D3D3D =


=3D3D3D



:some_doc_id;



In 8i (8.1.7.4 on Solaris 8), the 'doc_id=3D3D3D3D3D3D ' predicate is =


=3D



pushed =3D3D


=3D3D3D

=3D3D3D3D



into


the view, and it works fine.



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



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



Any thoughts or suggestions are welcome.



Thanks,



-Mark



PS  Here's the beastie:


CREATE OR REPLACE FORCE VIEW ADDS.VU_XAN_COMP_AGRMT_SINGLE

(DOC_ID, VLAD_ID, CPAG_TEXT, CPAG_TEXT_LENGTH, =3D3D3D

CPAG_DAY_EFFECTIVE,=3D3D3D3D3D20



 CPAG_CALC_DATE, CPAG_FORMAT_MASK)



AS=3D3D3D3D3D20


select /*+ index(cpag cpag_indx_pr02) */ distinct doc_id, 0 vlad_id,
  first_value(cpag_text) over (partition by doc_id
  order by cpag_day_effective desc)  cpag_text,
  first_value(length(cpag_text)) over (partition by doc_id

                order by cpag_day_effective desc)  cpag_text_length,


  first_value(cpag_day_effective) over (partition by doc_id
  order by cpag_day_effective desc)  cpag_day_effective,
  first_value(cpag_calc_date) over (partition by doc_id
  order by cpag_day_effective desc)  cpag_calc_date,
  first_value(cpag_format_mask) over (partition by doc_id

                order by cpag_day_effective desc)  cpag_format_mask


 from compressed_agreements cpag


 WHERE cpag.CPAG_DAY_EFFECTIVE <=3D3D3D3D3D3D
 DECODE(SIGN(TRUNC(SYSDATE)-cpag.cpag_calc_date),-1,0,
 TRUNC(SYSDATE)-cpag.cpag_calc_date + NVL(cpag.CPAG_DAY_EFFECTIVE,0))
 UNION



 select /*+ index(xpag cpag_indx_pr02) */ distinct doc_id,vlad_id,
  first_value(cpag_text) over (partition by doc_id,vlad_id
  order by vlad_id,cpag_day_effective desc)  cpag_text,
  first_value(length(cpag_text)) over (partition by doc_id,vlad_id

                order by vlad_id,cpag_day_effective desc)


cpag_text_length,


  first_value(cpag_day_effective) over (partition by doc_id,vlad_id

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


cpag_format_mask


 from xan_compressed_agreements xcpag


 WHERE xcpag.CPAG_DAY_EFFECTIVE <=3D3D3D3D3D3D
 DECODE(SIGN(TRUNC(SYSDATE)-xcpag.cpag_calc_date),-1,0,
 TRUNC(SYSDATE)-xcpag.cpag_calc_date + NVL(xcpag.CPAG_DAY_EFFECTIVE,0));






Mark J. Bobak


Oracle DBA


ProQuest Company


Ann Arbor, MI


"Post Hoc Ergo Propter Hoc"




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
-----------------------------------------------------------------
----------------------------------------------------------------
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
-----------------------------------------------------------------
----------------------------------------------------------------
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 - 17:10:32 CDT












Original text of this message













  HOME |
  ASK QUESTION |
  ADD INFO |
  SEARCH |
  E-MAIL US