表分析初體驗

----建立表
SQL> create table test as (select * from all_objects);

Table created.

SQL> insert into test (select * from all_objects);

38167 rows created.

SQL> commit;

Commit complete.

----建立索引
SQL> create index ind_test on test (object_name);

Index created.

-------表還沒有統計資訊
SQL> select table_name,last_analyzed from user_tables where table_name='TEST';

TABLE_NAME LAST_ANALYZED
------------------------------------------------------------ --------------
TEST

-------索引的統計資訊是新建立的時候的：2010年5月30日18:59:46
SQL> select index_name,to_char(last_analyzed,'yyyymmddHH24MISS') from user_indexes where index_name='IND_TEST';

INDEX_NAME TO_CHAR(LAST_ANALYZED,'YYYYM
--------------- ----------------------------
IND_TEST 20100530185946

SQL> select to_char(sysdate,'yyyymmddHH24MISS') from dual;

TO_CHAR(SYSDATE,'YYYYMMDDHH2
----------------------------
20100530190139

--------執行表分析
SQL> exec dbms_stats.gather_table_stats(ownname => 'charis',tabname => 'test');

PL/SQL procedure successfully completed.

--------更新了表的統計資訊
SQL> select table_name,to_char(last_analyzed,'yyyymmddHH24MISS') from user_tables where table_name='TEST';

TABLE_NAME TO_CHAR(LAST_ANALYZED,'YYYYM
-------------------- ----------------------------
TEST 20100530190256

--------更新了索引的統計資訊
SQL> select index_name,to_char(last_analyzed,'yyyymmddHH24MISS') from user_indexes where index_name='IND_TEST';

INDEX_NAME TO_CHAR(LAST_ANALYZED,'YYYYM
--------------- ----------------------------
IND_TEST 20100530190256

SQL> select count(1) from test;

COUNT(1)
----------
76334

SQL>

[@more@]

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/23850820/viewspace-1034366/，如需轉載，請註明出處，否則將追究法律責任。