Oracle优化器基础知识

SmileNicky

发布于 2019-03-20 11:56:42

6560

发布于 2019-03-20 11:56:42

本博客介绍一下属于oracle优化器范畴的一些基础知识，访问数据的方法，分为直接访问数据的方法和访问索引的方法两种，然后有了这些基础知识后，可以参考学习我的另外一篇博客：Oracle优化器简介，对Oracle 的一些原理的简单介绍，对于学习oracle方面的SQL优化是有帮助的，https://cloud.tencent.com/developer/article/1399323

一、访问数据的方法

Oracle访问表中数据的方法有两种，一种是直接表中访问数据，另外一种是先访问索引，如果索引数据不符合目标SQL，就回表，符合就不回表，直接访问索引就可以。本博客先介绍直接访问数据的方法，下一篇博客在访问索引的方法

1、直接访问数据

Oracle直接访问表中数据的方法又分为两种：一种是全表扫描；另一种是ROWID扫描

1.1 全表扫描

全表扫描是Oracle直接访问数据的一种方法，全表扫描时从第一个区(EXTENT)的第一个块(BLOCK)开始扫描，一直扫描的到表的高水位线(High Water Mark)，这个范围内的数据块都会扫描到

全表扫描是采用多数据块一起扫的，并不是一个个数据库扫的，然后我们经常说全表扫描慢是针对数据量很多的情况，数据量少的话，全表扫描并不慢的，不过随着数据量越多，高水位线也就越高，也就是说需要扫描的数据库越多，自然扫描所需要的IO越多，时间也越多

注意：数据量越多，全表扫描所需要的时间就越多，然后直接删了表数据呢？查询速度会变快？其实并不会的，因为即使我们删了数据，高位水线并不会改变，也就是同样需要扫描那么多数据块

1.2 ROWID扫描

ROWID也就是表数据行所在的物理存储地址，所谓的ROWID扫描是通过ROWID所在的数据行记录去定位。ROWID是一个伪列，数据库里并没有这个列，它是数据库查询过程中获取的一个物理地址，用于表示数据对应的行数。用sql查询：

select t.* , rowid from 表格

随意获取一个ROWID序列：AAAWSJAAFAAAWwUAAA，前6位表示对象编号(Data Object number)，其后3位文件编号(Relative file number)，接着其后6位表示块编号(Block number)，再其后3位表示行编号(Row number)

ROWID编码方法是：A ~ Z表示0到25；a ~ z表示26到51；0~9表示52到61；+表示62；/表示63；刚好64个字符。

这里随意找张表查一下文件编号、区编号、行编号，查询后会返回rowid的一系列物理地址和文件编号(rowid_relative_fno(rowid))、块编号(rowid_block_number(rowid))、行编号(rowid_row_number(rowid))

select t.seq,
       rowid,
       dbms_rowid.rowid_relative_fno(rowid),
       dbms_rowid.rowid_block_number(rowid),
       dbms_rowid.rowid_row_number(rowid)
  from t_info t

SQL查询一下表格名称为TABLE的对象编码

select owner,object_id,data_object_id,status from dba_objects where object_name='TABLE';

相对文件id和绝对文件编码 相对文件id是指相对于表空间，在表空间唯一;绝对文件编码是指相当于全局数据库而言的，全局唯一；下面SQL查询一下相对文件id和绝对文件编码

select file_name,file_id,relative_fno from dba_data_files;

2、访问索引

对于Oracle数据库来说，B树索引是最常见的了，下面给出B树索引的图，图来自《基于Oracle的SQL优化》一书：

对于B树索引，分成两种类型的数据块，一种是索引分支块，另外一种是索引叶子块，索引根块是一种特殊的索引分支块。

影响逻辑读的缓存:

清Buffer Cache

alter system flush buffer_cache;//请勿随意在生产环境执行此语句

清数据字典缓存(Data Dictionary Cache)

alter system flush shared_pool;//请勿随意在生产环境执行此语句

2.1 索引唯一扫描

索引唯一性扫描(INDEX UNIQUE SCAN)是针对唯一性索引(UNIQUE INDEX)来说的，也就是建立唯一性索引才能索引唯一性扫描，唯一性扫描，其结果集只会返回一条记录。创建唯一性索引SQL是：

create unique index 索引名 on 表名(列名)

例子，例子来自《基于Oracle的SQL优化》一书：

scott用户登录

SQL> create table emp_temp as select * from emp;//创建一个测试表
SQL> create unique index idx_emp_temp on emp_temp(empno);//创建唯一型索引
SQL> set autotrace on//设置执行计划显示
SQL> select * from emp_temp where empno =7369;//走索引唯一性扫描

     EMPNO ENAME      JOB              MGR HIREDATE              SAL       COMM
---------- ---------- --------- ---------- -------------- ---------- ----------
    DEPTNO
----------
      7369 SMITH      CLERK           7902 17-12月-80            800
        20



执行计划
----------------------------------------------------------
Plan hash value: 3451700904

