Pushing predicates and Analytic Functions (problem)

From: Khedr, Waleed <Waleed.Khedr_at_FMR.COM>
Date: Thu, 5 Aug 2004 12:38:32 -0400
Message-ID: <42BBD772AC30EA428B057864E203C9991159E0@MSGBOSCLF2WIN.DMN1.FMR.COM>

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 =3D b.c1;

create or replace view test_bug1 as
select --+ ordered index(a,bx1) use_nl(b) index(b,x2)

     a.c2=20
 from test_d1 a, test_f1 b
 where a.c1 =3D b.c1

 select *
  from test_bug
  where c2 =3D 10;

---

• Does a full index scan on BX1, I don't like it
• Did not push the predicate

  ---

 select *
  from test_bug1
  where c2 =3D 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_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 Aug 05 2004 - 11:35:15 CDT