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@xxxxxxxxxxxxxxx]=20 Sent: Thursday, August 05, 2004 1:26 PM To: oracle-l@xxxxxxxxxxxxx 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/SQL 10.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@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx] On Behalf Of Khedr, Waleed Sent: Thursday, August 05, 2004 12:39 PM To: oracle-l@xxxxxxxxxxxxx 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_rnk from test_d1 a, test_f1 b where a.c1 =3D3D3D b.c1; 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; ------------------------------------------------- -- Does a full index scan on BX1, I don't like it -- Did not push the predicate ------------------------------------------------- select * from test_bug1 where c2 =3D3D3D 10; --------------------------------------------------------- -- Uses the index correctly (range scan) and drives the NL -- The view has no rank function --------------------------------------------------------- Any ideas? Waleed ---------------------------------------------------------------- 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 ----------------------------------------------------------------- ---------------------------------------------------------------- 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 ----------------------------------------------------------------- ---------------------------------------------------------------- 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 -----------------------------------------------------------------