专栏首页从ORACLE起航,领略精彩的IT技术。SQL Tuning 基础概述10 - 体会索引的常见执行计划

SQL Tuning 基础概述10 - 体会索引的常见执行计划

在《SQL Tuning 基础概述05 - Oracle 索引类型及介绍》的1.5小节,提到了几种"索引的常见执行计划":

INDEX FULL SCAN:索引的全扫描,单块读,有序 INDEX RANGE SCAN:索引的范围扫描 INDEX FAST FULL SCAN:索引的快速全扫描,多块读,无序 INDEX FULL SCAN(MIN/MAX):针对MAX(),MIN()函数的查询 INDEX SKIP SCAN:查询条件没有用到组合索引的第一列,而组合索引的第一列重复度较高时,可能用到

本文用简单的测试案例,体会下索引使用这些执行计划的场景:

1.准备测试环境

创建测试表和索引:

conn jingyu/jingyu
drop table test_objects;
create table test_objects as select * from all_objects;
create index idx_test_objects_1 on test_objects(owner, object_name, subobject_name);
create index idx_test_objects_2 on test_objects(object_id);

查看测试表结构:

SQL> desc test_objects;
 Name                                                              Null?    Type
 ----------------------------------------------------------------- -------- --------------------------------------------
 OWNER                                                             NOT NULL VARCHAR2(30)
 OBJECT_NAME                                                       NOT NULL VARCHAR2(30)
 SUBOBJECT_NAME                                                             VARCHAR2(30)
 OBJECT_ID                                                         NOT NULL NUMBER
 DATA_OBJECT_ID                                                             NUMBER
 OBJECT_TYPE                                                                VARCHAR2(19)
 CREATED                                                           NOT NULL DATE
 LAST_DDL_TIME                                                     NOT NULL DATE
 TIMESTAMP                                                                  VARCHAR2(19)
 STATUS                                                                     VARCHAR2(7)
 TEMPORARY                                                                  VARCHAR2(1)
 GENERATED                                                                  VARCHAR2(1)
 SECONDARY                                                                  VARCHAR2(1)
 NAMESPACE                                                         NOT NULL NUMBER
 EDITION_NAME                                                               VARCHAR2(30)

查看测试表上的索引信息:

SQL> select index_name, column_name, column_position from user_ind_columns where table_name = 'TEST_OBJECTS';

INDEX_NAME                     COLUMN_NAME                              COLUMN_POSITION
------------------------------ ---------------------------------------- ---------------
IDX_TEST_OBJECTS_1             OWNER                                                  1
IDX_TEST_OBJECTS_1             OBJECT_NAME                                            2
IDX_TEST_OBJECTS_1             SUBOBJECT_NAME                                         3
IDX_TEST_OBJECTS_2             OBJECT_ID                                              1

分析表并清空测试环境的shared_pool和buffer_cache:

analyze table test_objects compute statistics;
alter system flush shared_pool;
alter system flush buffer_cache;

2.编写SQL语句

根据不同执行计划的场景,编写SQL语句:

--INDEX RANGE SCAN(索引的范围扫描)
SELECT owner, object_name FROM test_objects WHERE owner = 'SYS' AND object_name = 'DBMS_OUTPUT';

--INDEX SKIP SCAN(针对MAX(),MIN()函数的查询)
SELECT owner, object_name FROM test_objects WHERE object_name = 'DBMS_OUTPUT';

--INDEX FAST FULL SCAN(索引的快速全扫描,多块读,无序)
SELECT owner, object_name FROM test_objects;

--INDEX FULL SCAN(索引的全扫描,单块读,有序)
SELECT owner, object_name FROM test_objects order by 1, 2;

--INDEX FULL SCAN (MIN/MAX)(针对MAX(),MIN()函数的查询)
SELECT max(object_id) FROM test_objects;

3.实验环境验证

根据2中的SQL分别在实验环境中验证,没有问题,结果如下:

SQL> set autot trace
--1. INDEX RANGE SCAN
SQL> SELECT owner, object_name FROM test_objects WHERE owner = 'SYS' AND object_name = 'DBMS_OUTPUT';


Execution Plan
----------------------------------------------------------
Plan hash value: 3492129186

---------------------------------------------------------------------------------------
| Id  | Operation        | Name               | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT |                    |     1 |    29 |     3   (0)| 00:00:01 |
|*  1 |  INDEX RANGE SCAN| IDX_TEST_OBJECTS_1 |     1 |    29 |     3   (0)| 00:00:01 |
---------------------------------------------------------------------------------------

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

   1 - access("OWNER"='SYS' AND "OBJECT_NAME"='DBMS_OUTPUT')


Statistics
----------------------------------------------------------
         59  recursive calls
          0  db block gets
        104  consistent gets
         17  physical reads
          0  redo size
        676  bytes sent via SQL*Net to client
        519  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          6  sorts (memory)
          0  sorts (disk)
          2  rows processed

--2. INDEX SKIP SCAN
SQL> SELECT owner, object_name FROM test_objects WHERE object_name = 'DBMS_OUTPUT';


Execution Plan
----------------------------------------------------------
Plan hash value: 1228438998

