ORACLE 索引
时间:2016-05-29 02:45 来源:linux.it.net.cn 作者:IT网
大大降低访问I/O的次数，从而可以提高SQL语句的查询性能。一旦创建了索引，Oracle会自动维护和使用它们。如果我们更改了表中的数据Oracle也会自动更新索引数据。索引虽然对查询是有帮助的，但我们要对具体的列进行分析，从而创建不同类型的索引。不恰当的索引，很有可能会适得其反。下面列出的这些情况，应该考虑使用索引：
1、大表
2、主键列(Oracle自动产生索引)
3、唯一值列(Oracle自动产生索引)
4、外键列
5、大表中经常用在WHERE条件中的列
6、大表中经常Order By或者Group By的列
7、至少返回表中20%行的查询
8、不包括NULL值的列
索引的分类：
Oracle提供了两类有很大差异的索引，一类是B Tree索引，一类是位图索引。B Tree索引是二叉树数据结构，每一个节点有两个记录，一个是ROWID(行的位置)和正被索引的值。B Tree索引的数据结构如下图：
…………………………
位图索引比较适合于列的基数比较低(值很少)，2、位图索引：比较适合于具有低相异值(值相对较少)的列，比如一个表有1百万行而值只有1000个，这时应该优先考虑位图索引。位图索引创建的方式如下：
create bitmap index idex_name on table_name(column_name);
但是并不说一定得在低相异值的列上创建位图索引，在其它列上也可以创建，并且效率不一定比其它类型的索引低。假如某列的值只有ACTIVE INACTIVE PENDING COMPLETED，下图描述了该位图索引的结构：
………………
按照索引列是多列还是单列，索引可以分为单列索引和多列索引
1、单列索引：基于单个列所建立的索引,在一张表上可以建立多个单列索引，但是在一个列上只能建立一个单列索引。
2、复合索引：基于两列或两列以上建立的索引。在一张表上可以建立多个复合索引，但要求复合索引列的组合必须不同。比如下列建立的两个索引是合法的：
create index dept_dname_loc on dept(dname,loc);
create index dept_loc_dname on dept(loc,dname);
建立复合索引的列应遵循下列原则：
1、最频繁访问的列，应放在最前面。
按照索引列值是否唯一分类
1、唯一索引：索引列的值必须唯一。 Oracle会为主键约束和唯一约束自动建立唯一索引。通过使用create unique index关键字来创建索引。
2、非唯一索引：索引列的值不唯一。
按照列的特点进行分类
1、反向键索引：我们知道Oracle会自动为表的主键列建立索引，但是在有些时候这个索引可能效率并不会太高。比如主键是通过一个序列生产的连续的数字。基于这种情况我们可以将默认的序列更改成反向序列，以提高索引的效率。假如Oracle产生的索引名为pk_index,通过以下语句更改为反向键索引：
alter index pk_index rebuild reverse；
下列语句将反向键索引更改为普通索引：
alter index pk_index rebuild noreverse;
如果索引不是自动创建的，我们也可以在创建索引时将该索引指定为反向键索引：create index index_name on table_name(column_name) reverse;但是要注意，该列的值是比较密集的。如果我们在该列上用= <>于进行查询时，反向键索引的效率是很高的，相反如果我们使用between查询连续的值时，反向键索引效率并不明显。
2、基于函数的索引：经常以函数或者表达式作为查询条件，可以创建函数或表达式索引。如下代码创建了函数索引：
create index dept_upper_dname on dept(upper(dname));
如下代码创建了表达式索引：
create index emp_sal on emp(sal*2);
查询索引的信息
USER_INDEXES和 USER_IND_COLUMNS
当创建了索引后，Oracle会将索引信息存放到数据字典中。通过查询数据字典视图user_indexes(oracle提供了同义词ind)，可以显示当前用户下的所有索引信息，如：
select index_name,index_type,table_name,uniqueness from ind;
创建了索引后，Oracle还会存储索引和列的信息，可以通过user_ind_columns试图进行查询，如：
select * from user_ind_columns;
下列情况不适合建立索引
1、表中的数据很少
2、建立索引的列，不经常用在where中
3、表中的数据变化频繁
4、查询的结果集经常少于整张表的20%的查询
创建索引所需的权限
1、用户有权限在自己的表上创建、修改或者删除索引。
2、如果用户具有create any index权限，可以在其他用户表上创建索引。即使用户没有alter any index和drop any index权限，用户也可以修改或者删除自己创建的索引。
3、如果用户具有alter any index权限，则可以修改其他用户创建的索引。
4、如果用户具有drop any index权限，则用户可以删除其他用户创建的索引
(责任编辑：IT)
| 大大降低访问I/O的次数，从而可以提高SQL语句的查询性能。一旦创建了索引，Oracle会自动维护和使用它们。如果我们更改了表中的数据Oracle也会自动更新索引数据。索引虽然对查询是有帮助的，但我们要对具体的列进行分析，从而创建不同类型的索引。不恰当的索引，很有可能会适得其反。下面列出的这些情况，应该考虑使用索引： 1、大表 2、主键列(Oracle自动产生索引) 3、唯一值列(Oracle自动产生索引) 4、外键列 5、大表中经常用在WHERE条件中的列 6、大表中经常Order By或者Group By的列 7、至少返回表中20%行的查询 8、不包括NULL值的列 索引的分类： Oracle提供了两类有很大差异的索引，一类是B Tree索引，一类是位图索引。B Tree索引是二叉树数据结构，每一个节点有两个记录，一个是ROWID(行的位置)和正被索引的值。B Tree索引的数据结构如下图： ………………………… 位图索引比较适合于列的基数比较低(值很少)，2、位图索引：比较适合于具有低相异值(值相对较少)的列，比如一个表有1百万行而值只有1000个，这时应该优先考虑位图索引。位图索引创建的方式如下： create bitmap index idex_name on table_name(column_name); 但是并不说一定得在低相异值的列上创建位图索引，在其它列上也可以创建，并且效率不一定比其它类型的索引低。假如某列的值只有ACTIVE INACTIVE PENDING COMPLETED，下图描述了该位图索引的结构： ……………… 按照索引列是多列还是单列，索引可以分为单列索引和多列索引 1、单列索引：基于单个列所建立的索引,在一张表上可以建立多个单列索引，但是在一个列上只能建立一个单列索引。 2、复合索引：基于两列或两列以上建立的索引。在一张表上可以建立多个复合索引，但要求复合索引列的组合必须不同。比如下列建立的两个索引是合法的： create index dept_dname_loc on dept(dname,loc); create index dept_loc_dname on dept(loc,dname); 建立复合索引的列应遵循下列原则： 1、最频繁访问的列，应放在最前面。 按照索引列值是否唯一分类 1、唯一索引：索引列的值必须唯一。 Oracle会为主键约束和唯一约束自动建立唯一索引。通过使用create unique index关键字来创建索引。 2、非唯一索引：索引列的值不唯一。 按照列的特点进行分类 1、反向键索引：我们知道Oracle会自动为表的主键列建立索引，但是在有些时候这个索引可能效率并不会太高。比如主键是通过一个序列生产的连续的数字。基于这种情况我们可以将默认的序列更改成反向序列，以提高索引的效率。假如Oracle产生的索引名为pk_index,通过以下语句更改为反向键索引： alter index pk_index rebuild reverse； 下列语句将反向键索引更改为普通索引： alter index pk_index rebuild noreverse; 如果索引不是自动创建的，我们也可以在创建索引时将该索引指定为反向键索引：create index index_name on table_name(column_name) reverse;但是要注意，该列的值是比较密集的。如果我们在该列上用= <>于进行查询时，反向键索引的效率是很高的，相反如果我们使用between查询连续的值时，反向键索引效率并不明显。 2、基于函数的索引：经常以函数或者表达式作为查询条件，可以创建函数或表达式索引。如下代码创建了函数索引： create index dept_upper_dname on dept(upper(dname)); 如下代码创建了表达式索引： create index emp_sal on emp(sal*2); 查询索引的信息 USER_INDEXES和 USER_IND_COLUMNS 当创建了索引后，Oracle会将索引信息存放到数据字典中。通过查询数据字典视图user_indexes(oracle提供了同义词ind)，可以显示当前用户下的所有索引信息，如： select index_name,index_type,table_name,uniqueness from ind; 创建了索引后，Oracle还会存储索引和列的信息，可以通过user_ind_columns试图进行查询，如： select * from user_ind_columns; 下列情况不适合建立索引 1、表中的数据很少 2、建立索引的列，不经常用在where中 3、表中的数据变化频繁 4、查询的结果集经常少于整张表的20%的查询 创建索引所需的权限 1、用户有权限在自己的表上创建、修改或者删除索引。 2、如果用户具有create any index权限，可以在其他用户表上创建索引。即使用户没有alter any index和drop any index权限，用户也可以修改或者删除自己创建的索引。 3、如果用户具有alter any index权限，则可以修改其他用户创建的索引。 4、如果用户具有drop any index权限，则用户可以删除其他用户创建的索引 (责任编辑：IT) |