Pushing predicates and Analytic Functions (problem)
- From: "Khedr, Waleed" <Waleed.Khedr@xxxxxxx>
- To: <oracle-l@xxxxxxxxxxxxx>
- Date: Thu, 5 Aug 2004 12:38:32 -0400
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@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
-----------------------------------------------------------------
Other related posts:
