Oracle學習系列—資料庫優化—Analyze語句和資料字典

bq_wang發表於2007-04-16
Oracle資料庫優化

執行Analyze語句後,系統會把表索引統計記入相應資料字典表,CBO依據這些統計進行執行計劃分析.

下面是幾個簡單的例子,黃顏色部分是執行分析後,資料字典發生的變化


CREATE TABLE MainTable AS
SELECT * FROM ALL_ALL_TABLES;

CREATE TABLE Owner AS
SELECT DISTINCT Owner FROM MainTable;

CREATE INDEX MainTable_Owner ON MainTable (Owner);
CREATE INDEX Owner_Owner ON Owner (Owner);

ANALYZE TABLE MainTable Compute STATISTICS;

SELECT * FROM USER_ALL_TABLES

WHERE TABLE_NAME='MAINTABLE';

ANALYZE TABLE MainTable COMPUTE STATISTICS

SELECT * FROM USER_TAB_COLUMNS

WHERE TABLE_NAME='MAINTABLE' AND COLUMN_NAME='OWNER'

select * from user_tab_col_statistics(view base user_tab_columns)

ANALYZE INDEX MainTable_Owner COMPUTE STATISTICS

SELECT * FROM USER_INDEXES

WHERE INDEX_NAME='MAINTABLE_OWNER'

REM Create Chained_Rows Table

SQL> @C:/UserDefine/oracle/ora92/rdbms/admin/UTLCHAIN.SQL

ANALYZE TABLE MAINTABLE LIST CHAINED ROWS INTO Chained_Rows;

SELECT * FROM Chained_Rows

SQL> EXECUTE DBMS_STATS.GATHER_TABLE_STATS ('wbq','maintable', METHOD_OPT => 'FOR COLUMNS SIZE 10 tablespace_name');

SELECT * FROM USER_TAB_HISTOGRAMS where column_name='TABLE_NAME'

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

相關文章