Create composite list-list partitioned table
CREATE TABLE accounts_list_list
( id NUMBER
, account_number NUMBER
, customer_id NUMBER
, balance NUMBER
, branch_id NUMBER
, region VARCHAR(2)
, status VARCHAR2(1)
)
PARTITION BY LIST (region)
SUBPARTITION BY LIST (status)
( PARTITION p_northwest VALUES ('OR', 'WA')
( SUBPARTITION p_nw_bad VALUES ('B')
, SUBPARTITION p_nw_average VALUES ('A')
, SUBPARTITION p_nw_good VALUES ('G')
)
, PARTITION p_southwest VALUES ('AZ', 'UT', 'NM')
( SUBPARTITION p_sw_bad VALUES ('B')
, SUBPARTITION p_sw_average VALUES ('A')
, SUBPARTITION p_sw_good VALUES ('G')
)
, PARTITION p_northeast VALUES ('NY', 'VM', 'NJ')
( SUBPARTITION p_ne_bad VALUES ('B')
, SUBPARTITION p_ne_average VALUES ('A')
, SUBPARTITION p_ne_good VALUES ('G')
)
, PARTITION p_southeast VALUES ('FL', 'GA')
( SUBPARTITION p_se_bad VALUES ('B')
, SUBPARTITION p_se_average VALUES ('A')
, SUBPARTITION p_se_good VALUES ('G')
)
, PARTITION p_northcentral VALUES ('SD', 'WI')
( SUBPARTITION p_nc_bad VALUES ('B')
, SUBPARTITION p_nc_average VALUES ('A')
, SUBPARTITION p_nc_good VALUES ('G')
)
, PARTITION p_southcentral VALUES ('OK', 'TX')
( SUBPARTITION p_sc_bad VALUES ('B')
, SUBPARTITION p_sc_average VALUES ('A')
, SUBPARTITION p_sc_good VALUES ('G')
)
)
Table created.
Viewing the structure of the composite list-list partitioned table
SELECT SUBSTR(TABLE_NAME,1,32), SUBSTR(PARTITION_NAME,1,32), SUBSTR(SUBPARTITION_NAME,1,32) FROM USER_TAB_SUBPARTITIONS WHERE TABLE_NAME ='ACCOUNTS_LIST_LIST'
SUBSTR(TABLE_NAME,1,32) | SUBSTR(PARTITION_NAME,1,32) | SUBSTR(SUBPARTITION_NAME,1,32) | ACCOUNTS_LIST_LIST | P_NORTHCENTRAL | P_NC_AVERAGE | ACCOUNTS_LIST_LIST | P_NORTHCENTRAL | P_NC_BAD | ACCOUNTS_LIST_LIST | P_NORTHCENTRAL | P_NC_GOOD | ACCOUNTS_LIST_LIST | P_NORTHEAST | P_NE_AVERAGE | ACCOUNTS_LIST_LIST | P_NORTHEAST | P_NE_BAD | ACCOUNTS_LIST_LIST | P_NORTHEAST | P_NE_GOOD | ACCOUNTS_LIST_LIST | P_NORTHWEST | P_NW_AVERAGE | ACCOUNTS_LIST_LIST | P_NORTHWEST | P_NW_BAD | ACCOUNTS_LIST_LIST | P_NORTHWEST | P_NW_GOOD | ACCOUNTS_LIST_LIST | P_SOUTHCENTRAL | P_SC_AVERAGE | ACCOUNTS_LIST_LIST | P_SOUTHCENTRAL | P_SC_BAD | ACCOUNTS_LIST_LIST | P_SOUTHCENTRAL | P_SC_GOOD | ACCOUNTS_LIST_LIST | P_SOUTHEAST | P_SE_AVERAGE | ACCOUNTS_LIST_LIST | P_SOUTHEAST | P_SE_BAD | ACCOUNTS_LIST_LIST | P_SOUTHEAST | P_SE_GOOD | ACCOUNTS_LIST_LIST | P_SOUTHWEST | P_SW_AVERAGE | ACCOUNTS_LIST_LIST | P_SOUTHWEST | P_SW_BAD | ACCOUNTS_LIST_LIST | P_SOUTHWEST | P_SW_GOOD |
---|