Oracle数据访问和索引的使用
· 通过全表扫描的方式访问数据；
成都服务器托管，成都创新互联提供包括服务器租用、遂宁服务器托管、带宽租用、云主机、机柜租用、主机租用托管、CDN网站加速、域名注册等业务的一体化完整服务。电话咨询：18982081108
· 通过ROWID访问数据；
· 通过索引的方式访问数据；
· Oracle顺序读取表中所有的行，并逐条匹配WHERE限定条件。
· 采用多块读的方式进行全表扫描，可以有效提高系统的吞吐量，降低I/O次数。
· 即使创建索引，Oracle也会根据CBO的计算结果，决定是否使用索引。
注意事项：
· 只有全表扫描时才可以使用多块读。该方式下，单个数据块仅访问一次。
· 对于数据量较大的表，不建议使用全表扫描进行访问。
· 当访问表中的数据量超过数据总量的5%—10%时，通常Oracle会采用全表扫描的方式进行访问。
· 并行查询可能会导致优化器选择全表扫描的方式。1.2ROWID访问表
· Rowid是数据存放在数据库中的物理地址，能够唯一标识表中的一条数据。
· Rowid指出了一条记录所在的数据文件、块号以及行号的位置，因此通过ROWID定位单行数据是最快的方法。
注意事项：
· Rowid作为一个伪列，其数值并不存储在数据库中，当查询时才进行计算。
· Rowid除了在同一集簇中可能不唯一外，每条记录的Rowid唯一。1.3 INDEX访问表
· 通过索引查找相应数据行的Rowid，再根据Rowid查找表中实际数据的方式称为“索引查找”或者“索引扫描”。
· 一个Rowid对应一条数据行（根据Rowid查找结果，仅需要对Rowid相应数据的数据块进行一次I/O操作），因此该方式属于“单块读”。
· 对于索引，除了存储索引的数据外，还保存有该数据对应的Rowid信息。
· 索引扫描分为两步：1）扫描索引确定相应的Rowid信息。 2）根据Rowid从表中获得对应的数据。
注意事项：
· 对于选择性高的数据行，索引的使用会提升查询的性能。但对于DML操作，尤其是批量数据的操作，可能会导致性能的降低。
· 全表扫描的效率不一定比索引扫描差，关键看数据在数据块上的具体分布。
索引是关系数据库中用于存放每一条记录的一种对象，主要目的是加快数据的读取速度和完整性检查。建立索引是一项技术性要求高的工作。一般在数据库设计阶段的与数据库结构一道考虑。应用系统的性能直接与索引的合理直接有关。
(1) 单列索引
单列索引是基于单个列所建立的索引。
(2) 复合索引
复合索引是基于两列或是多列的索引，在同一张表上可以有多个索引，但是要求列的组合必须不同。
(1) 重命名索引
(2) 合并索引
（表使用一段时间后在索引中会产生碎片，此时索引效率会降低，可以选择重建索引或者合并索引,合并索引方式更好些，无需额外存储空间，代价较低）
(3) 重建索引
方式一：删除原来的索引，重新建立索引
当不需要时可以将索引删除以释放出硬盘空间。命令如下：
例如：
注：当表结构被删除时，有其相关的所有索引也随之被删除。
方式二： Alter index 索引名称 rebuild;
· 通过创建唯一性索引，可以保证数据库表中每一行数据的唯一性。
· 索引可以大大加快数据的检索速度，这是创建索引的最主要的原因。
· 可以加速表和表之间的连接，特别是在实现数据的参考完整性方面特别有意义。
· 在使用分组和排序子句进行数据检索时，同样可以显著减少查询中分组和排序的时间。
· 通过使用索引，可以在查询的过程中，使用优化隐藏器，提高系统的性能。
· 索引的层次不要超过4层。
· 创建索引和维护索引要耗费时间，这种时间随着数据量的增加而增加。
· 除了数据表占数据空间之外，每一个索引还要占一定的物理空间，如果要建立聚簇索引，那么需要的空间就会更大。
· 当对表中的数据进行增加、删除和修改的时候，索引也要动态的维护，这样就降低了数据的维护速度。
· 更新数据的时候，系统必须要有额外的时间来同时对索引进行更新，以维持数据和索引的一致性。
1) 不恰当的索引不但于事无补，反而会降低系统性能。因为大量的索引在进行插入、修改和删除操作时比没有索引花费更多的系统时间。
1) 应该建索引的列
· 在经常需要搜索的列上，可以加快搜索的速度；
· 在作为主键的列上，强制该列的唯一性和组织表中数据的排列结构；
· 在经常用在连接的列上，这些列主要是一些外键，可以加快连接的速度；
· 在经常需要根据范围进行搜索的列上创建索引，因为索引已经排序，其指定的范围是连续的；
· 在经常需要排序的列上创建索引，因为索引已经排序，这样查询可以利用索引的排序，加快排序查询时间；
· 在经常使用在WHERE子句中的列上面创建索引，加快条件的判断速度。
2) 不应该建索引的列
· 在大表上建立索引才有意义，小表无意义。
· 对于那些在查询中很少使用或者参考的列不应该创建索引。
· 对于那些只有很少数据值的列也不应该增加索引。比如性别，在查询的结果中，结果集的数据行占了表中数据行的很大比例，。增加索引，并不能明显加快检索速度。
· 对于那些定义为blob数据类型的列不应该增加索引。这是因为，这些列的数据量要么相当大，要么取值很少。
· 当修改性能远远大于检索性能时，不应该创建索引。
一个表中有几百万条数据，对某个字段加了索引，但是查询时性能并没有什么提高，这主要可能是oracle的索引限制造成的。Oracle的索引有一些索引限制，在这些索引限制发生的情况下，即使已经加了索引，oracle还是会执行一次全表扫描，查询的性能不会比不加索引有所提高，反而可能由于数据库维护索引的系统开销造成性能更差。
下面的查询即使在djlx列有索引，查询语句仍然执行一次全表扫描。
把上面的语句改成如下的查询语句，这样，在采用基于规则的优化器而不是基于代价的优化器（更智能）时，将会使用索引。
特别注意：通过把不等于操作符改成OR条件，就可以使用索引，避免全表扫描。
使用IS NULL或IS NOT NULL同样会限制索引的使用。因此在建表时，把需要索引的列设成NOT NULL。如果被索引的列在某些行中存在NULL值，就不会使用这个索引（除非索引是一个位图索引）。
如果不使用基于函数的索引，那么在SQL语句的WHERE子句中对存在索引的列使用函数时，会使优化器忽略掉这些索引。 下面的查询不会使用索引（只要它不是基于函数的索引）
也是比较难于发现的性能问题之一。比如：bdcs_qlr_xz中的zjh是NVARCHAR2类型,在zjh字段上有索引。如果使用下面的语句将执行全表扫描。
因为Oracle会自动把查询语句改为
特别注意：不匹配的数据类型之间比较会让Oracle自动限制索引的使用,即便对这个查询执行Explain Plan也不能让您明白为什么做了一次“全表扫描”。
(1) 索引无效
(2) 索引有效
如何使用oracle中的索引
方法如下： Oracle中建立索引，会提高查询速度： create index 索引名 on 表名(列名); 例如： create index index_userid on tbl_detail(userid); 如何找数据库表的主键字段的名称? SELECT * FROM user_constraints WHERE CONSTRAINT_TYPE='P' a...
oracle中怎么建立和使用索引
在程序中，oracle优化器在认为索引效率更高时，会自动调用索引。
也可以显式调用索引：
select
/*+index(A,索引名)*/
*
from
A
where
b=‘’,c='';
请教：oracle中索引应用
一．索引介绍
1.1 索引的创建语法：
CREATE UNIUQE | 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 | COMPRESSnn
NOSORT | REVERSE
PARTITION | GLOBAL PARTITIONpartition_setting
相关说明
1） UNIQUE | BITMAP：指定UNIQUE为唯一值索引，BITMAP为位图索引，省略为B-Tree索引。
2）column_name | expression ASC | DESC：可以对多列进行联合索引，当为expression时即“基于函数的索引”
3）TABLESPACE：指定存放索引的表空间(索引和原表不在一个表空间时效率更高)
4）STORAGE：可进一步设置表空间的存储参数
5）LOGGING | NOLOGGING：是否对索引产生重做日志(对大表尽量使用NOLOGGING来减少占用空间并提高效率)
6）COMPUTE STATISTICS：创建新索引时收集统计信息
7）NOCOMPRESS | COMPRESSnn：是否使用“键压缩”(使用键压缩可以删除一个键列中出现的重复值)
8）NOSORT | REVERSE：NOSORT表示与表中相同的顺序创建索引，REVERSE表示相反顺序存储索引值
9）PARTITION | NOPARTITION：可以在分区表和未分区表上对创建的索引进行分区
1.2 索引特点：
第一，通过创建唯一性索引，可以保证数据库表中每一行数据的唯一性。
第二，可以大大加快数据的检索速度，这也是创建索引的最主要的原因。
第三，可以加速表和表之间的连接，特别是在实现数据的参考完整性方面特别有意义。
第四，在使用分组和排序子句进行数据检索时，同样可以显著减少查询中分组和排序的时间。
第五，通过使用索引，可以在查询的过程中，使用优化隐藏器，提高系统的性能。
1.3 索引不足：
第一，创建索引和维护索引要耗费时间，这种时间随着数据量的增加而增加。
第二，索引需要占物理空间，除了数据表占数据空间之外，每一个索引还要占一定的物理空间，如果要建立聚簇索引，那么需要的空间就会更大。
第三，当对表中的数据进行增加、删除和修改的时候，索引也要动态的维护，这样就降低了数据的维护速度。
1.4 应该建索引列的特点：
1）在经常需要搜索的列上，可以加快搜索的速度；
2）在作为主键的列上，强制该列的唯一性和组织表中数据的排列结构；
3）在经常用在连接的列上，这些列主要是一些外键，可以加快连接的速度；
4）在经常需要根据范围进行搜索的列上创建索引，因为索引已经排序，其指定的范围是连续的；
5）在经常需要排序的列上创建索引，因为索引已经排序，这样查询可以利用索引的排序，加快排序查询时间；
6）在经常使用在WHERE子句中的列上面创建索引，加快条件的判断速度。
1.5 不应该建索引列的特点：
第一，对于那些在查询中很少使用或者参考的列不应该创建索引。这是因为，既然这些列很少使用到，因此有索引或者无索引，并不能提高查询速度。相反，由于增加了索引，反而降低了系统的维护速度和增大了空间需求。
第二，对于那些只有很少数据值的列也不应该增加索引。这是因为，由于这些列的取值很少，例如人事表的性别列，在查询的结果中，结果集的数据行占了表中数据行的很大比例，即需要在表中搜索的数据行的比例很大。增加索引，并不能明显加快检索速度。
第三，对于那些定义为blob数据类型的列不应该增加索引。这是因为，这些列的数据量要么相当大，要么取值很少。
第四，当修改性能远远大于检索性能时，不应该创建索引。这是因为，修改性能和检索性能是互相矛盾的。当增加索引时，会提高检索性能，但是会降低修改性能。当减少索引时，会提高修改性能，降低检索性能。因此，当修改性能远远大于检索性能时，不应该创建索引。
1.6 限制索引
限制索引是一些没有经验的开发人员经常犯的错误之一。在SQL中有很多陷阱会使一些索引无法使用。下面讨论一些常见的问题：
1.6.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条件，就可以使用索引，以避免全表扫描。
1.6.2 使用IS NULL 或IS NOT NULL
使用IS NULL 或IS NOT NULL同样会限制索引的使用。因为NULL值并没有被定义。在SQL语句中使用NULL会有很多的麻烦。因此建议开发人员在建表时，把需要索引的列设成 NOT NULL。如果被索引的列在某些行中存在NULL值，就不会使用这个索引（除非索引是一个位图索引，关于位图索引在稍后在详细讨论）。
1.6.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);
1.6.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也不能让您明白为什么做了一次“全表扫描”。
1.7 查询索引
查询DBA_INDEXES视图可得到表中所有索引的列表，注意只能通过USER_INDEXES的方法来检索模式(schema)的索引。访问USER_IND_COLUMNS视图可得到一个给定表中被索引的特定列。
1.8 组合索引
当某个索引包含有多个已索引的列时，称这个索引为组合（concatented）索引。在 Oracle9i引入跳跃式扫描的索引访问方法之前，查询只能在有限条件下使用该索引。比如：表emp有一个组合索引键，该索引包含了empno、 ename和deptno。在Oracle9i之前除非在where之句中对第一列（empno）指定一个值，否则就不能使用这个索引键进行一次范围扫描。
特别注意：在Oracle9i之前，只有在使用到索引的前导索引时才可以使用组合索引！
1.9 ORACLE ROWID
通过每个行的ROWID，索引Oracle提供了访问单行数据的能力。ROWID其实就是直接指向单独行的线路图。如果想检查重复值或是其他对ROWID本身的引用，可以在任何表中使用和指定rowid列。
ORACLE 索引具体应该怎么用啊？
create table bug(
seq_id number,
pc_id varchar2 (8),
desc_cont varchar2 (200),
bug_date date,
bug_cont varchar2 (200),
bug_state varchar2 (1),
bug_type varchar2 (2),
remarks varchar2 (200),
finishdate date,
pc_type varchar2 (2),
bug_ip varchar2 (20),
yuyue_date date
);
ALTER TABLE bug ADD (
CONSTRAINT bug PRIMARY KEY (seq_id)
);
这里面的seq_id是自动增长列。现在因为数据太多，为了增加效率。。要用到索引。索引字段有pc_id ，bug_date，bug_type
Oracle数据库中的索引详解
一 ROWID的概念
存储了row在数据文件中的具 *** 置 位编码的数据 A Z a z + 和 /
row在数据块中的存储方式
SELECT ROWID last_name FROM hr employees WHERE department_id = ;
比如 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;
具体到特定的物理文件
二 索引的概念
类似书的目录结构
Oracle 的 索引 对象 与表关联的可选对象 提高SQL查询语句的速度
索引直接指向包含所查询值的行的位置 减少磁盘I/O
与所索引的表是相互独立的物理结构
Oracle 自动使用并维护索引 插入 删除 更新表后 自动更新索引
语法 CREATE INDEX index ON table (column[ column] );
B tree结构(非bitmap)
[一]了解索引的工作原理
表 emp
目标 查询Frank的工资salary
建立索引 create index emp_name_idx on emp(name);
[试验]测试索引的作用
运行/rdbms/admin/utlxplan 脚本
建立测试表
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;
set autotrace trace explain
set timing on
分析表 可以得到cost
查询 object_name= DBA_INDEXES
在object_name列上建立索引
再查询
[思考]索引的代价
插入 更新
三 唯一索引
何时创建 当某列任意两行的值都不相同
当建立Primary Key(主键)或者Unique constraint(唯一约束)时 唯一索引将被自动建立
语法 CREATE UNIQUE INDEX index ON table (column);
演示
四 组合索引
何时创建 当两个或多个列经常一起出现在where条件中时 则在这些列上同时创建组合索引
组合索引中列的顺序是任意的 也无需相邻 但是建议将最频繁访问的列放在列表的最前面
演示(组合列 单独列)
五 位图索引
何时创建
列中有非常多的重复的值时候 例如某列保存了 性别 信息
Where 条件中包含了很多OR操作符
较少的update操作 因为要相应的跟新所有的bitmap
结构 位图索引使用位图作为键值 对于表中的每一数据行位图包含了TRUE( ) FALSE( ) 或NULL值
优点 位图以一种压缩格式存放 因此占用的磁盘空间比标准索引要小得多
语法 CREATE BITMAP INDEX index ON table (column[ column] );
掩饰
create table bitmaptable as select * from indextable where owner in( SYS PUBLIC );
分析 查找 建立索引 查找
六 基于函数的索引
何时创建 在WHERE条件语句中包含函数或者表达式时
函数包括 算数表达式 PL/SQL函数 程序包函数 SQL函数 用户自定义函数
语法 CREATE INDEX index ON table (FUNCTION(column));
演示
必须要分析表 并且query_rewrite_enabled=TRUE
或者使用提示/*+ INDEX(ic_index)*/
七 反向键索引
目的 比如索引值是一个自动增长的列
多个用户对集中在少数块上的索引行进行修改 容易引起资源的争用 比如对数据块的等待 此时建立反向索引
性能问题
语法
重建为标准索引 反之不行
八 键压缩索引
比如表landscp的数据如下
site feature job
Britten Park Rose Bed Prune
Britten Park Rose Bed Mulch
Britten Park Rose Bed Spray
Britten Park Shrub Bed Mulch
Britten Park Shrub Bed Weed
Britten Park Shrub Bed Hoe
……
查询时 以上 列均在where条件中同时出现 所以建立基于以上 列的组合索引 但是发现重复值很多 所以考虑压缩特性
Create index zip_idx
on landscp(site feature job)
press ;
将索引项分成前缀(prefix)和后缀(postfix)两部分 前两项被放置到前缀部分
Prefix : Britten Park Rose Bed
Prefix : Britten Park Shrub Bed
实际所以的结构为
Prune
Mulch
Spray
Mulch
Weed
Hoe
特点 组合索引的前缀部分具有非选择性时 考虑使用压缩 减少I/O 增加性能
九 索引组织表(IOT)
将表中的数据按照索引的结构存储在索引中 提高查询速度
牺牲插入更新的性能 换取查询性能 通常用于数据仓库 提供大量的查询 极少的插入修改工作
必须指定主键 插入数据时 会根据主键列进行B树索引排序 写入磁盘
十 分区索引
簇:
A cluster is a group of tables that share the same data blocks because they share mon columns and are often used together
lishixinzhi/Article/program/Oracle/201311/17769
网站名称：oracle如何引用索引 oracle添加索引
本文地址：
http://cdkjz.cn/article/hhpjos.html
