Oracle学习系列—数据库优化—Analyze语句和数据字典

执行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/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/6517/viewspace-145521/