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.
Best Answer
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
and not
as you expected.
Also check if your virtual column is the column your table is partitioned by.
From the VLDB and Partitioning Guide:
So I think select-statement
should be something like
for partition pruning to take place.
The
function you use in your select statement does not exist in oracle sql. The name of the function is
.