1. drop 掉重建 自动创建statistics 表 index 都是2.CF是block的170多倍说明一个index block 可能存170多个index 块3.CF高对于大量数据来说影响没有小量大因为后面的block已经放入cache.drop table tcreate table t (c1 constraint pk primary key, c2 not null, c3 not null, c4 not null) asselect level c1, ceil ( level / 100 ) c2,date2021-01-01 mod ( level, 37 ) c3,lpad ( stuff, 100, f ) c4from dualconnect by level 10000000;create index i231 on t ( c2, c3, c1 );create index i2 on t ( c2 );select /* full(t)*/ * from twhere c2 1;select c2, c3, c1 from t order by c2, c3, c1C2 C3 C11 1 01-Jan-21 372 1 01-Jan-21 743 1 02-Jan-21 14 1 02-Jan-21 385 1 02-Jan-21 756 1 03-Jan-21 27 1 03-Jan-21 398 1 03-Jan-21 769 1 04-Jan-21 310 1 04-Jan-21 4011 1 04-Jan-21 7712 1 05-Jan-21 413 1 05-Jan-21 4114 1 05-Jan-21 7815 1 06-Jan-21 516 1 06-Jan-21 4217 1 06-Jan-21 7918 1 07-Jan-21 619 1 07-Jan-21 4320 1 07-Jan-21 8021 1 08-Jan-21 722 1 08-Jan-21 4423 1 08-Jan-21 8124 1 09-Jan-21 825 1 09-Jan-21 4526 1 09-Jan-21 8227 1 10-Jan-21 928 1 10-Jan-21 4629 1 10-Jan-21 8330 1 11-Jan-21 1031 1 11-Jan-21 4732 1 11-Jan-21 8433 1 12-Jan-21 11create table t (c1 constraint pk primary key, c2 not null, c3 not null, c4 not null) asselect level c1, ceil ( level / 100 ) c2,0 c3,0 c4from dualconnect by level 10000000;select index_name, leaf_blocks, clustering_factor,clustering_factor/leaf_blocksfrom user_indexeswhere table_name T;select a.table_name,a.blocks,a.num_rows from user_tables a where a.table_name T;TABLE_NAME BLOCKS NUM_ROWST 175761 10000000INDEX_NAME LEAF_BLOCKS CLUSTERING_FACTOR1 PK 22132 1750952 I231 41548 73878173 I2 22165 175504TABLE_NAME BLOCKS NUM_ROWS1 T 27611 10000000INDEX_NAME LEAF_BLOCKS CLUSTERING_FACTOR1 PK 22132 274002 I231 33152 274003 I2 22165 27592嗨我有一个有大约百万行和大约30列的表。表名称:-示例表列:- ABCDE……..该表上有多个索引其中两个索引如下所示Create index idx_1 on example_table(A) ; Create index idx_2 on example_table(A,B,C,D);在这里您可以看到列A用于Sigle列索引和多列索引。是否建议在列A上使用单列索引因为它在多列索引中作为第一列存在或者只是该表上的开销-----对于10000行用全表 但是百万行还是用index 的专家解答也许吧。虽然两者都是在列A上搜索的候选者但您可能会发现在多列索引不是的某些情况下使用了单列索引。例如让我们创建一个表并在其上创建一个三列索引:create table t ( c1 constraint pk primary key, c2 not null, c3 not null, c4 not null ) as select level c1, ceil ( level / 100 ) c2, date2021-01-01 mod ( level, 37 ) c3, lpad ( stuff, 100, f ) c4 from dual connect by level 10000; create index i231 on t ( c2, c3, c1 );对索引的前导列的查询返回1% 行但优化器选择全表扫描:set serveroutput off select * from t where c2 1; select * from table(dbms_xplan.display_cursor(null, null, BASIC LAST)); ---------------------------------- | Id | Operation | Name | ---------------------------------- | 0 | SELECT STATEMENT | | | 1 | TABLE ACCESS FULL| T | ----------------------------------但是仅在此列和优化器上创建索引does使用它:create index i2 on t ( c2 ); select * from t where c2 1; select * from table(dbms_xplan.display_cursor(null, null, BASIC LAST)); ---------------------------------------------------- | Id | Operation | Name | ---------------------------------------------------- | 0 | SELECT STATEMENT | | | 1 | TABLE ACCESS BY INDEX ROWID BATCHED| T | | 2 | INDEX RANGE SCAN | I2 | ----------------------------------------------------因此... 这是为什么有几个原因。首先单列较小-其中的数据较少。所以读起来更快。其次多列指数的聚类因子要高得多-高出20倍以上:select index_name, leaf_blocks, clustering_factor from user_indexes where table_name T; INDEX_NAME LEAF_BLOCKS CLUSTERING_FACTOR PK 20 170 I231 37 7250 I2 20 170聚类因子是决定索引效果的关键因素从而决定了优化器选择它的可能性 (更低 更好)。(单)列CF越少聚类因子可能越低。创建尽可能少的索引是一个好主意。但是如该示例所示可能存在创建 “冗余” 索引会导致更快的查询的情况。答案部分Oracle数据库中最普通、最为常用的即为堆表堆表的数据存储方式为无序存储当对数据进行检索的时候非常消耗资源这个时候就可以为表创建索引了。在索引中数据是按照一定的顺序排列起来的。当新建或重建索引时索引列上的顺序是有序的而表上的顺序是无序的这样就存在了差异即表现为聚簇因子Clustering Factor简称CF也称为群集因子或集群因子等本书统一称为聚簇因子。聚簇因子值的大小对CBO判断是否选择相关的索引起着至关重要的作用。在Oracle数据库中聚簇因子是指按照索引键值排序的索引行和存储于对应表中数据行的存储顺序的相似程度也就是说表中数据的存储顺序和某些索引字段顺序的符合程度。CF是基于表上索引列上的一个值每一个索引都有一个CF值。Oracle按照索引块所存储的ROWID来标识相邻索引记录在表块中是否为相同块。Oracle通过如下方法计算CF检查索引块上每一个ROWID的值查看是否前一个ROWID的值与后一个ROWID指向了相同的数据块如果指向了不相同的数据块那么CF的值增加1。当索引块上的每一个ROWID被检查完毕即得到最终的CF值。举个例子比如说索引中有a、b、c、d、e五个记录首先比较a和b是否在同一个块如果不在同一个块那么CF1然后继续比较b和c。同理如果b和c不在同一个块那么CF1这样一直进行下去直到比较了所有的记录才结束最终得到CF的值。注意这里Oracle在比对ROWID的时候并不需要回表去访问相应的表块。具体来说计算CF的算法如下所示1聚簇因子的初始值为1。2Oracle首先定位到目标索引处于最左边的叶子块。3从最左边的叶子块的第一个索引键值所在的索引行开始顺序扫描在顺序扫描的过程中Oracle会比对当前索引行的ROWID和它之前的那个索引行它们是相邻的关系的ROWID如果这两个ROWID并不是指向同一个表块那么Oracle就将聚簇因子的当前值递增1如果这两个ROWID是指向同一个表块那么Oracle就不改变聚簇因子的当前值。注意这里Oracle在比对ROWID的时候并不需要回表去访问相应的表块。4上述比对ROWID的过程会一直持续下去直到顺序扫描完目标索引所有叶子块里的所有索引行。5上述顺序扫描操作完成后聚簇因子的当前值就是索引统计信息中的CLUSTERING_FACTOROracle会将其存储在数据字典里。好的CF值接近于表上的块数而差的CF值则接近于表上的行数。CF值越小相似度越高CF值越大相似度越低。如果CF的值接近块数那么说明表的存储和索引存储排序接近也就是说表中的记录很有序这样在做INDEX RANGE SCAN的时候读取少量的数据块就能得到想要的数据代价比较小。如果CF值接近表记录数那么说明表的存储和索引排序差异很大在做INDEX RANGE SCAN的时候由于表记录分散所以会额外读取多个块代价较高。由于聚簇因子高的索引走索引范围扫描时比相同条件下聚簇因子低的索引要耗费更多的物理I/O所以聚簇因子高的索引走索引范围扫描的成本会比相同条件下聚簇因子低的索引走索引范围扫描的成本高。Oracle选择索引范围扫描的成本可以近似看作是和聚簇因子成正比因此聚簇因子值的大小实际上对CBO判断是否走相关的索引起着至关重要的作用。其实聚簇因子决定着索引回表读的开销。在Oracle数据库中能够降低目标索引的聚簇因子的唯一方法就是对表中数据按照目标索引的索引键值排序后重新存储。需要注意的是这种方法可能会同时增加该表上存在的其它索引的聚簇因子的值。可以通过如下的命令显式的设置聚簇因子的值代码语言javascriptAI代码解释EXEC DBMS_STATS.SET_INDEX_STATS(OWNNAMELHR,INDNAMEIND2,CLSTFCT400000000,NO_INVALIDATEFALSE);CF值可以通过查询视图DBA_INDEXES中的CLUSTERING_FACTOR列来获取。下边的SQL是查询索引的相关信息通过视图DBA_INDEXES、DBA_OBJECTS和DBA_TABLES关联得到可以查询当前索引的大小、行数、创建日期、索引高度和聚簇因子等信息。代码语言javascriptAI代码解释SELECT DI.OWNER INDEX_OWNER, DI.TABLE_OWNER, DI.TABLE_NAME, DI.INDEX_NAME, DI.INDEX_TYPE, DI.UNIQUENESS, (SELECT DECODE(NB.CONSTRAINT_TYPE, P, YES) FROM DBA_CONSTRAINTS NB WHERE NB.CONSTRAINT_NAME DI.INDEX_NAME AND NB.OWNER DI.OWNER AND NB.CONSTRAINT_TYPE P) IS_PRIMARY_KEY, DI.PARTITIONED, (SELECT COUNT(1) FROM DBA_IND_COLUMNS DIC WHERE DIC.INDEX_NAME DI.INDEX_NAME AND DIC.TABLE_NAME DI.TABLE_NAME AND DIC.INDEX_OWNER DI.OWNER) 索引列个数, DI.TABLESPACE_NAME, DI.STATUS, DI.VISIBILITY, (SELECT (SUM(BYTES)) FROM DBA_SEGMENTS ND WHERE SEGMENT_NAME DI.INDEX_NAME AND ND.OWNER DI.OWNER GROUP BY SEGMENT_NAME) INDEX_SIZE_BYTES, DI.DOMIDX_OPSTATUS, DI.DOMIDX_STATUS, DI.PARAMETERS, DI.LAST_ANALYZED, DI.DEGREE, DT.NUM_ROWS TABLE_NUM_ROWS, DT.BLOCKS TABLE_BLOCKS, DI.NUM_ROWS INDEX_NUM_ROWS, DECODE(DI.NUM_ROWS, 0, , ROUND(DI.DISTINCT_KEYS / DI.NUM_ROWS, 2)) SELECTIVITY, DIS.STALE_STATS, DI.BLEVEL 索引的分支层数, DI.BLEVEL 1 索引的高度, DI.LEAF_BLOCKS 叶子结点的个数, DI.DISTINCT_KEYS 唯一值的个数, DI.AVG_LEAF_BLOCKS_PER_KEY 每个KEY的平均叶块个数, DI.AVG_DATA_BLOCKS_PER_KEY 每个KEY的平均数据块数, DI.CLUSTERING_FACTOR 集群因子, DI.COMPRESSION, DI.LOGGING, (SELECT D.CREATED FROM DBA_OBJECTS D WHERE D.OBJECT_NAME DI.INDEX_NAME AND D.OBJECT_TYPE INDEX AND D.OWNER DI.OWNER) INDEX_CREATE FROM DBA_INDEXES DI LEFT OUTER JOIN DBA_IND_STATISTICS DIS ON (DI.OWNER DIS.OWNER AND DI.INDEX_NAME DIS.INDEX_NAME AND DI.TABLE_NAME DIS.TABLE_NAME AND DI.TABLE_OWNER DIS.TABLE_OWNER AND DIS.OBJECT_TYPE INDEX) LEFT OUTER JOIN DBA_TABLES DT ON (DI.TABLE_NAME DT.TABLE_NAME AND DI.TABLE_OWNER DT.OWNER) WHERE DI.INDEX_NAME IDX_T_CF_20160927_LHR;使用PLSQL Developer工具运行查看可以得到如下的结果针对聚簇因子的内容可以做一个实验来深入理解它的作用。建立实验环境如下所示代码语言javascriptAI代码解释CREATE TABLE T_CF_161021_LHR_01 AS SELECT TRUNC(ROWNUM/100) ID ,OBJECT_NAME FROM DBA_OBJECTS WHERE ROWNUM1000; CREATE TABLE T_CF_161021_LHR_02 AS SELECT MOD(ROWNUM,100) ID ,OBJECT_NAME FROM DBA_OBJECTS WHERE ROWNUM1000; CREATE INDEX INX_T1_LHR ON T_CF_161021_LHR_01(ID); CREATE INDEX INX_T2_LHR ON T_CF_161021_LHR_02(ID); EXEC DBMS_STATS.GATHER_TABLE_STATS(USER,T_CF_161021_LHR_01,CASCADE TRUE); EXEC DBMS_STATS.GATHER_TABLE_STATS(USER,T_CF_161021_LHR_02,CASCADE TRUE);表T_CF_161021_LHR_01的数据量分布每个ID对应大约100行记录代码语言javascriptAI代码解释SELECT T.ID,COUNT(1) FROM T_CF_161021_LHR_01 T GROUP BY T.ID;表T_CF_161021_LHR_02的数据量分布每个ID对应大约10行记录代码语言javascriptAI代码解释SELECT T.ID,COUNT(1) FROM T_CF_161021_LHR_02 T GROUP BY T.ID;当这两个表的ID为2时查看其执行计划代码语言javascriptAI代码解释SYSlhrdb SET AUTOT TRACE EXP SYSlhrdb SELECT * FROM T_CF_161021_LHR_01 A WHERE A.ID2; 100 rows selected. Execution Plan ---------------------------------------------------------- Plan hash value: 894988015 -------------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | -------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 100 | 2000 | 2 (0)| 00:00:01 | | 1 | TABLE ACCESS BY INDEX ROWID| T_CF_161021_LHR_01 | 100 | 2000 | 2 (0)| 00:00:01 | |* 2 | INDEX RANGE SCAN | INX_T1_LHR | 100 | | 1 (0)| 00:00:01 | -------------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 2 - access(A.ID2) SYSlhrdb SELECT * FROM T_CF_161021_LHR_02 A WHERE A.ID2; 10 rows selected. Execution Plan ---------------------------------------------------------- Plan hash value: 775989556 ---------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ---------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 10 | 200 | 3 (0)| 00:00:01 | |* 1 | TABLE ACCESS FULL| T_CF_161021_LHR_02 | 10 | 200 | 3 (0)| 00:00:01 | ---------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 1 - filter(A.ID2)可以看到针对表T_CF_161021_LHR_01执行计划选择了索引扫描而针对表T_CF_161021_LHR_02执行计划选择了全表扫描。由于这两个表中都有999行记录而表T_CF_161021_LHR_01返回100行记录表T_CF_161021_LHR_02返回10行记录执行计划应该都选择索引才对但表T_CF_161021_LHR_02却选择了全表扫描。现在来看一下这两个表的聚簇因子情况如下所示代码语言javascriptAI代码解释SYSlhrdb SELECT A.INDEX_NAME, 2 B.NUM_ROWS, 3 B.BLOCKS, 4 A.CLUSTERING_FACTOR 5 FROM USER_INDEXES A, 6 USER_TABLES B 7 WHERE A.INDEX_NAME IN (INX_T1_LHR,INX_T2_LHR) 8 AND A.TABLE_NAME B.TABLE_NAME; INDEX_NAME NUM_ROWS BLOCKS CLUSTERING_FACTOR ------------------------------ ---------- ---------- ----------------- INX_T1_LHR 999 4 4 INX_T2_LHR 999 4 400可以看到T_CF_161021_LHR_01的CF值和表的块数相同说明表的存储和索引存储排序接近数据分布比较集中所以执行计划选择了索引扫描。表T_CF_161021_LHR_02的CF值是表行数的一半CF值较大说明表数据分布比较分散可能需要读取更多的块所以Oracle选择了全表扫描。由此看出聚簇因子和Oracle的执行计划是息息相关的。
