Oracle数据访问和索引的使用

· 通过全表扫描的方式访问数据；

我们提供的服务有：成都网站设计、网站制作、微信公众号开发、网站优化、网站认证、阳西ssl等。为千余家企事业单位解决了网站和推广的问题。提供周到的售前咨询和贴心的售后服务，是有科学管理、有技术的阳西网站制作公司

· 通过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全表扫描

1、如果该记录在数据库表（user）的第一条。在oracle中是不是就不会再去查询下面的记录？还是把表中的所有记录加载到缓存中再进行过滤？

答: 因为没有索引， Oracle 不知道 满足 username='test' 条件的， 总共有多少个， 所以全表还是要继续的。

2、如果username这个字段有索引的话，情况又是什么样子？

如果是 RBO (基于规则的优化)， 那么自动强制使用索引。

也就是先去索引那里找， 有哪些索引记录是 username='test' 的。

通过这些索引的 信息，拿到相关的 行的 物理位置的ID。

然后再通过这些 行的物理位置的ID， 去表里面，提取出相应的行。返回。

如果是 CBO(基于成本的优化)，那么首先分析，索引有没有使用的价值。

例如一百万条记录里面， 只有10个 username='test' 的记录，那么Oracle 将使用这个索引。（使用索引的操作步骤，同RBO）

假如一百万条记录里面，有50万条记录的username='test' 的记录，那么Oracle 发现使用索引，产生的工作量，比全表扫描还慢，因为要读取50W条索引，+50W条记录。于是就会选择直接全表扫描，不使用索引。

全表扫描

SELECT JOB_LOG_ID, JOB_GROUP FROM

SYS_JOB_LOG;

SELECT /*+ FULL(SYS_JOB_LOG) */ JOB_LOG_ID, JOB_GROUP FROM

SYS_JOB_LOG

SELECT JOB_LOG_ID, JOB_GROUP FROM

SYS_JOB_LOG

MINUS SELECT JOB_LOG_ID, JOB_GROUP FROM SYS_JOB_LOG@"DMSF3201_6_1_144_99"

SELECT /*+ FULL(SYS_JOB_LOG) / JOB_LOG_ID, JOB_GROUP FROM

SYS_JOB_LOG

MINUS SELECT / + FULL(SYS_JOB_LOG) */ JOB_LOG_ID, JOB_GROUP FROM SYS_JOB_LOG@"DMSF3201_6_1_144_99"

1、什么是全表扫描?

全表扫描就是扫表表中所有的行，实际上是扫描表中所有的数据块，因为Oracle中最小的存储单位是Oracle block。

扫描所有的数据块就包括高水位线以内的数据块，即使是空数据块在没有被释放的情形下也会被扫描而导致I/O增加。

在全表扫描期间，通常情况下，表上这些相邻的数据块被按顺序(sequentially)的方式访问以使得一次I/O可以读取多个数据块。

一次读取更多的数据块有助于全表扫描使用更少的I/O，对于可读取的数据块被限制于参数

hint 关键字： /+ full(表名)/

一般来说，sql 优化时，尽量走 index，但不绝对

有时候，全表扫描的效率比索引高

'索引' 和 '全表扫描' 的区别:

实际应用：

当查询 sql 时间范围较长时

当 sql 中使用索引(执行计划)，还是查询很慢时

Oracle 中实现随机抽取数据

一、Oracle取随机数据 1、Oracle访问数据的基本方法： 1）、全表扫描(Full table Scan)：执行全表扫描，Oracle读表中的所有记录，考查每一行是否满足WHERE条件。Oracle顺序的读分配给该表的每一个数据块，且每个数据块Oracle只读一次.这样全表扫描能够受益于多块读. 2）、采样表扫描(sample table scan)：扫描返回表中随机采样数据，这种访问方式需要在FROM语句中包含SAMPLE选项或者SAMPLE BLOCK选项. 注：从Oracle8i开始Oracle提供采样表扫描特性

2、使用sample获得随机结果集 2.1、语法： SAMPLE BLOCK [ SEED (seed_value) ] SAMPLE选项：表示按行采样来执行一个全表扫描，Oracle从表中读取特定百分比的记录，并判断是否满足WHERE子句以返回结果。 BLOCK： 表示使用随机块例举而不是随机行例举。 sample_percent：是随机获取一张表中记录的百分比。比如值为10，那就是表中的随机的百分之10的记录。 值必须大于等于.000001，小于100。

SEED：表示从哪条记录返回，类似于预先设定例举结果，因而每次返回的结果都是固定的。该值必须介于0和4294967295之间。

2.2、举例说明

(1)sample(sample_percent):

(2)、sample block(sample_percent)

(3)、sample block(sample_percent) seed(seed_value)

注意以下几点:

1.sample只对单表生效，不能用于表连接和远程表 2.sample会使SQL自动使用CBO

