Oracle数据库创建索引的几个原则

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_namefrom customerswhere cust_rating <> 'aa';把上面的语句改成如下的查询语句，这样，在采用基于规则的优化器而不是基于代价的优化器（更智能）时，将会使用索引。

oracle 索引总结oracle 索引总结简介1.说明 1）索引是数据库对象之⼀，⽤于加快数据的检索，类似于书籍的索引。

在数据库中索引可以减少数据库程序查询结果时需要读取的数据量，类似于在书籍中我们利⽤索引可以不⽤翻阅整本书即可找到想要的信息。

2）索引是建⽴在表上的可选对象；索引的关键在于通过⼀组排序后的索引键来取代默认的全表扫描检索⽅式，从⽽提⾼检索效率 3）索引在逻辑上和物理上都与相关的表和数据⽆关，当创建或者删除⼀个索引时，不会影响基本的表； 4）索引⼀旦建⽴，在表上进⾏DML 操作时（例如在执⾏插⼊、修改或者删除相关操作时），oracle 会⾃动管理索引，索引删除，不会对表产⽣影响 5）索引对⽤户是透明的，⽆论表上是否有索引，sql 语句的⽤法不变 6）oracle 创建主键时会⾃动在该列上创建索引索引原理1. 若没有索引，搜索某个记录时（例如查找name='wish'）需要搜索所有的记录，因为不能保证只有⼀个wish ，必须全部搜索⼀遍2. 若在name 上建⽴索引，oracle 会对全表进⾏⼀次搜索，将每条记录的name 值哪找升序排列，然后构建索引条⽬（name 和rowid ），存储到索引段中，查询name 为wish 时即可直接查找对应地⽅3.创建了索引并不⼀定就会使⽤，oracle ⾃动统计表的信息后，决定是否使⽤索引，表中数据很少时使⽤全表扫描速度已经很快，没有必要使⽤索引索引使⽤（创建、修改、删除、查看）1.创建索引语法CREATE [UNIQUE] | [BITMAP] INDEX index_name --unique 表⽰唯⼀索引ON table_name([column1 [ASC|DESC],column2 --bitmap ，创建位图索引[ASC|DESC],…] | [express])[TABLESPACE tablespace_name][PCTFREE n1] --指定索引在数据块中空闲空间[STORAGE (INITIAL n2)][NOLOGGING] --表⽰创建和重建索引时允许对表做DML 操作，默认情况下不应该使⽤[NOLINE][NOSORT]; --表⽰创建索引时不进⾏排序，默认不适⽤，如果数据已经是按照该索引顺序排列的可以使⽤2.修改索引1）重命名索引alter index index_sno rename to bitmap_index;2) 合并索引（表使⽤⼀段时间后在索引中会产⽣碎⽚，此时索引效率会降低，可以选择重建索引或者合并索引,合并索引⽅式更好些，⽆需额外存储空间，代价较低）alter index index_sno coalesce ;3)重建索引 ⽅式⼀：删除原来的索引，重新建⽴索引 ⽅式⼆：alter index index_sno rebuild;3.删除索引drop index index_sno;4.查看索引select index_name,index-type, tablespace_name, uniqueness from all_indexes where table_name ='tablename';-- eg:create index index_sno on student('name');select*from all_indexes where table_name='student';索引分类1. B树索引（默认索引，保存讲过排序过的索引列和对应的rowid值）1）说明： 1.oracle中最常⽤的索引；B树索引就是⼀颗⼆叉树；叶⼦节点（双向链表）包含索引列和指向表中每个匹配⾏的ROWID值 2.所有叶⼦节点具有相同的深度，因⽽不管查询条件怎样，查询速度基本相同 3.能够适应精确查询、模糊查询和⽐较查询2）分类： UNIQUE,NON-UNIQUE(默认),REVERSE KEY（数据列中的数据是反向存储的）3）创建例⼦craete index index_sno on student('sno');4）适合使⽤场景： 列基数（列不重复值的个数）⼤时适合使⽤B数索引2. 位图索引1）说明： 1.创建位图索引时，oracle会扫描整张表，并为索引列的每个取值建⽴⼀个位图（位图中，对表中每⼀⾏使⽤⼀位（bit，0或者1）来标识该⾏是否包含该位图的索引列的取值，如果为1，表⽰对应的rowid所在的记录包含该位图索引列值），最后通过位图索引中的映射函数完成位到⾏的ROWID的转换2)创建例⼦create bitmap index index_sno on student(sno);3) 适合场景：对于基数⼩的列适合简历位图索引（例如性别等）3.单列索引和复合索引（基于多个列创建）1) 注意： 即如果索引建⽴在多个列上，只有它的第⼀个列被where⼦句引⽤时，优化器才会使⽤该索引，即⾄少要包含组合索引的第⼀列4. 函数索引1)说明： 1. 当经常要访问⼀些函数或者表达式时，可以将其存储在索引中，这样下次访问时，该值已经计算出来了，可以加快查询速度 2. 函数索引既可以使⽤B数索引，也可以使⽤位图索引；当函数结果不确定时采⽤B树索引，结果是固定的某⼏个值时使⽤位图索引 3. 函数索引中可以⽔泥⽤len、trim、substr、upper（每⾏返回独⽴结果），不能使⽤如sum、max、min、avg等2）例⼦：create index fbi on student (upper(name));select*from student where upper(name) ='WISH';索引建⽴原则总结 1. 如果有两个或者以上的索引，其中有⼀个唯⼀性索引，⽽其他是⾮唯⼀，这种情况下oracle将使⽤唯⼀性索引⽽完全忽略⾮唯⼀性索引 2. ⾄少要包含组合索引的第⼀列（即如果索引建⽴在多个列上，只有它的第⼀个列被where⼦句引⽤时，优化器才会使⽤该索引） 3. ⼩表不要简历索引 4. 对于基数⼤的列适合建⽴B树索引，对于基数⼩的列适合简历位图索引 5. 列中有很多空值，但经常查询该列上⾮空记录时应该建⽴索引 6. 经常进⾏连接查询的列应该创建索引 7. 使⽤create index时要将最常查询的列放在最前⾯ 8. LONG（可变长字符串数据，最长2G）和LONG RAW（可变长⼆进制数据，最长2G）列不能创建索引 9.限制表中索引的数量（创建索引耗费时间，并且随数据量的增⼤⽽增⼤；索引会占⽤物理空间；当对表中的数据进⾏增加、删除和修改的时候，索引也要动态的维护，降低了数据的维护速度）注意事项1. 通配符在搜索词⾸出现时，oracle不能使⽤索引，eg：--我们在name上创建索引；create index index_name on student('name');--下⾯的⽅式oracle不适⽤name索引select*from student where name like'%wish%';--如果通配符出现在字符串的其他位置时，优化器能够利⽤索引；如下：select*from student where name like'wish%';2. 不要在索引列上使⽤not，可以采⽤其他⽅式代替如下：（oracle碰到not会停⽌使⽤索引，⽽采⽤全表扫描）select*from student where not (score=100);select*from student where score <>100;--替换为select*from student where score>100or score <1003. 索引上使⽤空值⽐较将停⽌使⽤索引， eg：select*from student where score is not null;。

