17 February, 2010

Table and Index Statistics with MOVE/REBUILD/TRUNCATE

Recently, I've noticed a few questions about whether GATHER_STATS should be re-run after a MOVE/REBUILD operation.

The proper answer is "Do a GATHER_STATS if there is a change to the data and/or segment size since the last time statistics were gathered".


Below, I demonstrate how
a) a MOVE does not update Table Statistics
b) a REBUILD *does* update Index Statistics (in 10g and above)
c) a TRUNCATE does not update Table Statistics
d) how these can impact "costing" of Execution Plans.


SQL> drop table target_table purge;

Table dropped.

SQL> select count(*) from dba_objects;

COUNT(*)
----------
50705

SQL>
SQL> create table target_table as select * from dba_objects where object_id is not null;

Table created.

SQL> create index target_table_ndx on target_table(object_id);

Index created.

SQL> exec dbms_stats.gather_table_stats('','TARGET_TABLE',estimate_percent=>100,method_opt=>'FOR COLUMNS OWNER SIZE 250', cascade=>TRUE);

PL/SQL procedure successfully completed.

SQL>
SQL> -- current statistics
SQL> select 'Table' Obj_Type, table_name Obj_Name, num_rows, blocks
2 from user_tables where table_name = 'TARGET_TABLE'
3 union
4 select 'Index', index_name, num_rows, leaf_blocks
5 from user_indexes where index_name = 'TARGET_TABLE_NDX'
6 order by 1 desc
7 /

OBJ_T OBJ_NAME NUM_ROWS BLOCKS
----- ------------------------------ ---------- ----------
Table TARGET_TABLE 50705 717
Index TARGET_TABLE_NDX 50705 112

SQL> select 'Table Seg' Seg_Type, segment_name Seg_Name, blocks
2 from user_segments where segment_name = 'TARGET_TABLE' and segment_type = 'TABLE'
3 union
4 select 'Index Seg', segment_name, blocks
5 from user_segments where segment_name = 'TARGET_TABLE_NDX' and segment_type = 'INDEX'
6 order by 1 desc
7 /

SEG_TYPE SEG_NAME BLOCKS
--------- ------------------------------ ----------
Table Seg TARGET_TABLE 768
Index Seg TARGET_TABLE_NDX 128

SQL>
SQL> REM REM REM ##########################################
SQL> -- a simple move/rebuild
SQL> alter table target_table move ;

Table altered.

SQL> alter index target_table_ndx rebuild;

Index altered.

SQL>
SQL> -- statistics after the move/rebuild
SQL> select 'Table' Obj_Type, table_name Obj_Name, num_rows, blocks
2 from user_tables where table_name = 'TARGET_TABLE'
3 union
4 select 'Index', index_name, num_rows, leaf_blocks
5 from user_indexes where index_name = 'TARGET_TABLE_NDX'
6 order by 1 desc
7 /

OBJ_T OBJ_NAME NUM_ROWS BLOCKS
----- ------------------------------ ---------- ----------
Table TARGET_TABLE 50705 717
Index TARGET_TABLE_NDX 50705 112

SQL> select 'Table Seg' Seg_Type, segment_name Seg_Name, blocks
2 from user_segments where segment_name = 'TARGET_TABLE' and segment_type = 'TABLE'
3 union
4 select 'Index Seg', segment_name, blocks
5 from user_segments where segment_name = 'TARGET_TABLE_NDX' and segment_type = 'INDEX'
6 order by 1 desc
7 /

SEG_TYPE SEG_NAME BLOCKS
--------- ------------------------------ ----------
Table Seg TARGET_TABLE 768
Index Seg TARGET_TABLE_NDX 128

SQL>
SQL> REM REM REM ##########################################
SQL> -- what happens if we have deleted rows ?
SQL> select count(*) from target_table;

COUNT(*)
----------
50705

SQL> delete target_table where owner = 'SYS';

22941 rows deleted.

SQL> select count(*) from target_table;

COUNT(*)
----------
27764

SQL> alter table target_table move ;

Table altered.

SQL> alter index target_table_ndx rebuild;

Index altered.

SQL>
SQL> -- statistics after the move/rebuild
SQL> select 'Table' Obj_Type, table_name Obj_Name, num_rows, blocks
2 from user_tables where table_name = 'TARGET_TABLE'
3 union
4 select 'Index', index_name, num_rows, leaf_blocks
5 from user_indexes where index_name = 'TARGET_TABLE_NDX'
6 order by 1 desc
7 /

