各版本数据库重建索引后是否自动分析表和索引9i+10g+11g

--重建索引后是否自动分析表和索引(9i+10g+11g)
--9i库
SQL> select * from v$version where rownum<5;

BANNER
----------------------------------------------------------------
Oracle9i Enterprise Edition Release 9.2.0.6.0 - 64bit Production
PL/SQL Release 9.2.0.6.0 - Production
CORE    9.2.0.6.0       Production
TNS for HPUX: Version 9.2.0.6.0 - Production

--建测试表
SQL> create table test1(id number,name varchar2(10));

Table created.

Elapsed: 00:00:00.00
SQL> insert into test1 values (1,'yallonking');

1 row created.

SQL> commit;

Commit complete.

--建索引
SQL> create index idx_test on test1(id);

Index created.
--表和索引的均没有分析
SQL> select LAST_ANALYZED from user_tables where table_name='TEST1';

LAST_ANALYZED
-------------------

SQL> select LAST_ANALYZED from user_indexes where table_name='TEST1';

LAST_ANALYZED
-------------------

--收集表信息
SQL> analyze table test1 compute statistics;

Table analyzed.

--表和索引信息已存在
SQL> select LAST_ANALYZED from user_tables where table_name='TEST1';

LAST_ANALYZED
-------------------
2012/09/08 11:17:44

SQL> select LAST_ANALYZED from user_indexes where table_name='TEST1';

LAST_ANALYZED
-------------------
2012/09/08 11:17:44

--记录当前时间
SQL> select sysdate from dual;

SYSDATE
-------------------
2012/09/08 11:18:23

--重建索引
SQL> alter index idx_test rebuild;

Index altered.

SQL> select LAST_ANALYZED from user_tables where table_name='TEST1';

LAST_ANALYZED
-------------------
2012/09/08 11:17:44

SQL> select LAST_ANALYZED from user_indexes where table_name='TEST1';

LAST_ANALYZED
-------------------
2012/09/08 11:17:44

--在线重建索引
SQL> alter index idx_test rebuild online;

Index altered.

Elapsed: 00:00:00.18

--查看表和索引分析时间（未变）
SQL> alter index idx_test rebuild online;

Index altered.

SQL> select LAST_ANALYZED from user_indexes where table_name='TEST1';

LAST_ANALYZED
-------------------
2012/09/08 11:17:44

SQL> select LAST_ANALYZED from user_tables where table_name='TEST1';

LAST_ANALYZED
-------------------
2012/09/08 11:17:44

--10g库
SQL> select * from v$version where rownum<5;

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bi
PL/SQL Release 10.2.0.4.0 - Production
CORE    10.2.0.4.0      Production
TNS for IBM/AIX RISC System/6000: Version 10.2.0.4.0 - Productio

--建测试表
SQL> create table test1(id number,name varchar2(10));

Table created.

SQL> insert into test1 values (1,'yallonking');

1 row created.

SQL> commit;

Commit complete.

--建索引
SQL> create index idx_test on test1(id);

Index created.

--表没有分析但是此时索引的却进行了分析（和9i不同）
SQL> select LAST_ANALYZED from user_tables where table_name='TEST1';

LAST_ANALYZED
-------------------

SQL> select LAST_ANALYZED from user_indexes where table_name='TEST1';

LAST_ANALYZED
-------------------
2012/09/08 11:44:13

--收集表信息
SQL> analyze table test1 compute statistics;

Table analyzed.

--表和索引信息已存在
SQL> select LAST_ANALYZED from user_tables where table_name='TEST1';

LAST_ANALYZED
-------------------
2012/09/08 11:44:38

SQL> select LAST_ANALYZED from user_indexes where table_name='TEST1';

LAST_ANALYZED
-------------------
2012/09/08 11:44:38

--记录当前时间
SQL> select sysdate from dual;

SYSDATE
-------------------
2012/09/08 11:45:10

--重建索引
SQL> alter index idx_test rebuild;

Index altered.

SQL> select LAST_ANALYZED from user_indexes where table_name='TEST1';

LAST_ANALYZED
-------------------
2012/09/08 11:45:34

SQL> select LAST_ANALYZED from user_tables where table_name='TEST1';

LAST_ANALYZED
-------------------
2012/09/08 11:44:38

--在线重建索引
SQL> alter index idx_test rebuild online;

Index altered.

SQL> select LAST_ANALYZED from user_tables where table_name='TEST1';

LAST_ANALYZED
-------------------
2012/09/08 11:44:38

SQL> select LAST_ANALYZED from user_indexes where table_name='TEST1';

LAST_ANALYZED
-------------------
2012/09/08 11:46:05

--11g库
SQL> select * from v$version where rownum<5;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
PL/SQL Release 11.2.0.1.0 - Production
CORE    11.2.0.1.0      Production
TNS for Linux: Version 11.2.0.1.0 - Production

--建测试表
SQL> create table test1(id number,name varchar2(10));

Table created.

SQL> insert into test1 values (1,'yallonking');

1 row created.

SQL> commit;

Commit complete.

--建索引
SQL> create index idx_test on test1(id);

Index created.
--表和索引的均没有分析
SQL> select LAST_ANALYZED from user_tables where table_name='TEST1';

LAST_ANALYZED
-------------------

SQL> select LAST_ANALYZED from user_indexes where table_name='TEST1';

LAST_ANALYZED
-------------------
2012/09/08 11:10:45

--收集表信息
SQL> analyze table test1 compute statistics;

Table analyzed.

--表和索引信息已存在
SQL> select LAST_ANALYZED from user_tables where table_name='TEST1';

LAST_ANALYZED
-------------------
2012/09/08 11:11:12

SQL> select LAST_ANALYZED from user_indexes where table_name='TEST1';

LAST_ANALYZED
-------------------
2012/09/08 11:11:12

--记录当前时间
SQL> select sysdate from dual;

SYSDATE
-------------------
2012/09/08 11:11:37

--重建索引
SQL> alter index idx_test rebuild;

Index altered.

SQL> select LAST_ANALYZED from user_tables where table_name='TEST1';

LAST_ANALYZED
-------------------
2012/09/08 11:11:12

SQL> select LAST_ANALYZED from user_indexes where table_name='TEST1';

LAST_ANALYZED
-------------------
2012/09/08 11:11:51

--在线重建索引
SQL> alter index idx_test rebuild online;

Index altered.

SQL> select LAST_ANALYZED from user_tables where table_name='TEST1';

LAST_ANALYZED
-------------------
2012/09/08 11:11:12

SQL> select LAST_ANALYZED from user_indexes where table_name='TEST1';

LAST_ANALYZED
-------------------
2012/09/08 11:12:16

结论：关于索引是否分析见下简表
库版本                                  9i库  10g库  11g库
创建时是否分析                    否       是   是
非在线重建索引是否分析      否       是   是
在线重建是否分析                否        是   是

                

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/26143577/viewspace-742932/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/26143577/viewspace-742932/