Anyway to optimize the optimizer

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

--
http://www.freelists.org/webpage/oracle-l