本篇文章给大家带来了Oracle中索引的创建和使用的相关知识，希望对大家有帮助。
OLTP系统索引创建
1、通过创建唯一性索引，可以保证数据库表中每一行数据的唯一性。
2、可以大大加快数据的检索速度，这也是创建索引的最主要的原因。
3、可以加速表和表之间的连接，特别是在实现数据的参考完整性方面特别有意义。
4、使用分组和排序子句进行数据检索时，同样可以显著减少查询中分组和排序的时间。
5、通过使用索引，可以在查询的过程中，使用优化隐藏器，提高系统的性能
1）在经常需要搜索的列上，可以加快搜索的速度；
2）在作为主键的列上，强制该列的唯一性和组织表中数据的排列结构；
3）在经常用在连接的列上，这些列主要是一些外键，可以加快连接的速度；
4）在经常需要根据范围进行搜索的列上创建索引，因为索引已经排序，其指定的范围 是连续的；
5）在经常需要排序的列上创建索引，因为索引已经排序，这样查询可以利用索引的排序，加快排序查询时间；
6）在经常使用在WHERE子句中的列上面创建索引，加快条件的判断速度。
1）对于那些在查询中很少使用或者参考的列不应该创建索引。这是因为，既然这些列很少使用到，因此有索引或者无索引，并不能提高查询速度。相反，由于增加了索引，反而 降低了系统的维护速度和增大了空间需求。
2）对于那些只有很少数据值的列也不应该增加索引。这是因为，由于这些列的取值很少，例如人事表的性别列，在查询的结果中，结果集的数据行占了表中数据行的很大比例，即需要在表中搜索的数据行的比例很大。增加索引，并不能明显加快检索速度。
3）对于那些定义为blob数据类型的列不应该增加索引。这是因为，这些列的数据量要么相当大，要么取值很少。
4）当修改性能远远大于检索性能时，不应该创建索引。这是因为，修改性能和检索性能是互相矛盾的。当增加索引时，会提高检索性能，但是会降低修改性能。当减少索引时，会提高修改性能，降低检索性能。因此，当修改性能远远大于检索性能时，不应该创建索引。（数据量庞大，考虑创建分区索引）
CREATEUNIUQE | BITMAP INDEX <schema>.<index_name> ON <schema>.<table_name> (<column_name> | <expression> ASC | DESC, <column_name> | <expression> ASC | DESC,...) TABLESPACE <tablespace_name> STORAGE <storage_settings> LOGGING | NOLOGGING COMPUTE STATISTICS NOCOMPRESS | COMPRESS<nn> NOSORT | REVERSE PARTITION | GLOBAL PARTITION<partition_setting>
相关说明
1） UNIQUE | BITMAP：指定 UNIQUE为唯一值索引， BITMAP为位图索引，省略为 B-Tree索引。
2）
3） TABLESPACE：指定存放索引的表空间(索引和原表不在一个表空间时效率更高)
4） STORAGE：可进一步设置表空间的存储参数
5） LOGGING | NOLOGGING：是否对索引产生重做日志(对大表尽量使用 NOLOGGING来减少占用空间并提高效率)
6） COMPUTESTATISTICS：创建新索引时收集统计信息
7） NOCOMPRESS | COMPRESS
8） NOSORT | REVERSE： NOSORT 表示与表中相同的顺序创建索引， REVERSE表示相反顺序存储索引值
9） PARTITION | NOPARTITION：可以在分区表和未分区表上对创建的索引进行分区
限制索引是一些没有经验的开发人员经常犯的错误之一。在 SQL中有很多陷阱会使一
些索引无法使用。下面讨论一些常见的问题：
1、使用不等于操作符（ <>、 !=）
下面的查询即使在cust_rating列有一个索引，查询语句仍然执行一次全表扫描。
select cust_Id,cust_name from customers wherecust_rating<> 'aa';
把上面的语句改成如下的查询语句，这样，在采用基于规则的优化器而不是基于代价的优化器（更智能）时，将会使用索引。
select cust_Id,cust_name fromcustomers where cust_rating<'aa' orcust_rating > 'aa';
特别注意：通过把不等于操作符改成OR条件，就可以使用索引，以避免全表扫描。
2、 使用 IS NULL或 IS NOT NULL
使用ISNULL或ISNOT NULL同样会限制索引的使用。因为NULL值并没有被定义。
在 SQL语句中使用NULL会有很多的麻烦。因此建议开发人员在建表时，把需要索引的列设成NOT NULL。如果被索引的列在某些行中存在NULL值，就不会使用这个索引（除非索引是一个位图索引，关于位图索引在稍后在详细讨论）。
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、比较不匹配的数据类型
也是比较难于发现的性能问题之一。 注意下面查询的例子，account_number是一个VARCHAR2类型,在 account_number字段上有索引。
下面的语句将执行全表扫描：
select bank_name,address,city,state,zip from banks whereaccount_number = 990354;
Oracle 可以自动把 where子句变成to_number(account_number)=990354，这样就限
制了索引的使用,改成下面的查询就可以使用索引：
select bank_name,address,city,state,zip from banks where account_number='990354';
特别注意： 不匹配的数据类型之间比较会让Oracle自动限制索引的使用,即便对这个查询执行ExplainPlan也不能让您明白为什么做了一次―全表扫描。
5、查询索引
查 询 DBA_INDEXES视 图 可 得 到 表 中 所 有 索 引 的 列表 ， 注 意 只 能 通 过USER_INDEXES的方法来检索模式(schema)的索引。访问 USER_IND_COLUMNS视图可得到一个给定表中被索引的特定列。
6、 组合索引
当某个索引包含有多个已索引的列时，称这个索引为组合（concatented）索引。在Oracle9i引入跳跃式扫描的索引访问方法之前，查询只能在有限条件下使用该索引。比如：表 emp 有一个组合索引键，该索引包含了 empno、 ename和 deptno。在Oracle9i之前除非在 where之句中对第一列（empno）指定一个值，否则就不能使用这个索引键进行一次范围扫描。
特别注意：在Oracle9i之前，只有在使用到索引的前导索引时才可以使用组合索引
Oracle提供了大量索引选项。知道在给定条件下使用哪个选项对于一个应用程序的性能来说非常重要。一个错误的选择可能会引发死锁，并导致数据库性能急剧下降或进程终止。而如果做出正确的选择，则可以合理使用资源，使那些已经运行了几个小时甚至几天的进程在几分钟得以完成，下面就将简单的讨论每个索引选项。
在这里讨论如下的索引类型：
B树索引(默认类型)
位图索引
HASH索引
索引组织表索引
反转键(reverse key)索引
基于函数的索引
分区索引(本地和全局索引)
位图连接索引
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操作符的查询；
适合高基数的列（唯一值多）
典型的树状结构；
每个结点都是数据块；
大多都是物理上一层、两层或三层不定，逻辑上三层；
叶子块数据是排序的，从左向右递增；
在分支块和根块中放的是索引的范围；
位图索引
位图索引非常适合于决策支持系统(Decision Support System，DSS)和数据仓库，它们不应该用于通过事务处理应用程序访问的表。它们可以使用较少到中等基数(不同值的数量)的列访问非常大的表。
尽管位图索引最多可达30个列，但通常它们都只用于少量的列。
例如，您的表可能包含一个称为Sex的列，它有两个可能值：男和女。这个基数只为2，如果用户频繁地根据Sex列的值查询该表，这就是位图索引的基列。当一个表内包含了多个位图索引时，您可以体会到位图索引的真正威力。如果有多个可用的位图索引，Oracle就可以合并从每个位图索引得到的结果集，快速删除不必要的数据。
Bitmapt特点：
适合与决策支持系统；做 UPDATE代价非常高
非常适合 OR操作符的查询；基数比较少的时候才能建位图索引；
技巧：对于有较低基数的列需要使用位图索引。性别列就是这样一个例子，它有两个可能值：男或女(基数仅为2)。位图对于低基数(少量的不同值)列来说非常快，这是因为索引的尺寸相对于B树索引来说小了很多。因为这些索引是低基数的 B 树索引，所以非常小，因此您可以经常检索表中超过半数的行，并且仍使用位图索引。
当大多数条目不会向位图添加新的值时，位图索引在批处理(单用户)操作中加载表(插入操作)方面通常要比B树做得好。当多个会话同时向表中插入行时不应该使用位图索引，在大多数事务处理应用程序中都会发生这种情况。
示例
下面来看一个示例表PARTICIPANT，该表包含了来自个人的调查数据。列Age_Code、Income_Level、Education_Level和Marital_Status都包括了各自的位图索引。 下图显示了每个直方图中的数据平衡情况，以及对访问每个位图索引的查询的执行路径。图中的执行路径显示了有多少个位图索引被合并，可以看出性能得到了显著的提高。
如上图图所示，优化器依次使用4个单独的位图索引，这些索引的列在WHERE子句中被引用。每个位图记录指针(例如0或1)，用于示表中的哪些行包含位图中的已知值。有了这些信息后，Oracle就执行BITMAP AND操作以查找将从所有4个位图中返回哪些行。该值然后被转换为ROWID值，并且查询继续完成剩余的处理工作。注意，所有4个列都有非常低的基数，使用索引可以非常快速地返回匹配的行。
技巧：在一个查询中合并多个位图索引后，可以使性能显著提高。位图索引使用固定长度的数据类型要比可变长度的数据类型好。较大尺寸的块也会提高对位图索引的存储和读取性能。
下面的查询可显示索引类型。
SQL> select index_name, index_type from user_indexes; INDEX_NAME INDEX_TYPE ------------------------------ ---------------------- TT_INDEX NORMAL IX_CUSTADDR_TP NORMAL
B 树索引作为NORMAL列出；而位图索引的类型值为BITMAP。
技巧：如果要查询位图索引列表，可以在USER_INDEXES视图中查询index_type列。
建议不要在一些联机事务处理(OLTP)应用程序中使用位图索引。B树索引的索引值中包含ROWID，这样Oracle就可以在行级别上锁定索引。位图索引存储为压缩的索引值，其中包含了一定范围的ROWID，因此Oracle必须针对一个给定值锁定所有范围内的ROWID
这种锁定类型可能在某些DML语句中造成死锁。SELECT语句不会受到这种锁定问题的影响。
位图索引的使用限制：
基于规则的优化器不会考虑位图索引。
当执行 ALTER TABLE语句并修改包含有位图索引的列时，会使位图索引失效。位图索引不包含任何列数据，并且不能用于任何类型的完整性检查。位图索引不能被声明为唯一索引。位图索引的最大长度为30。
技巧：不要在繁重的OLTP環境中使用位圖索引
#使用HASH索引必須要使用HASH叢集。建立一個集群或HASH集群的同時，也定義了一個集群鍵。這個鍵告訴Oracle如何在叢集儲存表。在儲存資料時，所有與這個叢集鍵相關的行都儲存在一個資料庫區塊上。
如果資料都儲存在同一個資料庫區塊上， 並且將HASH索引作為WHERE子句中的確切匹配，Oracle就可以透過執行一個HASH函數和I/O來存取資料－而透過使用一個二元高度為4的B樹索引來存取數據，則需要在檢索資料時使用4個I/O。
如下圖所示，其中的查詢是一個等價查詢，用於匹配HASH列和確切的值。
Oracle可以快速使用該值，基於HASH函數決定行的物理儲存位置。
HASH索引可能是存取資料庫中資料最快的方法，但它也有自身的缺點。集群鍵上不同值的數目必須在創建HASH集群之前就要知道。需要在創建HASH集群的時候指定這個值。低估了集群鍵的不同值的數字可能會造成集群的衝突(兩個集群的鍵值擁有相同的HASH值)。這種衝突是非常消耗資源的。衝突會造成用來儲存額外行的緩衝溢出，然後造成額外的I/O。如果不同HASH值的數目已經被低估，您就必須在重建這個集群之後改變這個值。
ALTER CLUSTER指令不能改變HASH鍵的數目。 HASH集群還可能浪費空間。如果無法確定需要多少空間來維護某個集群鍵上的所有行，就可能造成空間的浪費。如果無法為集群的未來成長分配好附加的空間，HASH集群可能就不是最好的選擇。
如果應用程式經常在叢集表上進行全表掃描，HASH叢集可能也不是最好的選擇。由於需要為未來的成長分配好叢集的剩餘空間量，全表掃描可能非常消耗資源。
在實作 HASH叢集之前一定要小心。您需要全面地觀察應用程序，保證在實現這個選項之前已經了解關於表和數據的大量資訊。通常，HASH對於一些包含有序值的靜態資料非常有效。
技巧：HASH索引在有限制條件(需要指定一個確定的值而不是一個值範圍)的情況下非常有用
索引組織表會把表的儲存結構改成B樹結構，以表的主鍵排序。這種特殊的表和其他類型的表一樣，可以在表上執行所有的DML和DDL語句。由於表格的特殊結構， ROWID並沒有被關聯到表格的行上。
對於一些涉及精確比對和範圍搜尋的語句，索引組織表提供了一種基於鍵的快速資料存取機制。基於主鍵值的UPDATE和DELETE語句的效能也同樣得以提高， 這是因為行在物理上有序。
由於鍵列的值在表和索引中都沒有重複，因此儲存所需的空間也隨之減少。如果不會經常根據主鍵列查詢數據，則需要在索引組織表中的其他列上建立二級索引。不會經常根據主鍵查詢表的應用程式不會了解到使用索引組織表的全部優點。 對於總是透過對主鍵的精確匹配或範圍掃描進行存取的表，就需要考慮使用索引組織表。
技巧：可以在索引組織表上建立二級索引。
當載入一些有序資料時，索引肯定會碰到與I/O相關的一些瓶頸。在資料載入期間，某部分索引和磁碟肯定會比其他部分使用頻繁得多。為了解決這個問題，可以把索引表空間存放在能夠把檔案實體分割在多個磁碟上的磁碟體系結構上。
為了解決這個問題，Oracle也提供了一種反轉鍵索引的方法。如果資料以反轉鍵索引存儲，這些資料的值就會與原先儲存的數值相反。這樣，資料1234、1235和1236就被儲存成4321、5321和6321。結果就是索引會為每次新插入的行更新不同的索引塊。
技巧：如果您的磁碟容量有限，同時也要執行大量的有序載入，就可以使用反轉鍵索引。
不可以將反轉鍵索引與點陣圖索引或索引組織表結合使用。因為不能對位圖索引和索引組織表進行反轉鍵處理。
可以在表中建立基於函數的索引。如果沒有基於函數的索引，任何在列上執行了函數的查詢都不能使用這個列的索引。例如，下面的查詢就不能使用JOB列上的索引，除非它是基於函數的索引：
select * from emp where UPPER(job) = 'MGR';
下面的查询使用 JOB列上的索引，但是它将不会返回JOB列具有Mgr或mgr值的行：
select * from emp where job = 'MGR';
可以创建这样的索引，允许索引访问支持基于函数的列或数据。可以对列表达式 UPPER(job)创建索引，而不是直接在JOB列上建立索引，如：
create index EMP$UPPER_JOB on emp(UPPER(job));尽管基于函数的索引非常有用，但在建立它们之前必须先考虑下面一些问题：
能限制在这个列上使用的函数吗？如果能，能限制所有在这个列上执行的所有函数吗？是否有足够应付额外索引的存储空间？在每列上增加的索引数量会对针对该表执行的DML语句的性能带来何种影响？
基于函数的索引非常有用，但在实现时必须小心。在表上创建的索引越多，INSERT、UPDATE和DELETE语句的执行就会花费越多的时间。
注意：对于优化器所使用的基于函数的索引来说，必须把初始参数QUERY_REWRITE _ ENABLED 设定为 TRUE。
示例：
select count(*) from sample where ratio(balance,limit) >.5; Elapsed time: 20.1 minutes create index ratio_idx1 on sample (ratio(balance, limit)); select count(*) from sample where ratio(balance,limit) >.5; Elapsed time: 7 seconds!!!
分区索引就是简单地把一个索引分成多个片断。通过把一个索引分成多个片断，可以访问更小的片断(也更快)，并且可以把这些片断分别存放在不同的磁盘驱动器上(避免I/O问题)。
B树和位图索引都可以被分区，而HASH索引不可以被分区。可以有好几种分区方法：表被分区而索引未被分区；表未被分区而索引被分区；表和索引都被分区。
不管采用哪种方法，都必须使用基于成本的优化器。分区能够提供更多可以提高性能和可维护性的可能性有两种类型的分区索引：本地分区索引和全局分区索引。
每个类型都有两个子类型，有前缀索引和无前缀索引。表各列上的索引可以有各种类型索引的组合。如果使用了位图索引，就必须是本地索引。
把索引分区最主要的原因是可以减少所需读取的索引的大小，另外把分区放在不同的表空间中可以提高分区的可用性和可靠性。在使用分区后的表和索引时，Oracle还支持并行查询和并行DML。这样就可以同时执行多个进程，从而加快处理这条语句。
可以使用与表相同的分区键和范围界限来对本地索引分区。每个本地索引的分区只包含了它所关联的表分区的键和ROWID。本地索引可以是B树或位图索引。如果是 B树索引，它可以是唯一或不唯一的索引。
这种类型的索引支持分区独立性，这就意味着对于单独的分区，可以进行增加、截取、删除、分割、脱机等处理，而不用同时删除或重建索引。Oracle自动维护这些本地索引。本地索引分区还可以被单独重建，而其他分区不会受到影响。
（1） 有前缀的索引
有前缀的索引包含了来自分区键的键，并把它们作为索引的前导。例如，让我们再次回顾 participant表。在创建该表后，使用survey_id和survey_date这两个列进行范围分区，然后在survey_id列上建立一个有前缀的本地索引，如下图所示。这个索引的所有分区都被等价划分，就是说索引的分区都使用表的相同范围界限来创建。
技巧：本地的有前缀索引可以让Oracle快速剔除一些不必要的分区。也就是说
没有包含 WHERE条件子句中任何值的分区将不会被访问，这样也提高了语句
的性能。
（ 2） 無前綴的索引
無前綴的索引並沒有以分割鍵的前導列當作索引的前導列。若使用相同分割區鍵(survey_id和survey_date)的相同分割表，建立在survey_date列上的索引就是一個本機的無前綴索引，如下圖所示。可以在表的任一列上建立本地無前綴
索引，但索引的每個分區只包含表的相應分區的鍵值。
如果要把無前綴的索引設為唯一索引，這個索引就必須包含分割區鍵的子集。 在這個例子中，我們必須把包含survey和(或)survey_id的欄位進行組合#(
只要survey_id不是索引的第一列，它就是有前綴的索引)
#技巧：對於一個唯一的無前綴索引，它必須包含分區鍵的子集。
全域分區索引在一個索引分割區中包含來自多個資料表分割區的鍵。一個全域分區索引的分區鍵是分區表中不同的或指定一個範圍的值。在建立全域分區索引時，必須定義分區鍵的範圍和值。 全域索引只能是B樹索引。 Oracle在預設情況下不會維護全域分區索引。如果一個分割區被截取、增加、分割、刪除等，就必須重建全域分割索引，除非在修改表時指定 ALTER TABLE指令的UPDATE GLOBAL INDEXES子句。 （2） 有前綴的索引通常，全域有前綴索引在底層表中沒有經過對等分割區。
沒有任何因素能限制索引的對等分割區，但Oracle在產生查詢計畫或執行分割維護作業時，並不會充分利用對等分割區。如果索引被對等分割區，就必須把它建立為一個本地索引，這樣Oracle可以維護這個索引，並使用它來刪除不必要的分割區，如下圖所示。在該圖的 3個索引分區中，每個分區都包含指向多個表格分區中行的索引條目。
############## ####
分区的、全局有前缀索引
技巧：如果一个全局索引将被对等分区，就必须把它创建为一个本地索引，
这样 Oracle可以维护这个索引，并使用它来删除不必要的分区。
（ 2） 无前缀的索引
Oracle不支持无前缀的全局索引。
位图连接索引是基于两个表的连接的位图索引，在数据仓库环境中使用这种索引改进连接维度表和事实表的查询的性能。创建位图连接索引时，标准方法是
连接索引中常用的维度表和事实表。当用户在一次查询中结合查询事实表和维度表时，就不需要执行连接，因为在位图连接索引中已经有可用的连接结果。通过压缩位图连接索引中的ROWID进一步改进性能，并且减少访问数据所需的I/O数量。
创建位图连接索引时，指定涉及的两个表。相应的语法应该遵循如下模式：
create bitmap index FACT_DIM_COL_IDX on FACT(DIM.Descr_Col) from FACT, DIM where FACT.JoinCol = DIM.JoinCol;
位图连接的语法比较特别，其中包含 FROM子句和WHERE子句，并且引用两个单独的表。索引列通常是维度表中的描述列——就是说，如果维度是
CUSTOMER，并且它的主键是CUSTOMER_ID，则通常索引Customer_Name
这样的列。如果事实表名为 SALES，可以使用如下的命令创建索引：
create bitmap index SALES_CUST_NAME_IDX on SALES(CUSTOMER.Customer_Name) from SALES, CUSTOMER where SALES.Customer_ID=CUSTOMER.Customer_ID;
如果用户接下来使用指定 Customer_Name列值的WHERE子句查询
SALES和CUSTOMER表，优化器就可以使用位图连接索引快速返回匹配连接
条件和 Customer_Name条件的行。
位图连接索引的使用一般会受到限制：
1） 只可以索引维度表中的列。
2） 用于连接的列必须是维度表中的主键或唯一约束；如果是复合主键，则
必须使用连接中的每一列。
3） 不可以对索引组织表创建位图连接索引，并且适用于常规位图索引的限
制也适用于位图连接索引。
注意：以上总结是对oracle数据库中索引创建的一些知识的介绍和关键点。需要读懂理解之后结合系统业务情况合理创建索引，以求达到预期性能。
本文部分内容摘自《Oracle超详细讲解.pdf》
推荐教程：《Oracle教程》
以上是Oracle中索引的建立與使用（總結分享）的詳細內容。更多資訊請關注PHP中文網其他相關文章！
