tool_rentals
(tool_num number,
tool_desc varchar2(50),
rental_rate number(6,2))
partition by range (tool_num)
subpartition by hash (tool_desc)
subpartition template (subpartition s1 tablespace prd01,
subpartition s2 tablespace prd02,
subpartition s3 tablespace prd03,
subpartition s4 tablespace prd04,
subpartition s5 tablespace prd01,
subpartition s6 tablespace prd02,
subpartition s7 tablespace prd03,
subpartition s8 tablespace prd04)
(partition tool_rentals_p1 values less than (101),
partition tool_rentals_p2 values less than (201),
partition tool_rentals_p3 values less than (301),
partition tool_rentals_p4 values less than (maxvalue));
The range partitions are logical only; there are a total of 32 physical partitions, one for each
combination of logical partition and subpartition in the template list. Note the subpartition
template clause; the template is used for creating the subpartitions in every partition that doesn??™t
have an explicit subpartition specification. It can be a real timesaver and reduce typing errors if
the subpartitions are explicitly specified for each partition. Alternatively, you could specify the
following clause, if you do not need the subpartitions explicitly named:
subpartitions 8 store in (prd01, prd02, prd03, prd04)
The physical partition information is available in DBA_TAB_SUBPARTITIONS, as for any
partitioned table. Here is a query to find out the partition components of the TOOL_RENTALS
table:
?–
?–
?–
?–
?–
?–
Chapter 16: Managing Large Databases 563
SQL> select table_name, partition_name, subpartition_name,
2 tablespace_name
3 from dba_tab_subpartitions
4 where table_name = 'TOOL_RENTALS';
TABLE_NAME PARTITION_NAME SUBPARTITION_NAME TABLESPACE
--------------- -------------------- ---------------------- ----------
TOOL_RENTALS TOOL_RENTALS_P1 TOOL_RENTALS_P1_S1 PRD01
TOOL_RENTALS TOOL_RENTALS_P1 TOOL_RENTALS_P1_S2 PRD02
TOOL_RENTALS TOOL_RENTALS_P1 TOOL_RENTALS_P1_S3 PRD03
TOOL_RENTALS TOOL_RENTALS_P1 TOOL_RENTALS_P1_S4 PRD04
TOOL_RENTALS TOOL_RENTALS_P1 TOOL_RENTALS_P1_S5 PRD01
TOOL_RENTALS TOOL_RENTALS_P1 TOOL_RENTALS_P1_S6 PRD02
TOOL_RENTALS TOOL_RENTALS_P1 TOOL_RENTALS_P1_S7 PRD03
TOOL_RENTALS TOOL_RENTALS_P1 TOOL_RENTALS_P1_S8 PRD04
TOOL_RENTALS TOOL_RENTALS_P2 TOOL_RENTALS_P2_S1 PRD01
TOOL_RENTALS TOOL_RENTALS_P2 TOOL_RENTALS_P2_S2 PRD02
.
Pages:
818
819
820
821
822
823
824
825
826
827
828
829
830
831
832
833
834
835
836
837
838
839
840
841
842