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

From: Jonathan Lewis <jonathan_at_jlcomp.demon.co.uk>
Date: Thu, 24 Jun 2004 23:47:17 +0100
Message-ID: <007b01c45a3d$35330ee0$7102a8c0@Primary>

Have you tried putting a pair of global
hints in the outer query to see if that
makes any difference.

What are the index definitions for
the two indexes hinted ?

Regards

Jonathan Lewis

http://www.jlcomp.demon.co.uk

http://www.jlcomp.demon.co.uk/faq/ind_faq.html The Co-operative Oracle Users' FAQ

http://www.jlcomp.demon.co.uk/seminar.html Optimising Oracle Seminar - schedule updated May 1st

Original Message ----- From: "Bobak, Mark" <Mark.Bobak_at_il.proquest.com> To: <oracle-l_at_freelists.org> Sent: Thursday, June 24, 2004 9:38 PM 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 =3D :some_doc_id;

In 8i (8.1.7.4 on Solaris 8), the 'doc_id=3D ' predicate is pushed 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, CPAG_DAY_EFFECTIVE,=20 CPAG_CALC_DATE, CPAG_FORMAT_MASK)
AS=20
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 <=3D
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 <=3D
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

Received on Thu Jun 24 2004 - 17:44:01 CDT