Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
![]() |
![]() |
Home -> Community -> Mailing Lists -> Oracle-L -> RE: Pushing predicates and Analytic Functions (problem)
Solaris and Oracle 9.2.0.4.=20
I looked the bug you mentioned, but I don't have union.
The test case I sent earlier is very simple and shows the bug.
Thanks
Waleed
-----Original Message-----
From: Bobak, Mark [mailto:Mark.Bobak_at_il.proquest.com]=20
Sent: Thursday, August 05, 2004 1:26 PM
To: oracle-l_at_freelists.org
Subject: RE: Pushing predicates and Analytic Functions (problem)
Waleed,
What platform/version???
Reference base bug #3607226, still open as I write this. I have an active TAR which I am pursuing. I have a test case that shows:
8.1.7.4 is fine 9.2.0.5 is broken 9.2.0.5+patch for 3607226 fixes SQL but not PL/SQL10.1.0.2 is broken
I'm currently waiting for development to correct the patch they released to fix the PL/SQL as well as the SQL case.
I can send you my test case, if you're interested. It's pretty large, though.
-Mark
Mark J. Bobak
Oracle DBA
ProQuest Company
Ann Arbor, MI
-----Original Message-----
From: oracle-l-bounce_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Khedr, Waleed
Sent: Thursday, August 05, 2004 12:39 PM
To: oracle-l_at_freelists.org
Subject: Pushing predicates and Analytic Functions (problem)
I'm having difficulty pushing a predicate into a view that has an
analytic function.
Optimizer is determined to keep it as a filter on the last step of the
execution plan instead of pushing it to run earlier and does them
filtering using the index.
Test case:
create table test_d1 ( c1 number not null, c2 number not null); create index bx1 on test_d1 (c2);
create table test_f1 ( c1 number not null, c2 number not null); create index bx2 on test_f1 (c1);
create or replace view test_bug as
select --+ ordered index(a,bx1) use_nl(b) index(b,x2)
a.c2, rank() over (partition by b.c1 order by b.c2 desc) m_rnkfrom test_d1 a, test_f1 b
create or replace view test_bug1 as
select --+ ordered index(a,bx1) use_nl(b) index(b,x2)
a.c2=3D3D20
from test_d1 a, test_f1 b
where a.c1 =3D3D3D b.c1
select *
from test_bug
where c2 =3D3D3D 10;
select *
from test_bug1
where c2 =3D3D3D 10;
Any ideas?
Waleed
FAQ is at http://www.freelists.org/help/fom-serve/cache/1.htmlput 'unsubscribe' in the subject line.
-----------------------------------------------------------------
----------------------------------------------------------------
Please see the official ORACLE-L FAQ: http://www.orafaq.com
----------------------------------------------------------------
To unsubscribe send email to: oracle-l-request_at_freelists.org
FAQ is at http://www.freelists.org/help/fom-serve/cache/1.htmlput 'unsubscribe' in the subject line.
-----------------------------------------------------------------
----------------------------------------------------------------
Please see the official ORACLE-L FAQ: http://www.orafaq.com
----------------------------------------------------------------
To unsubscribe send email to: oracle-l-request_at_freelists.org
![]() |
![]() |