温馨提示！由于新浪微博认证机制调整，您的新浪微博帐号绑定已过期，请重新绑定！&&|&&
LOFTER精选
网易考拉推荐
用微信&&“扫一扫”
将文章分享到朋友圈。
用易信&&“扫一扫”
将文章分享到朋友圈。
阅读(2340)|
用微信&&“扫一扫”
将文章分享到朋友圈。
用易信&&“扫一扫”
将文章分享到朋友圈。
历史上的今天
loftPermalink:'',
id:'fks_085067',
blogTitle:'oracle 如何判断索引是否被使用',
blogAbstract:'索引会增加io，增加空间，也会增加一些数据库的额外开销，对于没有用到得索引，应尽量避免不建，建立了的，把没用的索引根据情况删除。有唯一约束，主键的列不要删除。建议一个表的所以在4-5个左右，不要太多，这是一个参考值，当然还要具体分析，下面是判断的方法：1，分析索引alter index index_n2，产看,used是否为YES，当然要观察一段时间才能确定是否被使用。select table_name,index_name,used from v$object_ 下面是我写的批量分析索引是否被使用的存储过程：1，授予权限grant select on dba_indexes to 用户名;2，创建存储过程create or replace procedure us',
blogTag:'',
blogUrl:'blog/static/',
isPublished:1,
istop:false,
modifyTime:0,
publishTime:4,
permalink:'blog/static/',
commentCount:0,
mainCommentCount:0,
recommendCount:0,
bsrk:-100,
publisherId:0,
recomBlogHome:false,
currentRecomBlog:false,
attachmentsFileIds:[],
groupInfo:{},
friendstatus:'none',
followstatus:'unFollow',
pubSucc:'',
visitorProvince:'',
visitorCity:'',
visitorNewUser:false,
postAddInfo:{},
mset:'000',
remindgoodnightblog:false,
isBlackVisitor:false,
isShowYodaoAd:false,
hostIntro:'',
hmcon:'0',
selfRecomBlogCount:'0',
lofter_single:''
{list a as x}
{if x.moveFrom=='wap'}
{elseif x.moveFrom=='iphone'}
{elseif x.moveFrom=='android'}
{elseif x.moveFrom=='mobile'}
${a.selfIntro|escape}{if great260}${suplement}{/if}
{list a as x}
推荐过这篇日志的人：
{list a as x}
{if !!b&&b.length>0}
他们还推荐了：
{list b as y}
转载记录：
{list d as x}
{list a as x}
{list a as x}
{list a as x}
{list a as x}
{if x_index>4}{break}{/if}
${fn2(x.publishTime,'yyyy-MM-dd HH:mm:ss')}
{list a as x}
{if !!(blogDetail.preBlogPermalink)}
{if !!(blogDetail.nextBlogPermalink)}
{list a as x}
{if defined('newslist')&&newslist.length>0}
{list newslist as x}
{if x_index>7}{break}{/if}
{list a as x}
{var first_option =}
{list x.voteDetailList as voteToOption}
{if voteToOption==1}
{if first_option==false},{/if}&&“${b[voteToOption_index]}”&&
{if (x.role!="-1") },“我是${c[x.role]}”&&{/if}
&&&&&&&&${fn1(x.voteTime)}
{if x.userName==''}{/if}
网易公司版权所有&&
{list x.l as y}
{if defined('wl')}
{list wl as x}{/list}查看: 3926|回复: 4
什么情况下建立联合索引
论坛徽章:1
oracle 在什么情况下建立联合索引比较好？
论坛徽章:19
多条件的and关系查询
是否需要建索引，还的看使用这个查询的频率，因为维护索引的成本是很高的
招聘 : 论坛徽章:2
在谓词条件里包含多字段
认证徽章论坛徽章:99
組合條件查詢，查詢頻率高的情況。。
论坛徽章:3
并且要注意联合索引中列的顺序。
itpub.net All Right Reserved. 北京盛拓优讯信息技术有限公司版权所有　　　　
　北京市公安局海淀分局网监中心备案编号：10　广播电视节目制作经营许可证：编号（京）字第1149号oracle不使用索引的原因有哪些？
oracle 不使用索引的原因有哪些？
今天是，今天开始总结一下oracle不使用索引的原因有哪些。一边学习一边做笔记。
第一种：行数存在差异。
在视图user_tables存在一个num_rows字段，该字段是记录在统计信息收集后所对应对象的行数，在user_tab_columns视图中存在一个num_distinct字段，该字段记录每个字段内不同数值的个数。oracle认为当num_distinct越接近num_rows的时候索引的选择性越好，那么在执行查询的时候越容易使用索引。
第二种：聚簇因子：
什么是聚簇因子？
聚簇因子是衡量索引列数据顺序与表字段数据顺序相似性的一个值。我们都知道在创建的表中一般都是堆表，也就是数据在表中存储是无续的，那么为了更加快速的访问数据，我们通常使用索引进行数据访问，这时候没个索引都有一个聚簇因子，聚簇因子越接近对象的块数，那么选择性越好，越接近表的行数那么选择性越差。
之前听到有个朋友曾经提到这么一个问题&为什么我在测试环境查询一个数据很快和在生产环境查询数据怎么这么慢呢？表结构都一样的，数据也是一样的。&。那么不妨看看聚簇因子是多少。
聚簇因子的查看是从user_ind_statistics视图中： CLUSTERING_FACTOR 表示的。看一下官方介绍：
Indicates the amount of order of the rows in the table based on the values of the index.
If the value is near the number of blocks, then the table is very well ordered. In this case, the index entries in a single leaf block tend to point to rows in the same data blocks.
If the value is near the number of rows, then the table is very randomly ordered. In this case, it is unlikely that index entries in the same leaf block point to rows in the same data blocks.
往往聚簇因子的大小和数据获取的I/o存在一定的相似性。如果聚簇因子大，那么相对的物理或是逻辑（一般是）i/o开销很大，也就是块被频繁反复读取，一致数据获取很慢。
长查询的视图有dba_ind_statistics和dba_tab_statistics
第三种：使用不等条件：
当使用在进行查询数据的时候使用不等条件&&，那么oracle任务这个符号会需要读取大部分的数据块，那么就会跳过使用索引。eg：
SQL& select index_name,table_name,column_name from user_ind_columns where table_name='EMP';
INDEX_NAME
TABLE_NAME
COLUMN_NAME
------------------------------ ------------------------------ ----------------------------------------
SQL& select *
EMPNO ENAME
MGR HIREDATE
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7782 CLARK
7839 09-JUN-81
7934 MILLER
7782 23-JAN-82
7369 SMITH
7902 17-DEC-80
7566 JONES
7839 02-APR-81
7788 SCOTT
7566 19-APR-87
7876 ADAMS
7788 23-MAY-87
7566 03-DEC-81
7499 ALLEN
7698 20-FEB-81
7698 22-FEB-81
7654 MARTIN
7698 28-SEP-81
7698 BLAKE
7839 01-MAY-81
7844 TURNER
7698 08-SEP-81
7900 JAMES
7698 03-DEC-81
14 rows selected.
SQL& set autotrace trace exp
SQL& select * from emp where empno&&7900;
Execution Plan
----------------------------------------------------------
Plan hash value:
--------------------------------------------------------------------------------------------
| Operation
| Name | Rows
| Bytes | Cost (%CPU)| Time
| Pstart| Pstop |
--------------------------------------------------------------------------------------------
0 | SELECT STATEMENT
(0)| 00:00:01 |
PARTITION RANGE ALL|
(0)| 00:00:01 |
TABLE ACCESS FULL | EMP
(0)| 00:00:01 |
--------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter(&EMPNO&&&7900)
SQL& select /*+index(emp EMP_IDX1)*/ * from emp where empno&&7900;
Execution Plan
----------------------------------------------------------
Plan hash value:
---------------------------------------------------------------------------------------------------------------
| Operation
| Bytes | Cost (%CPU)| Time
| Pstart| Pstop |
---------------------------------------------------------------------------------------------------------------
0 | SELECT STATEMENT
(0)| 00:00:01 |
PARTITION RANGE ALL
(0)| 00:00:01 |
TABLE ACCESS BY LOCAL INDEX ROWID| EMP
(0)| 00:00:01 |
INDEX FULL SCAN
| EMP_IDX1 |
(0)| 00:00:01 |
---------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - filter(&EMPNO&&&7900)
可知，当使用&&就会跳过索引，但是我们可以使用hints（提示）让强制使用索引，注意not in或是in 关键字类似&&依然会跳过索引，那么替代办法要么更改sql查询语句，要么使用case when条件，那么也需要在建立函数索引了。
第四种：统计信息过旧
在基于成本的optimizer更具数据进行估计cast，当数据已经被修改（如进行了大量的dml操作），那么统计信息肯定过旧，那么oracle在更加统计信息进行执行计划选择的时候往往可能出现选择错误的执行计划。
在10g开始oracle会有自动收集统计信息的任务在运行。
17:15:22 sys@REPDB&select * from v$
----------------------------------------------------------------
Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bi
PL/SQL Release 10.2.0.5.0 - Production
10.2.0.5.0
Production
TNS for HPUX: Version 10.2.0.5.0 - Production
NLSRTL Version 10.2.0.5.0 - Production
17:15:28 sys@REPDB&select JOB_NAME,LAST_START_DATE,comments from dba_scheduler_
LAST_START_DATE
------------------------------ --------------------------------------------------------------------------- ------------------------------------------------------------
07-2?? -14 03.00.01.146182 &??? +08:00
purge log job
FGR$AUTOPURGE_JOB
file group auto-purge job
GATHER_STATS_JOB
Oracle defined automatic optimizer statistics collection job
AUTO_SPACE_ADVISOR_JOB
auto space advisor maintenance job
MGMT_CONFIG_JOB
06-2?? -14 10.00.02.198895 ???? +08:00
Configuration collection job.
MGMT_STATS_CONFIG_JOB
01-2?? -14 01.01.01.762793 &??? +08:00
OCM Statistics collection job.
RLM$EVTCLEANUP
07-2?? -14 04.18.33.923785 ???? +08:00
RLM$SCHDNEGACTION
07-2?? -14 05.02.43.177946 ???? +08:00
&?????8DD?￡
但在11g该内容开始取消；
SQL& select * from v$
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
PL/SQL Release 11.2.0.4.0 - Production
11.2.0.4.0
Production
TNS for : Version 11.2.0.4.0 - Production
NLSRTL Version 11.2.0.4.0 - Production
SQL& select JOB_NAME,LAST_START_DATE,comments from dba_scheduler_
LAST_START_DATE
------------------------------ -------------------------------------------------- --------------------------------------------------
XMLDB_NFS_CLEANUP_JOB
SM$CLEAN_AUTO_SPLIT_MERGE
07-FEB-14 12.00.00.522780 AM PST8PDT
auto clean job for auto split merge
RSE$CLEAN_RECOVERABLE_SCRIPT
07-FEB-14 12.00.00.242715 AM PST8PDT
auto clean job for recoverable script
FGR$AUTOPURGE_JOB
file group auto-purge job
BSLN_MAINTAIN_STATS_JOB
06-FEB-14 10.39.20.219977 PM -07:00
Oracle defined automatic moving window baseline st
atistics computation job
DRA_REEVALUATE_OPEN_FAILURES
31-JAN-14 06.00.02.807655 AM PST8PDT
Reevaluate open failures for DRA
HM_CREATE_OFFLINE_DICTIONARY
Create offline dictionary in ADR for DRA name tran
ORA$AUTOTASK_CLEAN
06-FEB-14 09.39.19.975070 PM PST8PDT
Delete obsolete AUTOTASK repository data
FILE_WATCHER
File watcher job
06-FEB-14 09.39.19.999313 PM PST8PDT
purge log job
MGMT_STATS_CONFIG_JOB
06-FEB-14 10.39.19.607560 PM -07:00
OCM Statistics collection job.
MGMT_CONFIG_JOB
07-FEB-14 01.01.01.661585 AM -07:00
Configuration collection job.
RLM$SCHDNEGACTION
07-FEB-14 04.32.12.069752 PM +08:00
RLM$EVTCLEANUP
07-FEB-14 01.49.46.407904 AM -07:00
14 rows selected.
那么当统计信息过久的时候通常使用dbms_stats包进行相应对象信息的收集。
第五种：使用通配符查询：
当谓词条件中存在%或是_通配符的时候，oracle会忽略索引，但是可以通过通配符位移解决，如下：
SQL& create index emp_idx2 on emp(ename);
Index created.
SQL& set autotrace trace exp
SQL& select * from emp where ename like '%OT%';
Execution Plan
----------------------------------------------------------
Plan hash value:
--------------------------------------------------------------------------------------------
| Operation
| Name | Rows
| Bytes | Cost (%CPU)| Time
| Pstart| Pstop |
--------------------------------------------------------------------------------------------
0 | SELECT STATEMENT
(0)| 00:00:01 |
PARTITION RANGE ALL|
(0)| 00:00:01 |
TABLE ACCESS FULL | EMP
(0)| 00:00:01 |
--------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter(&ENAME& LIKE '%OT%')
SQL& SELECT * FROM EMP WHERE ENAME LIKE 'SC%OT%';
Execution Plan
----------------------------------------------------------
Plan hash value:
---------------------------------------------------------------------------------------------------------------
| Operation
| Bytes | Cost (%CPU)| Time
| Pstart| Pstop |
---------------------------------------------------------------------------------------------------------------
0 | SELECT STATEMENT
(0)| 00:00:01 |
TABLE ACCESS BY GLOBAL INDEX ROWID| EMP
(0)| 00:00:01 | ROWID | ROWID |
INDEX RANGE SCAN
| EMP_IDX2 |
(0)| 00:00:01 |
---------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access(&ENAME& LIKE 'SC%OT%')
filter(&ENAME& LIKE 'SC%OT%')
第六种：在谓词条件中存在函数
无论在谓词中存在隐式函数转换或是显示函数那么oracle都不会使用索引，对于隐式函数转换例子如下：
https://blog.csdn.net/rhys_oracle/article/details/
第七种：谓词列存在null值。
当在谓词条件中的列存在null值，那么oracle将跳过索引，因为null值不存在索引段中，但是如果是复合索引，其中一列为null，另外一列不为null，那么数据库依然使用索引。
第八种：跳过前导列
这是最后一种不使用索引的原因，那就是如果是在默写列上创建了复合索引，但在谓词条件中没有涉及到前导列，那么将会是跳过索引。&博客分类：
1、表的主键、外键必须有索引；2、数据量超过300的表应该有索引；3、经常与其他表进行连接的表，在连接字段上应该建立索引；4、经常出现在Where子句中的字段，特别是大表的字段，应该建立索引；5、索引应该建在选择性高的字段上；6、索引应该建在小字段上，对于大的文本字段甚至超长字段，不要建索引；7、复合索引的建立需要进行仔细分析；尽量考虑用单字段索引代替：A、正确选择复合索引中的主列字段，一般是选择性较好的字段；B、复合索引的几个字段是否经常同时以AND方式出现在Where子句中？单字段查询是否极少甚至没有？如果是，则可以建立复合索引；否则考虑单字段索引；C、如果复合索引中包含的字段经常单独出现在Where子句中，则分解为多个单字段索引；D、如果复合索引所包含的字段超过3个，那么仔细考虑其必要性，考虑减少复合的字段；E、如果既有单字段索引，又有这几个字段上的复合索引，一般可以删除复合索引；8、频繁进行数据操作的表，不要建立太多的索引；9、删除无用的索引，避免对执行计划造成负面影响；以上是一些普遍的建立索引时的判断依据。一言以蔽之，索引的建立必须慎重，对每个索引的必要性都应该经过仔细分析，要有建立的依据。因为太多的索引与不充分、不正确的索引对性能都毫无益处：在表上建立的每个索引都会增加存储开销，索引对于插入、删除、更新操作也会增加处理上的开销。另外，过多的复合索引，在有单字段索引的情况下，一般都是没有存在价值的；相反，还会降低数据增加删除时的性能，特别是对频繁更新的表来说，负面影响更大。
浏览: 12338 次
(window.slotbydup=window.slotbydup || []).push({
id: '4773203',
container: s,
size: '200,200',
display: 'inlay-fix'mysql 复合索引和单列索引优缺点及什么情况下使用(mysql)
mysql复合索引，可以对多列建立索引，原则是“最左前缀”
1. 为什么是“最左前缀”？
& & & & 比如字段a，b，c建立复合索引相当于a，ab，abc建立3个索引，为什么不能建立bc，c这2个索引？
2. sql查询时，只能使用一个最优索引？
& & & & 比如 select * from tableName where a=1 and b=2 and c=3
& & & & 2种情况下
& & & & 1） a，b，c 建复合索引
& & & & 2） a，b，c 各自建单列索引
& & & & 有啥区别？
3. mysql5.2以上不是有组合索引的功能了吗？如果有多个单列索引，mysql也能像oracle一样使用多个索引组合使用
这个在以前肯定是只能使用一个最优索引，不知道对不对？
引用1. 为什么是“最左前缀”？
& & 比如字段a，b，c建立复合索引相当于a，ab，abc建立3个索引，为什么不能建立bc，c这2个索引？
where a=1 and b=4 and c=10
可以利用到索引 (a,b,c)
可以利用到索引 (a,b,c)
无法利用索引 (a,b,c)
引用2. sql查询时，只能使用一个最优索引？
& & 比如 select * from tableName where a=1 and b=2 and c=3
& & 2种情况下
& & 1） a，b，c 建复合索引
& & 2） a，b，c 各自建单列索
相关问答：
© 2009 ej38.com All Rights Reserved.