聚簇因子和执行计划的联系(r3笔记第90天)

jeanron100

发布于 2018-03-15 13:44:53

5870

发布于 2018-03-15 13:44:53

在平时的工作中，可能会碰到一种很奇怪的问题，本来在生产环境中有些sql语句执行没有问题，一个很普通的查询预期走了索引扫面，但是拷贝数据到其它环境之后，就发现却走了全表扫描。或者情况相反，本来出现问题的查询走了全表扫描，我们尝试在测试环境中浮现，但是测试环境中在相同的数据量的情况下，查询却又走了索引扫描，问题无法复现了。出现这种情况的原因比较复杂，涉及很多的原因，其中一个很重要的原因就是聚簇因子的导致的。聚簇因子是一个与索引相关的统计信息，它通过查看表中的数据块来进行计算得到。对于这个问题，可能直接说理论会有些枯燥。可以通过如下的问题来进行说明。

create table t1 as select trunc(rownum/100) id  ,object_name from all_objects where rownum<1000;
create table t2 as select mod(rownum,100) id  ,object_name from all_objects where rownum<1000; 
create index inx_t1 on t1(id);
create index inx_t2 on t2(id);
exec dbms_stats.gather_table_stats(null,'T1',CASCADE=>true);
exec  dbms_stats.gather_table_stats(null,'T2',CASCADE=>true);

查看表t1的数据类似下面的格式。

SQL> select *from t1 where rownum<20;
       ID OBJECT_NAME
----------  ------------------------------
         0 ICOL$
         0  I_USER1
         0 CON$
         0 UNDO$
         0 C_COBJ#
          0 I_OBJ#
         0 PROXY_ROLE_DATA$
         0 I_IND1
         0  I_CDEF2
         0 I_OBJ5
         0 I_PROXY_ROLE_DATA$_1
         0  FILE$
         0 UET$
         0 I_FILE#_BLOCK#
         0  I_FILE1
         0 I_CON1
         0 I_OBJ3
         0  I_TS#
         0 I_CDEF4
19 rows selected.

查看表t2的数据类似下面的格式。

SQL> select *from t2 where rownum<20;
       ID OBJECT_NAME
----------  ------------------------------
         1 ICOL$
         2  I_USER1
         3 CON$
         4 UNDO$
         5 C_COBJ#
          6 I_OBJ#
         7 PROXY_ROLE_DATA$
         8 I_IND1
         9  I_CDEF2
        10 I_OBJ5
        11 I_PROXY_ROLE_DATA$_1
        12  FILE$
        13 UET$
        14 I_FILE#_BLOCK#
        15  I_FILE1
        16 I_CON1
        17 I_OBJ3
        18 I_TS#
         19 I_CDEF4
19 rows selected.

下面的表格能够简要的说明数据的分布。 T1中数据的分布。

0	0	0	0	0
0	0	0	0	0
.	.	.	.	.
1	1	1	1	1
1	1	1	1	1
.	.	.	.	.

T2中数据的分布。

1	2	3	4	5
6	7	8	9	10
11	12	13	14	15
.	.	.	.	.
1	2	3	4	5
6	7	8	9	10

我们来看看同样的查询对应的执行计划。

SQL>select *from t1 where  id=2;
Execution  Plan
----------------------------------------------------------
Plan hash  value: 2808986199
--------------------------------------------------------------------------------------
|  Id  | Operation                   | Name   | Rows  | Bytes | Cost (%CPU)|  Time      |
--------------------------------------------------------------------------------------
|    0 | SELECT STATEMENT            |        |   100 |  1800 |     2   (0)| 00:00:01  |
|   1 |  TABLE ACCESS BY INDEX ROWID| T1     |   100 |  1800 |     2   (0)|  00:00:01 |
|*  2 |   INDEX RANGE  SCAN          | INX_T1 |   100 |       |     1   (0)| 00:00:01  |
--------------------------------------------------------------------------------------
Predicate Information (identified by operation  id):
---------------------------------------------------
  2 -  access("ID"=2)
Statistics
----------------------------------------------------------
           1  recursive calls
          0  db block gets
         17  consistent  gets
          0  physical reads
          0  redo size
       4130   bytes sent via SQL*Net to client
        586  bytes received via SQL*Net from  client
          8  SQL*Net roundtrips to/from client
          0  sorts  (memory)
          0  sorts (disk)
        100  rows processed
SQL>select *from t1 where  id=2; 
10 rows selected.
Execution  Plan
----------------------------------------------------------
Plan hash  value: 1513984157
--------------------------------------------------------------------------
|  Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time      |
--------------------------------------------------------------------------
|    0 | SELECT STATEMENT  |      |    10 |   180 |     4   (0)| 00:00:01 |
|*  1  |  TABLE ACCESS FULL| T2   |     10 |   180 |     4   (0)| 00:00:01  |
--------------------------------------------------------------------------
Predicate Information (identified by operation  id):
---------------------------------------------------
  1 -  filter("ID"=2)
Statistics
----------------------------------------------------------
           1  recursive calls
          0  db block gets
          6  consistent  gets
          0  physical reads
          0  redo size
        820   bytes sent via SQL*Net to client
        520  bytes received via SQL*Net from  client
          2  SQL*Net roundtrips to/from client
          0  sorts  (memory)
          0  sorts (disk)
         10  rows  processed

可以看到一个走了索引扫描，一个走了全表扫描。这个时候我们再来看看聚簇因子。

SQL>select  i.table_name,i.index_name,i.CLUSTERING_FACTOR,t.blocks,i.NUM_ROWS from  user_tables t,user_indexes i where t.table_name=i.table_name and  t.table_name  in ('T1','T2');
TAB INDEX_NAME           CLUSTERING_FACTOR      BLOCKS   NUM_ROWS
--- -------------------- -----------------  ---------- ----------
T1   INX_T1                               4          7        999
T2  INX_T2                              365          7         999

表t2的数据分布比较散，表的聚簇度高，接近于表中的数据，对于id=2,因为数据分布得很开，扫描的数据块就很很多，就很可能走全表扫描。而表中t1的数据聚簇度低，比如要查找id=2的数据，因为这些数据分布比较集中，扫描的数据块就要很少，索引就很可能走索引扫描。对于聚簇因子，可以通过重建索引，重建表，或者重新组织索引来改进，但是从实现的角度来说很困难，毕竟数据的分布情况很难模拟，如果要进行问题的复现和排查还是需要掌握不少的细节,通过备份库来复现问题也是一种思路。

本文参与腾讯云自媒体同步曝光计划，分享自微信公众号。

原始发表：2014-12-20，如有侵权请联系 cloudcommunity@tencent.com 删除

数据库