SQL> CREATE TABLE LIST_HASH_TAB (
2 id NUMBER,
3 code VARCHAR2(10),
4 description VARCHAR2(50),
5 created_date DATE
6 )
7 PARTITION BY LIST (code)
8 SUBPARTITION BY HASH (id)
9 (
10 PARTITION part_aa values ('AA')
11 (
12 SUBPARTITION part_aa_01,
13 SUBPARTITION part_aa_02
14 ),
15 partition part_bb values ('BB')
16 (
17 SUBPARTITION part_bb_01,
18 SUBPARTITION part_bb_02
19 )
20 );
Table created.
SQL> DECLARE
2 l_code VARCHAR2(10);
3 BEGIN
4 FOR i IN 1 .. 40 LOOP
5 IF MOD(i, 2) = 0 THEN
6 l_code := 'BB';
7 ELSE
8 l_code := 'AA';
9 END IF;
10
11 INSERT INTO LIST_HASH_TAB (id, code, description, created_date)
12 VALUES (i, l_code, 'Description for ' || i || ' ' || l_code, SYSDATE);
13 END LOOP;
14 COMMIT;
15 END;
16 /
PL/SQL procedure successfully completed.
SQL> COLUMN table_name FORMAT A20
SQL> COLUMN partition_name FORMAT A20
SQL> COLUMN subpartition_name FORMAT A20
SQL> SELECT table_name, partition_name, subpartition_name, num_rows
2 FROM user_tab_subpartitions
3 ORDER by table_name, partition_name, subpartition_name;
TABLE_NAME PARTITION_NAME SUBPARTITION_NAME NUM_ROWS
-------------------- -------------------- -------------------- ----------
LIST_HASH_TAB PART_AA PART_AA_01
LIST_HASH_TAB PART_AA PART_AA_02
LIST_HASH_TAB PART_BB PART_BB_01
LIST_HASH_TAB PART_BB PART_BB_02
SQL> exec DBMS_STATS.gather_table_stats('SA','LIST_HASH_TAB', granularity=>'ALL');
PL/SQL procedure successfully completed.
SQL> l
1 SELECT table_name, partition_name, subpartition_name, num_rows
2 FROM user_tab_subpartitions
3* ORDER by table_name, partition_name, subpartition_name
SQL> /
TABLE_NAME PARTITION_NAME SUBPARTITION_NAME NUM_ROWS
-------------------- -------------------- -------------------- ----------
LIST_HASH_TAB PART_AA PART_AA_01 7
LIST_HASH_TAB PART_AA PART_AA_02 13
LIST_HASH_TAB PART_BB PART_BB_01 10
LIST_HASH_TAB PART_BB PART_BB_02 10
SQL>
SQL>
SQL> spool off
SQL> COLUMN table_name FORMAT A20
SQL> COLUMN partition_name FORMAT A20
SQL> COLUMN subpartition_name FORMAT A20
SQL>
SQL> SELECT table_name, partition_name, subpartition_name, num_rows
2 FROM user_tab_subpartitions
3 ORDER by table_name, partition_name, subpartition_name;
TABLE_NAME PARTITION_NAME SUBPARTITION_NAME NUM_ROWS
-------------------- -------------------- -------------------- ----------
LIST_HASH_TAB PART_AA PART_AA_01 7
LIST_HASH_TAB PART_AA PART_AA_02 13
LIST_HASH_TAB PART_BB PART_BB_01 10
LIST_HASH_TAB PART_BB PART_BB_02 10
|
