Oracle索引（Index）介绍使⽤
１．什么是引                                             
索引是建⽴在表的⼀列或多个列上的辅助对象，⽬的是加快访问表中的数据；Oracle存储索引的数据结构是B*树，位图索引也是如此，只不过是叶⼦节点不同B*数索引；索引由根节点、分⽀节点和叶⼦节点组成，上级索引块包含下级索引块的索引数据，叶节点包含索引数据和确定⾏实际位置的rowid。
2.使⽤索引的⽬的                                           
当查询返回的记录数排序表<40%⾮排序表 <7%且表的碎⽚较多（频繁增加、删除）时可以加快查询速度减少I/O操作消除磁盘排序
3.索引的分类及结构                                           
从物理上说，索引通常可以分为：分区和⾮分区索引、常规B树索引、位图（bitmap）索引、翻转（reverse）索引等。其中，B树索引属于最常见的索引，由于我们的这篇⽂章主要就是对B树索引所做的探讨，因此下⾯只要说到索引，都是指B树索引。
B树索引是⼀个典型的树结构，其包含的组件主要是：
1) 叶⼦节点（Leaf node）：包含条⽬直接指向表⾥的数据⾏。
2) 分⽀节点（Branch node）：包含的条⽬指向索引⾥其他的分⽀节点或者是叶⼦节点。
3) 根节点（Root node）：⼀个B树索引只有⼀个根节点，它实际就是位于树的最顶端的分⽀节点。
可以⽤下图⼀来描述B树索引的结构。其中，B表⽰分⽀节点，⽽L表⽰叶⼦节点。
对于分⽀节点块（包括根节点块）来说，其所包含的索引条⽬都是按照顺序排列的（缺省是升序排列，也可以在创建索引时指定为降序排列）。每个索引条⽬（也可以叫做每条记录）都具有两个字段。第⼀个字段表⽰当前该分⽀节点块下⾯所链接的索引块中所包含的最⼩键值；第⼆个字段为四个字节，表⽰所链接的索引块的地址，该地址指向下⾯⼀个索引块。在⼀个分⽀节点块中所能容纳的记录⾏数由数据块⼤⼩以及索引键值的长度决定。⽐如从上图⼀可以看到，对于根节点块来说，包含三条记录，分别为（0 B1）、（500 B2）、（1000
B3），它们指向三个分⽀节点块。其中的0、500和1000分别表⽰这三个分⽀节点块所链接的键值的最⼩值。⽽B1、B2和B3则表⽰所指向的三个分⽀节点块的地址。
对于叶⼦节点块来说，其所包含的索引条⽬与分⽀节点⼀样，都是按照顺序排列的（缺省是升序排列，也可以在创建索引时指定为降序排列）。每个索引条⽬（也可以叫做每条记录）也具有两个字段。第⼀个字段表⽰索引的键值，对于单列索引来说是⼀个值；⽽对于多列索引来说则是多个值组合在⼀起的。第⼆个字段表⽰键值所对应的记录⾏的ROWID，该ROWID是记录⾏在表⾥的物理地址。如果索引是创建在⾮分区表上或者索引是分区表上的本地索引的话，则该ROWID占⽤6个字节；如果索引是创建在分区表上的全局索引的话，则该ROWID 占⽤10个字节。
知道这些信息以后，我们可以举个例⼦来说明如何估算每个索引能够包含多少条⽬，以及对于表来说，所产⽣的索引⼤约多⼤。对于每
个索引块来说，缺省的PCTFREE为10％，也就是说最多只能使⽤其中的90％。同时9i以后，这90％中也不可能⽤尽，只能使⽤其中的87％左右。也就是说，8KB的数据块中能够实际⽤来存放索引数据的空间⼤约为6488（8192×90％×88％）个字节。
假设我们有⼀个⾮分区表，表名为warecountd，其数据⾏数为130万⾏。该表中有⼀个列，列名为goodid，其类型为char（8），那么也就是说该goodid的长度为固定值：8。同时在该列上创建了⼀个B树索引。
在叶⼦节点中，每个索引条⽬都会在数据块中占⼀⾏空间。每⼀⾏⽤2到3个字节作为⾏头，⾏头⽤来存放标记以及锁定类型等信息。同时，在第⼀个表⽰索引的键值的字段中，每⼀个索引列都有1个字节表⽰数据长度，后⾯则是该列具体的值。那么对于本例来说，在叶⼦节点中的⼀⾏所包含的数据⼤致如下图⼆所⽰：
oracle中trunc函数用法
从上图可以看到，在本例的叶⼦节点中，⼀个索引条⽬占18个字节。同时我们知道8KB的数据块中真正可以⽤来存放索引条⽬的空间为6488字节，那么在本例中，⼀个数据块中⼤约可以放360（6488/18）个索引条⽬。⽽对于我们表中的130万条记录来说，则需要⼤约3611（1300000/360）个叶⼦节点块。
⽽对于分⽀节点⾥的⼀个条⽬（⼀⾏）来说，由于它只需保存所链接的其他索引块的地址即可，⽽不
需要保存具体的数据⾏在哪⾥，因此它所占⽤的空间要⽐叶⼦节点要少。分⽀节点的⼀⾏中所存放的所链接的最⼩键值所需空间与上⾯所描述的叶⼦节点相同；⽽存放的索引块的地址只需要4个字节，⽐叶⼦节点中所存放的ROWID少了2个字节，少的这2个字节也就是ROWID中⽤来描述在数据块中的⾏号所需的空间。因此，本例中在分⽀节点中的⼀⾏所包含的数据⼤致如下图三所⽰：
4.怎样建⽴索引                                         
create index<index_name>on<table_name>(<column_name>) [tablespace<tablespace_name>];
1、普通索引
create index index_text_txt on test(txt);
2、唯⼀索引 Oracle ⾃动在表的主键上创建唯⼀索引
create unique index <index_name> on <index_name>(<coiumn_name>);
3、位图索引
作⽤范围及优点：
1、位图索引适合创建在低级数列（重复的数值多，如性别）上
2、减少响应时间
3、节省空间占⽤
create bitmap index<index_name>on<table_name>(<column_name>)
4、组合索引
作⽤范围及优点：
1、组合索引是在表的多个列上创建的索引
2、索引中的顺序是任意的
3、如果SQL语⾔的WHERE⼦句中引⽤了组合索引的所有或⼤多数列，则可以提⾼检索速度
create index<index_name>on<table_name>(<column_name1><column_name2>)
5、基于函数索引
create index<index_name>on<table_name>(<function_name>(<column_name>));
6、反向键索引
create index<index_name>on<table_name>(column_name) reverse;
7.重置索引
alter index<index_name> rebuild;
8.删除索引
drop index<index_name>
5.索引失效细节                                             
1.使⽤不等于操作符(<>, !=)
下⾯这种情况，即使在列dept_id有⼀个索引，查询语句仍然执⾏⼀次全表扫描
select * from dept where staff_num <> 1000;
但是开发中的确需要这样的查询，难道没有解决问题的办法了吗？
有！
通过把⽤ or 语法替代不等号进⾏查询，就可以使⽤索引，以避免全表扫描：上⾯的语句改成下⾯这样的，就可以使⽤索引了。
select * from dept shere staff_num < 1000 or dept_id > 1000;
2.使⽤ is null 或 is not null
使⽤ is null 或is nuo null也会限制索引的使⽤，因为数据库并没有定义null值。如果被索引的列中有很多null，就不会使⽤这个索引（除⾮索引是⼀个位图索引，关于位图索引，会在以后的blog⽂章⾥做详细解释）。在sql语句中使⽤null会造成很多⿇烦。
解决这个问题的办法就是：建表时把需要索引的列定义为⾮空(not null)
3..使⽤函数
如果没有使⽤基于函数的索引，那么where⼦句中对存在索引的列使⽤函数时，会使优化器忽略掉这些索引。下⾯的查询就不会使⽤索引：
select * from staff where trunc(birthdate) = '01-MAY-82';
但是把函数应⽤在条件上，索引是可以⽣效的，把上⾯的语句改成下⾯的语句，就可以通过索引进⾏查。
select * from staff where birthdate < (to_date('01-MAY-82') + 0.9999);
4.⽐较不匹配的数据类型
⽐较不匹配的数据类型也是难于发现的性能问题之⼀。下⾯的例⼦中，dept_id是⼀个varchar2型的字段，在这个字段上有索引，但是下⾯的语句会执⾏全表扫描。
select * from dept where dept_id = 900198;
这是因为oracle会⾃动把where⼦句转换成to_number(dept_id)=900198，就是3所说的情况，这样就限制了索引的使⽤。把SQL语句改为如下形式就可以使⽤索引
select * from dept where dept_id = '900198';
5.使⽤like⼦句
使⽤like⼦句查询时，数据需要把所有的记录都遍历来进⾏判断，索引不能发挥作⽤，这种情况也要尽量避免。
Like 的字符串中第⼀个字符如果是‘%’则⽤不到索引
Column1 like ‘aaa%’ 是可以的
Column1 like ‘%aaa%’⽤不到
6.使⽤ｉｎ
尽管In写法要⽐exists简单⼀些，exists⼀般来说性能要⽐In要⾼的多
⽤In还是⽤Exists的时机
当in的集合⽐较⼩的时候，或者⽤Exists⽆法⽤到选择性⾼的索引的时候，⽤In要好，否则就要⽤Exists
例：select count(*) from person_info where xb in (select xb_id from dic_sex);
Select count(*) from n_acntbasic a where shbxdjm =:a and exists(select 1 from person_info where pi
d=a.pid and …);
Select * from person_info where zjhm=3101….；将会对person_info全表扫描
Select * from person_info where zjhm =‘3101…’才能⽤到索引
假定TEST表的dt字段是date类型的并且对dt建了索引。
如果要查‘20041010’⼀天的数据.下⾯的⽅法⽤不到索引
Select * from test where to_char(dt,’yyyymmdd’) =‘20041010’;
⽽select * from test where dt >=to_date(‘20041010’,’yyyymmdd’) and dt < to_date(‘20041010’,’yyyymmdd’) + 1 将会⽤到索引。
7.如果能不⽤到排序，则尽量避免排序
⽤到排序的情况有
集合操作。Union ,minus ,intersect等，注：union all 是不排序的。
Order by
Group by
Distinct
In 有时候也会⽤到排序
确实要排序的时候也尽量要排序⼩数据量
，尽量让排序在内存中执⾏，有⽂章说，内存排序的速度是硬盘排序的1万倍。

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。