各版本資料庫重建索引後是否自動分析表和索引9i+10g+11g

YallonKing發表於2012-09-08
原文網址 : http://blog.itpub.net/26143577/viewspace-742932/
資料庫索引
--重建索引後是否自動分析表和索引(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/，如需轉載，請註明出處，否則將追究法律責任。

相關文章