--------------------------------------------------------------------------------

------------

| Id  | Operation                   | Name         | Rows  | Bytes | Cost (%CPU)

| Time     |

--------------------------------------------------------------------------------

------------

|   0 | SELECT STATEMENT            |              |     1 |    87 |     1   (0)

| 00:00:01 |

|   1 |  TABLE ACCESS BY INDEX ROWID| EMP_TEMP     |     1 |    87 |     1   (0)

| 00:00:01 |

|*  2 |   INDEX UNIQUE SCAN         | IDX_EMP_TEMP |     1 |       |     0   (0)

| 00:00:01 |

--------------------------------------------------------------------------------

------------


Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("EMPNO"=7369)

2.2 索引范围扫描

索引范围扫描(INDEX RANGE SCAN)适用于所有类型的B树索引，一般不包括唯一性索引，因为唯一性索引走索引唯一性扫描。当扫描的对象是非唯一性索引的情况，where谓词条件为Between、=、<、>等等的情况就是索引范围扫描，注意，可以是等值查询，也可以是范围查询。如果where条件里有一个索引键值列没限定为非空的，那就可以走索引范围扫描，如果改索引列是非空的，那就走索引全扫描

前面说了，同样的SQL建的索引不同，就可能是走索引唯一性扫描，也有可能走索引范围扫描。在同等的条件下，索引范围扫描所需要的逻辑读和索引唯一性扫描对比，逻辑读如何？索引范围扫描可能返回多条记录，所以优化器为了确认，肯定会多扫描，所以在同等条件，索引范围扫描所需要的逻辑读至少会比相应的唯一性扫描的逻辑读多1

继续上面的例子：

SQL> drop index idx_emp_temp;//删了刚才的唯一性索引

索引已删除。

SQL> create index idx_emp_temp on emp_temp(empno);//创建一个B树索引

索引已创建。

SQL> select * from emp_temp where empno=7369;//走索引范围扫描，因为empno没有限定为非空

     EMPNO ENAME      JOB              MGR HIREDATE              SAL       COMM
---------- ---------- --------- ---------- -------------- ---------- ----------
    DEPTNO
----------
      7369 SMITH      CLERK           7902 17-12月-80            800
        20



执行计划
----------------------------------------------------------
Plan hash value: 351331621

--------------------------------------------------------------------------------

------------

| Id  | Operation                   | Name         | Rows  | Bytes | Cost (%CPU)

| Time     |

--------------------------------------------------------------------------------

------------

|   0 | SELECT STATEMENT            |              |     1 |    87 |     2   (0)

| 00:00:01 |

|   1 |  TABLE ACCESS BY INDEX ROWID| EMP_TEMP     |     1 |    87 |     2   (0)

| 00:00:01 |

|*  2 |   INDEX RANGE SCAN          | IDX_EMP_TEMP |     1 |       |     1   (0)

| 00:00:01 |

--------------------------------------------------------------------------------

------------


Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("EMPNO"=7369)

Note
-----
   - dynamic sampling used for this statement (level=2)

2.3 索引全扫描

索引全扫描(INDEX FULL SCAN)适用于所有类型的B树索引(包括唯一性索引和非唯一性索引)。

索引全扫描过程简述：索引全扫描是指扫描目标索引所有叶子块的索引行，但不意思着需要扫描所有的分支块，索引全扫描时只需要访问必要的分支块，然后定位到位于改索引最左边的叶子块的第一行索引行，就可以利用改索引叶子块之间的双向指针链表，从左往右依次顺序扫描所有的叶子块的索引行

索引全扫描的例子：直接查emp表就好，因为empno是非空的

SQL> select * from emp;

     EMPNO ENAME      JOB              MGR HIREDATE              SAL       COMM
---------- ---------- --------- ---------- -------------- ---------- ----------
    DEPTNO
----------
      7369 SMITH      CLERK           7902 17-12月-80            800
        20

      7499 ALLEN      SALESMAN        7698 20-2月 -81           1600        300
        30

      7521 WARD       SALESMAN        7698 22-2月 -81           1250        500
        30


     EMPNO ENAME      JOB              MGR HIREDATE              SAL       COMM
---------- ---------- --------- ---------- -------------- ---------- ----------
    DEPTNO
----------
      7566 JONES      MANAGER         7839 02-4月 -81           2975
        20

      7654 MARTIN     SALESMAN        7698 28-9月 -81           1250       1400
        30

      7698 BLAKE      MANAGER         7839 01-5月 -81           2850
        30


     EMPNO ENAME      JOB              MGR HIREDATE              SAL       COMM
---------- ---------- --------- ---------- -------------- ---------- ----------
    DEPTNO
----------
      7782 CLARK      MANAGER         7839 09-6月 -81           2450
        10

      7788 SCOTT      ANALYST         7566 19-4月 -87           3000
        20

      7839 KING       PRESIDENT            17-11月-81           5000
        10


     EMPNO ENAME      JOB              MGR HIREDATE              SAL       COMM