3、使用DBMS_RANDOM包 DBMS_RANDOM有两种主要的使用方法分别是：DBMS_RANDOM.VALUE()和DBMS_RANDOM.RANDOM

4、使用 内部函数sys_guid()

****注:****

在使用sys_guid() 这种方法时,有时会获取到相同的记录，即和前一次查询的结果集是一样的，查找相关资料，有些说是和 操作系统 有关，在windows平台下正常，获取到的数据是随机的，而在 Linux 等平台下始终是相同不变的数据集，有些说是因为sys_guid()函数本身的问题，即sys_guid()会在查询上生成一个16字节的全局唯一标识符，这个标识符在绝大部分平台上由一个宿主标识符和进程或进程的线程标识符组成，这就是说，它很可能是随机的，但是并不表示一定是百分之百的这样。

所以,为确保在不同的平台每次读取的数据都是随机的,我们大多采用使用sample函数或者DBMS_RANDOM包获得随机结果集，其中使用sample函数更常用，因为其查询时缩小了查询范围，在查询大表，且要提取数据不是很不多的情况下，会对查询速度上有明显的提高。

如何查找Oracle中存在全表扫描的SQL语句

1． 对返回的行无任何限定条件，即没有where 子句

2． 未对数据表与任何索引主列相对应的行限定条件

例如：在City-State-Zip列创建了三列复合索引，那么仅对State列限定条件不能使用这个索引，因为State不是索引的主列。

3． 对索引的主列有限定条件，但是在条件表达式里使用以下表达式则会使索引失效，造成全表扫描：

（1）where子句中对字段进行函数、表达式操作，这将导致引擎放弃使用索引而进行全表扫描,

Demo:

where upper(city)='TokYo' 或 City || 'X' like 'TOKYO%',

select id from t where num/2=100 应改为: select id from t where num=100*2

select * from emp where to_char(hire_date,'yyyymmdd')='20080411' (不使用)

select * from emp where hire_date = to_char('20080411','yyyymmdd') (使用)

（2）查询字段is null时索引失效，引起全表扫描。

where City is null 或 ,where City is not null,

解决方法：SQL语法中使用NULL会有很多麻烦，最好索引列都是NOT NULL的；对于is null，可以建立组合索引，nvl(字段,0),对表和索引analyse后，is null查询时可以重新启用索引查找,但是效率还不是值得肯定；is not null 时永远不会使用索引。一般数据量大的表不要用is null查询。

select id from t where num is null

可以在num上设置默认值0，确保表中num列没有null值，然后这样查询：

select id from t where num=0

(3)查询条件中使用了不等于操作符（、!=）会限制索引、引起全表扫描

Where city!='TOKYO'.

解决方法：通过把不等于操作符改成or，可以使用索引，避免全表扫描。例如，把column’aaa’，改成column’aaa’ or column’aaa’，就可以使用索引了。

(4)对索引的主列有限定条件，但是条件使用like操作以及值以‘%’开始或者值是一个赋值变量。例如：

where City like '%YOK%'

where City like: City_bind_Variable xl_rao

select * from emp where name like '%A' (不使用索引)

select * from emp where name like 'A%' (使用索引)

解决办法：首先尽量避免模糊查询，如果因为业务需要一定要使用模糊查询，则至少保证不要使用全模糊查询，对于右模糊查询，即like ‘…%’，是会使用索引的；左模糊like ‘%...’无法直接使用索引，但可以利用reverse + function index 的形式，变化成 like ‘…%’；全模糊是无法优化的，一定要的话考虑用搜索引擎。出于降低数据库服务器的负载考虑，尽可能地减少数据库模糊查询。

4． or语句使用不当会引起全表扫描

原因：where子句中比较的两个条件，一个有索引，一个没索引，使用or则会引起全表扫描。例如：where A=：1 or B=：2，A上有索引，B上没索引，则比较B=：2时会重新开始全表扫描

5．模糊查询效率很低：

原因：like本身效率就比较低，应该尽量避免查询条件使用like；对于like‘%...%’（全模糊）这样的条件，是无法使用索引的，全表扫描自然效率很低；另外，由于匹配算法的关系，模糊查询的字段长度越大，模糊查询效率越低。

解决办法：首先尽量避免模糊查询，如果因为业务需要一定要使用模糊查询，则至少保证不要使用全模糊查询，对于右模糊查询，即like‘…%’，是会使用索引的；左模糊like

‘%...’无法直接使用索引，但可以利用reverse + function index的形式，变化成like‘…%’；全模糊是无法优化的，一定要的话考虑用搜索引擎。出于降低数据库服务器的负载考虑，尽可能地减少数据库模糊查询。

6．查询条件中含有is null的select语句执行慢

原因：Oracle 中，查询字段is null时单索引失效，引起全表扫描。

解决方法：SQL语法中使用NULL会有很多麻烦，最好索引列都是NOT NULL的；对于is null，可以建立组合索引，nvl(字段,0),对表和索引analyse后，is null查询时可以重新启用索引查找,但是效率还不是值得肯定；is not null时永远不会使用索引。一般数据量大的表不要用is null查询。

