Oracle SQLチューニングの基本は実行計画を理解することです。実行計画はツリー構造で、様々なオペレーションから構成されます。

この記事では、SORT UNIQUE オペレーションについて説明します。

SORT UNIQUEオペレーションとはどのようなオペレーションか？

以下の様なSQLにおいて、データの重複を排除するため、内部的にソートが実行される場合に使用されるオペレーションです。

• DISTINCT句
• WHERE col IN (SELECT …)
• 集合演算子(UNION, MINUS, INTERSECT)

ただし、目的は重複の排除であるため、場合によってはSORT UNIQUEオペレーション以外のオペレーションが用いられる場合があります。

例えば、DISTINCT句でSORT UNIQUEオペレーションが使用されるのはOracle Database 10.1以前のみです。Oracle Database 10.2以降では、HASH UNIQUEオペレーションが使用されます。

前のステップからすべてのデータを受け取り、ソート処理を実行します。データが大量な場合は、一時表領域を使用してソート処理を実行するため、負荷が大きくなります。

実行計画の例

DISTINCT句

SELECT DISTINCT * FROM testtbl;

------------------------------------------------------------------------------
| Id  | Operation          | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |         |  3536 |    20M|  2741   (1)| 00:00:33 |
|   1 |  SORT UNIQUE       |         |  3536 |    20M|  2741   (1)| 00:00:33 |
|   2 |   TABLE ACCESS FULL| TESTTBL | 10000 |    57M|  2739   (1)| 00:00:33 |
------------------------------------------------------------------------------

WHERE col IN (SELECT …)

SELECT d.dname FROM DEPT d
  WHERE d.deptno IN (SELECT e.deptno FROM emp e WHERE e.sal > 2000);

----------------------------------------------------------------------------------------
| Id  | Operation                    | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |         |     3 |    60 |     6  (17)| 00:00:01 |
|   1 |  MERGE JOIN SEMI             |         |     3 |    60 |     6  (17)| 00:00:01 |
|   2 |   TABLE ACCESS BY INDEX ROWID| DEPT    |     4 |    52 |     2   (0)| 00:00:01 |
|   3 |    INDEX FULL SCAN           | PK_DEPT |     4 |       |     1   (0)| 00:00:01 |
|*  4 |   SORT UNIQUE                |         |     6 |    42 |     4  (25)| 00:00:01 |
|*  5 |    TABLE ACCESS FULL         | EMP     |     6 |    42 |     3   (0)| 00:00:01 |
----------------------------------------------------------------------------------------

   4 - access("D"."DEPTNO"="E"."DEPTNO")
       filter("D"."DEPTNO"="E"."DEPTNO")
   5 - filter("E"."SAL">2000)

集合演算子(UNION, MINUS, INTERSECT)

SELECT * FROM emp2
UNION
SELECT * FROM emp3;

----------------------------------------------------------------------------
| Id  | Operation           | Name | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |      |    14 |   532 |     8  (25)| 00:00:01 |
|   1 |  SORT UNIQUE        |      |    14 |   532 |     8  (25)| 00:00:01 |
|   2 |   UNION-ALL         |      |       |       |            |          |
|   3 |    TABLE ACCESS FULL| EMP2 |    12 |   456 |     3   (0)| 00:00:01 |
|   4 |    TABLE ACCESS FULL| EMP3 |     2 |    76 |     3   (0)| 00:00:01 |
----------------------------------------------------------------------------

SELECT * FROM emp
MINUS
SELECT * FROM emp2;

----------------------------------------------------------------------------
| Id  | Operation           | Name | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |      |    14 |   988 |     8  (25)| 00:00:01 |
|   1 |  MINUS              |      |       |       |            |          |
|   2 |   SORT UNIQUE       |      |    14 |   532 |     4  (25)| 00:00:01 |
|   3 |    TABLE ACCESS FULL| EMP  |    14 |   532 |     3   (0)| 00:00:01 |
|   4 |   SORT UNIQUE       |      |    12 |   456 |     4  (25)| 00:00:01 |
|   5 |    TABLE ACCESS FULL| EMP2 |    12 |   456 |     3   (0)| 00:00:01 |
----------------------------------------------------------------------------

SELECT * FROM emp
INTERSECT
SELECT * FROM emp2;

----------------------------------------------------------------------------
| Id  | Operation           | Name | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |      |    12 |   988 |     8  (25)| 00:00:01 |
|   1 |  INTERSECTION       |      |       |       |            |          |
|   2 |   SORT UNIQUE       |      |    14 |   532 |     4  (25)| 00:00:01 |
|   3 |    TABLE ACCESS FULL| EMP  |    14 |   532 |     3   (0)| 00:00:01 |
|   4 |   SORT UNIQUE       |      |    12 |   456 |     4  (25)| 00:00:01 |
|   5 |    TABLE ACCESS FULL| EMP2 |    12 |   456 |     3   (0)| 00:00:01 |
----------------------------------------------------------------------------

SORT UNIQUEオペレーションに関するFAQ

SORT UNIQUEオペレーションが使われる条件には何がありますか？

SORT UNIQUEオペレーションが使われる目的は重複の排除です。しかし、一般にソート処理の負荷は大きいため、積極的にSORT UNIQUEオペレーションを使うように配慮する理由は特にありません。

事実、Oracle Database 10.2以降では、DISTINCT句指定時、SORT UNIQUEオペレーションの代わりにHASH UNIQUEオペレーションを使用するように改善が図られています。

SORT UNIQUEオペレーションを使う実行計画は望ましくないですか？

先に説明した通り、データが大量な場合は、一時表領域を使用してソート処理を実行するため、負荷が大きくなります。可能であれば、データの絞り込み条件を指定し、ソート対象のデータ量を小さくできないか検討してください。

一時的にSORT_AREA_SIZEを大きく指定して、ソートをメモリ上で処理することで、負荷を軽減することが可能な場合もあります。

SORT UNIQUEオペレーションを使う実行計画に誘導するヒントは何ですか？

特にヒントはありません。

参考情報

• HASH UNIQUEオペレーション

キーワード

ソート SORT_AREA_SIZE HASH UNIQUE