Default behavior of create & rebuild index in 10G (zt)

very different from 9i

In oracle 10G, when create index or rebuild index, statistics will be computed automatically.

Here is my experiment:

C:>sqlplus sys/oracle as sysdba

SQL*Plus: Release 10.2.0.1.0 - Production on 星期三 5月 30 18:26:48 2007

Copyright (c) 1982, 2005, Oracle. All rights reserved.

Connected to:

Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production

With the Partitioning, OLAP and Data Mining options

SQL> select * from v$version;

BANNER

----------------------------------------------------------------

Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Prod

PL/SQL Release 10.2.0.1.0 - Production

CORE 10.2.0.1.0 Production

TNS for 32-bit Windows: Version 10.2.0.1.0 - Production

NLSRTL Version 10.2.0.1.0 - Production

SQL> conn wwf/wwf

Connected.

SQL> select * from tab;

SQL> create table tt as select * from all_objects;

Table Created

SQL> create index ind_tt on tt(object_id);

Index Created

SQL> alter session set nls_date_format = 'yyyy-mm-dd hh24:mi:ss';

Session altered

SQL> select index_name, last_analyzed, blevel, num_rows, distinct_keys from user_indexes

2 where index_name = 'IND_TT';

INDEX_NAME LAST_ANALYSED BLEVEL NUM_ROWS DISTINCT_KEYS

------------------------------ ------------------- ---------- ---------- -------------

IND_TT 2007-05-30 18:28:18 1 40809 40809

SQL> delete from tt where object_id < 20000;

14147 rows deleted

SQL> commit;

Committed

SQL> alter index ind_tt rebuild;

Index created

SQL> select index_name, last_analyzed, blevel, num_rows, distinct_keys from user_indexes

2 where index_name = 'IND_TT';

INDEX_NAME LAST_ANALYSED BLEVEL NUM_ROWS DISTINCT_KEYS

------------------------------ ------------------- ---------- ---------- -------------

IND_TT 2007-05-30 18:32:28 1 26662 26662

It seems that we have no way to prevent Oracle computing statistics.

Here is what I got from oracle official document():

COMPUTE STATISTICS In earlier releases, you could use this clause to start or stop the collection of statistics on an index. This clause has been deprecated. Oracle Database now automatically collects statistics during index creation and rebuild. This clause is supported for backward compatibility and will not cause errors.

There is one way to disable this behavior(http://download-west.oracle.com/docs/cd/B19306_01/appdev.102/b14258/d_stats.htm#i1043993):

When statistics on a table are locked, all the statistics depending on the table, including table statistics, column statistics, histograms and statistics on all dependent indexes, are considered to be locked.

This is my experiment:

SQL> drop index ind_tt;

Index Dropped

SQL> exec dbms_stats.lock_table_stats(user, 'TT');

PL/SQL executed successfully。

SQL> create index ind_tt on tt(object_id);

Index created

SQL> select index_name, last_analyzed, blevel, num_rows, distinct_keys from user_indexes

2 where index_name = 'IND_TT';

INDEX_NAME LAST_ANALYZED BLEVEL NUM_ROWS DISTINCT_KEYS

------------------------------ ------------------- ---------- ---------- -------------

IND_TT