应该在ORACLE数据库的哪些字段上建立索引where.docVIP

给数据库建索引的规则 博客分类： Oracle 建立索引常用的规则如下： 1、表的主键、外键必须有索引； 2、数据量超过300的表应该有索引； 3、经常与其他表进行连接的表，在连接字段上应该建立索引； 4、经常出现在Where子句中的字段，特别是大表的字段，应该建立索引； 5、索引应该建在选择性高的字段上； 6、索引应该建在小字段上，对于大的文本字段甚至超长字段，不要建索引； 7、复合索引的建立需要进行仔细分析；尽量考虑用单字段索引代替： A、正确选择复合索引中的主列字段，一般是选择性较好的字段； B、复合索引的几个字段是否经常同时以AND方式出现在Where子句中？单字段查询是否极少甚至没有？如果是，则可以建立复合索引；否则考虑单字段索引； C、如果复合索引中包含的字段经常单独出现在Where子句中，则分解为多个单字段索引； D、如果复合索引所包含的字段超过3个，那么仔细考虑其必要性，考虑减少复合的字段； E、如果既有单字段索引，又有这几个字段上的复合索引，一般可以删除复合索引； 8、频繁进行数据操作的表，不要建立太多的索引； 9、删除无用的索引，避免对执行计划造成负面影响； 以上是一些普遍的建立索引时的判断依据。一言以蔽之，索引的建立必须慎重，对每个索引的必要性都应该经过仔细分析，要有建立的依据。因为太多的索引与不充 分、不正确的索引对性能都毫无益处：在表上建立的每个索引都会增加存储开销，索引对于插入、删除、更新操作也会增加处理上的开销。另外，过多的复合索引， 在有单字段索引的情况下，一般都是没有存在价值的；相反，还会降低数据增加删除时的性能，特别是对频繁更新的表来说，负面影响更大 -ORACLE查询的优化及索引 作者：guojunfeng 提交日期：2006-9-17 1:07:00 申明:下面内容来自/keylife/iblog_show.asp?xid=92561.合理使用索引索引是数据库中重要的数据结构，它的根本目的就是为了提高查询效率。现在大多数的数据库产品都采用IBM最先提出的ISAM索引结构。索引的使用要恰到好处，其使用原则如下： 在经常进行连接，但是没有指定为外键的列上建立索引，而不经常连接的字段则由优化器自动生成索引。 在频繁进行排序或分组（即进行group by或order by操作）的列上建立索引。 在条件表达式中经常用到的不同值较多的列上建立检索，在不同值少的列上不要建立索引。比如在雇员表的“性别”列上只有“男”与“女”两个不同值，因此就无必要建立索引。如果建立索引不但不会提高查询效率，反而会严重降低更新速度。 如果待排序的列有多个，可以在这些列上建立复合索引（compound index）。 使用系统工具。如Informix数据库有一个tbcheck工具，可以在可疑的索引上进行检查。在一些数据库服务器上，索引可能失效或者因为频繁操作而使得读取效率降低，如果一个使用索引的查询不明不白地慢下来，可以试着用tbcheck工具检查索引的完整性，必要时进行修复。另外，当数据库表更新大量数据后，删除并重建索引可以提高查询速度。 (1)在下面两条select语句中: select * from table1 where field1=0; select * from table1 where field1=0 and field1=0,则第一条select语句要比第二条select语句效率高的多，因为第二条select语句的第一个条件耗费了大量的系统资源。 第一个原则：在where子句中应把最具限制性的条件放在最前面。(2)在下面的select语句中: select * from tab where a= and b= and c=; 若有索引index(a,b,c)，则where子句中字段的顺序应和索引中字段顺序一致。 第二个原则：where子句中字段的顺序应和索引中字段顺序一致。以下假设在field1上有唯一索引I1，在field2上有非唯一索引I2。(3) select field3,field4 from tb where field1=sdf 快 select * from tb where field1=sdf 慢，因为后者在索引扫描后要多一步ROWID表访问。(4) select field3,field4 from tb where field1=sdf 快select field3,field4 from tb where field1sdf 慢因为前者可以迅速定位索引。(5) select field3,field4 from tb where field2 like R% 快 select field3,field4 from tb where field2 like %R 慢， 因为后者不使用索引。(6) 使用函数如：select field3,field4 from tb where upper(field2)=RMN不使用索引。如果一个表有两万条记录，建议不使用函数；如果一个表有五万条以上记录，严格禁止使用函数！两万条记录以下没有限制。(7) 空值不在索引中存储，所以 select field3,field4 from tb where field2 isnot null不使用索引。(8) 不等式如 select field3,field4 from tb where field2!=TOM不使用索引。 相似地， select field3,field4 from tb where field2 not in(M,P)不使用索引。(9) 多列索引，只有当查询中索引首列被用于条件时，索引才能被使用。(10) MAX，MIN等函数，如Select max(field2) from tb使用索引。所以，如果需要对字段取max，min，sum等，应该加索引。一次只使用一个聚集函数，如：select “min”=min(field1), “max”=max(field1) from tb 不如：select “min”=(select min(field1) from tb) , “max”=(select max(field1) from tb) (11) 重复值过多的索引不会被查询优化器使用。而且因为建了索引，修改该字段值时还要修改索引，所以更新该字段的操作比没有索引更慢。(12) 索引值过大（如在一个char(40)的字段上建索引），会造成大量的I/O开销（甚至会超过表扫描的I/O开销）。因此，尽量使用整数索引。 Sp_estspace可以计算表和索引的开销。(13) 对于多列索引，order by的顺序必须和索引的字段顺序一致。(14) 在sybase中，如果order by的字段组成一个簇索引，那么无须做order by。记录的排列顺序是与簇索引一致的。(15) 多表联结（具体查询方案需要通过测试得到） where子句中限定条件尽量使用相关联的字段，且尽量把相关联的字段放在前面。 select a.field1,b.field2 from a,b where a.field3=b.field3 1. field3上没有索引的情况下: 对a作全表扫描，结果排序 对b作全表扫描，结果排序 结果合并。 对于很小的表或巨大的表比较合适。 2. field3上有索引 按照表联结的次序，b为驱动表，a为被驱动表 对b作全表扫描 对a作索引范围扫描 如果匹配，通过a的rowid访问(16) 避免一对多的join。如： select tb1.field3,tb1.field4,tb2.field2 from tb1,tb2 where tb1.field2=tb2.field2 and tb1.field2=BU1032 and tb2.field2= aaa 不如： declare a varchar(80)select a=field2 from tb2 where field2=aaa select tb1.field3,tb1.field4,a from tb1 where field2= aaa(16) 子查询 用exists/not exists代替in/not in操作 比较： select a.field1 from a where a.field2 in(select b.field1 from b where b.field2=100) select a.field1 from a where exists( select 1 from b where a.field2=b.field1 and b.field2=100) select field1 from a where field1 not in( select field2 from b) select field1 from a where not exists( select 1 from b where b.field2=a.field1)(17) 主、外键主要用于数据约束，sybase中创建主键时会自动创建索引，外键与索引无关，提高性能必须再建索引。(18) char类型的字段不建索引比int类型的字段不建索引更糟糕。建索引后性能只稍差一点。(19) 使用count(*)而不要使用count(column_name)，避免使用count(distinct column_name)。(20) 等号右边尽量不要使用字段名，如：select * from tb where field1 = field3 (21) 避免使用or条件，因为or不使用索引。2.避免使用order by和group by字句。 因为使用这两个子句会占用大量的临时空间(tempspace),如果一定要使用，可用视图、人工生成临时表的方法来代替。 如果必须使用，先检查memory、tempdb的大小。 测试证明，特别要避免一个查询里既使用join又使用group by，速度会非常慢！3.尽量少用子查询，特别是相关子查询。因为这样会导致效率下降。一个列的标签同时在主查询和where子句中的查询中出现，那么很可能当主查询中的列值改变之后，子查询必须重新查询一次。查询嵌套层次越多，效率越低，因此应当尽量避免子查询。如果子查询不可避免，那么要在子查询中过滤掉尽可能多的行。 4消除对大型表行数据的顺序存取 在嵌套查询中，对表的顺序存取对查询效率可能产生致命的影响。比如采用顺序存取策略，一个嵌套3层的查询，如果每层都查询1000行，那么这个查询就要查询10亿行数据。避免这种情况的主要方法就是对连接的列进行索引。例如，两个表：学生表（学号、姓名、年龄）和选课表（学号、课程号、成绩）。如果两个表要做连接，就要在“学号”这个连接字段上建立索引。 还可以使用并集来避免顺序存取。尽管在所有的检查列上都有索引，但某些形式的where子句强迫优化器使用顺序存取。下面的查询将强迫对orders表执行顺序操作： SELECT FROM orders WHERE (customer_num=104 AND order_num1001) OR order_num=1008 虽然在customer_num和order_num上建有索引，但是在上面的语句中优化器还是使用顺序存取路径扫描整个表。因为这个语句要检索的是分离的行的集合，所以应该改为如下语句： SELECT FROM orders WHERE customer_num=104 AND order_num1001 UNION SELECT FROM orders WHERE order_num=1008 这样就能利用索引路径处理查询。 5避免困难的正规表达式 MATCHES和LIKE关键字支持通配符匹配，技术上叫正规表达式。但这种匹配特别耗费时间。例如：SELECT FROM customer WHERE zipcode LIKE “98_ _ _” 即使在zipcode字段上建立了索引，在这种情况下也还是采用顺序扫描的方式。如果把语句改为SELECT FROM customer WHERE zipcode “98000”，在执行查询时就会利用索引来查询，显然会大大提高速度。 另外，还要避免非开始的子串。例如语句：SELECT FROM customer WHERE zipcode2，3 “80”，在where子句中采用了非开始子串，因而这个语句也不会使用索引。 6使用临时表加速查询 把表的一个子集进行排序并创建临时表，有时能加速查询。它有助于避免多重排序操作，而且在其他方面还能简化优化器的工作。例如： SELECT ，rcvbles.balance，other columns FROM cust，rcvbles WHERE cust.customer_id = rcvlbes.customer_id AND rcvblls.balance0 AND cust.postcode“98000” ORDER BY 如果这个查询要被执行多次而不止一次，可以把所有未付款的客户找出来放在一个临时文件中，并按客户的名字进行排序： SELECT ，rcvbles.balance，other columns FROM cust，rcvbles WHERE cust.customer_id = rcvlbes.customer_id AND rcvblls.balance0 ORDER BY INTO TEMP cust_with_balance 然后以下面的方式在临时表中查询： SELECT FROM cust_with_balance WHERE postcode“98000” 临时表中的行要比主表中的行少，而且物理顺序就是所要求的顺序，减少了磁盘I/O，所以查询工作量可以得到大幅减少。 注意：临时表创建后不会反映主表的修改。在主表中数据频繁修改的情况下，注意不要丢失数据。 7用排序来取代非顺序存取 非顺序磁盘存取是最慢的操作，表现在磁盘存取臂的来回移动。SQL语句隐藏了这一情况，使得我们在写应用程序时很容易写出要求存取大量非顺序页的查询。 有些时候，用数据库的排序能力来替代非顺序的存取能改进查询。-ORACLE索引与高性能SQL介绍什么是索引索引是建立在表的一列或多个列上的辅助对象，目的是加快访问表中的数据；Oracle存储索引的数据结构是B*树，位图索引也是如此，只不过是叶子节点不同B*数索引；索引由根节点、分支节点和叶子节点组成，上级索引块包含下级索引块的索引数据，叶节点包含索引数据和确定行实际位置的rowid。使用索引的目的加快查询速度减少I/O操作消除磁盘排序何时使用索引查询返回的记录数排序表40%非排序表 40%，排序表7%，建议采用并行机制来提高访问速度，DDS；索引访问。最常用的方法，包括索引唯一扫描和索引范围扫描，OLTP；快速完全索引扫描。访问索引中所有数据块，结果相当于全表扫描，可以用索引扫描代替全表扫描，例如：Select serv_id,count(* ) from tg_cdr01 group by serv_id;评估全表扫描的合法性如何实现并行扫描。永久并行化（不推荐）alter table customer parallel degree 8;。单个查询并行化select /*+ full(emp) parallel(emp,8)*/ * from emp;分区表效果明显优化SQL语句排序排序的操作：。order by 子句。group by 子句。select distinct子句。创建索引时。union或minus。排序合并连接如何避免排序。添加索引。在索引中使用distinct子句。避免排序合并连接使用提示进行调整使用提示的原则。语法：/*+ hint */。使用表别名:select /*+ index(e dept_idx)*/ * from emp e。检验提示常用的提示。rule。all_rows。first_rows。use_nl。use_hash-Oracle索引 博客分类： plsql OracleSQLOracle索引（转载）一.B-Tree索引: 1. 选项择索引字段的原则: l 在WHERE子句中最频繁使用的字段 l 联接语句中的联接字段 l 选择高选择性的字段(如果很少的字段拥有相同值,即有很多独特值,则选择性很好) l ORACLE在UNIQUE和主键字段上自动建立索引 l 在选择性很差的字段上建索引只有在这个字段的值分布非常倾斜的情况下下才有益(在这种情况下,某一,两个字段值比其它字值少出现很多) l 不要在很少独特值的字段上建B-TREE索引,在这种情况下,你可以考虑在这些字段上建位图索引.在联机事务处理环境下,所由并发性非常高,索引经常被修改,所以不应该建位图索引 l 不要在经常被修改的字段上建索引.当有UPDATE,DELETE,INSETT操作时,ORACLE除了要更新表的数据外,同时也要更新索引,而且就象更新数据一样,或产生还原和重做条目 l 不要在有用到函数的字段上建索引,ORACLE在这种情况,优化器不会用到索引,除非你建立函数索引 l 可以考虑在外键字段上建索引,这些索引允许当在主表上UPDATE,DELETE操作时,不需要共享子表的锁,这非常适用于在父表和子表上有很多并发的INSERT,UPDATE和DELETE操作的情况 l 当建立索引后,请比较一下索引后所获得的查询性能的提高和UPDATE,DELETE,INSERT操作性能上的损失,比较得失后,再最后决定是否需建立这个索引 2. 选择建立复合索引 复合索引的优点: l 改善选择性:复合索引比单个字段的索引更具选择性 l 减少I/O:如果要查询的字段刚好全部包含在复合索引的字段里,则ORACLE只须访问索引,无须访问表 什么情况下优化器会用到复合索引呢? (a)当SQL语句的WHERE子句中有用到复合索引的领导字段时,ORACLE优化器会考虑用到复合索引来访问. (b)当某几个字段在SQL语句的WHERE子句中经常通过AND操作符联合在一些使用作为过滤谓词,并且这几个字段合在一起时选择性比各自单个字段的选择性要更好时,可能考虑用这几个字段来建立复合索引. (c)当有几个查询语句都是查询同样的几个字段值时,则可以考虑在这几个字段上建立复合索引. 复合索引字段排序的原则: l 确保在WHERE子句中使用到的字段是复合索引的领导字段 l 如果某个字段在WHERE子句中最频繁使用,则在建立复合索引时,考虑把这个字段排在第一位(在CREATE INDEX语句中) l 如果所有的字段在WHERE子句中使用频率相同,则将最具选择性的字段排在最前面,将最不具选择性的字段排在最后面 l 如果所有的字段在WHERE子句中使用频率相同,如果数据在物理上是按某一个字段排序的,则考虑将这个字段放在复合索引的第一位 二.位图索引 什么情况下位图索引能够改善查询的性能呢? l WHERE子句包含多个谓词于中低基数的字段 l 单个的谓词在这些中低基数的字段上选取大量的行 l 已经有位图索引创建于某些或全部的这些中低基数的字段上 l 被查询的表包含很多行 l 可以在单一个表上建立多个位图索引,因此,位图索引能够改善包含冗长WHERE子句的复杂查询的性能,在合计查询和星形模型的联接查询语句中,位图索引也可以提供比较优良的性能 位图索引与B-TREE索引的比较 l 位图索引更节省存储空间 l 位图索引比较适用于数据仓库环境,但不适于联机事务处理环境.在数据仓库环境,数据维护通常上通过批量INSERT和批量UPDATE来完成的,所以索引的维护被延迟直互DML操作结束.举例:当你批量插入1000行数据时,这些插入的行被放置到排序缓存中(SORT BUFFER),然后批处理更新这1000个索引条目,所以,每一个位图段在每一个DML操作中只需更新一次,即使在那个位图段里有多行被更新 l 一个健值的压缩位图是由一个或多个位图段所组成,每一个位图段大约相当于半个BLOCK SIZE那么大,锁的最小粒度是一个位图段,在联机事务处理环境,如果多个事务执行同时的更新(即并发的更新),使用位图索引就会影响UPDATE,INSERT,DELETE性能了 l 一个B-TREE索引的条目只包含一个ROWID,因此,当一个索引条目被锁定,即一行被锁定.但是对于位图索引, 一个索引条目潜在地有可能包含一段ROWID(即某一个范围内的ROWID,有多个ROWID),当一个位图索引条目被锁定时,则这个条目包含的那一段ROWID都被锁定,从而影响并发性.当一个位图段内的ROWID的数量越多时,并发性就越差.虽然如此,对于BULK INSERT,UPDATE和DELETE,位图索引的性能还是比B-TREE索引要好 三,索引和NULL NULL值在索引中是被看做一个独特值的除非当一个索引的两行或多行的NON-NULL值是相等的情况下.在那种情况下,行被看做是相等的,因此,唯一索引不允许行包含空值以怕被看做是相等的.但是,当所有的行都是空值时,这个规则就不适用.ORACLE并不索引所有健值都为NULL的表的行,除非是位图索引或当簇键字段值是NULL时 -当WHERE子句对某一列使用函数时，除非利用这个简单的技术强制索引，否则Oracle优化器不能在查询中使用索引。 通常情况下，如果在WHERE子句中不使用诸如UPPER、REPLACE 或SUBSTRD等函数，就不能对指定列建立特定的条件。但如果使用了这些函数，则会出现一个问题：这些函数会阻碍Oracle优化器对列使用索引，因而与采用索引的情况相比较，查询会花费更多的时间。 庆幸的是，如果在使用函数的这些列中包含了字符型数据，可以用这样一种方法修改查询语句，以达到强制性使用索引，更有效地运行查询。这篇文章介绍了涉及的技术，并说明了在两种典型情况下怎样实现。 大小写混合情况 在讨论由于函数修改了列的内容，如何强制使用索引前，让我们首先看看为什么Oracle优化器在这种情况下不能使用索引。假定我们要搜寻包含了大小写混合的数据，如ADDRESS表的NAME列。因为数据是用户输入的，我们无法使用已经统一改为大写的数据。为了找到每一个名为john的地址，我们使用包含了UPPER子句的查询语句。如下所示： SQL select address from address where upper(name) like JOHN;在运行这个查询语句前，如果我们运行了命令set autotrace on, 将会得到下列结果，其中包含了执行过程： ADDRESS cleveland 1 row selected. Execution Plan SELECT STATEMENT TABLE ACCESS FULL ADDRESS可以看到，在这种情况下，Oracle优化器对ADDRESS 表作了一次完整的扫描，而没有使用NAME 列的索引。这是因为索引是根据列中数据的实际值建立的，而UPPER 函数已经将字符转换成大写,即修改了这些值，因此该查询不能使用这列的索引。优化器不能与索引项比较JOHN，没有索引项对应于JOHN-只有john 。 值得庆幸的是，如果在这种情况下想要强制使用索引，有一种简便的方法：只要在WHERE 子句中增加一个或多个特定的条件，用于测试索引值，并减少需要扫描的行，但这并没有修改原来SQL 编码中的条件。以下列查询语句为例： SQL select address from address where upper(name) like JO% AND (name like J% or name like j%);使用这种查询语句（已设置AUTOTRACE），可得到下列结果： ADDRESS cleveland 1 row selected. Execution Plan SELECT STATEMENT CONCATENATION TABLE ACCESS BY INDEX ROWID ADDRESS INDEX RANGE SCAN ADDRESS_I TABLE ACCESS BY INDEX ROWID ADDRESS INDEX RANGE SCAN ADDRESS_I现在，优化器为WHERE 子句中AND 联结的两个语句中每一个语句确定的范围进行扫描-第二个语句没有引用函数，因而使用了索引。在两个范围扫描后，将运行结果合并。 在这个例子中，如果数据库有成百上千行，可以用下列方法扩充WHERE 子句，进一步缩小扫描范围： select address from address where upper(name) like JOHN AND (name like JO% or name like jo% or name like Jo or name like jO );得到的结果与以前相同，但是，其执行过程如下所示，表明有4个扫描范围。 Execution Plan SELECT STATEMENT CONCATENATION TABLE ACCESS BY INDEX ROWID ADDRESS INDEX RANGE SCAN ADDRESS_I TABLE ACCESS BY INDEX ROWID ADDRESS INDEX RANGE SCAN ADDRESS_I TABL