oracle常⽤索引分析，使⽤原则和注意事项本⽂参考：索引简介• 索引（index）是数据库对象的⼀种。

索引的关键在于通过⼀组排序后的物理地址作为键来取代默认的全表扫描检索⽅式，就像为书本添加⽬录，通过牺牲物理内存的⽅式提⾼数据的检索效率。

• 它对⽤户时透明的，它的创建不会影响对表的sql操作。

索引⼀旦建⽴，在表上进⾏DML操作时（例如在执⾏插⼊、修改或者删除相关操作时），oracle会⾃动管理索引。

• oracle创建主键时会⾃动创建索引添加索引1.创建默认索引CREATE UNIQUE INDEX （索引名称） ON 表名 (列名1，列名2...) ； --unique（默认）表⽰唯⼀索引2.修改索引alter index （旧索引名称） rename to（新索引名称）;3.删除索引drop index （索引名称）;4.查看索引查看某表的所有索引：select index_name,index-type, tablespace_name, uniqueness from all_indexes where table_name =（表名）；常⽤的2种索引及适⽤场景1.b树索引（默认索引，保存排序过的索引列和对应的rowid值）b树索引是oracle最常见的的索引，它的原理是利⽤了b-树的数据结构（b树的原理：）b树索引结构图：b树的所有叶⼦节点拥有相同的深度，oracle将索引列和对应的rowid值存⼊叶⼦节点上，因此所有的检索速度基本都是相同的。

