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オペレーションを使う実行計画に誘導するヒントは何ですか？
特にヒントはありません。
参考情報
キーワード
ソート SORT_AREA_SIZE HASH UNIQUE
