How would an application developer execute a query based on specific partition

What is interval partitioning:


 Oracle Database 11g does, with a feature called Interval Partitioning. Here, you don't define partitions and their boundaries but merely an interval that defines each partition's boundaries. Here is the same example in interval partitioning:

create table sales6
(
   sales_id    number,
   sales_dt    date
)
partition by range (sales_dt)
                              
interval (numtoyminterval(1,'MONTH'))
(
   partition p0701 values less than (to_date('2007-02-01','yyyy-mm-dd'))
);

How would an application developer address a specific partition? One way is to know the name, which may not be possible, and even if you know, it is highly error prone. To facilitate the access to specific partition, Oracle Database 11g offers a new syntax for partitioning SQLs:

SQL> select * from sales6 partition for (to_date('15-may-2007','dd-mon-yyyy'));

  SALES_ID SALES_DT
  ----------    ---------
      1    01-MAY-07


Note the new clause for (value), which allows you to directly reference partitions without explicitly calling them by their exact name. If you want to truncate or drop a partition, you can call this extended portioning syntax.


After the table is created in this manner, the PARTITIONING_TYPE column in view DBA_PART_TABLES shows INTERVAL