Go to Qiita Advent Calendar 2024 Top
LoginSignup
2
4

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?

More than 5 years have passed since last update.

@Papageno

副問合せ SUBQUERY

Last updated at Posted at 2018-07-04

副問合せは一般にパフォーマンスが悪いのでなるべく使わない方が良い。他に回避方法がないときに限り消極的に使用する。副問合せを多用しなければならないシステムはパフォーマンスの悪さが懸念される。テーブル設計を疑うべし。

準備
CREATE TABLE PARENTS  -- 親
    (
    PARENT_ID    NUMBER(11) NOT NULL  -- 親ID
  , PARENT_NAME  VARCHAR2(256)        -- 親の名前
  , CONSTRAINT PARENTS_PK PRIMARY KEY(PARENT_ID) USING INDEX  -- PRIMARY KEY
    ) ;

CREATE TABLE CHILDREN  -- 子
    (
    PARENT_ID   NUMBER(11) NOT NULL  -- 親ID
  , CHILD_ID    NUMBER(2)  NOT NULL  -- 子ID
  , CHILD_NAME  VARCHAR2(256)        -- 子の名前
  , CONSTRAINT CHILDREN_PK PRIMARY KEY(PARENT_ID, CHILD_ID) USING INDEX  -- PRIMARY KEY
    ) ;
データを用意(パフォーマンスの調査には実際のデータに見合うデータ量を確保する)
INSERT INTO PARENTS(PARENT_ID, PARENT_NAME) VALUES(1, 'Padme Amidala Naberrie') ;
INSERT INTO PARENTS(PARENT_ID, PARENT_NAME) VALUES(2, 'Anakin Skywalker') ;
INSERT INTO CHILDREN(PARENT_ID, CHILD_ID, CHILD_NAME) VALUES(1, 1, 'Luke Skywalker') ;
INSERT INTO CHILDREN(PARENT_ID, CHILD_ID, CHILD_NAME) VALUES(1, 2, 'Leia Organa') ;
INSERT INTO CHILDREN(PARENT_ID, CHILD_ID, CHILD_NAME) VALUES(5, 1, '(Han Solo') ;
COMMIT ;

とテーブルがあったとして、副問合せの種類

-- (1) EXISTS, NOT EXISTS 方式

EXISTS
SELECT
    CS.PARENT_ID
  , CS.CHILD_ID
FROM CHILDREN CS
WHERE NOT EXISTS
    (
    SELECT
        PS.PARENT_ID
    FROM PARENTS PS
    WHERE PS.PARENT_ID = CS.PARENT_ID
    )
ORDER BY
    CS.PARENT_ID
  , CS.CHILD_ID ;

    PARENT_ID      CHILD_ID
------------- -------------
            5             1

1行が選択されました。
実行計画
SQL> explain plan for
  2  SELECT
  3      CS.PARENT_ID
  4    , CS.CHILD_ID
  5  FROM CHILDREN CS
  6  WHERE NOT EXISTS
  7      (
  8      SELECT
  9          PS.PARENT_ID
 10      FROM PARENTS PS
 11      WHERE PS.PARENT_ID = CS.PARENT_ID
 12      )
 13  ORDER BY
 14      CS.PARENT_ID
 15    , CS.CHILD_ID ;

解析されました。

SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY()) ;

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------
Plan hash value: 3979919563

----------------------------------------------------------------------------------
| Id  | Operation          | Name        | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |             |     2 |    18 |     1   (0)| 00:00:01 |
|   1 |  NESTED LOOPS ANTI |             |     2 |    18 |     1   (0)| 00:00:01 |
|   2 |   INDEX FULL SCAN  | CHILDREN_PK |     3 |    18 |     1   (0)| 00:00:01 |
|*  3 |   INDEX UNIQUE SCAN| PARENTS_PK  |     1 |     3 |     0   (0)| 00:00:01 |
----------------------------------------------------------------------------------

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

   3 - access("PS"."PARENT_ID"="CS"."PARENT_ID")

15行が選択されました。

-- (2) IN, NOT IN 方式

IN
SELECT
    CS.PARENT_ID
  , CS.CHILD_ID
FROM CHILDREN CS
WHERE CS.PARENT_ID NOT IN
    (
    SELECT
        PS.PARENT_ID
    FROM PARENTS PS
    )
