Database Reference
In-Depth Information
Figure 13-9.
The test table is composed of 48 partitions per year
Of course, the mapping between name and position is available in the data dictionary also in this case. The
following query shows how to get it from the
user_tab_partitions
and
user_tab_subpartitions
views:
SQL> SELECT subpartition_name, partition_position, subpartition_position
2 FROM user_tab_partitions p, user_tab_subpartitions s
3 WHERE p.table_name = 'T'
4 AND s.table_name = p.table_name
5 AND s.partition_name = p.partition_name
6 ORDER BY p.partition_position, s.subpartition_position;
SUBPARTITION_NAME PARTITION_POSITION SUBPARTITION_POSITION
----------------- ------------------ ---------------------
T_JAN_2014_SP_1 1 1
T_JAN_2014_SP_2 1 2
T_JAN_2014_SP_3 1 3
T_JAN_2014_SP_4 1 4
T_FEB_2014_SP_1 2 1
...
T_NOV_2014_SP_4 11 4
T_DEC_2014_SP_1 12 1
T_DEC_2014_SP_2 12 2
T_DEC_2014_SP_3 12 3
T_DEC_2014_SP_4 12 4
The following query is an example of a restriction at both the partition and subpartition levels. The operations
are those described in the previous sections. Operation 1 applies at the partition level, and operation 2 applies at
the subpartition level. At the partition level, partitions 1 to 7 are accessed. For each of them, only subpartition 3 is
