《基于Oracle的SQL优化》读书笔记之访问数据的方法

1.1 优化器的基础知识  

1.1.1访问数据的方法

Oracle访问表中数据的方法有两种：一种是直接访问表，一种是先访问索引再回表。

1.1.1.1访问表的方法

直接访问表的方法有两种：一种是全表扫描，另一种是ROWID扫描。

全表扫描是指Oracle在访问目标表的数据时，会从该表所占用的第一个区（EXTENT）的第一个块（BLOCK）开始扫描，一直扫描到该表的高水位线。

Oracle在做全表扫描时会使用多块读，这在目标表的数据量不大时执行效率是非常高的，但执行时间会随着目标表数据量的递增而递增。

在oracle中，如果对目标表不停地插入数据，分配给该表的现有空间不足时高水位线就会向上移动，但如果使用delete语句删除该表数据，高水位线不会随之往下移动。因此即使delete目标表的全部数据，高水位线还是在原来的位置。这意味着全表扫描该表时oracle还是需要扫描该表高水位线下的所有数据块，所以此时对该表的全表扫描操作所耗费的时间与之前相比并不会有明显的改观。

ROWID扫描是指oracle在访问目标表里的数据时，直接通过数据所在的ROWID去定位并访问这些数据。ROWID表示的是oracle中的数据行记录所在的物理存储地址。

ROWID扫描包含两层含义：一种是根据用户在SQL语句中输入的ROWID的值直接访问对应的数据行记录；另一种是先去访问相关的索引，然后根据访问索引后得到的ROWID再回表去访问对应的数据行记录。（看到这里，得出结论其实先访问索引再回表就是直接访问表的ROWID扫描。）

1.1.1.2访问索引的方法

唯一性索引比非唯一性索引节省存储空间，如果索引列中数据肯定是唯一的话，尽量创建唯一性索引。

通过B树索引访问表里行记录的效率不会随着相关表的数据量的递增而显著降低。

Oracle里通过B树索引访问数据的过程是先访问相关的B树索引，然后根据访问该索引得到的ROWID再回表访问对应的数据行记录。

Oracle访问索引的成本包括两部分：一部分是访问相关的B树索引的成本；另一部分是回表的成本。

1索引唯一性扫描（INDEX UNIQUE SCAN）

针对唯一性索引（UNIQUE INDEX），适用于where条件是等值查询的目标SQL。

2索引范围扫描（INDEX RANGE SCAN）

适用于所有类型的B树索引，当扫描的对象是唯一性索引时，目标SQL的where条件一定是范围查询（谓词条件是BETWEEN、<、>）；扫描的对象是非唯一性索引时，目标SQL的where条件没有限制，可以是等值查询，也可以是范围查询。

3索引全扫描（INDEX FULL SCAN）

索引全扫描，要扫描目标索引所有叶子块的所有索引行。索引全扫描的执行结果是有序的，并且是按照该索引的索引键值列来排序，这意味着走索引全扫描能够既达到排序的效果，又同时避免了对该索引的索引键值列的真正排序操作。

索引全扫描的扫描结果的有序性决定了索引全扫描是不能并行执行的，通常情况下索引全扫描使用的是单块读。

索引全扫描是不需要回表的，索引全扫描适用于目标SQL的查询列全部是目标索引键值列的情形。

Oracle中能做索引全扫描的前提条件是目标索引至少有一个索引键值列的属性是NOT NULL。

4索引快速全扫描（INDEX FAST FULL SCAN）

索引快速全扫描跟索引全扫描一样，适用于所有类型的B树索引（唯一性索引和非唯一性索引），索引快速全扫描也需要扫描目标索引所有叶子块的所有索引行。

索引快速全扫描和索引快速扫描的区别：

1） 索引快速全扫描只适用于CBO。

2） 索引快速全扫描可以使用多块读，也可以并行执行。

3） 索引快速全扫描的执行结果不一定是有序的。索引快速全扫描时oracle是根据索引行在磁盘上的物理存储顺序来扫描，不是根据索引行的逻辑顺序来扫描的，所以扫描结果不一定有序。

5索引跳跃式扫描（INDEX SKIP SCAN）

索引跳跃式扫描适用于所有类型的复合B树索引（包括唯一性索引和非唯一性索引），使那些在WHERE条件中没有对目标索引的前导列指定查询条件单同时又对索引的非前导列指定了查询条件的目标SQL依然可以使用该索引。

创建测试表

SQL> create table employee(gender varchar2(1),employee_id number);

将该表的列EMPLOYEE_ID的属性设为NOT NULL

SQL> alter table employee modify (employee_id not null);

表已更改。

创建复合B树索引，其中列GENDER是索引的前导列，列EMPLOYEE_ID是索引的第二列。

SQL> create index idx_employee on employee(gender,employee_id);

索引已创建。

插入10000条记录，其中5000条记录的列GENDER值是F,另外5000条记录的列GENDER值是M。

  1  begin

  2  for i in 1..5000 loop

  3  insert into employee values ('F',i);

  4  end loop;

  5  commit;

  6* end;

SQL> /

PL/SQL 过程已成功完成。

  1  begin

  2  for i in 1..5000 loop

  3  insert into employee values ('F',i);

  4  end loop;

  5  commit;

  6* end;

SQL> /

PL/SQL 过程已成功完成。

收集统计信息

SQL> exec dbms_stats.gather_table_stats(ownname=>'HR',tabname=>'EMPLOYEE',estima

te_percent=>100,cascade=>true,method_opt=>'for all columns size 1');

PL/SQL 过程已成功完成。

执行SQL

Oracle 中的索引跳跃式扫描仅仅适用于那些目标索引前导列的distinct值数量较少、后续非前导列的可选择性又非常好的情况，因为索引跳跃式扫描的执行效率一定会随着目标索引前导列的distinct值数量的递增而递减。

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/24986149/viewspace-1200653/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/24986149/viewspace-1200653/