单列索引列与单列索引列为首的组合索引列需要同时建在表上吗？
来源：互联网 发布：软件的特点是什么 编辑：程序博客网 时间：2024/05/17 09:14
在生产库的环境中经常会看到有些开发建索引如下：... idx_p_id_1 ON t1(p_id,bno);... idx_p_id_2 on t1(p_id);而其中p_id为选择率较高的索引列，如1/10，甚至有些直接就是主键索引； 真的需要同时存在这两个索引吗？我们试验测试下；Connected to:Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit ProductionWith the Partitioning, OLAP, Data Mining and Real Application Testing optionsSession altered.SQL> drop table t1 purge;Table dropped.--创建一胶SQL> create table t1 as select * from dba_objects;Table created.将表列object_id的选择率控制在1/10，并且分散开来不存储在1块QL> declare 2 cursor c1 is 3 select object_id from t1 where object_id like '%1' union all 4 select object_id from t1 where object_id like '%2' union all 5 select object_id from t1 where object_id like '%3' union all 6 select object_id from t1 where object_id like '%4' union all 7 select object_id from t1 where object_id like '%5' union all 8 select object_id from t1 where object_id like '%6' union all 9 select object_id from t1 where object_id like '%7' union all 10 select object_id from t1 where object_id like '%8' union all 11 select object_id from t1 where object_id like '%9' union all 12 select object_id from t1 where object_id like '%0' ; 13 type t_c1 is table of c1%rowtype; 14 v_c1 t_c1; 15 c_nt number; 16 n1 number; 17 begin 18 c_nt :=1; 19 open c1; 20 loop 21 n1:=seq01.nextval; 22 fetch c1 bulk collect into v_c1 limit 10; 23 forall idx_id in 1..v_c1.count 24 update t1 set object_id =n1 where object_id=v_c1(idx_id).object_id; 25 if mod(c_nt,2)=0 then 26 commit; 27 end if; 28 c_nt:=c_nt+1; 29 exit when c1%NOTFOUND; 30 end loop; 31 commit; 32 close c1; 33 end; 34 /PL/SQL procedure successfully completed.SQL> SQL> SQL> SQL> select count(distinct object_id) from t1; --选择率接近1/10COUNT(DISTINCTOBJECT_ID)------------------------ 7522SQL> exec dbms_stats.gather_table_stats('AIKI','T1',CASCADE=>TRUE);PL/SQL procedure successfully completed.SQL> select object_id from t1 where rownum<11; OBJECT_ID---------- 221072 221824 222576 223327 224080 224831 225585 218815 219567 22031910 rows selected.--行数据分开存储不会存储在物理相邻的块上，避免全表扫描成本过低SQL> select object_id ,dbms_rowid.rowid_block_number(rowid) bno from t1 where object_id=221072; OBJECT_ID BNO---------- ---------- 221072 523 221072 523 221072 523 221072 523 221072 523 221072 523 221072 523 221072 3308 221072 3308 221072 330810 rows selected.--查看统计信息，上面已存在列OBJECT_ID的索引IDX_T1_IDPlease enter Name of Table Owner (Null = AIKI): Please enter Table Name to show Statistics for: T1***********Table Level***********Table Number Empty Average Chain Average Global User Sample DateName of Rows Blocks Blocks Space Count Row Len Stats Stats Size MM-DD-YYYY--------------- -------------- -------- ------------ ------- -------- ------- ------ ------ -------------- ----------T1 75,212 1,126 0 0 0 97 YES NO 75,212 06-02-2014Column Column Distinct Number Number Global User Sample DateName Details Values Density Buckets Nulls Stats Stats Size MM-DD-YYYY------------------------- ------------------------ ------------ ------- ------- ---------- ------ ------ -------------- ----------OWNER VARCHAR2(30) 30 0 1 0 YES NO 75,212 06-02-2014OBJECT_NAME VARCHAR2(128) 46,184 0 1 0 YES NO 75,212 06-02-2014SUBOBJECT_NAME VARCHAR2(30) 141 0 1 74,718 YES NO 494 06-02-2014OBJECT_ID NUMBER(22) 7,522 0 1 0 YES NO 75,212 06-02-2014DATA_OBJECT_ID NUMBER(22) 9,512 0 1 65,650 YES NO 9,562 06-02-2014OBJECT_TYPE VARCHAR2(19) 45 0 1 0 YES NO 75,212 06-02-2014CREATED DATE 1,172 0 1 0 YES NO 75,212 06-02-2014LAST_DDL_TIME DATE 1,276 0 1 0 YES NO 75,212 06-02-2014TIMESTAMP VARCHAR2(19) 1,341 0 1 0 YES NO 75,212 06-02-2014STATUS VARCHAR2(7) 1 1 1 0 YES NO 75,212 06-02-2014TEMPORARY VARCHAR2(1) 2 1 1 0 YES NO 75,212 06-02-2014GENERATED VARCHAR2(1) 2 1 1 0 YES NO 75,212 06-02-2014SECONDARY VARCHAR2(1) 2 1 1 0 YES NO 75,212 06-02-2014NAMESPACE NUMBER(22) 21 0 1 0 YES NO 75,212 06-02-2014EDITION_NAME VARCHAR2(30) 0 0 0 75,212 YES NO 06-02-2014 B Average AverageIndex Tree Leaf Distinct Number Leaf Blocks Data Blocks Cluster Global User Sample DateName Unique Level Blks Keys of Rows Per Key Per Key Factor Stats Stats Size MM-DD-YYYY--------------- --------- ----- ---- -------------- -------------- ----------- ----------- ------------ ------ ------ -------------- ----------IDX_T1_ID NONUNIQUE 1 151 7,522 75,212 1 1 10,678 YES NO 75,212 06-02-2014Index Column Col ColumnName Name Pos Details--------------- ------------------------- ---- ------------------------IDX_T1_ID OBJECT_ID 1 NUMBER(22)--另外再创建以此索引为首的组合索引，并分测试2索引的开销SQL> create index idx_t1_id2 on t1(object_id,created);Index created.SQL> SQL> exec dbms_stats.gather_table_stats('AIKI','T1',CASCADE=>TRUE);PL/SQL procedure successfully completed. select status from t1 where object_id=99;Execution Plan----------------------------------------------------------Plan hash value: 190799060-----------------------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |-----------------------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 1 | 11 | 2 (0)| 00:00:01 || 1 | TABLE ACCESS BY INDEX ROWID| T1 | 1 | 11 | 2 (0)| 00:00:01 ||* 2 | INDEX RANGE SCAN | IDX_T1_ID | 1 | | 1 (0)| 00:00:01 |-----------------------------------------------------------------------------------------Predicate Information (identified by operation id):--------------------------------------------------- 2 - access("OBJECT_ID"=99)Statistics---------------------------------------------------------- 0 recursive calls 0 db block gets 2 consistent gets 0 physical reads 0 redo size 334 bytes sent via SQL*Net to client 512 bytes received via SQL*Net from client 1 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 0 rows processed select /*+ index(t1,idx_t1_id2) */status from t1 where object_id=1800; Execution Plan----------------------------------------------------------Plan hash value: 426355385------------------------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |------------------------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 1 | 11 | 3 (0)| 00:00:01 || 1 | TABLE ACCESS BY INDEX ROWID| T1 | 1 | 11 | 3 (0)| 00:00:01 ||* 2 | INDEX RANGE SCAN | IDX_T1_ID2 | 1 | | 2 (0)| 00:00:01 |------------------------------------------------------------------------------------------Predicate Information (identified by operation id):--------------------------------------------------- 2 - access("OBJECT_ID"=1800)Statistics---------------------------------------------------------- 0 recursive calls 0 db block gets 2 consistent gets 0 physical reads 0 redo size 334 bytes sent via SQL*Net to client 512 bytes received via SQL*Net from client 1 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 0 rows processed同上面的测试中可看出两组索引的成本是相当的，也就是在生产环境中，可以将第1个索引去除; 而不需要建2个功能类似的索引上去，增大DML的成本； 0 0
- 单列索引列与单列索引列为首的组合索引列需要同时建在表上吗？
- mysql 单列索引与多列索引
- MySQL数据库索引中的单列索引与多列索引
- MySQL数据库索引中的单列索引与多列索引
- 单列索引和多列索引
- 单列索引和多列索引
- 单列索引和多列索引
- mysql单列索引、多列索引的使用
- mysql的单列索引和多列索引
- mysql 单列索引 和 多列缩影
- 主键和聚集索引，单列索引和多列索引
- mysql 多列索引和多个单列索引
- 多列索引和多个单列索引
- 单列索引和多列索引性能对比，索引字段空值情况的处理
- mysql在当前表添加/删除索引 单列多列索引
- Atitit. 单列索引与多列索引 多个条件的查询原理与设计实现
- mysq 单列索引 与 组合索引区别
- 单列索引
- ubuntu13.10下MyEclipse进行swing时乱码问题
- 表达式计算（中缀表达式转后缀前缀表达式）
- centos6下安装部署hadoop2.2
- Jenkins build失败的条件
- WebService原理
- 单列索引列与单列索引列为首的组合索引列需要同时建在表上吗？
- ZOJ-1404
- STM32中NVIC(嵌套向量中断控制)的理解
- 面试题--数组旋转续
- OCP 1Z0 051 QUESTION NO: 15
- Linux环境编程之IPC进程间通信(二)：管道
- Android桌面悬浮窗效果实现，仿360手机卫士悬浮窗效果
- python简单速度测试
- python --enumerate用法
