Splitting a partition would cause re-gathering stats for both partitions

 

The test  process as following :

--create a test partition table

create table test_part3(

id number,

aaa varchar2(30),

bbb varchar2(30),

date1 date default sysdate,

name1 varchar2(25),

name2 varchar2(25),

name3 varchar2(25),

name4 varchar2(25),

name5 varchar2(25),

name6 varchar2(25),

name7 varchar2(25),

name8 varchar2(25),

name9 varchar2(25),

name10 varchar2(25),

name11 varchar2(25),

name12 varchar2(25),

name13 varchar2(25),

name14 varchar2(25),

name15 varchar2(25),

name16 varchar2(25),

name17 varchar2(25),

name18 varchar2(25),

name19 varchar2(25),

name20 varchar2(25),

name21 varchar2(25),

name22 varchar2(25),

name23 varchar2(25),

name24 varchar2(25),

name25 varchar2(25) ) partition by range (id)

( partition partition_01 values less than (10000000) tablespace DATA,

  partition partition_02 values less than (20000000) tablespace DATA,

  partition partition_03 values less than (30000000) tablespace DATA,

  partition partition_04 values less than (40000000) tablespace DATA);

 

--enable gathering stats by incremental 

exec dbms_stats.set_table_prefs('KIMI','TEST_PART3','INCREMENTAL','TRUE');

select dbms_stats.get_prefs(pname => 'INCREMENTAL',ownname => 'KIMI',tabname=> 'TEST_PART3') from dual;--TRUE

-- insert 10000 rows into partition_01

begin

for i in 1..10000 loop

INSERT INTO test_part3 VALUES

  (i,'aaa'||i,'bbb'||i,sysdate,'name'||i,'name'||i,'name'||i,'name'||i,'name'||i,'name'||i,'name'||i,'name'||i,'name'||i,

  'name'||i,'name'||i,'name'||i,'name'||i,'name'||i,'name'||i,'name'||i,'name'||i,'name'||i,'name'||i,'name'||i,

  'name'||i,'name'||i,'name'||i,'name'||i,'name'||i);

end loop;

commit;

end;

-- find out the records of each partition

Declare

Cursor c1 is

select table_name,partition_name from all_tab_partitions where table_name = 'TEST_PART3';

v_sql varchar2(2000);

temp_var number:=0;

BEGIN

for rec in c1

Loop

execute immediate 'select count(*) from '||rec.table_name||' partition('||rec.partition_name||')' into temp_var;

dbms_output.put_line('The Partition '||rec.partition_name||' of table '||rec.table_name||' has '||temp_var||' rows');

END LOOP;

END;

The Partition PARTITION_04 of table TEST_PART3 has 0 rows

The Partition PARTITION_03 of table TEST_PART3 has 0 rows

The Partition PARTITION_02 of table TEST_PART3 has 0 rows

The Partition PARTITION_01 of table TEST_PART3 has 10000 rows    -- that is the partition in which 10000 rows were inserted

-- gathering stats and check stats for each partition

exec dbms_stats.gather_table_stats(ownname=>'KIMI',tabname=>'TEST_PART3');

select * from dba_objects where object_name='TEST_PART3';  -- object_id : 946253

select * from SYS.TABPART$ where bo#=946253;

OBJ#	DATAOBJ#	BO#	PART#	HIBOUNDLEN	HIBOUNDVAL	TS#	FILE#	BLOCK#	PCTFREE$	PCTUSED$	INITRANS	MAXTRANS	FLAGS	ANALYZETIME	SAMPLESIZE	ROWCNT	BLKCNT	EMPCNT	AVGSPC	CHNCNT	AVGRLN	SPARE1	SPARE2	SPARE3	BHIBOUNDVAL
946254	946254	946253	10	8	10000000	7	8	43153	10	40	1	255	18	2/13/2015 0:00	10000	10000	366	0	0	0	253	0	0	0	(BLOB)
946255	946255	946253	20	8	20000000	7	0	0	10	40	1	255	65554	2/13/2015 0:00	0	0	0	0	0	0	0	0	0	0	(BLOB)
946256	946256	946253	30	8	30000000	7	0	0	10	40	1	255	65554	2/13/2015 0:00	0	0	0	0	0	0	0	0	0	0	(BLOB)
946257	946257	946253	40	8	40000000	7	0	0	10	40	1	255	65554	2/13/2015 0:00	0	0	0	0	0	0	0	0	0	0	(BLOB)

-- now , splitting partition : PARTITION_01 , keep the old partition name there.     and check stats again

