(oracle的表分析策略.docVIP

oracle的表分析策略 对表进行分析，通常情况下可以对表，索引，列进行单独分析，或者进行组合分析，但这三者哪些是相对重要的，哪些分析显得不那么重要？通过本篇文章的实验相信大家也会对直方图有更一步的了解. 1.首先创建测试表,并插入100000条数据SQL create table test(id number,nick varchar2(30)); Table created. SQL begin? 2????? for i in 1..100000 loop? 3??????????? insert into test(id) values(i);? 4????? end loop;? 5????? commit;? 6? end;? 7? / PL/SQL procedure successfully completed. 更新nick字段，使数据发生严重倾斜SQL update test set nick=abc where rownum99999;? 99998 rows updated. SQL commit; Commit complete. SQL create index idx_test_nick on test(nick); Index created. SQL update test set nick=def where nick is null; 2 rows updated. SQL commit; Commit complete. --只对索引进行分析SQL analyze index idx_test_nick compute statistics; Index analyzed. SQL select index_name,LEAF_BLOCKS,DISTINCT_KEYS,NUM_ROWS from user_indexes where index_name=IDX_TEST_NICK; INDEX_NAME???????????????????? LEAF_BLOCKS DISTINCT_KEYS?? NUM_ROWS------------------------------ ----------- ------------- ----------IDX_TEST_NICK????????????????????????? 210???????????? 2???? 100000 SQL select COLUMN_NAME,NUM_BUCKETS,num_distinct from USER_tab_columns where table_name=TEST; COLUMN_NAME??????????????????? NUM_BUCKETS NUM_DISTINCT------------------------------ ----------- ------------IDNICK 查看只分析索引的情况下的执行计划,从执行计划可以看出，优化器选择RBO，都走索引SQL set autotrace trace expSQL select * from test where nick =abc; Execution Plan----------------------------------------------------------?? 0????? SELECT STATEMENT Optimizer=CHOOSE?? 1??? 0?? TABLE ACCESS (BY INDEX ROWID) OF TEST?? 2??? 1???? INDEX (RANGE SCAN) OF IDX_TEST_NICK (NON-UNIQUE) ? SQL select * from test where nick =def; Execution Plan----------------------------------------------------------?? 0????? SELECT STATEMENT Optimizer=CHOOSE?? 1??? 0?? TABLE ACCESS (BY INDEX ROWID) OF TEST?? 2??? 1???? INDEX (RANGE SCAN) OF IDX_TEST_NICK (NON-UNIQUE) 分析有数据严重倾斜的nick列后,也没有产生正确的执行计划,此时使用的优化器仍然是RBO通过此实验，说明只分析索引和列，ORACLE不会使用CBO的优化器SQL analyze table test compute statistics for column