Oracle 数据库表访问方法

两种访问方法：全表扫描、索引扫描

表中数据有100万行，整个表是堆表，没有任何索引。

CREATE TABLE SELECT_TEST  
   ( ID  NUMBER(*,0), 
V1  VARCHAR2(100), 
V2  VARCHAR2(100), 
V3  VARCHAR2(100), 
V4  VARCHAR2(100), 
V5  VARCHAR2(100), 
V6  VARCHAR2(100), 
V7  VARCHAR2(100), 
V8  VARCHAR2(100), 
V9  VARCHAR2(100)
   ) ;

通过以下语句插入数据

insert into select_test
select level,lpad('a',100,'a'),lpad('a',100,'a'),lpad('a',100,'a'),lpad('a',100,'a'),lpad('a',100,'a'),lpad('a',100,'a'),lpad('a',100,'a'),lpad('a',100,'a'),lpad('a',100,'a')
 from dual connect by level<=1000000;

此时执行以下三个语句

select * from  select_test where id =1;
select * from  select_test where id =1000;
select * from  select_test where id =1000000;

通过查询计划可知: set autotrace traceonly

全表扫描:三个查询都要扫描完全部的数据才能返回数据。故三个语句的查询速度是一样的。

如果执行以下三个语句

select * from SELECT_TEST t where t.id=1 and rownum=1;    --速度最快
select * from SELECT_TEST t where t.id=10000 and rownum=1;      --速度介于上下两者之间
select * from SELECT_TEST t where t.id=1000000 and rownum=1;    --速度最慢

当建立主键约束或者唯一约束或者建立索引时（主键约束、唯一约束会自动建立索引，可通过select * from user_indexes查询）。

通过查询计划可知: set autotrace traceonly

索引扫描：三个查询都会先访问索引块，然后才会去查询数据块。因为所有的数据都在索引的叶子节点，所以必定要先访问到叶子节点，然后才会根据叶子节点地址去找数据。因叶子节点的深度一样，故三个语句的查询速度还是一样的。同理，select min(id) from select_test;select max(id) from select _test;语句执行速度也是一样的。

但select min(id), max(id) from SELECT_TEST 相比于单个的最大/最小值查询则会多很多的逻辑读。

数据访问时选择全表扫描还是索引扫描还和数据存储以及数据分布有关。

如果数据存储格式为1 2 3 4 5 6 7 8 9 10 1 2 3 4......这种循环的格式，可能全表扫描的效率更高。

如果数据存储格式为1 1 1 1 1 1 1 2 2 2 2 2 2 2 2......这种格式存储的数据，索引扫描的效率更高些。

如果数据存储格式为1 2 3 4 5 6 6 6 6 6 6 6 6 6 6......这个存储格式的数据当选择6时，全表扫描效率更高。当选择其他数据时索引扫描效率更高。

create table delete_test
as

select * from SELECT_TEST where id<=2000;

delete from delete_test where id<1000;

另全表扫描和高水位线有关，如果将数据删除了一部分:

select count(*) from delete_test;   --执行速度和删除前速度一样。

select * from delete_test where id=1; --执行速度和删除前速度一样。

select * from delete_test where id=1999; --执行速度和删除前速度一样。

如果是建立主键约束或者唯一约束或者建立索引，则

select * from delete_test where id=1; --执行速度比删除前速度快，少一个由索引块信息到数据块的读过程。

select * from delete_test where id=1999; --执行速度和删除前速度一样。