-- we can see that no ANALYZETIME value there.

alter table TEST_PART3 split partition PARTITION_01  at (5000)  into (partition PARTITION_01, partition PARTITION_11);                                                                                                                                

select * from SYS.TABPART$ where bo#=946253

OBJ#	DATAOBJ#	BO#	PART#	HIBOUNDLEN	HIBOUNDVAL	TS#	FILE#	BLOCK#	PCTFREE$	PCTUSED$	INITRANS	MAXTRANS	FLAGS	ANALYZETIME	SAMPLESIZE	ROWCNT	BLKCNT	EMPCNT	AVGSPC	CHNCNT	AVGRLN	SPARE1	SPARE2	SPARE3	BHIBOUNDVAL
946254	946293	946253	9	4	5000	7	8	50321	10	40	1	255	0		0	0	0	0	0	0	0	0	0	0	(BLOB)
946294	946294	946253	11	8	10000000	7	8	51345	10	40	1	255	0		0	0	0	0	0	0	0	0	0	0	(BLOB)
946255	946255	946253	20	8	20000000	7	0	0	10	40	1	255	65554	2/13/2015 0:00	0	0	0	0	0	0	0	0	0	0	(BLOB)
946256	946256	946253	30	8	30000000	7	0	0	10	40	1	255	65554	2/13/2015 0:00	0	0	0	0	0	0	0	0	0	0	(BLOB)
946257	946257	946253	40	8	40000000	7	0	0	10	40	1	255	65554	2/13/2015 0:00	0	0	0	0	0	0	0	0	0	0	(BLOB)

--  convert ANALYZETIME value to be more exact ( used to compare with stats below)

OBJ#	DATAOBJ#	BO#	PART#	HIBOUNDLEN	HIBOUNDVAL	FLAGS	to_char(ANALYZETIME,'YYYY-MM-DD HH24:MI:SS')
946254	946293	946253	9	4	5000	0
946294	946294	946253	11	8	10000000	0
946255	946255	946253	20	8	20000000	65554	2015-02-13 01:36:02
946256	946256	946253	30	8	30000000	65554	2015-02-13 01:36:02
946257	946257	946253	40	8	40000000	65554	2015-02-13 01:36:02

-- gathering stats again , compare the stats with the former and find out the only two splitting-related partitions got stats again .

-- meanwhile , the stats and gathering time  for those partitions with none change remain the same as it was . That is also the idea of incremental stats.

exec dbms_stats.gather_table_stats(ownname=>'KIMI',tabname=>'TEST_PART3');

OBJ#	DATAOBJ#	BO#	PART#	HIBOUNDLEN	HIBOUNDVAL	TS#	FILE#	BLOCK#	PCTFREE$	PCTUSED$	INITRANS	MAXTRANS	FLAGS	ANALYZETIME	SAMPLESIZE	ROWCNT	BLKCNT	EMPCNT	AVGSPC	CHNCNT	AVGRLN	SPARE1	SPARE2	SPARE3	BHIBOUNDVAL
946254	946293	946253	9	4	5000	7	8	50321	10	40	1	255	18	2/13/2015 0:00	4999	4999	193	0	0	0	253	0	0	0	(BLOB)
946294	946294	946253	11	8	10000000	7	8	51345	10	40	1	255	18	2/13/2015 0:00	5001	5001	197	0	0	0	253	0	0	0	(BLOB)
946255	946255	946253	20	8	20000000	7	0	0	10	40	1	255	65554	2/13/2015 0:00	0	0	0	0	0	0	0	0	0	0	(BLOB)
946256	946256	946253	30	8	30000000	7	0	0	10	40	1	255	65554	2/13/2015 0:00	0	0	0	0	0	0	0	0	0	0	(BLOB)
946257	946257	946253	40	8	40000000	7	0	0	10	40	1	255	65554	2/13/2015 0:00	0	0	0	0	0	0	0	0	0	0	(BLOB)

OBJ#	DATAOBJ#	BO#	PART#	HIBOUNDLEN	HIBOUNDVAL	FLAGS	TO_CHAR(ANALYZETIME,'YYYY-MM-DDHH24:MI:SS')
946254	946293	946253	9	4	5000	18	2015-02-13 02:25:45
946294	946294	946253	11	8	10000000	18	2015-02-13 02:25:45
946255	946255	946253	20	8	20000000	65554	2015-02-13 01:36:02
946256	946256	946253	30	8	30000000	65554	2015-02-13 01:36:02
946257	946257	946253	40	8	40000000	65554	2015-02-13 01:36:02