ORDER BY
    CS.PARENT_ID
  , CS.CHILD_ID ;

    PARENT_ID      CHILD_ID
------------- -------------
            5             1

1行が選択されました。

外側のWHERE句に列があり、IN or NOT IN が記述される。SUBQUERYの中のWHERE句がなくなる(他に条件がある場合を除く)

実行計画
SQL> explain plan for
  2  SELECT
  3      CS.PARENT_ID
  4    , CS.CHILD_ID
  5  FROM CHILDREN CS
  6  WHERE CS.PARENT_ID NOT IN
  7      (
  8      SELECT
  9          PS.PARENT_ID
 10      FROM PARENTS PS
 11      )
 12  ORDER BY
 13      CS.PARENT_ID
 14    , CS.CHILD_ID ;

解析されました。

SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY()) ;

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------
Plan hash value: 3979919563

----------------------------------------------------------------------------------
| Id  | Operation          | Name        | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |             |     2 |    18 |     1   (0)| 00:00:01 |
|   1 |  NESTED LOOPS ANTI |             |     2 |    18 |     1   (0)| 00:00:01 |
|   2 |   INDEX FULL SCAN  | CHILDREN_PK |     3 |    18 |     1   (0)| 00:00:01 |
|*  3 |   INDEX UNIQUE SCAN| PARENTS_PK  |     1 |     3 |     0   (0)| 00:00:01 |
----------------------------------------------------------------------------------

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

   3 - access("CS"."PARENT_ID"="PS"."PARENT_ID")

15行が選択されました。

-- (3) 相関副問合せ : イコール、NOT イコール方式 =, <> (or !=)

相関副問合せ
SELECT
    CS.PARENT_ID
  , CS.CHILD_ID
FROM CHILDREN CS
WHERE CS.PARENT_ID <>
    (
    SELECT
        PS.PARENT_ID
    FROM PARENTS PS
    WHERE PS.PARENT_ID = 1
    )
ORDER BY
    CS.PARENT_ID
  , CS.CHILD_ID ;

    PARENT_ID      CHILD_ID
------------- -------------
            5             1

1行が選択されました。
実行計画
SQL> explain plan for
  2  SELECT
  3      CS.PARENT_ID
  4    , CS.CHILD_ID
  5  FROM CHILDREN CS
  6  WHERE CS.PARENT_ID <>
  7      (
  8      SELECT
  9          PS.PARENT_ID
 10      FROM PARENTS PS
 11      WHERE PS.PARENT_ID = 1
 12      )
 13  ORDER BY
 14      CS.PARENT_ID
 15    , CS.CHILD_ID ;

解析されました。

SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY()) ;

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------
Plan hash value: 2518209649

----------------------------------------------------------------------------------
| Id  | Operation          | Name        | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |             |     2 |    12 |     1   (0)| 00:00:01 |
|*  1 |  INDEX FULL SCAN   | CHILDREN_PK |     2 |    12 |     1   (0)| 00:00:01 |
|*  2 |   INDEX UNIQUE SCAN| PARENTS_PK  |     1 |     3 |     0   (0)| 00:00:01 |
----------------------------------------------------------------------------------

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

   1 - filter("CS"."PARENT_ID"<> (SELECT "PS"."PARENT_ID" FROM "PARENTS"
              "PS" WHERE "PS"."PARENT_ID"=1))
   2 - access("PS"."PARENT_ID"=1)

16行が選択されました。
エラー
SQL> SELECT
  2      CS.PARENT_ID
  3    , CS.CHILD_ID
  4  FROM CHILDREN CS
  5  WHERE CS.PARENT_ID <>
  6      (
  7      SELECT
  8          PS.PARENT_ID
  9      FROM PARENTS PS
 10      )
 11  ORDER BY
 12      CS.PARENT_ID
 13    , CS.CHILD_ID ;
    SELECT
    *
行7でエラーが発生しました。:
ORA-01427: 単一行副問合せにより2つ以上の行が戻されます

  相関副問合せではインラインビューでのSELECT結果が2行以上あるとエラーになる。

-- (4) スカラー副問合せ since Oracle9.0.1
後日追加

2
4
0

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
Sign upLogin
2
4

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?