tech_memo / ORACLE / order_by_magic

tech_memo/ORACLE

• 以下のSQLにORDER BYを付随するしないでINDEXのスキャン計画が変わってしまい、性能が劣化した。

• ORDER BYなし。こちらは一瞬で終了する。

  SQL> SELECT APPLICATION_ID, BIOMETRIC_DATA_LEN FROM APPLICATION_TABLE WHERE APP_ID = 1001;
  
  APPLICATION_ID BIOMETRIC_DATA_LEN
  ------------- ------------------
              2              11512
       16888896              11512
  
  
  Execution Plan
  ----------------------------------------------------------
  Plan hash value: 226578166
  
  ------------------------------------------------------------------------------------------------------
  | Id  | Operation                   | Name                   | Rows  | Bytes | Cost (%CPU)| Time     |
  ------------------------------------------------------------------------------------------------------
  |   0 | SELECT STATEMENT            |                        |     2 |    78 |     1   (0)| 00:00:01 |
  |   1 |  TABLE ACCESS BY INDEX ROWID| APPLICATION_TABLE      |     2 |    78 |     1   (0)| 00:00:01 |
  |*  2 |   INDEX RANGE SCAN          | APPLICATION_TABLE_IDX2 |     2 |       |     1   (0)| 00:00:01 |
  ------------------------------------------------------------------------------------------------------
  
  Predicate Information (identified by operation id):
  ---------------------------------------------------
  
     2 - access("APP_ID"=1001)
  
  Note
  -----
     - dynamic sampling used for this statement (level=2)
  
  
  Statistics
  ----------------------------------------------------------
            0  recursive calls
            0  db block gets
            6  consistent gets
            0  physical reads
            0  redo size
          678  bytes sent via SQL*Net to client
          524  bytes received via SQL*Net from client
            2  SQL*Net roundtrips to/from client
            0  sorts (memory)
            0  sorts (disk)
            2  rows processed

• ORDER BYあり。数分かかる。レコードが1000万を超えるテーブルへのINDEX FULL SCANのため。

  SQL> SELECT APPLICATION_ID, BIOMETRIC_DATA_LEN FROM APPLICATION_TABLE WHERE APP_ID = 1001 order by APPLICATION_ID;
  
  APPLICATION_ID BIOMETRIC_DATA_LEN
  ------------- ------------------
              2              11512
       16888896              11512
  
  
  Execution Plan
  ----------------------------------------------------------
  Plan hash value: 86274002
  
  ----------------------------------------------------------------------------------------------------
  | Id  | Operation                   | Name                 | Rows  | Bytes | Cost (%CPU)| Time     |
  ----------------------------------------------------------------------------------------------------
  |   0 | SELECT STATEMENT            |                      |     2 |    78 |     1   (0)| 00:00:01 |
  |*  1 |  TABLE ACCESS BY INDEX ROWID| APPLICATION_TABLE    |     2 |    78 |     1   (0)| 00:00:01 |
  |   2 |   INDEX FULL SCAN           | APPLICATION_TABLE_PK |    17M|       |     1   (0)| 00:00:01 |
  ----------------------------------------------------------------------------------------------------
  
  Predicate Information (identified by operation id):
  ---------------------------------------------------
  
     1 - filter("APP_ID"=1001)
  
  Note
  -----
     - dynamic sampling used for this statement (level=2)
  
  
  Statistics
  ----------------------------------------------------------
            0  recursive calls
            0  db block gets
      1848998  consistent gets
            0  physical reads
            0  redo size
          678  bytes sent via SQL*Net to client
          524  bytes received via SQL*Net from client
            2  SQL*Net roundtrips to/from client
            0  sorts (memory)
            0  sorts (disk)

• http://www.oracle.co.jp/forum/thread.jspa?threadID=2001796
  • 上記リンクより抜粋

    ＞http://www.oracle-training.cc/oracle_tips_sort_operations.htm
    >＞でもこのサイト（英文）の記述によれば、group by, distinct, unique,
    >＞join,aggregateなどの場合、暗黙なソート処理あると書いていますが…
    >　経験則的なTipsではないんですか？たしかに、9iぐらいまでは、
    >Group byやdistinctの指定がある場合は、内部的にsortを利用していた
    >ようですが。それとも、Oracleが保証している　という記事なのでしょうか？
    
    (内部的に)ソート処理が行われるという事と、結果がソートされて
    返されるという事を混同しない方が良いですよね。
    
    少なくともあるバージョンでは、それらのコマンド実行時にソート処理が
    行われる(可能性がある)という事で、結果がソートされて返されるという
    事ではありません。ソート処理自体も必ず行われるわけではなく、
    たとえばORDER BYをつけてもINDEX RANGE SCANなどの場合だったら、
    内部的にSORT処理が行われない事もあります。