b树索引的不适⽤场景：不适合键值较少的列（重复数据较多的列）假如索引列TYPE有5个键值，如果有1万条数据，那么 WHERE TYPE = 1将访问表中的2000个数据块。

再加上访问索引块，⼀共要访问⼤于200个的数据块。

如果全表扫描，假设10条数据⼀个数据块，那么只需访问1000个数据块，既然全表扫描访问的数据块少⼀点，肯定不会利⽤b树索引了。

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：在索引中不能说明该参数。

Oracle视图中建立索引的注意事项在视图上创建索引需要三个条件：一、视图必须绑定到架构。

要做到这点，在 CREATE VIEW 语句中，必须加上 WITH SCHEMABINDING，如果是使用企业管理器，则在设计界面的空白处点击右键，属性，选中“绑定到架构”。

二、索引必须是唯一索引。

要做到这点，在 CREATE INDEX 中必须指定 UNIQUE。

三、索引必须是聚集索引。

要做到这点，在 CREATE INDEX 中必须指定 CLUSTERED。

例：CREATE VIEW viewFoo WITH SCHEMABINDINGASSELECT 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 部分的名称。

视图中的表达式所引用的所有函数必须是确定性的。

OBJECTPROPERTY 函数的 IsDeterministic 属性报告用户定义的函数是否是确定性的。

Oracle索引
在正确使用索引的前提下，索引可以提高检索相应的表的速度。

当用户考虑在表中使用索引时，应遵循下列一些基本原则。

（1）在表中插入数据后创建索引。

在表中插入数据后，创建索引效率将更高。

如果在装载数据之前创建索引，那么插入每行时oracle都必须更改索引。

（2）索引正确的表和列。

如果经常检索包含大量数据的表中小于15%的行，就需要创建索引。

为了改善多个表的相互关系，常常使用索引列进行关系连接。

（3）主键和唯一关键字所在的列自动具有索引，但应该在与之关联的表中的外部关键字所在的列上创建索引。

（4）合理安排索引列。

在createindex语句中，列的排序会影响查询的性能，通常将最常用的列放在前面。

创建一个索引来提高多列的查询效率时，应该清楚地了解这个多列的索引对什么列的存取有效，对什么列的存取无效。

例如：在A,B,C三列上创建索引A有效AB有效ABC有效（5）限制表中索引的数量。

尽管表可以有任意数量的索引，可是索引越多，在修改表中的数据时对索引做出相应更改的工作量也越大，效率也就越低。

同样，目前不用的索引应该及时删除。

（6）指定索引数据块空间的使用。

创建索引时，索引的数据块是用表中现存的值填充的，直到达到PCTFREE为止。

如果打算将许多行插入到被索引的表中，PCTFREE就应设置得大一点，不能给索引指定PCTUSED。

（7）根据索引大小设置存储参数。

创建索引之前应先估计索引的大小，以便更好地促进规划和管理磁盘空间。

单个索引项的最大值大约是数据块大小的一半。

