Anyway to optimize the optimizer
- From: "Khedr, Waleed" <Waleed.Khedr@xxxxxxx>
- To: <oracle-l@xxxxxxxxxxxxx>
- Date: Wed, 2 Mar 2005 12:23:49 -0500
I'm trying to achieve partition pruning when accessing a table using a
column other than the partition key.
Here is the test:
create table test_p_v ( c1 number , c2 number, c3 number)=20
partition by range (c1)=20
( partition p1 values less than (100),=20
partition p2 values less than (200),
partition p3 values less than (300));
Relationship between c2 and c1 is:
c1 <=3D c2 <=3D c1 + 10
create or replace view test_v_v1 as
select *=20
from test_p_v
where c1 <=3D c2
and c1 >=3D -10 + c2;
Now let's see how Oracle will evaluate the sql below:
=20
select *=20
from test_v_v1
where c2 =3D 150 ;
=20
Here is how Oracle was able to optimize the predicates:
"TEST_P_V"."C2"=3D150 AND=20
"TEST_P_V"."C1"<=3D"TEST_P_V"."C2" AND=20
"TEST_P_V"."C1">=3D(-10)+"TEST_P_V"."C2" AND=20
"TEST_P_V"."C1"<=3D150 AND=20
(-10)+"TEST_P_V"."C2"<=3D150
It replaced C2 with 150 everywhere it appeared just by itself.
Can't substituted it in (-10)+"TEST_P_V"."C2"
The only part that will affect partitions is:
"TEST_P_V"."C1"<=3D150
And this is not perfect, since it will have to access all partitions
below p3.
Any ideas achieving the above?
Waleed
=20
--
//www.freelists.org/webpage/oracle-l
Other related posts:
