SQL> SQL> COLUMN column_name format a26 SQL> COLUMN low_val format a20 SQL> COLUMN high_val format a20 SQL> COLUMN data_type format a20 SQL> SQL> alter session set nls_date_format='DD-MON-YYYY HH24:MI:SS'; Session altered. SQL> SQL> PROMPT **************************************************** **************************************************** SQL> PROMPT First build the table and index and INSERT some data First build the table and index and INSERT some data SQL> PROMPT **************************************************** **************************************************** SQL> SQL> set pages 9999 SQL> set lines 132 SQL> set echo on SQL> SQL> COLUMN column_name format a26 SQL> COLUMN low_val format a20 SQL> COLUMN high_val format a20 SQL> COLUMN data_type format a20 SQL> SQL> alter session set nls_date_format='DD-MON-YYYY HH24:MI:SS'; Session altered. SQL> SQL> DROP TABLE TEST_TAB1; Table dropped. SQL> SQL> CREATE TABLE TEST_TAB1 2 ( 3 REPORTING_DATE NUMBER NOT NULL, 4 SOURCE_SYSTEM VARCHAR2(30 CHAR) NOT NULL, 5 SEQ_ID NUMBER NOT NULL, 6 STATUS VARCHAR2(1 CHAR) NOT NULL 7 ) 8 PARTITION BY RANGE (REPORTING_DATE) 9 SUBPARTITION BY LIST (SOURCE_SYSTEM) 10 SUBPARTITION TEMPLATE 11 (SUBPARTITION GROT VALUES ('GROT') TABLESPACE TEST_DATA, 12 SUBPARTITION JUNE VALUES ('JUNE') TABLESPACE TEST_DATA, 13 SUBPARTITION HALO VALUES ('HALO') TABLESPACE TEST_DATA, 14 SUBPARTITION OTHERS VALUES (DEFAULT) TABLESPACE TEST_DATA) 15 ( 16 PARTITION P_20100207 VALUES LESS THAN (20100208) NOLOGGING NOCOMPRESS 17 ) 18 NOCOMPRESS 19 NOCACHE 20 NOPARALLEL 21 MONITORING; Table created. SQL> SQL> SQL> SQL> CREATE UNIQUE INDEX TEST_TAB1_IX1 ON TEST_TAB1 2 (REPORTING_DATE, SOURCE_SYSTEM, SEQ_ID) 3 LOCAL NOPARALLEL COMPRESS 1; Index created. SQL> SQL> ALTER TABLE TEST_TAB1 2 ADD PARTITION P_20100209 VALUES LESS THAN (20100210); Table altered. SQL> SQL> DROP TABLE LOAD_TAB1; Table dropped. SQL> SQL> CREATE TABLE LOAD_TAB1 2 AS SELECT * FROM TEST_TAB1 WHERE 1=0; Table created. SQL> SQL> CREATE UNIQUE INDEX LOAD_TAB1_IX1 ON LOAD_TAB1 2 (REPORTING_DATE, SOURCE_SYSTEM, SEQ_ID) 3 NOPARALLEL COMPRESS 1; Index created. SQL> SQL> INSERT INTO LOAD_TAB1 VALUES (20100209, 'GROT', 400, 'P'); 1 row created. SQL> INSERT INTO LOAD_TAB1 VALUES (20100209, 'GROT', 600, 'P'); 1 row created. SQL> INSERT INTO LOAD_TAB1 VALUES (20100209, 'GROT', 900, 'Z'); 1 row created. SQL> SQL> exec dbms_stats.gather_table_stats('TESTUSER', 'LOAD_TAB1'); PL/SQL procedure successfully completed. SQL> SQL> ALTER TABLE test_tab1 EXCHANGE SUBPARTITION P_20100209_GROT WITH TABLE load_tab1; Table altered. SQL> ALTER TABLE test_tab1 MODIFY SUBPARTITION P_20100209_GROT REBUILD UNUSABLE LOCAL INDEXES; Table altered. SQL> SQL> TRUNCATE TABLE LOAD_TAB1; Table truncated. SQL> SQL> INSERT INTO LOAD_TAB1 VALUES (20100209, 'JUNE', 400, 'U'); 1 row created. SQL> INSERT INTO LOAD_TAB1 VALUES (20100209, 'JUNE', 600, 'U'); 1 row created. SQL> INSERT INTO LOAD_TAB1 VALUES (20100209, 'JUNE', 900, 'U'); 1 row created. SQL> SQL> exec dbms_stats.gather_table_stats('TESTUSER', 'LOAD_TAB1'); PL/SQL procedure successfully completed. SQL> SQL> ALTER TABLE test_tab1 EXCHANGE SUBPARTITION P_20100209_JUNE WITH TABLE load_tab1; Table altered. SQL> ALTER TABLE test_tab1 MODIFY SUBPARTITION P_20100209_JUNE REBUILD UNUSABLE LOCAL INDEXES; Table altered. SQL> SQL> TRUNCATE TABLE LOAD_TAB1; Table truncated. SQL> SQL> INSERT INTO LOAD_TAB1 VALUES (20100209, 'HALO', 400, 'N'); 1 row created. SQL> INSERT INTO LOAD_TAB1 VALUES (20100209, 'HALO', 600, 'N'); 1 row created. SQL> INSERT INTO LOAD_TAB1 VALUES (20100209, 'HALO', 900, 'N'); 1 row created. SQL> SQL> exec dbms_stats.gather_table_stats('TESTUSER', 'LOAD_TAB1'); PL/SQL procedure successfully completed. SQL> SQL> ALTER TABLE test_tab1 EXCHANGE SUBPARTITION P_20100209_HALO WITH TABLE load_tab1; Table altered. SQL> ALTER TABLE test_tab1 MODIFY SUBPARTITION P_20100209_HALO REBUILD UNUSABLE LOCAL INDEXES; Table altered. SQL> SQL> TRUNCATE TABLE LOAD_TAB1; Table truncated. SQL> SQL> INSERT INTO LOAD_TAB1 VALUES (20100209, 'ZZZZ', 400, 'P'); 1 row created. SQL> INSERT INTO LOAD_TAB1 VALUES (20100209, 'ZZZZ', 600, 'P'); 1 row created. SQL> INSERT INTO LOAD_TAB1 VALUES (20100209, 'ZZZZ', 900, 'Z'); 1 row created. SQL> SQL> exec dbms_stats.gather_table_stats('TESTUSER', 'LOAD_TAB1'); PL/SQL procedure successfully completed. SQL> SQL> ALTER TABLE test_tab1 EXCHANGE SUBPARTITION P_20100209_OTHERS WITH TABLE load_tab1; Table altered. SQL> ALTER TABLE test_tab1 MODIFY SUBPARTITION P_20100209_OTHERS REBUILD UNUSABLE LOCAL INDEXES; Table altered. SQL> SQL> select table_name, global_stats, last_analyzed, num_rows 2 from dba_tables 3 where table_name='TEST_TAB1' 4 and owner='TESTUSER' 5 order by 1, 2, 4 desc nulls last; TABLE_NAME GLO LAST_ANALYZED NUM_ROWS ------------------------------ --- -------------------- ---------- TEST_TAB1 NO SQL> SQL> select table_name, partition_name, global_stats, last_analyzed, num_rows 2 from dba_tab_partitions 3 where table_name='TEST_TAB1' 4 and table_owner='TESTUSER' 5 order by 1, 2, 4 desc nulls last; TABLE_NAME PARTITION_NAME GLO LAST_ANALYZED NUM_ROWS ------------------------------ ------------------------------ --- -------------------- ---------- TEST_TAB1 P_20100207 NO TEST_TAB1 P_20100209 NO SQL> SQL> select table_name, subpartition_name, global_stats, last_analyzed, num_rows 2 from dba_tab_subpartitions 3 where table_name='TEST_TAB1' 4 and table_owner='TESTUSER' 5 order by 1, 2, 4 desc nulls last; TABLE_NAME SUBPARTITION_NAME GLO LAST_ANALYZED NUM_ROWS ------------------------------ ------------------------------ --- -------------------- ---------- TEST_TAB1 P_20100207_GROT NO TEST_TAB1 P_20100207_HALO NO TEST_TAB1 P_20100207_JUNE NO TEST_TAB1 P_20100207_OTHERS NO TEST_TAB1 P_20100209_GROT YES 07-MAY-2010 05:11:19 3 TEST_TAB1 P_20100209_HALO YES 07-MAY-2010 05:11:20 3 TEST_TAB1 P_20100209_JUNE YES 07-MAY-2010 05:11:19 3 TEST_TAB1 P_20100209_OTHERS YES 07-MAY-2010 05:11:20 3 8 rows selected. SQL> SQL> PROMPT ************************ ************************ SQL> PROMPT Table-Level Column Stats Table-Level Column Stats SQL> PROMPT ************************ ************************ SQL> SQL> select 2 a.column_name, 3 a.num_distinct, 4 display_raw(a.low_value,b.data_type) as low_val, 5 display_raw(a.high_value,b.data_type) as high_val, 6 b.data_type 7 from 8 dba_tab_col_statistics a, dba_tab_cols b 9 where 10 a.owner='TESTUSER' and 11 a.table_name='TEST_TAB1' and 12 a.table_name=b.table_name and 13 a.column_name=b.column_name 14 order by 1 15 / no rows selected SQL> SQL> PROMPT **************************** **************************** SQL> PROMPT Partition-Level Column Stats Partition-Level Column Stats SQL> PROMPT **************************** **************************** SQL> SQL> select 2 a.column_name, a.partition_name, 3 a.num_distinct, 4 display_raw(a.low_value,b.data_type) as low_val, 5 display_raw(a.high_value,b.data_type) as high_val, 6 b.data_type 7 from 8 dba_part_col_statistics a, dba_tab_cols b 9 where 10 a.owner='TESTUSER' and 11 a.table_name='TEST_TAB1' and 12 a.table_name=b.table_name and 13 a.column_name=b.column_name 14 order by 1, 2 15 / COLUMN_NAME PARTITION_NAME NUM_DISTINCT LOW_VAL HIGH_VAL -------------------------- ------------------------------ ------------ -------------------- -------------------- DATA_TYPE -------------------- REPORTING_DATE P_20100207 NUMBER REPORTING_DATE P_20100207 NUMBER REPORTING_DATE P_20100209 NUMBER REPORTING_DATE P_20100209 NUMBER SEQ_ID P_20100207 NUMBER SEQ_ID P_20100207 NUMBER SEQ_ID P_20100209 NUMBER SEQ_ID P_20100209 NUMBER SOURCE_SYSTEM P_20100207 VARCHAR2 SOURCE_SYSTEM P_20100207 VARCHAR2 SOURCE_SYSTEM P_20100209 VARCHAR2 SOURCE_SYSTEM P_20100209 VARCHAR2 STATUS P_20100207 VARCHAR2 STATUS P_20100207 VARCHAR2 STATUS P_20100209 VARCHAR2 STATUS P_20100209 VARCHAR2 16 rows selected. SQL> SQL> PROMPT ******************************* ******************************* SQL> PROMPT Subpartition-Level Column Stats Subpartition-Level Column Stats SQL> PROMPT ******************************* ******************************* SQL> SQL> select 2 a.column_name, a.subpartition_name, 3 a.num_distinct, 4 display_raw(a.low_value,b.data_type) as low_val, 5 display_raw(a.high_value,b.data_type) as high_val, 6 b.data_type 7 from 8 dba_subpart_col_statistics a, dba_tab_cols b 9 where 10 a.owner='TESTUSER' and 11 a.table_name='TEST_TAB1' and 12 a.table_name=b.table_name and 13 a.column_name=b.column_name 14 order by 1, 2 15 / COLUMN_NAME SUBPARTITION_NAME NUM_DISTINCT LOW_VAL HIGH_VAL -------------------------- ------------------------------ ------------ -------------------- -------------------- DATA_TYPE -------------------- REPORTING_DATE P_20100207_GROT NUMBER REPORTING_DATE P_20100207_GROT NUMBER REPORTING_DATE P_20100207_HALO NUMBER REPORTING_DATE P_20100207_HALO NUMBER REPORTING_DATE P_20100207_JUNE NUMBER REPORTING_DATE P_20100207_JUNE NUMBER REPORTING_DATE P_20100207_OTHERS NUMBER REPORTING_DATE P_20100207_OTHERS NUMBER REPORTING_DATE P_20100209_GROT 1 20100209 20100209 NUMBER REPORTING_DATE P_20100209_GROT 1 20100209 20100209 NUMBER REPORTING_DATE P_20100209_HALO 1 20100209 20100209 NUMBER REPORTING_DATE P_20100209_HALO 1 20100209 20100209 NUMBER REPORTING_DATE P_20100209_JUNE 1 20100209 20100209 NUMBER REPORTING_DATE P_20100209_JUNE 1 20100209 20100209 NUMBER REPORTING_DATE P_20100209_OTHERS 1 20100209 20100209 NUMBER REPORTING_DATE P_20100209_OTHERS 1 20100209 20100209 NUMBER SEQ_ID P_20100207_GROT NUMBER SEQ_ID P_20100207_GROT NUMBER SEQ_ID P_20100207_HALO NUMBER SEQ_ID P_20100207_HALO NUMBER SEQ_ID P_20100207_JUNE NUMBER SEQ_ID P_20100207_JUNE NUMBER SEQ_ID P_20100207_OTHERS NUMBER SEQ_ID P_20100207_OTHERS NUMBER SEQ_ID P_20100209_GROT 3 400 900 NUMBER SEQ_ID P_20100209_GROT 3 400 900 NUMBER SEQ_ID P_20100209_HALO 3 400 900 NUMBER SEQ_ID P_20100209_HALO 3 400 900 NUMBER SEQ_ID P_20100209_JUNE 3 400 900 NUMBER SEQ_ID P_20100209_JUNE 3 400 900 NUMBER SEQ_ID P_20100209_OTHERS 3 400 900 NUMBER SEQ_ID P_20100209_OTHERS 3 400 900 NUMBER SOURCE_SYSTEM P_20100207_GROT VARCHAR2 SOURCE_SYSTEM P_20100207_GROT VARCHAR2 SOURCE_SYSTEM P_20100207_HALO VARCHAR2 SOURCE_SYSTEM P_20100207_HALO VARCHAR2 SOURCE_SYSTEM P_20100207_JUNE VARCHAR2 SOURCE_SYSTEM P_20100207_JUNE VARCHAR2 SOURCE_SYSTEM P_20100207_OTHERS VARCHAR2 SOURCE_SYSTEM P_20100207_OTHERS VARCHAR2 SOURCE_SYSTEM P_20100209_GROT 1 GROT GROT VARCHAR2 SOURCE_SYSTEM P_20100209_GROT 1 GROT GROT VARCHAR2 SOURCE_SYSTEM P_20100209_HALO 1 HALO HALO VARCHAR2 SOURCE_SYSTEM P_20100209_HALO 1 HALO HALO VARCHAR2 SOURCE_SYSTEM P_20100209_JUNE 1 JUNE JUNE VARCHAR2 SOURCE_SYSTEM P_20100209_JUNE 1 JUNE JUNE VARCHAR2 SOURCE_SYSTEM P_20100209_OTHERS 1 ZZZZ ZZZZ VARCHAR2 SOURCE_SYSTEM P_20100209_OTHERS 1 ZZZZ ZZZZ VARCHAR2 STATUS P_20100207_GROT VARCHAR2 STATUS P_20100207_GROT VARCHAR2 STATUS P_20100207_HALO VARCHAR2 STATUS P_20100207_HALO VARCHAR2 STATUS P_20100207_JUNE VARCHAR2 STATUS P_20100207_JUNE VARCHAR2 STATUS P_20100207_OTHERS VARCHAR2 STATUS P_20100207_OTHERS VARCHAR2 STATUS P_20100209_GROT 2 P Z VARCHAR2 STATUS P_20100209_GROT 2 P Z VARCHAR2 STATUS P_20100209_HALO 1 N N VARCHAR2 STATUS P_20100209_HALO 1 N N VARCHAR2 STATUS P_20100209_JUNE 1 U U VARCHAR2 STATUS P_20100209_JUNE 1 U U VARCHAR2 STATUS P_20100209_OTHERS 2 P Z VARCHAR2 STATUS P_20100209_OTHERS 2 P Z VARCHAR2 64 rows selected. SQL> SQL> alter session set "_minimal_stats_aggregation"=FALSE; Session altered. SQL> SQL> TRUNCATE TABLE LOAD_TAB1; Table truncated. SQL> SQL> INSERT INTO LOAD_TAB1 VALUES (20100209, 'GROT', 400, 'P'); 1 row created. SQL> INSERT INTO LOAD_TAB1 VALUES (20100209, 'GROT', 600, 'P'); 1 row created. SQL> INSERT INTO LOAD_TAB1 VALUES (20100209, 'GROT', 900, 'Z'); 1 row created. SQL> SQL> exec dbms_stats.gather_table_stats('TESTUSER', 'LOAD_TAB1'); PL/SQL procedure successfully completed. SQL> SQL> ALTER TABLE test_tab1 EXCHANGE SUBPARTITION P_20100209_GROT WITH TABLE load_tab1; Table altered. SQL> ALTER TABLE test_tab1 MODIFY SUBPARTITION P_20100209_GROT REBUILD UNUSABLE LOCAL INDEXES; Table altered. SQL> SQL> TRUNCATE TABLE LOAD_TAB1; Table truncated. SQL> SQL> INSERT INTO LOAD_TAB1 VALUES (20100209, 'JUNE', 400, 'U'); 1 row created. SQL> INSERT INTO LOAD_TAB1 VALUES (20100209, 'JUNE', 600, 'U'); 1 row created. SQL> INSERT INTO LOAD_TAB1 VALUES (20100209, 'JUNE', 900, 'U'); 1 row created. SQL> SQL> exec dbms_stats.gather_table_stats('TESTUSER', 'LOAD_TAB1'); PL/SQL procedure successfully completed. SQL> SQL> ALTER TABLE test_tab1 EXCHANGE SUBPARTITION P_20100209_JUNE WITH TABLE load_tab1; Table altered. SQL> ALTER TABLE test_tab1 MODIFY SUBPARTITION P_20100209_JUNE REBUILD UNUSABLE LOCAL INDEXES; Table altered. SQL> SQL> TRUNCATE TABLE LOAD_TAB1; Table truncated. SQL> SQL> INSERT INTO LOAD_TAB1 VALUES (20100209, 'HALO', 400, 'N'); 1 row created. SQL> INSERT INTO LOAD_TAB1 VALUES (20100209, 'HALO', 600, 'N'); 1 row created. SQL> INSERT INTO LOAD_TAB1 VALUES (20100209, 'HALO', 900, 'N'); 1 row created. SQL> SQL> exec dbms_stats.gather_table_stats('TESTUSER', 'LOAD_TAB1'); PL/SQL procedure successfully completed. SQL> SQL> ALTER TABLE test_tab1 EXCHANGE SUBPARTITION P_20100209_HALO WITH TABLE load_tab1; Table altered. SQL> ALTER TABLE test_tab1 MODIFY SUBPARTITION P_20100209_HALO REBUILD UNUSABLE LOCAL INDEXES; Table altered. SQL> SQL> TRUNCATE TABLE LOAD_TAB1; Table truncated. SQL> SQL> INSERT INTO LOAD_TAB1 VALUES (20100209, 'ZZZZ', 400, 'P'); 1 row created. SQL> INSERT INTO LOAD_TAB1 VALUES (20100209, 'ZZZZ', 600, 'P'); 1 row created. SQL> INSERT INTO LOAD_TAB1 VALUES (20100209, 'ZZZZ', 900, 'Z'); 1 row created. SQL> SQL> exec dbms_stats.gather_table_stats('TESTUSER', 'LOAD_TAB1'); PL/SQL procedure successfully completed. SQL> SQL> ALTER TABLE test_tab1 EXCHANGE SUBPARTITION P_20100209_OTHERS WITH TABLE load_tab1; Table altered. SQL> ALTER TABLE test_tab1 MODIFY SUBPARTITION P_20100209_OTHERS REBUILD UNUSABLE LOCAL INDEXES; Table altered. SQL> SQL> select table_name, global_stats, last_analyzed, num_rows 2 from dba_tables 3 where table_name='TEST_TAB1' 4 and owner='TESTUSER' 5 order by 1, 2, 4 desc nulls last; TABLE_NAME GLO LAST_ANALYZED NUM_ROWS ------------------------------ --- -------------------- ---------- TEST_TAB1 NO SQL> SQL> select table_name, partition_name, global_stats, last_analyzed, num_rows 2 from dba_tab_partitions 3 where table_name='TEST_TAB1' 4 and table_owner='TESTUSER' 5 order by 1, 2, 4 desc nulls last; TABLE_NAME PARTITION_NAME GLO LAST_ANALYZED NUM_ROWS ------------------------------ ------------------------------ --- -------------------- ---------- TEST_TAB1 P_20100207 NO TEST_TAB1 P_20100209 NO 07-MAY-2010 05:11:21 12 SQL> SQL> select table_name, subpartition_name, global_stats, last_analyzed, num_rows 2 from dba_tab_subpartitions 3 where table_name='TEST_TAB1' 4 and table_owner='TESTUSER' 5 order by 1, 2, 4 desc nulls last; TABLE_NAME SUBPARTITION_NAME GLO LAST_ANALYZED NUM_ROWS ------------------------------ ------------------------------ --- -------------------- ---------- TEST_TAB1 P_20100207_GROT NO TEST_TAB1 P_20100207_HALO NO TEST_TAB1 P_20100207_JUNE NO TEST_TAB1 P_20100207_OTHERS NO TEST_TAB1 P_20100209_GROT YES 07-MAY-2010 05:11:20 3 TEST_TAB1 P_20100209_HALO YES 07-MAY-2010 05:11:21 3 TEST_TAB1 P_20100209_JUNE YES 07-MAY-2010 05:11:20 3 TEST_TAB1 P_20100209_OTHERS YES 07-MAY-2010 05:11:21 3 8 rows selected. SQL> SQL> PROMPT ************************ ************************ SQL> PROMPT Table-Level Column Stats Table-Level Column Stats SQL> PROMPT ************************ ************************ SQL> SQL> select 2 a.column_name, 3 a.num_distinct, 4 display_raw(a.low_value,b.data_type) as low_val, 5 display_raw(a.high_value,b.data_type) as high_val, 6 b.data_type 7 from 8 dba_tab_col_statistics a, dba_tab_cols b 9 where 10 a.owner='TESTUSER' and 11 a.table_name='TEST_TAB1' and 12 a.table_name=b.table_name and 13 a.column_name=b.column_name 14 order by 1 15 / no rows selected SQL> SQL> PROMPT **************************** **************************** SQL> PROMPT Partition-Level Column Stats Partition-Level Column Stats SQL> PROMPT **************************** **************************** SQL> SQL> select 2 a.column_name, a.partition_name, 3 a.num_distinct, 4 display_raw(a.low_value,b.data_type) as low_val, 5 display_raw(a.high_value,b.data_type) as high_val, 6 b.data_type 7 from 8 dba_part_col_statistics a, dba_tab_cols b 9 where 10 a.owner='TESTUSER' and 11 a.table_name='TEST_TAB1' and 12 a.table_name=b.table_name and 13 a.column_name=b.column_name 14 order by 1, 2 15 / COLUMN_NAME PARTITION_NAME NUM_DISTINCT LOW_VAL HIGH_VAL -------------------------- ------------------------------ ------------ -------------------- -------------------- DATA_TYPE -------------------- REPORTING_DATE P_20100207 NUMBER REPORTING_DATE P_20100207 NUMBER REPORTING_DATE P_20100209 1 20100209 20100209 NUMBER REPORTING_DATE P_20100209 1 20100209 20100209 NUMBER SEQ_ID P_20100207 NUMBER SEQ_ID P_20100207 NUMBER SEQ_ID P_20100209 3 400 900 NUMBER SEQ_ID P_20100209 3 400 900 NUMBER SOURCE_SYSTEM P_20100207 VARCHAR2 SOURCE_SYSTEM P_20100207 VARCHAR2 SOURCE_SYSTEM P_20100209 4 GROT ZZZZ VARCHAR2 SOURCE_SYSTEM P_20100209 4 GROT ZZZZ VARCHAR2 STATUS P_20100207 VARCHAR2 STATUS P_20100207 VARCHAR2 STATUS P_20100209 3 N Z VARCHAR2 STATUS P_20100209 3 N Z VARCHAR2 16 rows selected. SQL> SQL> PROMPT ******************************* ******************************* SQL> PROMPT Subpartition-Level Column Stats Subpartition-Level Column Stats SQL> PROMPT ******************************* ******************************* SQL> SQL> select 2 a.column_name, a.subpartition_name, 3 a.num_distinct, 4 display_raw(a.low_value,b.data_type) as low_val, 5 display_raw(a.high_value,b.data_type) as high_val, 6 b.data_type 7 from 8 dba_subpart_col_statistics a, dba_tab_cols b 9 where 10 a.owner='TESTUSER' and 11 a.table_name='TEST_TAB1' and 12 a.table_name=b.table_name and 13 a.column_name=b.column_name 14 order by 1, 2 15 / COLUMN_NAME SUBPARTITION_NAME NUM_DISTINCT LOW_VAL HIGH_VAL -------------------------- ------------------------------ ------------ -------------------- -------------------- DATA_TYPE -------------------- REPORTING_DATE P_20100207_GROT NUMBER REPORTING_DATE P_20100207_GROT NUMBER REPORTING_DATE P_20100207_HALO NUMBER REPORTING_DATE P_20100207_HALO NUMBER REPORTING_DATE P_20100207_JUNE NUMBER REPORTING_DATE P_20100207_JUNE NUMBER REPORTING_DATE P_20100207_OTHERS NUMBER REPORTING_DATE P_20100207_OTHERS NUMBER REPORTING_DATE P_20100209_GROT 1 20100209 20100209 NUMBER REPORTING_DATE P_20100209_GROT 1 20100209 20100209 NUMBER REPORTING_DATE P_20100209_HALO 1 20100209 20100209 NUMBER REPORTING_DATE P_20100209_HALO 1 20100209 20100209 NUMBER REPORTING_DATE P_20100209_JUNE 1 20100209 20100209 NUMBER REPORTING_DATE P_20100209_JUNE 1 20100209 20100209 NUMBER REPORTING_DATE P_20100209_OTHERS 1 20100209 20100209 NUMBER REPORTING_DATE P_20100209_OTHERS 1 20100209 20100209 NUMBER SEQ_ID P_20100207_GROT NUMBER SEQ_ID P_20100207_GROT NUMBER SEQ_ID P_20100207_HALO NUMBER SEQ_ID P_20100207_HALO NUMBER SEQ_ID P_20100207_JUNE NUMBER SEQ_ID P_20100207_JUNE NUMBER SEQ_ID P_20100207_OTHERS NUMBER SEQ_ID P_20100207_OTHERS NUMBER SEQ_ID P_20100209_GROT 3 400 900 NUMBER SEQ_ID P_20100209_GROT 3 400 900 NUMBER SEQ_ID P_20100209_HALO 3 400 900 NUMBER SEQ_ID P_20100209_HALO 3 400 900 NUMBER SEQ_ID P_20100209_JUNE 3 400 900 NUMBER SEQ_ID P_20100209_JUNE 3 400 900 NUMBER SEQ_ID P_20100209_OTHERS 3 400 900 NUMBER SEQ_ID P_20100209_OTHERS 3 400 900 NUMBER SOURCE_SYSTEM P_20100207_GROT VARCHAR2 SOURCE_SYSTEM P_20100207_GROT VARCHAR2 SOURCE_SYSTEM P_20100207_HALO VARCHAR2 SOURCE_SYSTEM P_20100207_HALO VARCHAR2 SOURCE_SYSTEM P_20100207_JUNE VARCHAR2 SOURCE_SYSTEM P_20100207_JUNE VARCHAR2 SOURCE_SYSTEM P_20100207_OTHERS VARCHAR2 SOURCE_SYSTEM P_20100207_OTHERS VARCHAR2 SOURCE_SYSTEM P_20100209_GROT 1 GROT GROT VARCHAR2 SOURCE_SYSTEM P_20100209_GROT 1 GROT GROT VARCHAR2 SOURCE_SYSTEM P_20100209_HALO 1 HALO HALO VARCHAR2 SOURCE_SYSTEM P_20100209_HALO 1 HALO HALO VARCHAR2 SOURCE_SYSTEM P_20100209_JUNE 1 JUNE JUNE VARCHAR2 SOURCE_SYSTEM P_20100209_JUNE 1 JUNE JUNE VARCHAR2 SOURCE_SYSTEM P_20100209_OTHERS 1 ZZZZ ZZZZ VARCHAR2 SOURCE_SYSTEM P_20100209_OTHERS 1 ZZZZ ZZZZ VARCHAR2 STATUS P_20100207_GROT VARCHAR2 STATUS P_20100207_GROT VARCHAR2 STATUS P_20100207_HALO VARCHAR2 STATUS P_20100207_HALO VARCHAR2 STATUS P_20100207_JUNE VARCHAR2 STATUS P_20100207_JUNE VARCHAR2 STATUS P_20100207_OTHERS VARCHAR2 STATUS P_20100207_OTHERS VARCHAR2 STATUS P_20100209_GROT 2 P Z VARCHAR2 STATUS P_20100209_GROT 2 P Z VARCHAR2 STATUS P_20100209_HALO 1 N N VARCHAR2 STATUS P_20100209_HALO 1 N N VARCHAR2 STATUS P_20100209_JUNE 1 U U VARCHAR2 STATUS P_20100209_JUNE 1 U U VARCHAR2 STATUS P_20100209_OTHERS 2 P Z VARCHAR2 STATUS P_20100209_OTHERS 2 P Z VARCHAR2 64 rows selected. SQL> SQL> SQL> -- set MSA back to default value SQL> alter session set "_minimal_stats_aggregation"=TRUE; Session altered. SQL> SQL> PROMPT ***************************************************************************************** ***************************************************************************************** SQL> PROMPT TEST 12 - Create new partition, insert data and then copy stats from a previous partition TEST 12 - Create new partition, insert data and then copy stats from a previous partition SQL> PROMPT ***************************************************************************************** ***************************************************************************************** SQL> SQL> ALTER TABLE TEST_TAB1 2 ADD PARTITION P_20100210 VALUES LESS THAN (20100211); Table altered. SQL> SQL> TRUNCATE TABLE LOAD_TAB1; Table truncated. SQL> SQL> INSERT INTO LOAD_TAB1 VALUES (20100210, 'GROT', 1000, 'P'); 1 row created. SQL> INSERT INTO LOAD_TAB1 VALUES (20100210, 'GROT', 30000, 'P'); 1 row created. SQL> INSERT INTO LOAD_TAB1 VALUES (20100210, 'GROT', 2000, 'P'); 1 row created. SQL> INSERT INTO LOAD_TAB1 VALUES (20100210, 'GROT', 10000, 'N'); 1 row created. SQL> INSERT INTO LOAD_TAB1 VALUES (20100210, 'GROT', 2400, 'P'); 1 row created. SQL> INSERT INTO LOAD_TAB1 VALUES (20100210, 'GROT', 500, 'P'); 1 row created. SQL> INSERT INTO LOAD_TAB1 VALUES (20100210, 'GROT', 1200, 'P'); 1 row created. SQL> INSERT INTO LOAD_TAB1 VALUES (20100210, 'GROT', 400, 'P'); 1 row created. SQL> INSERT INTO LOAD_TAB1 VALUES (20100210, 'GROT', 600, 'P'); 1 row created. SQL> INSERT INTO LOAD_TAB1 VALUES (20100210, 'GROT', 700, 'Z'); 1 row created. SQL> SQL> COMMIT; Commit complete. SQL> SQL> --THIS WAS THE BIG MISSING LINE!!! SQL> SQL> exec dbms_stats.delete_table_stats('TESTUSER', 'LOAD_TAB1'); PL/SQL procedure successfully completed. SQL> SQL> ALTER TABLE test_tab1 EXCHANGE SUBPARTITION P_20100210_GROT WITH TABLE load_tab1; Table altered. SQL> ALTER TABLE test_tab1 MODIFY SUBPARTITION P_20100210_GROT REBUILD UNUSABLE LOCAL INDEXES; Table altered. SQL> SQL> exec dbms_stats.copy_table_stats(ownname => 'TESTUSER', tabname => 'TEST_TAB1', srcpartname => 'P_20100209', dstpartname => 'P_20100210'); PL/SQL procedure successfully completed. SQL> SQL> select table_name, global_stats, last_analyzed, num_rows 2 from dba_tables 3 where table_name='TEST_TAB1' 4 and owner='TESTUSER' 5 order by 1, 2, 4 desc nulls last; TABLE_NAME GLO LAST_ANALYZED NUM_ROWS ------------------------------ --- -------------------- ---------- TEST_TAB1 NO SQL> SQL> select table_name, partition_name, global_stats, last_analyzed, num_rows 2 from dba_tab_partitions 3 where table_name='TEST_TAB1' 4 and table_owner='TESTUSER' 5 order by 1, 2, 4 desc nulls last; TABLE_NAME PARTITION_NAME GLO LAST_ANALYZED NUM_ROWS ------------------------------ ------------------------------ --- -------------------- ---------- TEST_TAB1 P_20100207 NO TEST_TAB1 P_20100209 NO 07-MAY-2010 05:11:22 12 TEST_TAB1 P_20100210 NO SQL> SQL> select table_name, subpartition_name, global_stats, last_analyzed, num_rows 2 from dba_tab_subpartitions 3 where table_name='TEST_TAB1' 4 and table_owner='TESTUSER' 5 order by 1, 2, 4 desc nulls last; TABLE_NAME SUBPARTITION_NAME GLO LAST_ANALYZED NUM_ROWS ------------------------------ ------------------------------ --- -------------------- ---------- TEST_TAB1 P_20100207_GROT NO TEST_TAB1 P_20100207_HALO NO TEST_TAB1 P_20100207_JUNE NO TEST_TAB1 P_20100207_OTHERS NO TEST_TAB1 P_20100209_GROT YES 07-MAY-2010 05:11:20 3 TEST_TAB1 P_20100209_HALO YES 07-MAY-2010 05:11:21 3 TEST_TAB1 P_20100209_JUNE YES 07-MAY-2010 05:11:20 3 TEST_TAB1 P_20100209_OTHERS YES 07-MAY-2010 05:11:21 3 TEST_TAB1 P_20100210_GROT NO TEST_TAB1 P_20100210_HALO NO TEST_TAB1 P_20100210_JUNE NO TEST_TAB1 P_20100210_OTHERS NO 12 rows selected. SQL> SQL> PROMPT ************************ ************************ SQL> PROMPT Table-Level Column Stats Table-Level Column Stats SQL> PROMPT ************************ ************************ SQL> SQL> select 2 a.column_name, 3 a.num_distinct, 4 display_raw(a.low_value,b.data_type) as low_val, 5 display_raw(a.high_value,b.data_type) as high_val, 6 b.data_type 7 from 8 dba_tab_col_statistics a, dba_tab_cols b 9 where 10 a.owner='TESTUSER' and 11 a.table_name='TEST_TAB1' and 12 a.table_name=b.table_name and 13 a.column_name=b.column_name 14 order by 1 15 / no rows selected SQL> SQL> PROMPT **************************** **************************** SQL> PROMPT Partition-Level Column Stats Partition-Level Column Stats SQL> PROMPT **************************** **************************** SQL> SQL> select 2 a.column_name, a.partition_name, 3 a.num_distinct, 4 display_raw(a.low_value,b.data_type) as low_val, 5 display_raw(a.high_value,b.data_type) as high_val, 6 b.data_type 7 from 8 dba_part_col_statistics a, dba_tab_cols b 9 where 10 a.owner='TESTUSER' and 11 a.table_name='TEST_TAB1' and 12 a.table_name=b.table_name and 13 a.column_name=b.column_name 14 order by 1, 2 15 / COLUMN_NAME PARTITION_NAME NUM_DISTINCT LOW_VAL HIGH_VAL -------------------------- ------------------------------ ------------ -------------------- -------------------- DATA_TYPE -------------------- REPORTING_DATE P_20100207 NUMBER REPORTING_DATE P_20100207 NUMBER REPORTING_DATE P_20100209 1 20100209 20100209 NUMBER REPORTING_DATE P_20100209 1 20100209 20100209 NUMBER REPORTING_DATE P_20100210 NUMBER REPORTING_DATE P_20100210 NUMBER SEQ_ID P_20100207 NUMBER SEQ_ID P_20100207 NUMBER SEQ_ID P_20100209 3 400 900 NUMBER SEQ_ID P_20100209 3 400 900 NUMBER SEQ_ID P_20100210 NUMBER SEQ_ID P_20100210 NUMBER SOURCE_SYSTEM P_20100207 VARCHAR2 SOURCE_SYSTEM P_20100207 VARCHAR2 SOURCE_SYSTEM P_20100209 4 GROT ZZZZ VARCHAR2 SOURCE_SYSTEM P_20100209 4 GROT ZZZZ VARCHAR2 SOURCE_SYSTEM P_20100210 VARCHAR2 SOURCE_SYSTEM P_20100210 VARCHAR2 STATUS P_20100207 VARCHAR2 STATUS P_20100207 VARCHAR2 STATUS P_20100209 3 N Z VARCHAR2 STATUS P_20100209 3 N Z VARCHAR2 STATUS P_20100210 VARCHAR2 STATUS P_20100210 VARCHAR2 24 rows selected. SQL> SQL> PROMPT ******************************* ******************************* SQL> PROMPT Subpartition-Level Column Stats Subpartition-Level Column Stats SQL> PROMPT ******************************* ******************************* SQL> SQL> select 2 a.column_name, a.subpartition_name, 3 a.num_distinct, 4 display_raw(a.low_value,b.data_type) as low_val, 5 display_raw(a.high_value,b.data_type) as high_val, 6 b.data_type 7 from 8 dba_subpart_col_statistics a, dba_tab_cols b 9 where 10 a.owner='TESTUSER' and 11 a.table_name='TEST_TAB1' and 12 a.table_name=b.table_name and 13 a.column_name=b.column_name 14 order by 1, 2 15 / COLUMN_NAME SUBPARTITION_NAME NUM_DISTINCT LOW_VAL HIGH_VAL -------------------------- ------------------------------ ------------ -------------------- -------------------- DATA_TYPE -------------------- REPORTING_DATE P_20100207_GROT NUMBER REPORTING_DATE P_20100207_GROT NUMBER REPORTING_DATE P_20100207_HALO NUMBER REPORTING_DATE P_20100207_HALO NUMBER REPORTING_DATE P_20100207_JUNE NUMBER REPORTING_DATE P_20100207_JUNE NUMBER REPORTING_DATE P_20100207_OTHERS NUMBER REPORTING_DATE P_20100207_OTHERS NUMBER REPORTING_DATE P_20100209_GROT 1 20100209 20100209 NUMBER REPORTING_DATE P_20100209_GROT 1 20100209 20100209 NUMBER REPORTING_DATE P_20100209_HALO 1 20100209 20100209 NUMBER REPORTING_DATE P_20100209_HALO 1 20100209 20100209 NUMBER REPORTING_DATE P_20100209_JUNE 1 20100209 20100209 NUMBER REPORTING_DATE P_20100209_JUNE 1 20100209 20100209 NUMBER REPORTING_DATE P_20100209_OTHERS 1 20100209 20100209 NUMBER REPORTING_DATE P_20100209_OTHERS 1 20100209 20100209 NUMBER REPORTING_DATE P_20100210_GROT NUMBER REPORTING_DATE P_20100210_GROT NUMBER REPORTING_DATE P_20100210_HALO NUMBER REPORTING_DATE P_20100210_HALO NUMBER REPORTING_DATE P_20100210_JUNE NUMBER REPORTING_DATE P_20100210_JUNE NUMBER REPORTING_DATE P_20100210_OTHERS NUMBER REPORTING_DATE P_20100210_OTHERS NUMBER SEQ_ID P_20100207_GROT NUMBER SEQ_ID P_20100207_GROT NUMBER SEQ_ID P_20100207_HALO NUMBER SEQ_ID P_20100207_HALO NUMBER SEQ_ID P_20100207_JUNE NUMBER SEQ_ID P_20100207_JUNE NUMBER SEQ_ID P_20100207_OTHERS NUMBER SEQ_ID P_20100207_OTHERS NUMBER SEQ_ID P_20100209_GROT 3 400 900 NUMBER SEQ_ID P_20100209_GROT 3 400 900 NUMBER SEQ_ID P_20100209_HALO 3 400 900 NUMBER SEQ_ID P_20100209_HALO 3 400 900 NUMBER SEQ_ID P_20100209_JUNE 3 400 900 NUMBER SEQ_ID P_20100209_JUNE 3 400 900 NUMBER SEQ_ID P_20100209_OTHERS 3 400 900 NUMBER SEQ_ID P_20100209_OTHERS 3 400 900 NUMBER SEQ_ID P_20100210_GROT NUMBER SEQ_ID P_20100210_GROT NUMBER SEQ_ID P_20100210_HALO NUMBER SEQ_ID P_20100210_HALO NUMBER SEQ_ID P_20100210_JUNE NUMBER SEQ_ID P_20100210_JUNE NUMBER SEQ_ID P_20100210_OTHERS NUMBER SEQ_ID P_20100210_OTHERS NUMBER SOURCE_SYSTEM P_20100207_GROT VARCHAR2 SOURCE_SYSTEM P_20100207_GROT VARCHAR2 SOURCE_SYSTEM P_20100207_HALO VARCHAR2 SOURCE_SYSTEM P_20100207_HALO VARCHAR2 SOURCE_SYSTEM P_20100207_JUNE VARCHAR2 SOURCE_SYSTEM P_20100207_JUNE VARCHAR2 SOURCE_SYSTEM P_20100207_OTHERS VARCHAR2 SOURCE_SYSTEM P_20100207_OTHERS VARCHAR2 SOURCE_SYSTEM P_20100209_GROT 1 GROT GROT VARCHAR2 SOURCE_SYSTEM P_20100209_GROT 1 GROT GROT VARCHAR2 SOURCE_SYSTEM P_20100209_HALO 1 HALO HALO VARCHAR2 SOURCE_SYSTEM P_20100209_HALO 1 HALO HALO VARCHAR2 SOURCE_SYSTEM P_20100209_JUNE 1 JUNE JUNE VARCHAR2 SOURCE_SYSTEM P_20100209_JUNE 1 JUNE JUNE VARCHAR2 SOURCE_SYSTEM P_20100209_OTHERS 1 ZZZZ ZZZZ VARCHAR2 SOURCE_SYSTEM P_20100209_OTHERS 1 ZZZZ ZZZZ VARCHAR2 SOURCE_SYSTEM P_20100210_GROT VARCHAR2 SOURCE_SYSTEM P_20100210_GROT VARCHAR2 SOURCE_SYSTEM P_20100210_HALO VARCHAR2 SOURCE_SYSTEM P_20100210_HALO VARCHAR2 SOURCE_SYSTEM P_20100210_JUNE VARCHAR2 SOURCE_SYSTEM P_20100210_JUNE VARCHAR2 SOURCE_SYSTEM P_20100210_OTHERS VARCHAR2 SOURCE_SYSTEM P_20100210_OTHERS VARCHAR2 STATUS P_20100207_GROT VARCHAR2 STATUS P_20100207_GROT VARCHAR2 STATUS P_20100207_HALO VARCHAR2 STATUS P_20100207_HALO VARCHAR2 STATUS P_20100207_JUNE VARCHAR2 STATUS P_20100207_JUNE VARCHAR2 STATUS P_20100207_OTHERS VARCHAR2 STATUS P_20100207_OTHERS VARCHAR2 STATUS P_20100209_GROT 2 P Z VARCHAR2 STATUS P_20100209_GROT 2 P Z VARCHAR2 STATUS P_20100209_HALO 1 N N VARCHAR2 STATUS P_20100209_HALO 1 N N VARCHAR2 STATUS P_20100209_JUNE 1 U U VARCHAR2 STATUS P_20100209_JUNE 1 U U VARCHAR2 STATUS P_20100209_OTHERS 2 P Z VARCHAR2 STATUS P_20100209_OTHERS 2 P Z VARCHAR2 STATUS P_20100210_GROT VARCHAR2 STATUS P_20100210_GROT VARCHAR2 STATUS P_20100210_HALO VARCHAR2 STATUS P_20100210_HALO VARCHAR2 STATUS P_20100210_JUNE VARCHAR2 STATUS P_20100210_JUNE VARCHAR2 STATUS P_20100210_OTHERS VARCHAR2 STATUS P_20100210_OTHERS VARCHAR2 96 rows selected. SQL> SQL> select sysdate from dual; SYSDATE -------------------- 07-MAY-2010 05:11:22 SQL> SQL> exit