概述
索引在各种关系型数据库系统中都是举足轻重的组成部分，其对于提高检索数据的速度起至关重要的作用。在Oracle中，索引基本分为以下几种：B*Tree索引，反向索引，降序索引，位图索引，函数索引，interMedia全文索引等。
Oracle提供了大量索引选项。知道在给定条件下使用哪个选项对于一个应用程序的性能来说非常重要。一个错误的选择可能会引发死锁，并导致数据库性能急剧下降或进程终止。而如果做出正确的选择，则可以合理使用资源，使那些已经运行了几个小时甚至几天的进程在几分钟得以完成，这样会使您立刻成为一位英雄。这篇文章就将简单的讨论每个索引选项。主要有以下内容：
[1] 基本的索引概念
查询DBA_INDEXES视图可得到表中所有索引的列表，注意只能通过USER_INDEXES的方法来检索模式(schema)的索引。访问USER_IND_COLUMNS视图可得到一个给定表中被索引的特定列。
[2] 组合索引
当某个索引包含有多个已索引的列时，称这个索引为组合（concatented）索引。在Oracle9i引入跳跃式扫描的索引访问方法之前，查询只能在有限条件下使用该索引。比如：表emp有一个组合索引键，该索引包含了empno、ename和deptno。在Oracle9i之前除非在where之句中对第一列（empno）指定一个值，否则就不能使用这个索引键进行一次范围扫描。
特别注意：在Oracle9i之前，只有在使用到索引的前导索引时才可以使用组合索引！
[3] ORACLE ROWID
通过每个行的ROWID，索引Oracle提供了访问单行数据的能力。ROWID其实就是直接指向单独行的线路图。如果想检查重复值或是其他对ROWID本身的引用，可以在任何表中使用和指定rowid列。
[4] 限制索引
限制索引是一些没有经验的开发人员经常犯的错误之一。在SQL中有很多陷阱会使一些索引无法使用。下面讨论一些常见的问题：
4.1 使用不等于操作符（<>、!=）
下面的查询即使在cust_rating列有一个索引，查询语句仍然执行一次全表扫描。
select cust_Id,cust_name
from customers
where cust_rating <> 'aa';
把上面的语句改成如下的查询语句，这样，在采用基于规则的优化器而不是基于代价的优化器（更智能）时，将会使用索引。select cust_Id,cust_name
from customers
where cust_rating < 'aa' or cust_rating > 'aa';
特别注意：通过把不等于操作符改成OR条件，就可以使用索引，以避免全表扫描。
4.2 使用IS NULL 或IS NOT NULL
使用IS NULL 或IS NOT NULL同样会限制索引的使用。因为NULL值并没有被定义。在SQL语句中使用NULL会有很多的麻烦。因此建议开发人员在建表时，把需要索引的列设成NOT NULL。如果被索引的列在某些行中存在NULL值，就不会使用这个索引（除非索引是一个位图索引，关于位图索引在稍后在详细讨论）。
4.3 使用函数
如果不使用基于函数的索引，那么在SQL语句的WHERE子句中对存在索引的列使用函数时，会使优化器忽略掉这些索引。下面的查询不会使用索引（只要它不是基于函数的索引）
select empno,ename,deptno
from emp
where trunc(hiredate)='01-MAY-81';
把上面的语句改成下面的语句，这样就可以通过索引进行查找。
select empno,ename,deptno
from emp
where hiredate<(to_date('01-MAY-81')+0.9999);
4.4 比较不匹配的数据类型
比较不匹配的数据类型也是比较难于发现的性能问题之一。
注意下面查询的例子，account_number是一个VARCHAR2类型，在account_number字段上有索引。下面的语句将执行全表扫描。
select bank_name,address,city,state,zip
from banks
where account_number = 990354;
Oracle可以自动把where子句变成to_number(account_number)=990354，这样就限制了索引的使用,改成下面的查询就可以使用索引：
select bank_name,address,city,state,zip
from banks
where account_number ='990354';
特别注意：不匹配的数据类型之间比较会让Oracle自动限制索引的使用，即便对这个查询执行Explain Plan也不能让您明白为什么做了一次“全表扫描”。
[5] 选择性
使用USER_INDEXES视图，该视图中显示了一个distinct_keys列。比较一下唯一键的数量和表中的行数，就可以判断索引的选择性。选择性越高，索引返回的数据就越少。
[6] 群集因子(Clustering Factor)
Clustering Factor位于USER_INDEXES视图中。该列反映了数据相对于已索引的列是否显得有序。如果Clustering Factor 列的值接近于索引中的树叶块(leaf block)的数目，表中的数据就越有序。如果它的值接近于表中的行数，则表中的数据就不是很有序。
[7] 二元高度(Binary height)
索引的二元高度对把ROWID返回给用户进程时所要求的I/O量起到关键作用。在对一个索引进行分析后，可以通过查询DBA_INDEXES的B-level列查看它的二元高度。二元高度主要随着表的大小以及被索引的列中值的范围的狭窄程度而变化。索引上如果有大量被删除的行，它的二元高度也会增加。更新索引列也类似于删除操作，因为它增加了已删除键的数目。重建索引可能会降低二元高度。
[8] 快速全局扫描
在Oracle7.3后就可以使用快速全局扫描(Fast Full Scan)这个选项。这个选项允许Oracle执行一个全局索引扫描操作。快速全局扫描读取B-树索引上所有树叶块。初始化文件中的DB_FILE_MULTIBLOCK_READ_COUNT参数可以控制同时被读取的块的数目。
[9] 跳跃式扫描
从Oracle9i开始，索引跳跃式扫描特性可以允许优化器使用组合索引，即便索引的前导列没有出现在WHERE子句中。索引跳跃式扫描比全索引扫描要快的多。下面的程序清单显示出性能的差别：
create index skip1 on emp5(job,empno);
index created.
select count(*)
from emp5
where empno=7900;
Elapsed:00:00:03.13
Execution Plan
0 SELECT STATEMENT Optimizer=CHOOSE(Cost=4 Card=1 Bytes=5)
1 0 SORT(AGGREGATE)
2 1 INDEX(FAST FULL SCAN) OF 'SKIP1'(NON-UNIQUE)
Statistics
6826 consistent gets
6819 physical reads
select /*+ index(emp5 skip1)*/ count(*)
from emp5
where empno=7900;
Elapsed:00:00:00.56
Execution Plan
0 SELECT STATEMENT Optimizer=CHOOSE(Cost=6 Card=1 Bytes=5)
1 0 SORT(AGGREGATE)
2 1 INDEX(SKIP SCAN) OF 'SKIP1'(NON-UNIQUE)
Statistics
21 consistent gets
17 physical reads
[10] 索引的类型
B-树索引
位图索引
HASH索引
索引编排表
反转键索引
基于函数的索引
分区索引
本地和全局索引
简要解释：
b*tree index：几乎所有的关系型数据库中都有b*tree类型索引，也是被最多使用的。其树结构与二叉树比较类似，根据rid快速定位所访问的行。
反向索引：反转了b*tree索引码中的字节，是索引条目分配更均匀，多用于并行服务器环境下，用于减少索引叶的竞争。
降序索引：8i中新出现的索引类型，针对逆向排序的查询。
位图索引：使用位图来管理与数据行的对应关系，多用于OLAP系统。
函数索引：这种索引中保存了数据列基于function返回的值，在select * from table where function(column)=value这种类型的语句中起作用。
B*Tree索引
B*Tree索引是最常见的索引结构，默认建立的索引就是这种类型的索引。B*Tree索引在检索高基数数据列（高基数数据列是指该列有很多不同的值）时提供了最好的性能。当取出的行数占总行数比例较小时B-Tree索引比全表检索提供了更有效的方法。但当检查的范围超过表的10%时就不能提高取回数据的性能。B-Tree索引是基于二叉树的，由分支块（branch block）和叶块（leaf block）组成。在树结构中，位于最底层底块被称为叶块，包含每个被索引列的值和行所对应的rowid。在叶节点的上面是分支块，用来导航结构，包含了索引列（关键字）范围和另一索引块的地址。
假设我们要找索引中值为80的行，从索引树的最上层入口开始，定位到大于等于50，然后往左找，找到第2个分支块，定位为75－100，最后再定位到叶块上，找到80所对应的rowid，然后根据rowid去读取数据块获取数据。如果查询条件是范围选择的，比如where column >20 and column <80，那么会先定位到第一个包含20的叶块，然后横向查找其他的叶块，直到找到包含80的块为止，不用每次都从入口进去再重新定位。
反向索引
反向索引是B*Tree索引的一个分支，它的设计是为了运用在某些特定的环境下的。Oracle推出它的主要目的就是为了降低在并行服务器（Oracle Parallel Server）环境下索引叶块的争用。当B*Tree索引中有一列是由递增的序列号产生的话，那么这些索引信息基本上分布在同一个叶块，当用户修改或访问相似的列时，索引块很容易产生争用。反向索引中的索引码将会被分布到各个索引块中，减少了争用。反向索引反转了索引码中每列的字节，通过dump（）函数我们可以清楚得看见它做了什么。举个例子：1，2，3三个连续的数，用dump()函数看它们在Oracle内部的表示方法。
SQL> select 'number',dump(1,16) from dual
union all select 'number',dump(2,16) from dual
union all select 'number',dump(3,16) from dual;
'NUMBE DUMP(1,16)
------ -----------------
number Typ=2 Len=2: c1,2 （1）
number Typ=2 Len=2: c1,3 （2）
number Typ=2 Len=2: c1,4 （3）
再对比一下反向以后的情况：
SQL> select 'number',dump(reverse(1),16) from dual
2 union all select 'number',dump(reverse(2),16) from dual
3 union all select 'number',dump(reverse(3),16) from dual;
'NUMBE DUMP(REVERSE(1),1
------ -----------------
number Typ=2 Len=2: 2,c1 （1）
number Typ=2 Len=2: 3,c1 （2）
number Typ=2 Len=2: 4,c1 （3）
我们发现索引码的结构整个颠倒过来了，这样1，2，3个索引码基本上不会出现在同一个叶块里，所以减少了争用。不过反向索引又一个缺点就是不能在所有使用常规索引的地方使用。在范围搜索中其不能被使用，例如，where column>value,因为在索引的叶块中索引码没有分类，所以不能通过搜索相邻叶块完成区域扫描。
降序索引
降序索引是8i里面新出现的一种索引，是B*Tree的另一个衍生物,它的变化就是列在索引中的储存方式从升序变成了降序，在某些场合下降序索引将会起作用。举个例子，我们来查询一张表并进行排序：
SQL> select * from test where a between 1 and 100 order by a desc，b asc;
已选择100行。
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=2 Card=100 Bytes=400)
1 0 SORT(ORDER BY)(Cost=
2 Card=100 Bytes=400)
2 1 INDEX (RANGE SCAN) OF 'IND_BT' (NON-UNIQUE) (Cost=2 Card=100 Bytes=400)
这里优化器首先选择了一个索引范围扫描,然后还有一个排序的步骤。如果使用了降序索引，排序的过程会被取消。
SQL> create index test.ind_desc on test.testrev(a desc，b asc);
索引已创建。
SQL> analyze index test.ind_desc compute statistics;
索引已分析
再来看下执行路径：
SQL> select * from test where a between 1 and 100 order by a desc，b asc;
已选择100行。
Execution Plan（SQL执行计划，稍后会讲解如何使用）。
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=2 Card=100 Bytes=400)
1 0 INDEX (RANGE SCAN) OF 'IND_DESC' (NON-UNIQUE) (Cost=
2 Card=100 Bytes=400)
我们看到排序过程消失了，这是因为创建降序索引时Oracle已经把数据都按降序排好了。
另外一个需要注意的地方是要设置init.ora里面的compatible参数为8.1.0或以上，否则创建时desc关键字将被忽略。
位图索引
位图索引主要用于决策支持系统或静态数据，不支持行级锁定。位图索引最好用于低cardinality列（即列的唯一值除以行数为一个很小的值，接近零），例如又一个“性别”列，列值有“Male”，“Female”，“Null”等3种，但一共有300万条记录，那么3/3000000约等于0，这种情况下最适合用位图索引。
位图索引可以是简单的（单列）也可以是连接的（多列），但在实践中绝大多数是简单的。在这些列上多位图索引可以与AND 或OR操作符结合使用。位图索引使用位图作为键值，对于表中的每一数据行位图包含了TRUE（1）、FALSE（0）、或NULL 值。位图索引的位图存放在B-Tree结构的页节点中。B-Tree结构使查找位图非常方便和快速。另外，位图以一种压缩格式存放，因此占用的磁盘空间比B-Tree索引要小得多。位图索引的格式如表26-1所示。
表26-1 位图索引的格式
行
值1 2 3 4 5 6 7 8 9 10
Male 1 0 0 0 0 0 0 0 1 1
Female 0 1 1 1 0 0 1 1 0 0
Null 0 0 0 0 1 1 0 0 0 0
如果搜索where gender=?Male?,要统计性别是”Male”的列行数的话，Oracle很快就能从位图中找到共3行即第1，9，10行是符合条件的；如果要搜索where gender=?Male? or gender=?Female?的列的行数的话，也很容易从位图中找到共8行即1，2，3，4，7，8，9，10行是符合条件的。如果要搜索表的值的话，那么Oracle会用内部的转换函数将位图中的相关信息转换成rowid来访问数据块。
函数索引
基于函数的索引也是8i以来的新产物，它有索引计算列的能力，它易于使用并且提供计算好的值，在不修改应用程序的逻辑上提高了查询性能。使用基于函数的索引有几个先决条件：
1）必须拥有QUERY REWRITE（本模式下）或GLOBAL QUERY REWRITE（其他模式下）权限。
2）必须使用基于成本的优化器，基于规则的优化器将被忽略。
3）必须设置以下两个系统参数：
QUERY_REWRITE_ENABLED=TRUE
QUERY_REWRITE_INTEGRITY=TRUSTED
可以通过alter system set,alter session set在系统级或线程级设置，也可以通过在init.ora添加实现。
这里举一个基于函数的索引的例子：
SQL> create index test.ind_fun on test.testindex(upper(a));
索引已创建。
SQL> insert into testindex values('a',2);
已创建1 行。
提交完成。
SQL> select /*+ RULE*/* FROM test.testindex where upper(a)='A';
A B
-- ----------
a 2
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=HINT: RULE
1 0 TABLE ACCESS (FULL) OF 'TESTINDEX'
(优化器选择了全表扫描)
--------------------------------------------------------------------
SQL> select * FROM test.testindex where upper(a)='A';
A B
-- ----------
a 2
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=2 Card=1 Bytes=5)
1 0 TABLE ACCESS (BY INDEX ROWID) OF 'TESTINDEX' (Cost=
2 Card=
1 Bytes=5)
2 1 INDEX (RANGE SCAN) OF 'IND_FUN' (NON-UNIQUE) (Cost=1 Car
d=1)(使用了ind_fun索引)
各种索引的创建方法
（1）*Tree索引。
Create index indexname on tablename(columnname[columnname...])
（2）反向索引。
Create index indexname on tablename(columnname[columnname...]) reverse （3）降序索引。
Create index indexname on tablename(columnname DESC[columnname...]) （4）位图索引。
Create BITMAP index indexname on tablename(columnname[columnname...])
Create index indexname on tablename(functionname(columnname))
注意：创建索引后分析要索引才能起作用。
analyze index indexname compute statistics;
各种索引使用场合及建议
（1）B*Tree索引。
常规索引，多用于oltp系统，快速定位行，应建立于高cardinality列（即列的唯一值除以行数为一个很大的值，存在很少的相同值）。
（2）反向索引。
B*Tree的衍生产物，应用于特殊场合，在ops环境加序列增加的列上建立，不适合做区域扫描。
（3）降序索引。
B*Tree的衍生产物，应用于有降序排列的搜索语句中，索引中储存了降序排列的索引码，提供了快速的降序搜索。
（4）位图索引。
位图方式管理的索引，适用于OLAP（在线分析）和DSS（决策处理）系统，应建立于低cardinality列，适合集中读取，不适合插入和修改，提供比B*Tree索引更节省的空间。
（5）函数索引。
B*Tree的衍生产物，应用于查询语句条件列上包含函数的情况，索引中储存了经过函数计算的索引码值。可以在不修改应用程序的基础上能提高查询效率。
索引什么时候不工作
首先要声明两个知识点：
（1）RBO&CBO。
Oracle有两种执行优化器，一种是RBO(Rule Based Optimizer)基于规则的优化器，这种优化器是基于sql语句写法选择执行路径的；另一种是CBO（Cost Based Optimizer）基于规则的优化器，这种优化器是Oracle根据统计分析信息来选择执行路径，如果表和索引没有进行分析，Oracle将会使用RBO代替CBO；如果表和索引很久未分析，CBO也有可能选择错误执行路径，不过CBO是Oracle发展的方向，自8i版本来已经逐渐取代RBO.
（2）AUTOTRACE。
要看索引是否被使用我们要借助Oracle的一个叫做AUTOTRACE功能,它显示了sql语句的执行路径，我们能看到Oracle内部是怎么执行sql的，这是一个非常好的辅助工具，在sql调优里广泛被运用。我们来看一下怎么运用AUTOTRACE：
①由于AUTOTRACE自动为用户指定了Execution Plan,因此该用户使用AUTOTRACE前必须已经建立了PLAN_TABLE。如果没有的话，请运行utlxplan.sql脚本（它在$ORACLE_HOME/rdbms/admin目录中）。
②AUTOTRACE可以通过运行plustrce.sql脚本(它在$ORACLE_HOME/sqlplus/admin目录中)来设置，用sys用户登陆然后运行plustrce.sql后会建立一个PLUSTRACE角色，然后给相关用户授予PLUSTRACE角色，然后这些用户就可以使用AUTOTRACE功能了。
③AUTOTRACE的默认使用方法是set autotrace on，但是这方法不总是适合各种场合，特别当返回行数很多的时候。Set autotrace traceonly提供了只查看统计信息而不查询数据的功能。
SQL> set autotrace on
SQL> select * from test;
A
----------
1
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE
1 0 TABLE ACCESS (FULL) OF 'TEST' Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
0 consistent gets
0 physical reads
0 redo size
0 bytes sent via SQL*Net to client
0 bytes received via SQL*Net from client
0 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
rows processed
SQL> set autotrace traceonly
SQL> select * from test.test;
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE
1 0 TABLE ACCESS (FULL) OF 'TEST' Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
0 consistent gets
0 physical reads
0 redo size
0 bytes sent via SQL*Net to client
0 bytes received via SQL*Net from client
0 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
rows processed
Hints是Oracle提供的一个辅助用法，按字面理解就是…提示?的意思，确实它起得作用也是提示优化器按它所提供的关键字来选择执行路径，特别适用于sql调整的时候。使用方法如下：
{DELETE|INSERT|SELECT|UPDATE} /*+ hint [text] [hint[text]]... */
具体可参考Oracle SQL Reference。
有了前面这些知识点，接下来让我们来看一下什么时候索引是不起作用的。以下列出几种情况。
（1）类型不匹配时。
SQL> create table test.testindex (a varchar(2),b number);
表已创建。
SQL> create index ind_cola on test.testindex(a);
索引已创建。
SQL> insert into test.testindex values('1',1);
已创建1 行。
SQL> commit;
提交完成。
SQL> analyze table test.testindex compute statistics for all indexes;
表已分析。
SQL> set autotrace on;
SQL> select /*+RULE */* FROM test.testindex where a='1';(使用基于rule的优化器，数据类型匹配的情况下)
A B
-- ----------
1 1
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=HINT: RULE
1 0 TABLE ACCESS (BY INDEX ROWID) OF 'TESTINDEX'
2 1 INDEX (RANGE SCAN) OF 'IND_COLA' (NON-UNIQUE)（使用了索引ind_cola）――――――――――――――――――――――――――――――――――
SQL> select /*+RULE */* FROM test.testindex where a=1;（数据类型不匹配的情况）
A B
-- ----------
1 1
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=HINT: RULE
1 0 TABLE ACCESS (FULL) OF 'TESTINDEX'（优化器选择了全表扫描）
（2）条件列包含函数但没有创建函数索引。
SQL> select /*+ RULE */* FROM test.testindex where upper(a)= 'A';（使用了函数upper()在列a上）;
A B
-- ----------
a 2
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=HINT: RULE
1 0 TABLE ACCESS (FULL) OF 'TESTINDEX'（优化器选择全表扫描）
----------------------------------------------------------
创建基于函数的索引
SQL> create index test.ind_fun on test.testindex(upper(a));
索引已创建。
SQL> insert into testindex values('a',2);
已创建1行。
SQL> commit;
提交完成。
SQL> select /*+ RULE*/* FROM test.testindex where upper(a)='A';
A B
-- ----------
a 2
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=HINT: RULE
1 0 TABLE ACCESS (FULL) OF 'TESTINDEX'
(在RULE优化器下忽略了函数索引选择了全表扫描)
-----------------------------------------------------------
SQL> select * FROM test.testindex where upper(a)
='A';
A B
-- ----------
a 2
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=2 Card=1 Bytes=5)
1 0 TABLE ACCESS (BY INDEX ROWID) OF 'TESTINDEX' (Cost=
2 Card=
1 Bytes=5)
2 1 INDEX (RANGE SCAN) OF 'IND_FUN' (NON-UNIQUE) (Cost=1 Car
d=1)(CBO优化器使用了ind_fun索引)
（3）复合索引中的前导列没有被作为查询条件。
创建一个复合索引
SQL> create index ind_com on test.testindex(a,b);
索引已创建。
SQL> select /*+ RULE*/* from test.testindex where a='1';
A B
-- ----------
1 2
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=HINT: RULE
1 0 INDEX (RANGE SCAN) OF 'IND_COM' (NON-UNIQUE)(条件列表包含前导列时使用索引ind_com) SQL> select /*+ RULE*/* from test.testindex where b=1;
未选定行
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=HINT: RULE
1 0 TABLE ACCESS (FULL) OF 'TESTINDEX'(条件列表不包括前导列是选择全表扫描) -----------------------------------------------------------
（4）CBO模式下选择的行数比例过大，优化器采取了全表扫描。
SQL> select * from test.testindex where a='1';
A B
-- ----------
1 2
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=1 Card=1 Bytes=5)
1 0 TABLE ACCESS (FULL) OF 'TESTINDEX' (Cost=1 Card=1 Bytes=5)
（表一共2行，选择比例为50%,所以优化器选择了全表扫描）――――――――――――――――――――――――――――――――――
下面增加表行数
SQL> declare i number;
2 begin
3 for i in 1 .. 100 loop
4 insert into test.testindex values (to_char(i),i);
5 end loop;
6 end;
7 /
PL/SQL 过程已成功完成。
SQL> commit;
提交完成。
SQL> select count(*) from test.testindex;
COUNT(*)
----------
102
SQL> select * from test.testindex where a='1';
---- ----------
1 1
1 2
Execution Plan
SELECT STATEMENT Optimizer=CHOOSE (Cost=1 Card=1 Bytes=5)
1 0 INDEX (RANGE SCAN) OF 'IND_COM' (NON-UNIQUE) (Cost=1 Card=1 Bytes=5) （表一共102行，选择比例为2/102=2%,所以优化器选择了索引扫描）
（5）CBO模式下表很久没分析，表的增长明显，优化器采取了全表扫描。
SQL> select * from test.testindex where a like '1%';
A B
---- ----------
1 2
1 1
10 10
11 11
12 12
13 13
14 14
15 15
16 16
17 17
18 18
19 19
100 100
已选择13行。
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=1 Card=13 Bytes=52)
1 0 TABLE ACCESS (FULL) OF 'TESTINDEX' (Cost=1 Card=13 Bytes=52)
(表一共102行，选择比例为13/102>10%,优化器选择了全表扫描) ――――――――――――――――――――――――――――――――――
SQL> declare i number;
2 begin
3 for i in 200 .. 1000 loop
4 insert into test.testindex values (to_char(i),i);
5 end loop;
6 end;
7 /
PL/SQL 过程已成功完成。
SQL> commit;
提交完成。
SQL> select count(*) from test.testindex;
COUNT(*)
----------
903
SQL> select * from test.testindex where a like '1%';
A B
---- ----------
1 2
1 1
10 10
11 11
12 12
13 13
14 14
15 15
16 16
17 17
18 18
19 19
100 100
1000 1000
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=1 Card=13 Bytes=52)
1 0 TABLE ACCESS (FULL) OF 'TESTINDEX' (Cost=1 Card=13 Bytes=52) (表一共903行，选择比例为14/903<5%,优化器选择了全表扫描,选择路径是错误的) ―――――――――――――――――――――――――――――
给表做分析
SQL> analyze table test.testindex compute statistics for table for all indexed c olumns for all indexes;
表已分析。
SQL> select * from test.testindex where a like '1%';
A B
---- ----------
1 2
1 1
10 10
100 100
1000 1000
11 11
12 12
13 13
14 14
15 15
16 16
17 17
18 18
19 19
已选择14行。
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=4 Card=24 Bytes=120)
1 0 TABLE ACCESS (BY INDEX ROWID) OF 'TESTINDEX' (Cost=4 Card=
24 Bytes=120)
2 1 INDEX (RANGE SCAN) OF 'IND_COLA' (NON-UNIQUE) (Cost=2 Ca
rd=24)
（经过分析后优化器选择了正确的路径，使用了ind_cola索引）
龙源期刊网 https://www.doczj.com/doc/cb7345073.html, Oracle索引分析与查询优化 作者：崔黎明志远李婧 来源：《数字技术与应用》2016年第07期 摘要：Oracle是目前国内在大型数据存储中用得比较多的一种关系型数据库，磁盘阵列技术（RAID）和集群技术（RAC）的运用，使Oracle在处理数据效率和数据安全上有非常大的提高，在国内交通、电力，通信和金融等重要领域都有广泛的用途。本文基于Oracle 11g版本，对Rowid和索引的原理机制做出分析，并论述利用这些原理对大型数据表查询的优化。 关键词：Oracle Rowid 索引查询 中图分类号：TP311.13 文献标识码：A 文章编号：1007-9416（2016）07-0234-02 在逻辑上，Oracle是由多个表空间构成的，在新建一个表空间的时候，必须指定存储的文件，可以指向多个存储在不同磁盘上的数据。表空间下面分为段、区、块。新建一张表的时候，Oracle数据库就会为它创建一个段。所谓区就是指连续的块（block）构成的空间，一般区包含8个空块，block是Oracle数据库最小的数据空间，一般为8k或16k，当开始往这个表中插入数据的时候，Oralce数据库会自动为这个表分配一个区，并把数据不断往此区进行填充，当数据填满此区后，Oralce数据库会重新为该表分配一个区而不是一个段。实际上，对于我们不同的查询过程中，就是通过一些谓词过滤条件，从对应的数据块中获取正确的一行数据或多行数据。如何快速定位到该数据行，是一个数据库学习者不断探索的方向。 1 Rowid 1.1 简介 从字面上理解是行标识的意思，它是Oracle数据库中数据表的一个伪列，用于存放该表中每一行数据的地址，在8i版本之前，Oracle采用受限制的rowid，它是由数据文件编号，块编号和数据在该块内的偏移量这三个部分构成，长度为6个字节，因此在8i之前的版本中，每 个数据库最多可以包含1022个文件，每个文件最多能有4m个数据块，而每个数据库最多能 存储64k条记录。为了突破长度的限制和解决其他一些缺陷，Oracle数据库引入了这样一个概念：相对文件号。它的主要特点是改变之前rowid中数据文件编号是整个数据库范围组成的表空间，即文件编号为5的文件不再是数据库中编号为5的数据文件，而是表空间中对应编号的数据文件。如下图1所示为Oracle11g中一个普通表的rowid。 从该图1中可以看到rowid共有18位，分为四部分，格式为：AAAAAABBBCCCCCCDDD，其中AAAAAA六位表示dataobjectid，根据这个id可以确定该行数据在哪个段中；BBB三位表示相对文件号，通过这个字段号可以用来确定该行数据的绝 对文件号；CCCCCC六位表示datablocknumber，它是相对于datafile的编号；最后三位DDD
oracle索引基本分类法分类 逻辑上： Single column 单行索引 Concatenated 多行索引 Unique 唯一索引 NonUnique 非唯一索引 Function-based函数索引 Domain 域索引 物理上： Partitioned 分区索引 NonPartitioned 非分区索引 B-tree： Normal 正常型B树 Rever Key 反转型B树 Bitmap 位图索引 索引结构： B-tree： 适合与大量的增、删、改（OLTP）； 不能用包含OR操作符的查询； 适合高基数的列（唯一值多） 典型的树状结构； 每个结点都是数据块； 大多都是物理上一层、两层或三层不定，逻辑上三层； 叶子块数据是排序的，从左向右递增； 在分支块和根块中放的是索引的范围； Bitmap: 适合与决策支持系统； 做UPDATE代价非常高； 非常适合OR操作符的查询； 基数比较少的时候才能建位图索引； 树型结构： 索引头 开始ROWID，结束ROWID（先列出索引的最大范围） BITMAP 每一个BIT对应着一个ROWID，它的值是1还是0，如果是1，表示着BIT 对应的ROWID有值； =============================================== ======================
Oracle的索引主要包含两类：BTree和位图索引。默认情况下大多使用Btree 索引，该索引就是通常所见唯一索引、聚簇索引等等，Btree用在OLTP，加快查询速度。位图索引是Oracle的比较引人注目的地方，其主要用在OLAP（联机数据分析）方面，也就是数据仓库方面用到，目的是在加快查询速度是，节省存储空间。通常情况下，索引都要耗费比较大的存储空间，位图采用了压缩技术实现磁盘空间缩减。Btree用在高基数（即列的数据相异度大），位图用在低基数列。位图索引的基本原理是在索引中使用位图而不是列值。通常在事实表和维表的键之间有很低的集的势（cardinality），使用位图索引，存储更为有效，与B*Tree 索引比较起来，只需要更少的存储空间，这样每次读取可以读到更多的记录，而且与B*Tree索引相比,位图索引将比较,连接和聚集都变成了位算术运算,大大减少了运行时间，从而得到性能上的极大的提升。 在Oracle中如何合理的使用位图索引？以下的几个事项应该考虑。 *如果要使用位图索引，初始化参数STAR_TRANSFORMATION_ENABLED 应该设置为TRUE. *优化模式应该是CBO。对于数据仓库的环境中，总是应该考虑使用CBO （COST-BASEDOPTIMIZER）。 *位图索引应该建立在每一个事实表的外键列上。(这只是一个一般的规则.) 此外，对于数据表中的cardinality如何客观的确定也是一个问题，一万条数据中只包含3个值的集和算是低的了，那么一亿条记录中包含3万条记录算不算低的呢？对于这样的情况，建议几行一下数据的模拟测试，一般来说，在数据仓库环境中，位图索引的性能要好于B*Tree索引。还要注意位图索引不是为OLTP 数据库设计的，不应该在OLTP数据库中大量的使用它，尤其是对那些有更新操作的表。 ==================================================================== ========== B*Tree索引 B*Tree索引是最常见的索引结构，默认建立的索引就是这种类型的索引。B*Tree索引在检索高基数数据列（高基数数据列是指该列有很多不同的值）时提供了最好的性能。当取出的行数占总行数比例较小时B-Tree索引比全表检索提供了更有效的方法。但当检查的范围超过表的10%时就不能提高取回数据的性能。B-Tree索引是基于二叉树的，由分支块（branch block）和叶块（leaf block）组成。在树结构中，位于最底层底块被称为叶块，包含每个被索引列的值和行所对应的rowid。在叶节点的上面是分支块，用来导航结构，包含了索引列（关键字）范围和另一索引块的地址，如图26-1所示。
Oracle数据库中的索引详解 一、ROWID的概念 存储了row在数据文件中的具体位置：64位编码的数据，A-Z, a-z, 0-9, +, 和/，row在数据块中的存储方式 SELECT ROWID, last_name FROM hr.employees WHERE department_id = 20; 比如：OOOOOOFFFBBBBBBRRR OOOOOO：data object number, 对应dba_objects.data_object_id FFF：file#, 对应v$datafile.file# BBBBBB：block# RRR：row# Dbms_rowid包 SELECT dbms_rowid.rowid_block_number('AAAGFqAABAAAIWEAAA') from dual; 具体到特定的物理文件 二、索引的概念 1、类似书的目录结构 2、Oracle 的“索引”对象，与表关联的可选对象，提高SQL查询语句的速度 3、索引直接指向包含所查询值的行的位置，减少磁盘I/O 4、与所索引的表是相互独立的物理结构 5、Oracle 自动使用并维护索引，插入、删除、更新表后，自动更新索引 6、语法：CREA TE INDEX index ON table (column[, column]...); 7、B-tree结构(非bitmap)： [一]了解索引的工作原理： 表：emp
目标：查询Frank的工资salary 建立索引：create index emp_name_idx on emp(name);
[试验]测试索引的作用： 1. 运行/rdbms/admin/utlxplan 脚本 2. 建立测试表 create table t as select * from dba_objects; insert into t select * from t; create table indextable as select rownum id,owner,object_name,subobject_name, object_id,data_object_id,object_type,created from t; 3. set autotrace trace explain 4. set timing on 5. 分析表，可以得到cost 6. 查询object_name=’DBA_INDEXES’ 7. 在object_name列上建立索引 8. 再查询 [思考]索引的代价： 插入，更新 三、唯一索引 1、何时创建：当某列任意两行的值都不相同 2、当建立Primary Key(主键)或者Unique constraint(唯一约束)时，唯一索引将被自动建立 3、语法：CREA TE UNIQUE INDEX index ON table (column); 4、演示
索引是对数据库表中一列或多列的值进行排序的一种结构，使用索引可快速访问数据库表中的特定信息。 例如这样一个查询：SELECT * FROM TABLE1 WHERE ID = 44。如果没有索引，必须遍历整个表，直到ID等于44的这一行被找到为止；有了索引之后(必须是在ID这一列上建立的索引)，直接在索引里面找44（也就是在ID这一列找），就可以得知这一行的位置，也就是找到了这一行。可见，索引是用来定位的。 建立索引的目的是加快对表中记录的查找或排序。为表设置索引要付出代价的：一是增加了数据库的存储空间，二是在插入和修改数据时要花费较多的时间(因为索引也要随之变动)。 虽然建立索引能加快对表中记录的查询或者排序速度，但是并不是索引建得越多越好，这就需要我们了解使用索引过程中，索引的一些优点以及缺陷： 使用索引的好处： 创建索引可以大大提高系统的性能： 第一，通过创建唯一性索引，可以保证数据库表中每一行数据的唯一性。 第二，可以大大加快数据的检索速度，这也是创建索引的最主要的原因。 第三，可以加速表和表之间的连接，特别是在实现数据的参考完整性方面特别有意义。 第四，在使用分组和排序子句进行数据检索时，同样可以显著减少查询中分组和排序的时间。 第五，通过使用索引，可以在查询的过程中，使用优化隐藏器，提高系统的性能。使用索引的一些不足： 第一，创建索引和维护索引要耗费时间，这种时间随着数据量的增加而增加。第二，索引需要占物理空间，除了数据表占数据空间之外，每一个索引还要占一定的物理空间，如果要建立聚簇索引，那么需要的空间就会更大。 第三，当对表中的数据进行增加、删除和修改的时候，索引也要动态的维护，这样就降低了数据的维护速度。 索引是建立在数据库表中的某些列的上面。在创建索引的时候，应该考虑在哪些列上可以创建索引，在哪些列上不能创建索引。一般来说，应该在这些列上创建索引： ?在经常需要搜索的列上，可以加快搜索的速度；在作为主键的列上，强制该列的唯一性和组织表中数据的排列结构； ?在经常用在连接的列上，这些列主要是一些外键，可以加快连接的速度； ?在经常需要根据范围进行搜索的列上创建索引，因为索引已经排序，其指定的范围是连续的； ?在经常需要排序的列上创建索引，因为索引已经排序，这样查询可以利用索引的排序，加快排序查询时间； ?在经常使用在WHERE子句中的列上面创建索引，加快条件的判断速度。 同样，对于有些列不应该创建索引。一般来说，不应该创建索引的的这些列具有下列特点： ?对于那些在查询中很少使用或者参考的列不应该创建索引。这是因为，既然这些列很少使用到，因此有索引或者无索引，并不能提高查询速度。相反，由于增加了索引，反而降低了系统的维护速度和增大了空间需求。 ?对于那些只有很少数据值的列也不应该增加索引。这是因为，由于这些列的取值很少，例如人事表的性别列，在查询的结果中，结果集的数据行占了
Oracle索引简单介绍 更多Oracle资料下载，请收藏https://www.doczj.com/doc/cb7345073.html,
一、引言 对数据库索引的关注从未淡出我的们的讨论，那么数据库索引是什么样的？聚集索引与非聚集索引有什么不同？希望本文对各位同仁有一定的帮助。有不少存疑的地方，诚心希望各位不吝赐教指正，共同进步。 二、B-Tree 我们常见的数据库系统，其索引使用的数据结构多是B-Tree或者B+Tree。例如，MsSql使用的是B+Tree，Oracle及Sysbase使用的是B-Tree。所以在最开始，简单地介绍一下 B-Tree。 B-Tree不同于Binary Tree（二叉树，最多有两个子树），一棵M阶的B-Tree满足以下条件：1）每个结点至多有M个孩子； 2）除根结点和叶结点外，其它每个结点至少有M/2个孩子； 3）根结点至少有两个孩子（除非该树仅包含一个结点）； 4）所有叶结点在同一层，叶结点不包含任何关键字信息； 5）有K个关键字的非叶结点恰好包含K+1个孩子； 另外，对于一个结点，其内部的关键字是从小到大排序的。以下是B-Tree（M=4）的样例：
对于每个结点，主要包含一个关键字数组Key[]，一个指针数组（指向儿子）Son[]。在B-Tree 内，查找的流程是：使用顺序查找（数组长度较短时）或折半查找方法查找Key[]数组，若找到关键字K，则返回该结点的地址及K在Key[]中的位置；否则，可确定K在某个Key[i]和Key[i+1]之间，则从Son[i]所指的子结点继续查找，直到在某结点中查找成功；或直至找到叶结点且叶结点中的查找仍不成功时，查找过程失败。 接着，我们使用以下图片演示如何生成B-Tree（M=4，依次插入1~6）： 从图可见，当我们插入关键字4时，由于原结点已经满了，故进行分裂，基本按一半的原则进行分裂，然后取出中间的关键字2，升级（这里是成为根结点）。其它的依类推，就是这样一个大概的过程。 三、数据库索引 1．什么是索引 在数据库中，索引的含义与日常意义上的“索引”一词并无多大区别（想想小时候查字典），它是用于提高数据库表数据访问速度的数据库对象。 A）索引可以避免全表扫描。多数查询可以仅扫描少量索引页及数据页，而不是遍历所有数据页。B）对于非聚集索引，有些查询甚至可以不访问数据页。 C）聚集索引可以避免数据插入操作集中于表的最后一个数据页。
Oracle创建索引 Oracle在创建索引时要遵循以下的原则： ●平衡查询和DML的需要。在易挥发（DML操作频繁）的表上尽量减少索引的数量， 因为索引虽然加快了查询的速度，但却降低了DML操作速度。 ●将其放入单独的表空间，不要与表、临时段或还原（回滚）段放在一个表空间，因为索 引段会与这些段竞争输入/输出（I/O）。 ●使用统一的EXTENT尺寸：数据块尺寸的5倍，或表空间的MINIMUM EXTENT的尺 寸。这样做的目的是为了减少系统的转换时间。 ●对大索引可以考虑使用NOLOGGING。这样做的目的是通过减少REDO操作来提高系 统的效率，但是如果一旦系统发生崩溃，则该索引一般是无法进行完全灰度的。不过问题也不是很大，因为真正的数据还在表中，所以可以通过重建该索引来恢复与之前完全相同的效果。 ●索引的INITRANS参数通常应该比相对应表的高。以为索引项要比表中的数据行小的 多，所以一个数据块可以存放更多的索引项（记录）。 创建索引的命令格式： CREATE (UNIQUE|BITMAP) INDEX [用户名.]索引名 ON [用户名.]表名 （列名[ASC | DESC] [,列名[ASC| DESC ] ]…） [TABLESPACE 表空间名] [PCTFREE 正整型数] [INITRANS 正整型数] [MAXTRANS 正整型数] [存储子句] [LOGGING | NOLOGGING] [NOSORT] 其中， ●UNIQUE：说明该索引是唯一索引，默认是非唯一的 ●ASC：说明所创建的索引为升序 ●DESC：说明所创建的索引为降序 ●表空间名：说明将要创建的索引的表空间名 ●PCTFREE：在创建索引时每一个块中预留的空间 ●INITRANS：在每一个块中预分配的事物记录数，默认值为2 ●MAXTRANS：在每一个块中可以分配的事物记录数的上限，默认为255 ●存储子句：说明在索引中EXTENTS怎样分配 ●LOGGING：说明在创建索引是和以后的索引操作中要记录联机重做日志文件（默认）●NOLOGGING：说明索引的创建和一些数据装入操作将不记录联机重做日志文件 ●NOSORT：数据库中所存的数据行已经按升序排好，因此在创建索引时不需要再排序了●PCTUSED：在索引中不能说明该参数。因为索引记录必须以正确的顺序存放，所以用 户不能控制何时向索引块中插入索引数据行 使用数据字典的dba_indexes获取有关scott用户的索引基于表、所在的表空间、索引的类型和索引的状态等信息。 SQL> select index_name,table_name,tablespace_name,index_type, uniqueness,status from dba_indexes where owner = 'SCOTT' AND INDEX_NAME not like 'SYS%'
Oracle视图中建立索引的注意事项 在视图上创建索引需要三个条件： 一、视图必须绑定到架构。 要做到这点，在 CREATE VIEW 语句中，必须加上 WITH SCHEMABINDING，如果是使用企业管理器，则在设计界面的空白处点击右键，属性，选中“绑定到架构”。 二、索引必须是唯一索引。 要做到这点，在 CREATE INDEX 中必须指定 UNIQUE。 三、索引必须是聚集索引。 要做到这点，在 CREATE INDEX 中必须指定 CLUSTERED。 例： CREATE VIEW viewFoo WITH SCHEMABINDING AS SELECT id... CREATE UNIQUE CLUSTERED INDEX index_viewFoo ON viewFoo(id) 在视图上创建聚集索引之前，该视图必须满足下列要求： 当执行 CREATE VIEW 语句时，ANSI_NULLS 和 QUOTED_IDENTIFIER 选项必须设置为 ON。OBJECTPROPERTY 函数通 过 ExecIsAnsiNullsOn 或 ExecIsQuotedIdentOn 属性为视图报告此信息。 为执行所有 CREATE TABLE 语句以创建视图引用的表，ANSI_NULLS 选项必须设置为 ON。 视图不能引用任何其它视图，只能引用基表。 视图引用的所有基表必须与视图位于同一个数据库中，并且所有者也与视图相同。 必须使用 SCHEMABINDING 选项创建视图。SCHEMABINDING 将视图绑定到基础基表的架构。 必须已使用 SCHEMABINDING 选项创建了视图中引用的用户定义的函数。 表和用户定义的函数必须由 2 部分的名称引用。不允许使用 1 部分、3 部分和 4 部分的名称。
在Oracle数据库中要合理的创建索引有如下几个要求。 一、根据表的大小来创建索引。 虽然给表创建索引，可以提高查询的效率。但是数据库管理员需要注意的是，索引也需要一定的开销的。为此并不是说给所有的表都创建索引，那么就可以提高数据库的性能。这个认识是错误的。恰恰相反，如果不管三七二十一，给所有的表都创建了索引，那么其反而会给数据库的性能造成负面的影响。因为此时滥用索引的开销可能已经远远大于由此带来的性能方面的收益。所以笔者认为，数据库管理员首先需要做到，为合适的表来建立索引，而不是为所有的表建立索引。 一般来说，不需要为比较小的表创建索引。如在一个ERP系统的数据库中，department表用来存储企业部门的信息。一般企业的部分也就十几个，最多不会超过一百个。这100条记录对于人来说，可能算是比较多了。但是对于计算机来说，这给他塞塞牙缝都还不够。所以，对类似的小表没有必要建立索引。因为即使建立了索引，其性能也不会得到很大的改善。相反索引建立的开销，如维护成本等等，要比这个要大。也就是说，付出的要比得到的多，显然违反常理。 另外，就是对于超大的表，也不一定要建立索引。有些表虽然比较大，记录数量非常的多。但是此时为这个表建立索引并一定的合适。如系统中有一张表，其主要用来保存数据库中的一些变更信息。往往这些信息只给数据库管理员使用。此时为这张表建立索引的话，反而不合适。因为这张表很少用到，只有在出问题的时候才需要查看。其次其即使查看，需要查询的纪录也不会很多，可能就是最近一周的更新记录等等。对于对于一些超大的表，建立索引有时候往往不能够达到预计的效果。而且在打表上建立索引，其索引的开销要比普通的表大的多。那么到底是否给大表建立索引呢?笔者认为，主要是看两个方面的内容。首先是需要关注一下，在这张大表中经常需要查询的记录数量。一般来说，如果经常需要查询的数据不超过10%到15%的话，那就没有必要为其建立索引的必要。因为此时建立索引的开销可能要比性能的改善大的多。这个比例只是一个经验的数据。如果数据库管理员需要得出一个比较精确的结论，那么就需要进行测试分析。即数据库管理员需要测试一下全表扫描的时间，看看其是否比建立索引后的查询时间要长或者短。如果是长的话，则说明有建立索引的必要。但是如果没有的话，则说明还是全表扫描速度来的快。此时也就没有必要建立索引了。 总之，在考虑是否该为表建立索引时，一般来说小表没有建立索引的必要。而对于打表的话，则需要进行实际情况实际分析。简单一点的，可以根据大致的比率来确定。如果要精确一点的，则可以进行全表扫描性能分析，以判断建立索引后是否真的如预期那样改善了数据库性能。 二、根据列的特征来创建索引。
1.不同类型索引和适用范围 Oracle提供了大量索引选项。知道在给定条件下使用哪个选项对于一个应用程序的性能来 说非常重要。一个错误的选择可能会引发死锁，并导致数据库性能急剧下降或进程终止。 而如果做出正确的选择，则可以合理使用资源，使那些已经运行了几个小时甚至几天的进 程在几分钟得以完成，这样会使您立刻成为一位英雄。下面就将简单的讨论每个索引选项。 1.1.B树索引 (默认类型) 树索引在Oracle中是一个通用索引。在创建索引时它就是默认的索引类型。B树索引可以是一个列的(简单)索引，也可以是组合/复合(多个列)的索引。B树索引最多可以包括32列。在下图的例子中，B树索引位于雇员表的last_name列上。这个索引的二元高度为3；接 下来，Oracle会穿过两个树枝块(branch block)，到达包含有ROWID的树叶块。在每个树枝块中，树枝行包含链中下一个块的ID号。 树叶块包含了索引值、ROWID，以及指向前一个和后一个树叶块的指针。Oracle可以从两个方向遍历这个二叉树。B树索引保存了在索引列上有值的每个数据行的ROWID值。Oracle不会对索引列上包含NULL值的行进行索引。如果索引是多个列的组合索引，而其 中列上包含NULL值，这一行就会处于包含NULL值的索引列中，且将被处理为空(视为NULL)。 技巧：索引列的值都存储在索引中。因此，可以建立一个组合(复合)索引，这些索引可以 直接满足查询，而不用访问表。这就不用从表中检索数据，从而减少了I/O量。 B-tree 特点： 适合与大量的增、删、改（OLTP） 不能用包含OR操作符的查询； 适合高基数的列（唯一值多） 典型的树状结构； 每个结点都是数据块； 大多都是物理上一层、两层或三层不定，逻辑上三层； 叶子块数据是排序的，从左向右递增； 在分支块和根块中放的是索引的范围；
Oracle索引的应用 在本练习中，将为HR模式中的STUDENT表创建索引，并查看索引的使用情况。 （1）连接到HR模式，使用如下的语句在STUDENT表的SNAME列上创建B树非惟一索引： create index name_index on Student(Sname) tablespace users; （2）在STUDENT的班级信息列上创建位图索引： create bitmap index sclass_index on Student(classid) tablespace users; （3）执行如下的查询语句，并查看Oracle的执行计划。 set autotrace traceonly select * from student where sname='王丽'; （4）根据索引的使用情况，并删除不经常使用的索引。 alter index name_index monitoring usage; select * from v$object_usage where table_name=upper('student'); （5）查看索引的状态，是否存有过多的碎片，当被删除的叶结点过多时，就说明该B 树索引存在过多的碎片，这就需要重建或合并该索引。 analyze index name_index validate structure; select br_pows,br_blks,lf_rows,del_lf_rows from index_stats where name=upper('name_index'); alter index name_index COALESCE DEALLOCATE UNUSED;--合并索引 alter index name_index REBUILD; --在原来的表空间重建索引
第一章由浅至深讲解Oracle数据库 B-tree索引B-tree索引结构： B-tree索引： ·索引会随着时间的增加而变的不平衡； ·删除的索引空间不会被重用； ·随着索引层数的增加，索引将会变得无效并需要重建； ·聚簇因子差，索引需要重建； ·为了提高性能，索引需要经常重建； 索引基础 ·一个更新由一个删除和一个插入组成；
·页块由索引条目（row header(2/3B)|length(1B)|indexed data value(nB)|length (1B)|RowID(6B)）和相应的rowid组成； ·每个页块包含两个指针分别前面的页块和后面页块； Treedump alter session set events ‘immediate trace name treedump level index_object _id’; ----- begin tree dump branch: 0x424362 4342626 (0: nrow: 2, level: 1) leaf: 0x424363 4342627 (-1: nrow: 540 rrow: 540) leaf: 0x424364 4342628 (0: nrow: 461 rrow: 461) ----- end tree dump 以上dump包含的信息如下： 块类型：branch（分支块）；leaf（页块）； 块地址：0x424362 4342626； nrow：索引条目的数量； rrow：当前块中的索引条目数量； level：分支块等级（页块隐示为0）； Block Dump alter system dump datafile X block X; alter system dump datafile X block min X1 block max X2
Oracle 索引详解 https://www.doczj.com/doc/cb7345073.html,/tianlesoftware/article/details/5347098 一．索引介绍 1.1 索引的创建语法： CREATE UNIUQE | BITMAP INDEX
[Oracle全文索引技术] 使用说明文档 金联万家(北京)电子支付科技发展有限公司
修改记录 目录 1 前言 (3) 1.1 编写目的 (3) 1.2 名词解释 (3) 1.3 参考资料 (3) 2 oracle全文索引技术 (4) 3 建立全文索引的操作步骤 (4) 3.1 查看用户与角色 (4) 3.2 开启目标用户全文索引权限 (4) 3.3 设置语法分析器Lexer (5) 3.4 建立索引 (5) 3.5 使用索引 (6) 4 全文索引的种类 (6) 5 对多字段建立索引 (6) 6 Oracle全文索引之 - CONTEXT (7) 6.1 函数 CONTAINS 语法 (7) 6.2 全文索引的维护 (7) 6.2.1 同步数据 (8) 6.3 单个中文字查询问题 (9) 7 建立全文索引的完整脚本 (10) 8 附录 (14)
1前言 1.1编写目的 本文档主要说明Oracle全文索引技术的使用与维护，为数据库操作使用人员提供参考维护手册。 1.2名词解释 表 1.术语表 1.3参考资料 表 2.参考资料列表
2oracle全文索引技术 全文检索：是指计算机索引程序通过扫描文章中的每一个词，对每一个词建立一个索引，指明该词在文章中出现的次数和位置，当用户查询时，检索程序就根据事先建立的索引进行查找，并将查找的结果反馈给用户的检索方式。这个过程类似于通过字典中的检索字表查字的过程。 【Oracle Text 全文检索】 Oracle一直致力于全文检索技术的研究，当Oracle9i Rlease2发布之时，Oracle数据库的全文检索技术已经非常完美，Oracle Text使Oracle9i具备了强大的文本检索能力和智能化的文本管理能力。Oracle Text是Oracle9i 采用的新名称，在Oracle8/8i中它被称作Oracle interMedia Text，在Oracle8以前它的名称是Oracle ConText Cartridge。使用Oracle9i和Oracle Text，可以方便而有效地利用标准的SQL工具来构建基于文本的新的开发工具或对现有应用程序进行扩展。应用程序开发人员可以在任何使用文本的Oracle数据库应用程序中充分利用Oracle Text搜索，应用范围可以是现有应用程序中可搜索的注释字段，也可是实现涉及多种文档格式和复杂搜索标准的大型文档管理系统。Oracle Text支持Oracle数据库所支持的大多数语言的基本全文搜索功能。 扩展阅读： https://www.doczj.com/doc/cb7345073.html,/view/663956.htm 3建立全文索引的操作步骤 3.1查看用户与角色 检查数据库中是否有CTXSYS 用户和CTXAPP 角色 如果没有这个用户和角色，意味着你的数据库创建时未安装intermedia功能。你必须修改数据库以安装这项功能。默认安装情况下，ctxsys用户是被锁定的，因此要先启用ctxsys的用户。 -- 锁住用户 ALTER USER ctxsys ACCOUNT LOCK PASSWORD EXPIRE; -- 解锁用户 ALTER USER ctxsys ACCOUNT UNLOCK IDENTIFIED BY ctxsys; 如果没有该用户，则需要打开dbca工具中选择configrue database options，然后选择所有数据库组件安装即可。 3.2开启目标用户全文索引权限 赋权在ctxsys用户下把ctx_ddl的执行权限赋于要使用全文索引的用户，例： GRANT EXECUTE ON ctx_ddl TO username;
Oracle 显示索引信息 为了显示Oracle索引的信息，Oracle提供了一系列的数据字典视图。通过查询这些数据字典视图，用户可以了解索引的各方面信息。 1．显示表的所有索引 索引是用于加速数据存储的数据库对象。通过查询数据字典视图DBA_INDEXES，可以显示数据库的所有索引；通过查询数据字典视图ALL_INDEXES，可以显示当前用户可访问的所有索引；查询数据字典视图USER_INDEXES，可以显示当前用户的索引信息。下面以显示SCOTT用户EMP表的所有索引为例，说明使用数据字典视图DBA_INDEXES的方法： SQL> connect system/password 已连接。 SQL> select index_name,index_type,uniqueness 2 from dba_indexes 3 where owner='SCOTT' and table_name='EMP'; INDEX_NAME INDEX_TYPE UNIQUENES ------------------------------ --------------------------- --------- EMP_ENAME_INDEX NORMAL NONUNIQUE EMP_JOB_BMP BITMAP NONUNIQUE IDX_ENAME FUNCTION-BASED NORMAL NONUNIQUE PK_EMP NORMAL/REV UNIQUE 如上所示，INDEX_NAME用于标识索引名。INDEX_TYPE用于标识索引类型：NORMAL表示普通B树索引；REV表示反向键索引；BITMAP表示位图索引；FUNCTION 表示基于函数的索引。UNIQUENESS用于标识索引的惟一性；OWNER用于标识对象的所有者；TABLE_NAME用于标识表名。 2．显示索引列 创建索引时，需要提供相应的表列。通过查询数据字典视图DBA_IND_COLUMNS，可以显示所有索引的表列信息；通过查询数据字典视图ALL_IND_COLUMNS，可以显示当前用户可访问所有索引的表列信息；通过查询数据字典视图USER_IND_COLUMNS，可以显示当前用户索引的表列信息。 例如，下面的语句将显示SCOTT用户的PK_EMP索引列信息： SQL> col column_name format a20 SQL> select column_name,column_position,column_length 2 from user_ind_columns 3 where index_name='PK_EMP'; COLUMN_NAME COLUMN_POSITION COLUMN_LENGTH -------------------- ---------------------------- ------------- EMPNO 1 22 如上所示，COLUMN_NAME用于标识索引列的名称；COLUMN_POSITION用于标识列在索引中的位置；COLUMN_LENGTH用于标识索引列的长度。
索引优化规则： 1. like件中不要以通配符(WILDCARD)开始,否则索引将不被采用. 例:SELECT LODGING FROM LODGING WHERE MANAGER LIKE ‘％HANMAN'; 2.避免在索引列上使用计算或改变索引列的类型或使用‘!=’及<> 例: SELECT …FROM DEPT WHERE SAL * 12 > 25000; SELECT … FROM EMP WHERE EMP_TYPE=to_char(123); select …. Where ACCOUNT_NAME||ACCOUNT_TYPE='AMEXA'; select …where empno!=8888 ; 3.避免在索引列上使用NOT . 4.用>=替代> . 高效: SELECT * FROM EMP WHERE DEPTNO >=4 低效: SELECT * FROM EMP WHERE DEPTNO >3 两者的区别在于, 前者DBMS将直接跳到第一个DEPT等于4的记录而后者将首先定位到DEPTNO=3的记录并且向前扫描到第一个DEPT大于3的记录. ?5.用UNION替换OR (适用于索引列) ? 通常情况下, 用UNION替换WHERE子句中的OR将会起到较好的效果. 对索引列使用OR将造成全表扫描. 注意, 以上只针对多个索引列有效. 如果有column 没有被索引, 查询效率可能会因为你没有选择O规则R而降低. 在下面的例子中, LOC_ID 和REGION上都建有索引. 高效: SELECT LOC_ID , LOC_DESC , REGION FROM LOCATION
Oracle索引用法大总结,好处与坏处,优点与缺点二 索引使用的好处与坏处(Oracle) 分类：OracleDB ?创建索引的好处 –帮助用户提高查询速度 –利用索引的唯一性来控制记录的唯一性 –可以加速表与表之间的连接 –降低查询中分组和排序的时间 ?创建索引的坏处 –存储索引占用磁盘空间
–执行数据修改操作(INSERT、UPDATE、DELETE)产生索引维护 ---------------------------------------------------------------------------------------------- 索引是提高数据查询最有效的方法，也是最难全面掌握的技术，因为正确的索引可能使效率提高10000倍，而无效的索引可能是浪费了数据库空间，甚至大大降低查询性能。 索引的管理成本 1、存储索引的磁盘空间 2、执行数据修改操作(INSERT、UPDATE、DELETE)产生的索引维护 3、在数据处理时回需额外的回退空间。 实际数据修改测试: 一个表有字段A、B、C，同时进行插入10000行记录测试 在没有建索引时平均完成时间是2.9秒
在对A字段建索引后平均完成时间是6.7秒 在对A字段和B字段建索引后平均完成时间是10.3秒 在对A字段、B字段和C字段都建索引后平均完成时间是11.7秒 从以上测试结果可以明显看出索引对数据修改产生的影响 索引按存储方法分类 B*树索引 B*树索引是最常用的索引，其存储结构类似书的索引结构，有分支和叶两种类型的存储数据块，分支块相当于书的大目录，叶块相当于索引到的具体的书页。一般索引及唯一约束索引都使用B*树索引。 位图索引 位图索引储存主要用来节省空间，减少ORACLE对数据块的访问，它采用位图偏移方式来与表的行ID号对应，采用位图索引一般是重复值太多的表字段。位图索引在实际密集型OLTP(数据事务处理)中用得比较少，因
概述 索引在各种关系型数据库系统中都是举足轻重的组成部分，其对于提高检索数据的速度起至关重要的作用。在Oracle中，索引基本分为以下几种：B*Tree索引，反向索引，降序索引，位图索引，函数索引，interMedia全文索引等。 Oracle提供了大量索引选项。知道在给定条件下使用哪个选项对于一个应用程序的性能来说非常重要。一个错误的选择可能会引发死锁，并导致数据库性能急剧下降或进程终止。而如果做出正确的选择，则可以合理使用资源，使那些已经运行了几个小时甚至几天的进程在几分钟得以完成，这样会使您立刻成为一位英雄。这篇文章就将简单的讨论每个索引选项。主要有以下内容： [1] 基本的索引概念 查询DBA_INDEXES视图可得到表中所有索引的列表，注意只能通过USER_INDEXES的方法来检索模式(schema)的索引。访问USER_IND_COLUMNS视图可得到一个给定表中被索引的特定列。 [2] 组合索引 当某个索引包含有多个已索引的列时，称这个索引为组合（concatented）索引。在Oracle9i引入跳跃式扫描的索引访问方法之前，查询只能在有限条件下使用该索引。比如：表emp有一个组合索引键，该索引包含了empno、ename和deptno。在Oracle9i之前除非在where之句中对第一列（empno）指定一个值，否则就不能使用这个索引键进行一次范围扫描。 特别注意：在Oracle9i之前，只有在使用到索引的前导索引时才可以使用组合索引！ [3] ORACLE ROWID 通过每个行的ROWID，索引Oracle提供了访问单行数据的能力。ROWID其实就是直接指向单独行的线路图。如果想检查重复值或是其他对ROWID本身的引用，可以在任何表中使用和指定rowid列。 [4] 限制索引 限制索引是一些没有经验的开发人员经常犯的错误之一。在SQL中有很多陷阱会使一些索引无法使用。下面讨论一些常见的问题： 4.1 使用不等于操作符（<>、!=） 下面的查询即使在cust_rating列有一个索引，查询语句仍然执行一次全表扫描。 select cust_Id,cust_name from customers where cust_rating <> 'aa'; 把上面的语句改成如下的查询语句，这样，在采用基于规则的优化器而不是基于代价的优化器（更智能）时，将会使用索引。select cust_Id,cust_name from customers where cust_rating < 'aa' or cust_rating > 'aa'; 特别注意：通过把不等于操作符改成OR条件，就可以使用索引，以避免全表扫描。 4.2 使用IS NULL 或IS NOT NULL 使用IS NULL 或IS NOT NULL同样会限制索引的使用。因为NULL值并没有被定义。在SQL语句中使用NULL会有很多的麻烦。因此建议开发人员在建表时，把需要索引的列设成NOT NULL。如果被索引的列在某些行中存在NULL值，就不会使用这个索引（除非索引是一个位图索引，关于位图索引在稍后在详细讨论）。 4.3 使用函数