执行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博客

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

最新文章