OBJ_T OBJ_NAME NUM_ROWS BLOCKS
----- ------------------------------ ---------- ----------
Table TARGET_TABLE 50705 717
Index TARGET_TABLE_NDX 27764 62

SQL> select 'Table Seg' Seg_Type, segment_name Seg_Name, blocks
2 from user_segments where segment_name = 'TARGET_TABLE' and segment_type = 'TABLE'
3 union
4 select 'Index Seg', segment_name, blocks
5 from user_segments where segment_name = 'TARGET_TABLE_NDX' and segment_type = 'INDEX'
6 order by 1 desc
7 /

SEG_TYPE SEG_NAME BLOCKS
--------- ------------------------------ ----------
Table Seg TARGET_TABLE 512
Index Seg TARGET_TABLE_NDX 72

SQL>
SQL> REM REM REM ##########################################
SQL> -- what happens if we TRUNCATE ?
SQL> truncate table target_table ;

Table truncated.

SQL> alter table target_table move ;

Table altered.

SQL> alter index target_table_ndx rebuild;

Index altered.

SQL>
SQL> -- statistics after the move/rebuild
SQL> select 'Table' Obj_Type, table_name Obj_Name, num_rows, blocks
2 from user_tables where table_name = 'TARGET_TABLE'
3 union
4 select 'Index', index_name, num_rows, leaf_blocks
5 from user_indexes where index_name = 'TARGET_TABLE_NDX'
6 order by 1 desc
7 /

OBJ_T OBJ_NAME NUM_ROWS BLOCKS
----- ------------------------------ ---------- ----------
Table TARGET_TABLE 50705 717
Index TARGET_TABLE_NDX 0 0

SQL> select 'Table Seg' Seg_Type, segment_name Seg_Name, blocks
2 from user_segments where segment_name = 'TARGET_TABLE' and segment_type = 'TABLE'
3 union
4 select 'Index Seg', segment_name, blocks
5 from user_segments where segment_name = 'TARGET_TABLE_NDX' and segment_type = 'INDEX'
6 order by 1 desc
7 /

SEG_TYPE SEG_NAME BLOCKS
--------- ------------------------------ ----------
Table Seg TARGET_TABLE 8
Index Seg TARGET_TABLE_NDX 8

SQL>
SQL> REM REM REM ##
SQL> REM Findings :
SQL> REM 1. Index Statistics are updated at the REBUILD
SQL> REM -- Note : 10g defaults to COMPUTE STATISTICS with the REBUILD of an Index
SQL> REM 9i and below do not default to COMPUTE STATISTICS
SQL> REM 2. Table Statistics are NOT updated at the MOVE
SQL>
SQL> REM Implications :
SQL> REM If you have done a MOVE/REBUILD because there has been significant change in data
SQL> REM and you expect the Oracle to generate different execution plans
SQL> REM you might be suprised.
SQL> REM Plans that are influenced by the Index are changed
SQL> REM Plans that cannot use an Index at all are not changed
SQL> REM -- However this can be detrimental to costing for the table in Joins with other Tables
SQL> REM -- as the Join costing for a FullTableScan / HashJoin may not be truthful
SQL>
SQL> REM for example, although the table has been TRUNCATED see below :
SQL>
SQL> explain plan for select owner, object_name, object_type from target_table where owner = 'SYS';

Explained.

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
Plan hash value: 4001333823

----------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 22941 | 1859K| 197 (1)| 00:00:03 |
|* 1 | TABLE ACCESS FULL| TARGET_TABLE | 22941 | 1859K| 197 (1)| 00:00:03 |
----------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

1 - filter("OWNER"='SYS')

13 rows selected.

SQL>
SQL> REM Oracle still expects 22,941 rows for 'SYS' in the table -- even though it has been TRUNCATed
SQL>



Notice how Table Statistics are not updated but Index Statistics *ARE* updated -- simply because 10g defaults to including a COMPUTE STATISTICS when an Index is Rebuilt.

.
.
.

4 comments:

Randolf said...

Hemant,

you say:
"c) a TRUNCATE does not update Table Statistics but does update Index Statistics"

Are you sure that the TRUNCATE itself updates index statistics?

I think in your example it is always the REBUILD that updates the index statistics.

Randolf

Hemant K Chitale said...

Randolf,
Yes, you are correct. I have updated that line to "The TRUNCATE does not update Table Statistics".
It is the REBUILD that updated the Index Statistics in my test case.

Hemant

Raghu Yella (10g Oracle Certified Professional) said...

Nice analysis Hemant!!!

Unknown said...

Very helpful hemant...
Keeping posting

Surya