(Index)Oracle 中Index的运行机制

从表单访问数据时，Oracle提供了两种方法 a.全表扫描（从表中读取每一行）;b.通过ROWID一次读取一行；

索引改进性能的程度部分取决于数据的选择性以及在表的块之间分布数据的方式；

 

数据的选择性：

如果数据非常具有选择性，即只有很少的行匹配索引值(for instance PassPort Id).Oracle能快速查询的匹配索引值的RowID索引，并且可以快速查询少量的相关表块。

如果数据不是非常具有选择性(例如国家名)，则索引可能返回许多ROWID，导致从表中查询许多单独的块。

 

如果数据非常具有选择性，但是相关的行在表中的存储位置并不互相靠近，则会进一步减少索引的益处。

如果匹配索引值的数据分散在表的多个块中，则必须从表中选择多个单独的块以满足查询。

 

在一些情况中，您会发现当数据分散在表的多个块中时，最好是不使用索引，而是执行全表扫描。

 

执行全表扫描时，Oracle使用多块读取以快速扫描表。

执行索引的读取是Oracle使用单块读取，因此在使用索引时的目标是减少解决查询所需的单个块的数量。

 

通过使用Oracle中的一些可用选项，比如分区、并行DML、并行查询操作以及使用db_file_multiblock_read_count进行更大的I/O操作，全表扫描和索引查找之间的平衡点发生了改变。硬件更为快速，磁盘可以在磁盘上的高速缓存中缓存更多的信息，内存也变得更为廉价。与此同时，Oracle已经增强的索引特性，包括了跳跃式扫描索引和其他减少检索数据所需时间的操作。

 

Index的优点：

索引通常能提高查询的性能。SELECT语句、UPDATE和DELETE命令的WHERE子句的性能(当访问的行较少时)可以从索引中获益。

 

Index的缺点：

1）增加索引会降低INSERT语句的性能，因为需要同时对表和索引进行插入。

2）索引列的UPDATE操作将会减慢执行速度，因为数据库必须管理对表和索引的改动。

3）大量行的DELETE操作将会由于表中存在索引而减慢执行速度。

备注：

表中的每个索引都会使对表执行的INSERT操作变慢两倍；

使用两条索引通常会使插入操作变慢一倍；

一个由两部分组成的单一索引并不比只有一个部分组成的单一索引差很多；

 

如何创建和查看索引：

--创建索引  建立索引时最好只写columns的名 如果格式table_name.columns_name会无法识别，在同一张表中创建了两个index--
create index index_emp on employees(salary) 
create index index_emp2 on employees(employee_id,first_name,email)

--查看索引--

--a.获取不了数据--
select table_name,index_name from user_indexes where table_name= 'employees'
--b.获取的了数据--
select table_name,index_name from user_indexes where table_name='EMPLOYEES'

---在Oracle数据库中Column和关键字是不区分大小写的,而在查询内容即数据中要区分大小写

     当发出这些命令时，数据库将在EMP表上创建两个单独的索引。每个索引将包含employees表中的指定值以及匹配指定值的行的ROWID值。如果需要查找Sal值为1000的employees记录，优化器就会使用index_emp索引查找该值，在索引中查找相关的ROWID，并且使用该ROWID在表中查找正确的行

结果：

 

获得给定表中被索引的特定列，可访问USER_IND_COLUMNS视图。同样需要注意的是，DBA可以通过访问DBA_IND_COLUMNS视图检索所有模式中被索引的列，而通过访问ALL_IND_COLUMNS视图则可以查看所有表的索引列。

select table_name,index_name,column_name,column_position from user_ind_columns where table_name='EMPLOYEES' order by table_name,index_name,column_position;

 

 

结果:

 

index_emp2是一个组合(concatenated)索引(复合索引)，它对employee_id,first_name,email这几列进行索引。显示在程序清单中的Column_Position显示了组合索引中的列顺序，即按照Empno、Ename和Deptno的顺序。

index_emp只对Sal列进行索引。

组合索引：当某个索引包含有多个已索引的列时，我们称这个索引为组合(concatenated)索引或是复合索引。Oracle 9i引入的跳跃式扫描索引访问方法增强了,优化器在使用组合索引时的选择，但是您应该谨慎地选择索引中的列顺序。一般来说，索引的第一列应该是最有可能在WHERE子句中使用的列，并且也是索引中最具选择性的列。(Oracle的优化器在执行索引时是按照索引的column_position来顺序执行的)；

 

      如果没有使用跳跃式扫描功能，除非在WHERE子句中对第一列(Empno)指定一个值，否则Oracle一般不会使用这个索引。

select * from employees where employees.first_name='irs'

     因为employees.first_name不是索引的第一列，优化器可能会选择不使用该索引。随着在Oracle 9i中引入了跳跃式扫描功能，即使在WHERE子句中没有指定employees.employee_id值，优化器也可能会选择使用该索引。相反，优化器可能会选择索引的快速全局扫描或全表扫描。

 

    如果在WHERE子句中使用索引的第三列，也会产生相同的情况：

select * from employees where employees.email='justin@yahoo.com'

 

     在该程序清单中，WHERE子句指定了索引中第三列的值。优化器可能选择执行索引快速扫描访问、索引快速全局扫描或全表扫描。通过创建索引，您可以在执行查询时为数据库提供更多的选择。从而有希望改进整体的性能。注意，用户的代码没有改变，优化器可以识别该索引，并且根据每种替代方法的预期成本决定使用何种方法。

 

两种最常见的索引扫描类型是唯一扫描和范围扫描。在唯一扫描中，数据库知道索引包含一个唯一值列表。在范围扫描中，数据库将根据查询标准从索引中返回多个值。在该示例中，index_emp和index_emp3索引没有被创建为唯一索引。Oracle将在检索它们的值时执行范围扫描。在创建索引时，使用CREATE UNIQUE INDEX命令可以创建唯一索引。

 

如何创建唯一索引（unique index）：

1）用创建命令：CREATE UNIQUE INDEX

2）创建主键约束，Oracle将基于指定的列自动创建唯一索引(除非使用DISABLE子句创建约束)。

3）创建UNIQUE约束时，Oracle将基于指定的列自动创建唯一索引(除非使用DISABLE子句创建约束)

4）如果创建多列的主键，Oracle将创建组合索引，其中的列按照在创建主键时指定的顺序排列。
----------------------------------------------------------------------------------------------------------------------