| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> 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_at_il.proquest.com]
Sent: Thursday, June 24, 2004 4:13 PM
To: oracle-l_at_freelists.org
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_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org]On Behalf Of Juan Carlos Reyes
Pacheco
Sent: Thursday, June 24, 2004 4:52 PM
To: oracle-l_at_freelists.org
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_at_freelists.org=3D0D
Date: 06/24/04 16:37:05=3D0D
To: oracle-l_at_freelists.org=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
order by cpag_day_effective desc) cpag_format_mask=3D0D
from compressed_agreements cpag=3D0D
order by vlad_id,cpag_day_effective desc)=3D0D
cpag_text_length,=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
----------------------------------------------------------------=3D0DPlease see the official ORACLE-L FAQ: http://www.orafaq.com=3D0D
----------------------------------------------------------------=3D0DTo unsubscribe send email to: oracle-l-request_at_freelists.org=3D0D put 'unsubscribe' in the subject line.=3D0D --=3D0D
-- 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 ----------------------------------------------------------------- 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_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 - 16:35:29 CDT
 |
 |