oracle创建索引数据库索引是为了提⾼查询速度的⼀种数据结构。

索引的创建语句索引的创建语句⾮常简单。

CREATE INDEX 索引名 ON 表名(列名);除了单列索引，还可以创建包含多个列的复合索引。

CREATE INDEX 索引名 ON 表名(列名1, 列名2, 列名3, ...);删除索引也⾮常简单。

DROP INDEX 索引名;查看某个表中的所有索引也同样简单。

SELECT * FROM ALL_INDEXES WHERE TABLE_NAME = '表名'还可以查看某个表中建⽴了索引的所有列。

SELECT * FROM ALL_IND_COLUMNS WHERE TABLE_NAME = '表名'索引的建⽴原则1.索引应该建⽴在WHERE⼦句中经常使⽤的列上。

如果某个⼤表经常使⽤某个字段进⾏查询，并且检索的啊⾏数⼩于总表⾏数的5%，则应该考虑在该列上建⽴索引。

2.对于两个表连接的字段，应该建⽴索引。

3.如果经常在某表的⼀个字段上进⾏Order By的话，则也应该在这个列上建⽴索引。

4.不应该在⼩表上建⽴索引。

索引的优缺点索引主要是为了提⾼数据的查询速度，这就是索引的优点。

但是当进⾏增删改的时候，会更新索引。

因此索引越多，增删改的速度就会越慢，因为有⼀个维护索引的过程。

创建索引之前需要权衡该字段是否经常发⽣增删改操作，否则可能会带来负优化的问题。

索引的优点1.很⼤地提⾼了数据的检索速度。

2.创建唯⼀索引能保证数据库表中每⼀⾏数据的唯⼀性（唯⼀性约束）。

3.提⾼表与表之间的连接速度。

索引的缺点1.索引需要占⽤物理空间。

2.当对表中的数据进⾏增加、删除和修改的时候，索引也要动态地进⾏维护，降低数据的维护速度。

关于查询优化器当Oracle拿到SQL语句的时候，会使⽤查询优化器去分析该语句，并根据分析结果⽣成查询执⾏计划。

也就是说，数据库是执⾏的查询计划，⽽不是SQL语句。

在Oracle数据库中，创建索引虽然比较简单。

但是要合理的创建索引则比较困难了。

笔者认为，在创建索引时要做到三个适当，即在适当的表上、适当的列上创建适当数量的索引。

虽然这可以通过一句话来概括优化的索引的基本准则，但是要做到这一点的话，需要数据库管理员做出很大的努力。

具体的来说，要做到这个三个适当有如下几个要求。

一、根据表的大小来创建索引。

虽然给表创建索引，可以提高查询的效率。

但是数据库管理员需要注意的是，索引也需要一定的开销的。

为此并不是说给所有的表都创建索引，那么就可以提高数据库的性能。

这个认识是错误的。

恰恰相反，如果不管三七二十一，给所有的表都创建了索引，那么其反而会给数据库的性能造成负面的影响。

因为此时滥用索引的开销可能已经远远大于由此带来的性能方面的收益。

所以笔者认为，数据库管理员首先需要做到，为合适的表来建立索引，而不是为所有的表建立索引。

一般来说，不需要为比较小的表创建索引。

如在一个ERP系统的数据库中，department表用来存储企业部门的信息。

一般企业的部分也就十几个，最多不会超过一百个。

这100条记录对于人来说，可能算是比较多了。

但是对于计算机来说，这给他塞塞牙缝都还不够。

所以，对类似的小表没有必要建立索引。

因为即使建立了索引，其性能也不会得到很大的改善。

相反索引建立的开销，如维护成本等等，要比这个要大。

也就是说，付出的要比得到的多，显然违反常理。

另外，就是对于超大的表，也不一定要建立索引。

有些表虽然比较大，记录数量非常的多。

但是此时为这个表建立索引并一定的合适。

