《2022年oracle的表分析策略 .pdf》由会员分享，可在线阅读，更多相关《2022年oracle的表分析策略 .pdf（5页珍藏版）》请在taowenge.com淘文阁网|工程机械CAD图纸|机械工程制图|CAD装配图下载|SolidWorks_CaTia_CAD_UG_PROE_设计图分享下载上搜索。
1、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/SQ
2、L procedure successfully completed. 更新 nick 字段，使数据发生严重倾斜SQL update test set nick=abc where rownum 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 in
3、dex 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_
4、tab_columns where table_name=TEST; COLUMN_NAME NUM_BUCKETS NUM_DISTINCT - - - ID NICK 查看只分析索引的情况下的执行计划,从执行计划可以看出，优化器选择RBO，都走索引SQL set autotrace trace exp 名师资料总结 - - -精品资料欢迎下载 - - - - - - - - - - - - - - - - - - 名师精心整理 - - - - - - - 第 1 页，共 5 页 - - - - - - - - - SQL select * from test where nick =abc
5、; 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 (RANG
6、E SCAN) OF IDX_TEST_NICK (NON-UNIQUE) 分析有数据严重倾斜的nick 列后 ,也没有产生正确的执行计划,此时使用的优化器仍然是RBO 通过此实验，说明只分析索引和列，ORACLE 不会使用CBO 的优化器SQL analyze table test compute statistics for columns size 2 nick; Table analyzed. SQL select * from test where nick =abc; Execution Plan - 0 SELECT STATEMENT Optimizer=CHOOSE 1 0
7、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) 现在分析表后 ,产生了正确的执行计划SQL
8、 analyze table test compute statistics for table; Table analyzed. SQL select * from test where nick =abc; Execution Plan - 0 SELECT STATEMENT Optimizer=CHOOSE (Cost=50 Card=99998 Bytes= 1499970) 1 0 TABLE ACCESS (FULL) OF TEST (Cost=50 Card=99998 Bytes=14 99970) 名师资料总结 - - -精品资料欢迎下载 - - - - - - - -
9、- - - - - - - - - - 名师精心整理 - - - - - - - 第 2 页，共 5 页 - - - - - - - - - SQL select * from test where nick =def; Execution Plan - 0 SELECT STATEMENT Optimizer=CHOOSE (Cost=1 Card=2 Bytes=30) 1 0 TABLE ACCESS (BY INDEX ROWID) OF TEST (Cost=1 Card=2 Bytes=30) 2 1 INDEX (RANGE SCAN) OF IDX_TEST_NICK (NON
10、-UNIQUE) (Cost =1 Card=2) 删除所有的统计数据，并只对表与列进行分析，不分析索引， ORACLE 使用 CBO 的优化器，并产生了正确的执行计划SQL analyze table test delete statistics; Table analyzed. SQL analyze table test compute statistics for table for columns size 2 nick; Table analyzed. SQL select * from test where nick =abc; Execution Plan - 0 SELECT
11、 STATEMENT Optimizer=CHOOSE (Cost=50 Card=99998 Bytes= 1499970) 1 0 TABLE ACCESS (FULL) OF TEST (Cost=50 Card=99998 Bytes=14 99970) SQL select * from test where nick =def; Execution Plan - 0 SELECT STATEMENT Optimizer=CHOOSE (Cost=1 Card=2 Bytes=30) 1 0 TABLE ACCESS (BY INDEX ROWID) OF TEST (Cost=1
12、Card=2 Bytes=30) 2 1 INDEX (RANGE SCAN) OF IDX_TEST_NICK (NON-UNIQUE) (Cost =1 Card=2) 创建 TEST 表 ID 列上的索引，但不对索引进行分析SQL create index idx_test_id on test(id); Index created. SQL select * from test where id=1; Execution Plan - 0 SELECT STATEMENT Optimizer=CHOOSE (Cost=1 Card=1000 Bytes=15 000) 1 0 TABL
13、E ACCESS (BY INDEX ROWID) OF TEST (Cost=1 Card=1000 Bytes=15000) 2 1 INDEX (RANGE SCAN) OF IDX_TEST_ID (NON-UNIQUE) (Cost=1 Card=400) 当条件中即有id，又有 nick 时，因为 nick 上有直方图 ,ORACLE 知道 nick=abc 的值特别的名师资料总结 - - -精品资料欢迎下载 - - - - - - - - - - - - - - - - - - 名师精心整理 - - - - - - - 第 3 页，共 5 页 - - - - - - - - -
14、多，所以不走IDX_TEST_NICK索引 ,走 IDX_TEST_ID上的索引SQL select * from test where id=5 and nick=abc; Execution Plan - 0 SELECT STATEMENT Optimizer=CHOOSE (Cost=1 Card=1000 Bytes=15 000) 1 0 TABLE ACCESS (BY INDEX ROWID) OF TEST (Cost=1 Card=1000 Bytes=15000) 2 1 INDEX (RANGE SCAN) OF IDX_TEST_ID (NON-UNIQUE) (Co
15、st=1 Card=400) 当条件中即有id，又有nick 时，因为 nick 上有直方图 ,ORACLE 知道 nick=def 的值特别的少，所以走IDX_TEST_NICK上的索引 ,不走 IDX_TEST_ID索引SQL select * from test where id=5 and nick=def; Execution Plan - 0 SELECT STATEMENT Optimizer=CHOOSE (Cost=1 Card=1 Bytes=15) 1 0 TABLE ACCESS (BY INDEX ROWID) OF TEST (Cost=1 Card=1 Bytes
16、=15) 2 1 INDEX (RANGE SCAN) OF IDX_TEST_NICK (NON-UNIQUE) (Cost =1 Card=2) 在分析 ID 列后，ORACLE 发现 ID 列的选择度更高，所以不再选择IDX_TEST_NICK索引，而是选择IDX_TEST_ID SQL analyze table test compute statistics for columns size 1 id; Table analyzed. SQL select * from test where id=5 and nick=def; Execution Plan - 0 SELECT S
17、TATEMENT Optimizer=CHOOSE (Cost=1 Card=1 Bytes=7) 1 0 TABLE ACCESS (BY INDEX ROWID) OF TEST (Cost=1 Card=1 Bytes=7) 2 1 INDEX (RANGE SCAN) OF IDX_TEST_ID (NON-UNIQUE) (Cost=1 Card=1) 下面来看另外一种情况，我们删除所有的统计数据，然后在 ID 列上创建唯一索引,在此条件下，只分析表与分析列nick, 我们看到ORACLE 走了正确的执行计划，走了UK_TEST_ID,其实从这里也给我们带来很多的启示：在主键与唯一键
18、约束的列上是否需要直方图的问题?如果在这些列上有像这样的查询where id 100 and id analyze table test delete statistics; Table analyzed. SQL drop index idx_test_id; Index dropped. SQL create unique index uk_test_id on test(id); Index created. 名师资料总结 - - -精品资料欢迎下载 - - - - - - - - - - - - - - - - - - 名师精心整理 - - - - - - - 第 4 页，共 5 页
19、- - - - - - - - - SQL analyze table test compute statistics for table for columns size 2 nick; Table analyzed. SQL select * from test where id=5 and nick=def; Execution Plan - 0 SELECT STATEMENT Optimizer=CHOOSE (Cost=1 Card=1 Bytes=15) 1 0 TABLE ACCESS (BY INDEX ROWID) OF TEST (Cost=1 Card=1 Bytes=
20、15) 2 1 INDEX (UNIQUE SCAN) OF UK_TEST_ID (UNIQUE) (Cost=1 Car d=1) 从以上一系列的实验可以看出，对ORACLE的优化器CBO 来说，表的分析与列的分析才是最重要的， 索引的分析次之。还有我们可以考虑我们的哪些列上需要直方图，对于 bucket的个数问题 ,oracle 的默认值是75 个，所以根据你的应用规则，选择合适的桶数对性能也是有帮助的。因为不必要的桶的个数的大量增加，必然会带来SQL 语句硬解析时产生执行计划的复杂度问题。名师资料总结 - - -精品资料欢迎下载 - - - - - - - - - - - - - - - - - - 名师精心整理 - - - - - - - 第 5 页，共 5 页 - - - - - - - - -