7.查询条件中使用了不等于操作符（、!=）的select语句执行慢

原因：SQL中，不等于操作符会限制索引，引起全表扫描，即使比较的字段上有索引

　解决方法：通过把不等于操作符改成or，可以使用索引，避免全表扫描。例如，把column’aaa’，改成column’aaa’or column’aaa’，就可以使用索引了。

8.使用组合索引，如果查询条件中没有前导列，那么索引不起作用，会引起全表扫描；但是从Oracle9i开始，引入了索引跳跃式扫描的特性，可以允许优化器使用组合索引，即便索引的前导列没有出现在WHERE子句中。例如：create index skip1 on emp5(job,empno); 全索引扫描select count(*) from emp5 where empno=7900; 索引跳跃式扫描select /*+ index(emp5 skip1)*/ count(*) from emp5 where empno=7900;前一种是全表扫描，后一种则会使用组合索引。

9. or语句使用不当会引起全表扫描

原因：where子句中比较的两个条件，一个有索引，一个没索引，使用or则会引起全表扫描。例如：where A=：1 or B=：2，A上有索引，B上没索引，则比较B=：2时会重新开始全表扫描。

10.组合索引，排序时应按照组合索引中各列的顺序进行排序，即使索引中只有一个列是要排序的，否则排序性能会比较差。例如：create index skip1 on emp5(job,empno，date); select job，empno from emp5 where job=’manager’and empno=’10’order by job,empno,date desc;实际上只是查询出符合job=’manager’and empno=’10’条件的记录并按date降序排列，但是写成order by date desc性能较差。

11.Update语句，如果只更改1、2个字段，不要Update全部字段，否则频繁调用会引起明显的性能消耗，同时带来大量日志。

　12.对于多张大数据量的表JOIN，要先分页再JOIN，否则逻辑读会很高，性能很差。

　13.select count(*) from table；这样不带任何条件的count会引起全表扫描，并且没有任何业务意义，是一定要杜绝的。

14.sql的where条件要绑定变量，比如where column=：1，不要写成where column=‘aaa’，这样会导致每次执行时都会重新分析，浪费CPU和内存资源。

15.不要使用in操作符，这样数据库会进行全表扫描，

推荐方案：在业务密集的SQL当中尽量不采用IN操作符

16.not in 使用not in也不会走索引

推荐方案：用not exists或者（外联结+判断为空）来代替

17. 及 操作符（大于或小于操作符）

大于或小于操作符一般情况下是不用调整的，因为它有索引就会采用索引查找，但有的情况下可以对它进行优化，如一个表有100万记录，一个数值型字段 A，30万记录的A=0，30万记录的A=1，39万记录的A=2，1万记录的A=3。那么执行A2与A=3的效果就有很大的区别了，因为A2时ORACLE会先找出为2的记录索引再进行比较，而A=3时ORACLE则直接找到=3的记录索引。

18.UNION操作符

UNION在进行表链接后会筛选掉重复的记录，所以在表链接后会对所产生的结果集进行排序运算，删除重复的记录再返回结果。实际大部分应用中是不会产生重复的记录，最常见的是过程表与历史表UNION。如：

select * from gc_dfys

union

select * from ls_jg_dfys

这个SQL在运行时先取出两个表的结果，再用排序空间进行排序删除重复的记录，最后返回结果集，如果表数据量大的话可能会导致用磁盘进行排序。

推荐方案：采用UNION ALL操作符替代UNION，因为UNION ALL操作只是简单的将两个结果合并后就返回。

19.WHERE后面的条件顺序影响

WHERE子句后面的条件顺序对大数据量表的查询会产生直接的影响，如

Select * from zl_yhjbqk where dy_dj = '1K以下' and xh_bz=1

Select * from zl_yhjbqk where xh_bz=1 and dy_dj = '1K以下'

以上两个SQL中dy_dj及xh_bz两个字段都没进行索引，所以执行的时候都是全表扫描，第一条SQL的dy_dj = '1KV以下'条件在记录集内比率为99%，而xh_bz=1的比率只为0.5%，在进行第一条SQL的时候99%条记录都进行dy_dj及xh_bz的比较，而在进行第二条SQL的时候0.5%条记录都进行dy_dj及xh_bz的比较，以此可以得出第二条SQL的CPU占用率明显比第一条低。

20.查询表顺序的影响

在FROM后面的表中的列表顺序会对SQL执行性能影响，在没有索引及ORACLE没有对表进行统计分析的情况下ORACLE会按表出现的顺序进行链接，由此因为表的顺序不对会产生十分耗服务器资源的数据交叉。（注：如果对表进行了统计分析，ORACLE会自动先进小表的链接，再进行大表的链接）

分享题目：oracle如何全表扫描 oracle强制全表扫描
转载来于：http://ybzwz.com/article/hjscid.html