如系统中有一张表，其主要用来保存数据库中的一些变更信息。

往往这些信息只给数据库管理员使用。

此时为这张表建立索引的话，反而不合适。

因为这张表很少用到，只有在出问题的时候才需要查看。

其次其即使查看，需要查询的纪录也不会很多，可能就是最近一周的更新记录等等。

对于对于一些超大的表，建立索引有时候往往不能够达到预计的效果。

而且在打表上建立索引，其索引的开销要比普通的表大的多。

oracle 索引原则
Oracle索引的原则包括以下几点：
1. 唯一性原则：索引的值必须是唯一的，确保在索引字段上不存在重复值。

这可以通过在创建索引时添加UNIQUE约束来实现。

2. 精确性原则：索引应该被正确地定义和使用，以确保在查询时只返回需要的结果，并且能够准确地匹配查询条件。

使用合适的数据类型和大小限制可以提高索引的精确性。

3. 冗余性原则：索引应该避免对重复或冗余数据进行索引。

对于经常被查询的字段，可以考虑创建索引，而对于很少被查询的字段，则可以避免创建索引，以提高性能。

4. 支持性原则：索引应该能够支持常见的查询模式和条件，以提高查询性能。

合理选择索引字段和顺序，可以使索引更好地支持常用的查询操作。

5. 可管理性原则：索引应该易于管理，包括创建、修改和删除索引。

不需要的索引应该及时删除，以减少数据库的维护工作和存储空间占用。

6. 统计信息原则：索引应该基于准确和实时的统计信息进行优化。

Oracle提供了收集统计信息的机制，可以通过收集和更新统计信息来帮助数据库优化器选择最佳的查询计划。

综上所述，索引的原则是确保唯一性、精确性、避免冗余、支持常用查询模式、易于管理和基于准确统计信息进行优化。

这些原则有助于提高数据库的性能和可用性。

oracle 索引的添加规则Oracle 索引是数据库中一种重要的数据结构，它能够使查询操作的速度得到显著的提升。

一个好的索引可以提高 SQL 查询的效率，加快数据库查询速度，但不当使用也会影响数据库性能。

以下是在 Oracle 数据库中添加索引的步骤：第一步：确定需要的列确定需要加索引的列，在查询中用到的列是需要加索引的。

在可以增加索引的列，首先应该考虑唯一值的列是否适合加索引。

第二步：创建索引在确定需要加索引的列以后，我们就需要创建索引。

常用的有 B 树索引和位图索引，其中 B 树索引就是在非数字列上建立排序树，使检索更快，位图索引则应用与高基数（distinct value）的列，如性别或邮政编码等。

创建索引语法如下：create index 索引名 on 表名(列名);第三步：判断索引的类型Oracle 中的索引类型非常多，如 B 树索引、位图索引、函数索引、簇索引等等。

在具体选择索引类型时，需要根据具体的场景进行选择。

比如说 B 树索引适用于经常用到相等比较而不是范围比较; 位图索引适用于取值范围比较小的情况下，索引复杂度低，查询效率很高。

第四步：考虑索引的创建时间对于查询次数较少的表，其创建时间基本上不会影响表的性能。

但是对于查询次数较多的表，建立索引的时间就会影响表的性能，因为在建立索引的过程中会锁定表，这个时间段里面所有涉及到此表的查询都会受到影响。

第五步：考虑索引的数量虽然索引可以加快查询效率，但是在建立索引的时候，需要考虑索引数量的大小。

如果索引数量过多，会导致插入、删除、更新表时，重建索引太慢，甚至无法索引，无意间会导致表的性能变慢。

所以需要对索引数量进行合理控制。

总之，在 Oracle 数据库中添加索引是一项非常重要的工作，如果合理使用索引可以让查询效率得到提升，加快数据库查询速度。

但是需要注意的是，在添加索引时不要过度，要考虑好各个因素，才能使索引发挥最优性能。