注意两点:子表t_detail的high_value列为空,说明该数据表并没有一个明确的分区键,主表分区键owner在子表中也不存在。另外,子表分区结构、数量和名称与主表完全相同。
从段结构segment上,我们可以看到11g的defered segment creation并没有应用。
SQL> select segment_name, partition_name, segment_type from dba_segments where owner='SYS' and segment_name in ('T_MASTER','T_DETAIL');
SEGMENT_NAME PARTITION_NAME SEGMENT_TYPE
--------------- -------------------- ----------------------
T_MASTER P3 TABLE PARTITION
T_MASTER P1 TABLE PARTITION
T_MASTER P0 TABLE PARTITION
T_DETAIL P3 TABLE PARTITION
T_DETAIL P1 TABLE PARTITION
T_DETAIL P0 TABLE PARTITION
6 rows selected
3、数据插入实验
下面进行数据插入和分区分布实验。首先进行主表数据插入:
SQL> insert into t_master select object_id, owner, object_name, object_type from dba_objects;
120361 rows inserted
SQL> commit;
Commit complete
SQL> exec dbms_stats.gather_table_stats(user,'T_MASTER',cascade => true);
PL/SQL procedure successfully completed
子表数据插入:
SQL> insert into t_detail select seq_t_detail.nextval, object_id, object_name, object_type from dba_objects where object_name not in
('SEQ_T_DETAIL');
120361 rows inserted
SQL> commit;
Commit complete
SQL> exec dbms_stats.gather_table_stats(user,'T_DETAIL',cascade => true);
PL/SQL procedure successfully completed
按照当前的数据关系,应该是一条主表记录,对应一条子表记录的关系。我们检查数据字典情况。
SQL> select table_name, partition_name, high_value,num_rows from dba_tab_partitions where table_owner='SYS' and table_name in ('T_MASTER');
TABLE_NAME PARTITION_NAME HIGH_VALUE NUM_ROWS
------------------------------ -------------------- --------------- ----------
T_MASTER P0 'PUBLIC' 33996
T_MASTER P1 'SYS' 37817
T_MASTER P3 default 48548
SQL> select table_name, partition_name, high_value,num_rows from dba_tab_partitions where table_owner='SYS' and table_name in ('T_DETAIL');
TABLE_NAME PARTITION_NAME HIGH_VALUE NUM_ROWS
------------------------------ -------------------- --------------- ----------
T_DETAIL P0 33996
T_DETAIL P1 37817
T_DETAIL P3 48548