---------------------------------------------------------------------------------------
| Id  | Operation        | Name               | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT |                    |     2 |    58 |    27   (0)| 00:00:01 |
|*  1 |  INDEX SKIP SCAN | IDX_TEST_OBJECTS_1 |     2 |    58 |    27   (0)| 00:00:01 |
---------------------------------------------------------------------------------------

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

   1 - access("OBJECT_NAME"='DBMS_OUTPUT')
       filter("OBJECT_NAME"='DBMS_OUTPUT')


Statistics
----------------------------------------------------------
          2  recursive calls
          0  db block gets
         32  consistent gets
         23  physical reads
          0  redo size
        684  bytes sent via SQL*Net to client
        519  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          3  rows processed

--3. INDEX FAST FULL SCAN
SQL> SELECT owner, object_name FROM test_objects;

84311 rows selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 2324984732

-------------------------------------------------------------------------------------------
| Id  | Operation            | Name               | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |                    | 84311 |  2387K|   138   (0)| 00:00:02 |
|   1 |  INDEX FAST FULL SCAN| IDX_TEST_OBJECTS_1 | 84311 |  2387K|   138   (0)| 00:00:02 |
-------------------------------------------------------------------------------------------


Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
       6097  consistent gets
        480  physical reads
          0  redo size
    3509341  bytes sent via SQL*Net to client
      62339  bytes received via SQL*Net from client
       5622  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
      84311  rows processed

--4. INDEX FULL SCAN
SQL> SELECT owner, object_name FROM test_objects order by 1, 2;

84311 rows selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 2751381935

---------------------------------------------------------------------------------------
| Id  | Operation        | Name               | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT |                    | 84311 |  2387K|   505   (1)| 00:00:07 |
|   1 |  INDEX FULL SCAN | IDX_TEST_OBJECTS_1 | 84311 |  2387K|   505   (1)| 00:00:07 |
---------------------------------------------------------------------------------------


Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
       6090  consistent gets
          0  physical reads
          0  redo size
    3509341  bytes sent via SQL*Net to client
      62339  bytes received via SQL*Net from client
       5622  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
      84311  rows processed

--5. INDEX FULL SCAN (MIN/MAX)
SQL> SELECT max(object_id) FROM test_objects;


Execution Plan
----------------------------------------------------------
Plan hash value: 729623451

-------------------------------------------------------------------------------------------------
| Id  | Operation                  | Name               | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT           |                    |     1 |     4 |     2   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE            |                    |     1 |     4 |            |          |
|   2 |   INDEX FULL SCAN (MIN/MAX)| IDX_TEST_OBJECTS_2 |     1 |     4 |     2   (0)| 00:00:01 |
-------------------------------------------------------------------------------------------------


Statistics
----------------------------------------------------------
          2  recursive calls
          0  db block gets
          5  consistent gets
          2  physical reads
          0  redo size
        534  bytes sent via SQL*Net to client
        519  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

我来说两句

0 条评论
登录 后参与评论

相关文章

  • Oracle之SQL优化专题03-如何看懂SQL的执行计划

    专题第一篇《Oracle之SQL优化专题01-查看SQL执行计划的方法》讲到了查看SQL执行计划的方法,并介绍了各种方法的应用场景,那么这一篇就主要介绍下如何看...

    Alfred Zhao
  • Oracle数据库异机升级

    环境: A机:RHEL5.5 + Oracle 10.2.0.4 B机:RHEL5.5 需求: A机10.2.0.4数据库,在B机升级到11.2.0.4...

    Alfred Zhao
  • 案例:使用dbms_xplan.display_cursor无法获取执行计划

    案例:使用dbms_xplan.display_cursor无法获取执行计划 环境:RHEL 6.5 + Oracle 11.2.0.4

    Alfred Zhao
  • INDEX FULL SCAN vs INDEX FAST FULL SCAN

         INDEX FULL SCAN 与 INDEX FAST FULL SCAN两个长相差不多,乃是一母同胞,因此既有其共性,也有其个性。两者来说其共性是...

    Leshami
  • 11g升级性能问题之一 重建user_synonyms (笔记27天)

    在测试环境11g升级之后,从测试那边反馈查询syn反应很慢。要持续差不多10分钟。其实这个syn中的数据只有200多条第一反应是cpu 100%了,查看果然是因...

    jeanron100
  • 为什么 SQL 正在击败 NoSQL,这对未来的数据意味着什么

    经过多年的沉寂之后,今天的 SQL 正在复出。缘由如何? 这对数据社区有什么影响?看看本文的分析。以下为译文。

    云加社区
  • gulp + gulp-better-rollup + rollup 构建 ES6 开发环境

    关于 Gulp 就不过多啰嗦了。常用的 js 模块打包工具主要有 webpack、rollup 和 browserify 三个,Gulp 构建 ES6 开发环境...

    用户6167509
  • 每天一道剑指offer-牛客网二进制中1的个数

    乔戈里
  • 替换与转置函数

    今天要跟大家分享两个经常会用到的函数——替换与转置函数! ▽▼▽ excel中的替换函数有两个:substitute/replace 转置函数:TRANSPOS...

    数据小磨坊
  • Oracle 数据库实例启动关闭过程

    Oracle数据库实例的启动,严格来说应该是实例的启动,数据库仅仅是在实例启动后进行装载。Oracle数据启动的过程被划分为

    Leshami

扫码关注云+社区

领取腾讯云代金券