Making the Oracle optimizer use a virtual column to find out about a partition I have a large table that has three columns like this:

"START_DATE" DATE,
"START_VALUE" NUMBER(10,7)
"START_DATE_VALUE" NUMBER(18,7) 
  GENERATED ALWAYS AS 
  (
    (extract(YEAR FROM START_DATE) * 10000 + 
     extract(MONTH FROM START_DATE)*100 + 
     extract(DAY FROM START_DATE))*power(10,3) + 
     (START_VALUE+180)
  ) VIRTUAL


The START_DATE_VALUE column is a virtual column that is used for partitioning. However, when I have a query like this:

select * 
from mytable
where
      start_date &gt; to_date('02-01-2012', 'MM-DD-YYYY')
 and start_value &gt; 120.23452


It scans all partitions for the result. How can I make Oracle use the virtual column and then pick just the right partition to work on it?

Sorry my table definition is very large, I can't copy it in here.
I think you have a problem with your virtual column definition. For your special values 2012-02-01 (in YYYY-MM-DD format) and 120.23452 the value of the virtual column is

2012*10000+2*100+1*1000+180+120.23452 = 20120000+200+1000+300.23452 =20121500.23452


and not 

20120201300.23452


as you expected.

Also check if your virtual column is the column your table is partitioned by.

From the VLDB and Partitioning Guide:


  Virtual column-based partitioned tables benefit from partition pruning
  for statements that use the virtual column-defining expression in the
  SQL statement.


So I think select-statement 

select * from mytable where start_date &gt; todate('02-01-2012', 'MM-DD-YYYY') and    start_value &gt; 120.23452


should be something like

select * from mytable where (
  extract(YEAR FROM START_DATE) * 10000 + 
  extract(MONTH FROM START_DATE)*100 + 
  extract(DAY FROM START_DATE))*power(10,3) + 
  (START_VALUE+180)
)  &gt; 20121500.23452


for partition pruning to take place. 

The 

todate


function you use in your select statement does not exist in oracle sql. The name of the function is 

to_date


.