---------- ---------- --------- ---------- -------------- ---------- ----------
    DEPTNO
----------
      7844 TURNER     SALESMAN        7698 08-9月 -81           1500          0
        30

      7876 ADAMS      CLERK           7788 23-5月 -87           1100
        20

      7900 JAMES      CLERK           7698 03-12月-81            950
        30


     EMPNO ENAME      JOB              MGR HIREDATE              SAL       COMM
---------- ---------- --------- ---------- -------------- ---------- ----------
    DEPTNO
----------
      7902 FORD       ANALYST         7566 03-12月-81           3000
        20

      7934 MILLER     CLERK           7782 23-1月 -82           1300
        10


已选择14行。


执行计划
----------------------------------------------------------
Plan hash value: 3956160932

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |    14 |   532 |     3   (0)| 00:00:01 |
|   1 |  TABLE ACCESS FULL| EMP  |    14 |   532 |     3   (0)| 00:00:01 |
--------------------------------------------------------------------------

2.4 索引快速全扫描

索引快速全扫描和索引全扫描很类似，也适用于所有类型的B树索引(包括唯一性索引和非唯一性索引)。和索引全扫描类似，也是扫描所有叶子块的索引行，这些都是索引快速全扫描和索引全扫描的相同点

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

索引快速全扫描只适应于CBO(基于成本的优化器)
索引快速全扫描可以使用多块读，也可以并行执行
索引全扫描会按照叶子块排序返回，而索引快速全扫描则是按照索引段内存储块顺序返回
索引快速全扫描的执行结果不一定是有序的，而索引全扫描的执行结果是有序的，因为索引快速全扫描是根据索引行在磁盘的物理存储顺序来扫描的，不是根据索引行的逻辑顺序来扫描的

条件是使用复合索引，而且使用Hint

/*+ index_ffs(表名 索引名) */

select /*+ index_ffs(emp_test pk_emp_test) */ empno from emp_test;

例子：

SQL> create table emp_test (empno number,ename varchar2(50));

表已创建。

SQL> alter table emp_test add constraint pk_emp_test primary key (empno,ename);

表已更改。

SQL> insert into emp_test select empno,ename from emp;

已创建14行。

SQL> commit;

提交完成。

SQL> select count(1) from emp_test;

SQL> select /*+ index_ffs(emp_test pk_emp_test) */ empno from emp_test;

     EMPNO
----------
      7369
      7499
      7521
      7566
      7654
      7698
      7782
      7788
      7839
      7844
      7876

     EMPNO
----------
      7900
      7902
      7934

已选择14行。


执行计划
----------------------------------------------------------
Plan hash value: 3550420785

--------------------------------------------------------------------------------

----

| Id  | Operation            | Name        | Rows  | Bytes | Cost (%CPU)| Time
   |

--------------------------------------------------------------------------------

----

|   0 | SELECT STATEMENT     |             |    14 |   182 |     2   (0)| 00:00:

01 |

|   1 |  INDEX FAST FULL SCAN| PK_EMP_TEST |    14 |   182 |     2   (0)| 00:00:

01 |

--------------------------------------------------------------------------------

----


Note
-----
   - dynamic sampling used for this statement (level=2)

2.5 索引跳跃式扫描

索引跳跃式扫描(INDEX SKIP SCAN)适用于所有类型的***复合B树索引***(包括唯一性索引和非唯一性索引)，索引跳跃式扫描可以使那些在where条件中没有目标索引的前导列指定查询条件但是有索引的非前导列指定查询条件的目标SQL依然可以使用跳跃索引，定义解释有点绕，举个例子说明

假如新建了复合索引:

create index 索引名 on 表名(列名1,列名2)

这里新建了复合索引，假如查询如：

select * from 表名 where 列名1 = 条件1

假如改目标SQL符合索引跳跃式扫描的条件，即使，只有列名1这个前导列，还是可以走索引跳跃式扫描的，这个就是跳跃式扫描，不需要如下sql，全部索引列都定位到

select * from 表名 where 列名1 = 条件1 and 列名2 = 条件2

当然索引跳跃式扫描并不是说适用所有情况，不加前导列，有时候是不走跳跃式扫描的，Oracle中的索引跳跃式扫描仅仅适用于那些目标索引前导列的distinct值数量较少，后续非导列的可选择性又非常好的情况，索引跳跃式扫描的执行效率一定会随着目标索引前导列的distinct值数量的递增而递减的

拓展补充

对于索引来说，如果索引条件有null值，是不走索引的

本文参与腾讯云自媒体分享计划，分享自作者个人站点/博客。

原始发表：2019年03月19日，如有侵权请联系 cloudcommunity@tencent.com 删除

oracle

sql

数据库

本文分享自作者个人站点/博客前往查看

如有侵权，请联系 cloudcommunity@tencent.com 删除。

本文参与腾讯云自媒体分享计划，欢迎热爱写作的你一起参与！

oracle

sql

数据库

登录后参与评论

0 条评论

热度