Index Only Access (Index Only Scan) っていいよね（デメリットもあるけどさ） #2 Tweet

Index Only Accessのいいとこ、紹介しちゃいますの続きです。

前回は、索引しかアクセスしない（Index Only Access）場合と、索引＋表データもアクセスしちゃう場合の実行計画上の違いを確認しましたよね。

今回は、Index Only Accessで得られる改善効果の１つであるソート処理の回避について簡単な例で確認してみます。

※VISIBLE/INVISIBLEにしている索引の詳細は前回の記事を参照してくださいね。

Now Playing ♪ - ハイスクールララバイ / イモ欽トリオ - 1981

まず、最初は、悪い子の例から。

索引を全表走査した上で　order by seq#　でソート処理が実行されます。酷いですね。検索条件列に適切な索引を作れよ〜〜〜っ。という状態ですね。

SQL> alter index tab10_i01 invisible;

索引が変更されました。

SQL> alter index tab10_i02 invisible;

索引が変更されました。

SQL> select seq# from tab10 where non_unique_key = '0000000001' order by seq#;

10行が選択されました。

経過: 00:00:04.99

実行計画
----------------------------------------------------------
Plan hash value: 2100371779

----------------------------------------------------------------------------
| Id  | Operation           | Name  | Rows | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |       |   10 |   160 | 16788   (1)| 00:03:22 |
|   1 |  SORT ORDER BY      |       |   10 |   160 | 16788   (1)| 00:03:22 |
|*  2 |   TABLE ACCESS FULL | TAB10 |   10 |   160 | 16787   (1)| 00:03:22 |
----------------------------------------------------------------------------

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

   2 - filter("NON_UNIQUE_KEY"='0000000001')


統計
----------------------------------------------------------
      0  recursive calls
      0  db block gets
  61544  consistent gets
  61540  physical reads
      0  redo size
    655  bytes sent via SQL*Net to client
    520  bytes received via SQL*Net from client
      2  SQL*Net roundtrips to/from client
      1  sorts (memory)
      0  sorts (disk)
     10  rows processed

次に、普通の子の例。

検索条件であるnon_unique_keyに定義された索引を使いIndex range scanかつ、seq#をアクセスするために表データをrowidでアクセス。その後にソート処理が行われています。

普通の子らしい、よい実行計画ですね。 :)

SQL> alter index tab10_i01 visible;

索引が変更されました。

SQL> alter index tab10_i02 invisible;  

索引が変更されました。

SQL> select seq# from tab10 where non_unique_key = '0000000001' order by seq#;

10行が選択されました。

経過: 00:00:00.08

実行計画
----------------------------------------------------------
Plan hash value: 1783972576

------------------------------------------------------------------------------------------
| Id  | Operation                    | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |           |    10 |   160 |     5  (20)| 00:00:01 |
|   1 |  SORT ORDER BY               |           |    10 |   160 |     5  (20)| 00:00:01 |
|   2 |   TABLE ACCESS BY INDEX ROWID| TAB10     |    10 |   160 |     4   (0)| 00:00:01 |
|*  3 |    INDEX RANGE SCAN          | TAB10_I01 |    10 |       |     3   (0)| 00:00:01 |
------------------------------------------------------------------------------------------

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

   3 - access("NON_UNIQUE_KEY"='0000000001')


統計
----------------------------------------------------------
      0  recursive calls
      0  db block gets
      4  consistent gets
     17  physical reads
      0  redo size
    655  bytes sent via SQL*Net to client
    520  bytes received via SQL*Net from client
      2  SQL*Net roundtrips to/from client
      1  sorts (memory)
      0  sorts (disk)
     10  rows processed

そして、最後は、良い子の登場。

この子、データ量が多くなった場合、ソート処理も足枷になると考えたようで　Index only accessを利用したソート処理回避作戦を取ったようです。　
実行計画をみればわかりますが、索引しかアクセスしておらず、SORT ORDER BY というオペレーションも消えています!

検索条件である、non_unique_key列とソート対象のseq#列の２列からなる複合索引を利用するIndex only accessを狙ったようですね。

ただし、これには order by seq# [asc]であるという大前提があります。
order by seq# desc　というソートも考慮する必要がある場合には、non_unique_key [asc]とseq# desc とした別の複合索引必要になります。（デメリットといえばデメリットですかね、）

order by seq# descというソート条件が仕様上無い事を事前に確認しておけば、なお完璧ですよね〜。　良い子。流石です。 :)

SQL> alter index tab10_i01 invisible;

索引が変更されました。

SQL> alter index tab10_i02 visible;  

索引が変更されました。

SQL> select seq# from tab10 where non_unique_key = '0000000001' order by seq#;

10行が選択されました。

経過: 00:00:00.01

実行計画
----------------------------------------------------------
Plan hash value: 489654843

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

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

   1 - access("NON_UNIQUE_KEY"='0000000001')


統計
----------------------------------------------------------
      0  recursive calls
      0  db block gets
      4  consistent gets
     16  physical reads
      0  redo size
    655  bytes sent via SQL*Net to client
    520  bytes received via SQL*Net from client
      2  SQL*Net roundtrips to/from client
      0  sorts (memory)
      0  sorts (disk)
     10  rows processed

次回へつづく。（他のソート回避の例も紹介しちゃうか、考えちう）

---

・Index Only Access (Index Only Scan) っていいよね（デメリットもあるけどさ） #1

・いん！、イン！、Index どっぷり Inde Only Access生活w - Oracle OpenWorld Unconference presented by JPOUG