This content has been marked as final. Show 2 replies
You can't bind in the partition name.1 person found this helpful
If you really need to do this then you'd have to use dynamic sql.
declare a varchar2(20); b date; begin a := 'TEST2012'; execute immediate 'select col_date from test partition('||a||')' into b; end ; /
Yes, you can not do that.
You should just use the following:
However this will cycle through all partitions because you have not given the optimiser any help whatsoever.
select col_date into b from test
If you said
then the optimiser will use the partition.
--explain plan for select col_date from test where col_date >=to_date('01-01-2012','DD-MM-YYYY') and col_date < to_date('01-01-2013','DD-MM-YYYY')
To see this just remove '--' from before explain plan for '
You should see the partition in your output.
select * from table(dbms_xplan.display());
Your previous query will show it is iterating through all the partitions.
Edited by: Keith Jamieson on Nov 20, 2012 3:52 PM