To query from a specific partition, we used provide partition name.
Like below:
select * from Table_1 partition (Q1_2012);
But it is hard to remember partition names and also we cannot use it inside a procedure/functions, since it is difficult to construct partition names at run time.
--> To facilitate the access to specific partition, Oracle Database 11g offers a new syntax for partitioning SQLs:
select * from Table_1 partition for (to_date('02-Feb-2012','dd-mon-yyyy'));
This method allows us to query from a specific partition without explicitly stating the partition name.
--> This can be useful while loading the data too.One can dynamically pass the date and oracle finds the partition it belongs to.
insert into SALES /*+ APPEND*/
partition for (to_date(''||vstart_id||'', ''''YYYYMM''''))
select PROD_ID, CUST_ID, TIME_ID, CHANNEL_ID, PROMO_ID, QUANTITY_SOLD, AMOUNT_SOLD
from SRC_SALES
where time_id between to_date(:vstart_id, ''''YYYYMM'''') and last_day(to_date(:vstart_id, ''''YYYYMM'''')) '';
--> While Dropping Partitions
alter table SALES drop partition for (to_date('02-feb-2012','DD-MON-YYYY'));
