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 > to_date('02-01-2012', 'MM-DD-YYYY')
 and start_value > 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 > todate('02-01-2012', 'MM-DD-YYYY') and    start_value > 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)
)  > 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

.

StackExchange Database Administrators Q#16209, answer score: 2