sql oracle exists

ネストされた副問合せのネスト解除

https://docs.oracle.com/cd/E16338_01/server.112/b56299/queries008.htm

副問合せは、親である文のWHERE句内にあるときはネストされています。ネストされた副問合せを持つ文を評価する場合、Oracle Databaseは、副問合せ部分を複数回評価する必要があり、効果的なアクセス・パスまたは結合を見逃してしまう可能性があります。

バージョン


[oracle@centos7 ~]$ docker exec -it orcl_18cr3 bash
[oracle@d102981b4117 ~]$ sqlplus aine/ORACLE_PWD@pdb1

SQL*Plus: Release 18.0.0.0.0 - Production on Sun Jan 13 01:41:52 2019
Version 18.3.0.0.0

Copyright (c) 1982, 2018, Oracle.  All rights reserved.

Last Successful login time: Sun Jan 13 2019 01:19:00 +00:00

Connected to:
Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production
Version 18.3.0.0.0

テストデータ


PURGE RECYCLEBIN;

DROP TABLE warehouse_mst PURGE;
CREATE TABLE warehouse_mst AS
SELECT DISTINCT
    rpad('w_',6,dbms_random.STRING('X',4)) AS warehouse_code
	,TRUNC(ABS(dbms_random.VALUE(10,20)),0) AS shipping_permission_over_days
FROM
	dual
CONNECT BY
	LEVEL <= &1
;

ALTER TABLE warehouse_mst ADD CONSTRAINT warehouse_mst_pk PRIMARY KEY(warehouse_code);

DROP TABLE item_mst PURGE;
CREATE TABLE item_mst AS
SELECT DISTINCT
    s1.warehouse_code
    ,rpad('p_',6,dbms_random.STRING('X',4)) AS item
    ,to_char(TRUNC(ABS(dbms_random.VALUE(1,10)),0)) AS item_kbn
    ,dbms_random.STRING('U',2) || lpad(TRUNC(ABS(dbms_random.VALUE(1,10)),0),5,'0') AS main_loc
FROM
    warehouse_mst s1
CONNECT BY
	LEVEL <= &1
;

ALTER TABLE item_mst ADD CONSTRAINT item_mst_pk PRIMARY KEY(warehouse_code,item);

DROP TABLE loc_mst PURGE;
CREATE TABLE loc_mst AS 
SELECT DISTINCT
	warehouse_code
    ,main_loc AS loc
	,lpad(ROWNUM,2,'0') AS ZONE
	,substr(main_loc,1,2) AS BLOCK
	,lpad(TRUNC(ABS(dbms_random.VALUE(1,10)),0),3,'0') AS area
	,lpad(TRUNC(ABS(dbms_random.VALUE(1,10)),0),2,'0') AS LINE
FROM
    item_mst
;

ALTER TABLE loc_mst ADD CONSTRAINT loc_mst_pk PRIMARY KEY(warehouse_code,loc);

DROP TABLE zone_mst PURGE;
CREATE TABLE zone_mst AS 
SELECT DISTINCT
    warehouse_code
	,ZONE
	,to_char(TRUNC(ABS(dbms_random.VALUE(1,10)),0)) AS zone_kbn
FROM
    loc_mst
;

ALTER TABLE zone_mst ADD CONSTRAINT zone_mst_pk PRIMARY KEY(warehouse_code,ZONE);

DROP TABLE cust_mst PURGE;
CREATE TABLE cust_mst AS 
SELECT
    rpad('c_',6,dbms_random.STRING('X',4)) AS cust_id
	,to_char(TRUNC(ABS(dbms_random.VALUE(1,10)),0)) AS cust_kbn
	,'bas' || lpad(TRUNC(dbms_random.VALUE(1,99),0),2,'0') AS basket_no
	,'car' || lpad(TRUNC(dbms_random.VALUE(1,99),0),2,'0') AS cart_no
FROM
	dual
CONNECT BY
	LEVEL <= &1
;

ALTER TABLE cust_mst ADD  CONSTRAINT cust_mst_pk PRIMARY KEY(cust_id) ;

DROP TABLE basket_mst PURGE;
CREATE TABLE basket_mst AS 
SELECT DISTINCT
    basket_no
	,TRUNC(ABS(dbms_random.VALUE(2000,5000)),0) AS upper_limit_weight
	,TRUNC(ABS(dbms_random.VALUE(2000,4000)),0) AS upper_limit_volume
	,to_char(TRUNC(ABS(dbms_random.VALUE(1,10)),0)) AS basket_kbn
FROM
    cust_mst
;

ALTER TABLE basket_mst ADD  CONSTRAINT basket_mst_pk PRIMARY KEY(basket_no) ;

DROP TABLE cart_mst PURGE;
CREATE TABLE cart_mst AS 
SELECT DISTINCT
    cart_no
	,TRUNC(ABS(dbms_random.VALUE(1,20)),0) AS upper_stack_cnt
	,to_char(TRUNC(ABS(dbms_random.VALUE(1,10)),0)) AS cart_kbn
FROM
    cust_mst
;

ALTER TABLE cart_mst ADD  CONSTRAINT cart_mst_pk PRIMARY KEY(cart_no) ;

DROP TABLE route_mst PURGE;
CREATE TABLE route_mst AS 
SELECT DISTINCT
	warehouse_code
	,rpad('r_',6,dbms_random.STRING('X',4)) AS route_no
	,lpad(TRUNC(dbms_random.VALUE(1,99),0),2,'0') AS barth_no
FROM
    warehouse_mst
;

ALTER TABLE route_mst ADD CONSTRAINT route_mst_pk PRIMARY KEY(warehouse_code,route_no);

DROP TABLE store_mst PURGE;
CREATE TABLE store_mst AS
SELECT DISTINCT
	rpad('s_',6,dbms_random.STRING('X',4)) AS store_no
	,s1.route_no AS route_no
    ,lpad(TRUNC(ABS(dbms_random.VALUE(1,99)),0),2,'0') AS route_order
    ,s2.cust_id AS cust_id
	,to_char(TRUNC(ABS(dbms_random.VALUE(1,10)),0)) AS store_kbn
FROM
    route_mst s1
	,cust_mst s2
;

ALTER TABLE store_mst ADD CONSTRAINT store_mst_pk PRIMARY KEY(store_no);

DROP TABLE stock PURGE;
CREATE TABLE stock AS
WITH make_date AS (
    SELECT
        to_char(A.date_from + ROWNUM - 1, 'YYYYMMDD') AS std_date
    FROM
        (
            SELECT
                TO_DATE(to_char(sysdate, 'YYYY/MM/DD'), 'YYYY/MM/DD') AS date_from
                , TO_DATE(to_char(sysdate + 2, 'YYYY/MM/DD'), 'YYYY/MM/DD') AS date_to
            FROM
                dual
        ) A
    CONNECT BY
        LEVEL <= A.date_to - A.date_from + 1
)
SELECT
    s0.item
    ,s1.warehousing_date AS warehousing_date
    , s2.expiration_date AS expiration_date
    , TRUNC(ABS(dbms_random.VALUE(100, 300)), 0) AS stock_qty
    , TRUNC(ABS(dbms_random.VALUE(10, 90)), 0) AS reserved_stock_qty
FROM
    item_mst s0
    ,(SELECT s1.std_date,to_char(TO_DATE(s1.std_date) - INTERVAL '10' DAY,'YYYYMMDD') AS warehousing_date FROM make_date s1) s1
    , LATERAL(SELECT s2.std_date,to_char(TO_DATE(s2.std_date) + INTERVAL '30' DAY,'YYYYMMDD') AS expiration_date FROM make_date s2 WHERE s1.std_date <= s2.std_date) s2
;

ALTER TABLE stock ADD CONSTRAINT stock_pk PRIMARY KEY(item,warehousing_date,expiration_date);

DROP TABLE sales_trn PURGE;
CREATE TABLE sales_trn AS
WITH make_date AS (
    SELECT
        to_char(A.date_from + ROWNUM - 1, 'YYYYMMDD') AS std_date
    FROM
        (
            SELECT
                TO_DATE(to_char(sysdate, 'YYYY/MM/DD'), 'YYYY/MM/DD') AS date_from
                , TO_DATE(to_char(sysdate + 2, 'YYYY/MM/DD'), 'YYYY/MM/DD') AS date_to
            FROM
                dual
        ) A
    CONNECT BY
        LEVEL <= A.date_to - A.date_from + 1
)
SELECT
    ROWNUM AS seq
    ,s0.item
    ,TRUNC(ABS(dbms_random.VALUE(10,100)),0) AS prov_inst_qty
    ,s1.cust_id
    ,s1.store_no
    ,s2.std_date AS arrive_date
FROM
    item_mst s0
    ,store_mst s1
    ,make_date s2
;

ALTER TABLE sales_trn ADD  CONSTRAINT sales_trn_pk PRIMARY KEY(seq);

no_unnestヒントなし


SELECT
    s0.*
FROM
    sales_trn s0
    ,cust_mst s1
WHERE
    s0.cust_id = s1.cust_id
AND EXISTS(
            SELECT
                1
            FROM
                store_mst s2
            WHERE
                s0.store_no = s2.store_no
            )
AND EXISTS(
            SELECT
                1
            FROM
                cart_mst s3
            WHERE
                s1.cart_no = s3.cart_no
            )
AND EXISTS(
            SELECT
                1
            FROM
                basket_mst s4
            WHERE
                s1.basket_no = s4.basket_no
            )
;

AINE@pdb1> SELECT
  2      s0.*
  3  FROM
  4      sales_trn s0
  5      ,cust_mst s1
  6  WHERE
  7      s0.cust_id = s1.cust_id
  8  AND EXISTS(
  9              SELECT
 10                  1
 11              FROM
 12                  store_mst s2
 13              WHERE
 14                  s0.store_no = s2.store_no
 15              )
 16  AND EXISTS(
 17              SELECT
 18                  1
 19              FROM
 20                  cart_mst s3
 21              WHERE
 22                  s1.cart_no = s3.cart_no
 23              )
 24  AND EXISTS(
 25              SELECT
 26                  1
 27              FROM
 28                  basket_mst s4
 29              WHERE
 30                  s1.basket_no = s4.basket_no
 31              )
 32  ;

1053 rows selected.

Elapsed: 00:00:00.03

Execution Plan
----------------------------------------------------------
Plan hash value: 582343668

---------------------------------------------------------------------------------------
| Id  | Operation             | Name          | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |               |  1053 | 78975 |     7   (0)| 00:00:01 |
|   1 |  NESTED LOOPS         |               |  1053 | 78975 |     7   (0)| 00:00:01 |
|*  2 |   HASH JOIN           |               |  1053 | 71604 |     7   (0)| 00:00:01 |
|   3 |    NESTED LOOPS       |               |     3 |    93 |     3   (0)| 00:00:01 |
|   4 |     NESTED LOOPS      |               |     3 |    75 |     3   (0)| 00:00:01 |
|   5 |      TABLE ACCESS FULL| CUST_MST      |     3 |    57 |     3   (0)| 00:00:01 |
|*  6 |      INDEX UNIQUE SCAN| BASKET_MST_PK |     1 |     6 |     0   (0)| 00:00:01 |
|*  7 |     INDEX UNIQUE SCAN | CART_MST_PK   |     1 |     6 |     0   (0)| 00:00:01 |
|   8 |    TABLE ACCESS FULL  | SALES_TRN     |  1053 | 38961 |     4   (0)| 00:00:01 |
|*  9 |   INDEX UNIQUE SCAN   | STORE_MST_PK  |     1 |     7 |     0   (0)| 00:00:01 |
---------------------------------------------------------------------------------------

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

   2 - access("S0"."CUST_ID"="S1"."CUST_ID")
   6 - access("S1"."BASKET_NO"="S4"."BASKET_NO")
   7 - access("S1"."CART_NO"="S3"."CART_NO")
   9 - access("S0"."STORE_NO"="S2"."STORE_NO")

Note
-----
   - this is an adaptive plan


Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
        159  consistent gets
          0  physical reads
          0  redo size
      34555  bytes sent via SQL*Net to client
       1394  bytes received via SQL*Net from client
         72  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
       1053  rows processed

no_unnestヒントあり


SELECT
    s0.*
FROM
    sales_trn s0
    ,cust_mst s1
WHERE
    s0.cust_id = s1.cust_id
AND EXISTS(
            SELECT /*+ no_unnest */
                1
            FROM
                store_mst s2
            WHERE
                s0.store_no = s2.store_no
            )
AND EXISTS(
            SELECT /*+ no_unnest */
                1
            FROM
                cart_mst s3
            WHERE
                s1.cart_no = s3.cart_no
            )
AND EXISTS(
            SELECT /*+ no_unnest */
                1
            FROM
                basket_mst s4
            WHERE
                s1.basket_no = s4.basket_no
            )
;

AINE@pdb1> SELECT
  2      s0.*
  3  FROM
  4      sales_trn s0
  5      ,cust_mst s1
  6  WHERE
  7      s0.cust_id = s1.cust_id
  8  AND EXISTS(
  9              SELECT /*+ no_unnest */
 10                  1
 11              FROM
 12                  store_mst s2
 13              WHERE
 14                  s0.store_no = s2.store_no
 15              )
 16  AND EXISTS(
 17              SELECT /*+ no_unnest */
 18                  1
 19              FROM
 20                  cart_mst s3
 21              WHERE
 22                  s1.cart_no = s3.cart_no
 23              )
 24  AND EXISTS(
 25              SELECT /*+ no_unnest */
 26                  1
 27              FROM
 28                  basket_mst s4
 29              WHERE
 30                  s1.basket_no = s4.basket_no
 31              )
 32  ;

1053 rows selected.

Elapsed: 00:00:00.03

Execution Plan
----------------------------------------------------------
Plan hash value: 3552102129

-----------------------------------------------------------------------------------------------
| Id  | Operation                     | Name          | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |               |    13 |   728 |     7  (15)| 00:00:01 |
|*  1 |  FILTER                       |               |       |       |            |          |
|   2 |   MERGE JOIN                  |               |  1053 | 58968 |     7  (15)| 00:00:01 |
|   3 |    TABLE ACCESS BY INDEX ROWID| CUST_MST      |     3 |    57 |     2   (0)| 00:00:01 |
|   4 |     INDEX FULL SCAN           | CUST_MST_PK   |     3 |       |     1   (0)| 00:00:01 |
|*  5 |    SORT JOIN                  |               |  1053 | 38961 |     5  (20)| 00:00:01 |
|   6 |     TABLE ACCESS FULL         | SALES_TRN     |  1053 | 38961 |     4   (0)| 00:00:01 |
|*  7 |   INDEX UNIQUE SCAN           | STORE_MST_PK  |     1 |     7 |     0   (0)| 00:00:01 |
|*  8 |   INDEX UNIQUE SCAN           | CART_MST_PK   |     1 |     6 |     0   (0)| 00:00:01 |
|*  9 |   INDEX UNIQUE SCAN           | BASKET_MST_PK |     1 |     6 |     0   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------

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

   1 - filter( EXISTS (SELECT /*+ NO_UNNEST */ 0 FROM "STORE_MST" "S2" WHERE
              "S2"."STORE_NO"=:B1) AND  EXISTS (SELECT /*+ NO_UNNEST */ 0 FROM "CART_MST" "S3" WHERE
              "S3"."CART_NO"=:B2) AND  EXISTS (SELECT /*+ NO_UNNEST */ 0 FROM "BASKET_MST" "S4"
              WHERE "S4"."BASKET_NO"=:B3))
   5 - access("S0"."CUST_ID"="S1"."CUST_ID")
       filter("S0"."CUST_ID"="S1"."CUST_ID")
   7 - access("S2"."STORE_NO"=:B1)
   8 - access("S3"."CART_NO"=:B1)
   9 - access("S4"."BASKET_NO"=:B1)


Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
         31  consistent gets
          0  physical reads
          0  redo size
      34589  bytes sent via SQL*Net to client
       1394  bytes received via SQL*Net from client
         72  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
       1053  rows processed

filter述語の部分がもっとすっきり見えないか??


SELECT
    *
FROM
    TABLE ( dbms_xplan.display_cursor(format => 'ALIAS PREDICATE') );

-------------------------------------------------------------------------------------
| Id  | Operation           | Name          | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |               |       |       |     7 (100)|          |
|*  1 |  FILTER             |               |       |       |            |          |
|*  2 |   HASH JOIN         |               |   351 | 19656 |     7   (0)| 00:00:01 |
|   3 |    TABLE ACCESS FULL| CUST_MST      |     1 |    19 |     3   (0)| 00:00:01 |
|   4 |    TABLE ACCESS FULL| SALES_TRN     |  1053 | 38961 |     4   (0)| 00:00:01 |
|*  5 |   INDEX UNIQUE SCAN | STORE_MST_PK  |     1 |     7 |     0   (0)|          |
|*  6 |   INDEX UNIQUE SCAN | CART_MST_PK   |     1 |     6 |     0   (0)|          |
|*  7 |   INDEX UNIQUE SCAN | BASKET_MST_PK |     1 |     6 |     0   (0)|          |
-------------------------------------------------------------------------------------
 
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
 
   1 - SEL$1
   3 - SEL$1 / S1@SEL$1
   4 - SEL$1 / S0@SEL$1
   5 - SEL$2 / S2@SEL$2
   6 - SEL$3 / S3@SEL$3
   7 - SEL$4 / S4@SEL$4
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   1 - filter(( IS NOT NULL AND  IS NOT NULL AND  IS NOT NULL))
   2 - access("S0"."CUST_ID"="S1"."CUST_ID")
   5 - access("S2"."STORE_NO"=:B1)
   6 - access("S3"."CART_NO"=:B1)
   7 - access("S4"."BASKET_NO"=:B1)

sql oracle hash join

Left-Deep-Join&Right-Deep-Join

Left-Deep-Join
結合結果からHashテーブルを作成し、次の結合対象テーブルをProbeしていく方法
→Probe表を主とした結合方法
Right-Deep-Join
サイズが小さいマスタ表をBuild表として作成してから、結合していく方法
→Build表を主とした結合方法

バージョン


[oracle@centos7 ~]$ docker exec -it orcl_18cr3 bash
[oracle@d102981b4117 ~]$ sqlplus aine/ORACLE_PWD@pdb1

SQL*Plus: Release 18.0.0.0.0 - Production on Sun Jan 13 01:41:52 2019
Version 18.3.0.0.0

Copyright (c) 1982, 2018, Oracle.  All rights reserved.

Last Successful login time: Sun Jan 13 2019 01:19:00 +00:00

Connected to:
Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production
Version 18.3.0.0.0

テストデータ


PURGE RECYCLEBIN;

DROP TABLE warehouse_mst PURGE;
CREATE TABLE warehouse_mst AS
SELECT DISTINCT
    rpad('w_',6,dbms_random.STRING('X',4)) AS warehouse_code
	,TRUNC(ABS(dbms_random.VALUE(10,20)),0) AS shipping_permission_over_days
FROM
	dual
CONNECT BY
	LEVEL <= &1
;

ALTER TABLE warehouse_mst ADD CONSTRAINT warehouse_mst_pk PRIMARY KEY(warehouse_code);

DROP TABLE item_mst PURGE;
CREATE TABLE item_mst AS
SELECT DISTINCT
    s1.warehouse_code
    ,rpad('p_',6,dbms_random.STRING('X',4)) AS item
    ,to_char(TRUNC(ABS(dbms_random.VALUE(1,10)),0)) AS item_kbn
    ,dbms_random.STRING('U',2) || lpad(TRUNC(ABS(dbms_random.VALUE(1,10)),0),5,'0') AS main_loc
FROM
    warehouse_mst s1
CONNECT BY
	LEVEL <= &1
;

ALTER TABLE item_mst ADD CONSTRAINT item_mst_pk PRIMARY KEY(warehouse_code,item);

DROP TABLE loc_mst PURGE;
CREATE TABLE loc_mst AS 
SELECT DISTINCT
	warehouse_code
    ,main_loc AS loc
	,lpad(ROWNUM,2,'0') AS ZONE
	,substr(main_loc,1,2) AS BLOCK
	,lpad(TRUNC(ABS(dbms_random.VALUE(1,10)),0),3,'0') AS area
	,lpad(TRUNC(ABS(dbms_random.VALUE(1,10)),0),2,'0') AS LINE
FROM
    item_mst
;

ALTER TABLE loc_mst ADD CONSTRAINT loc_mst_pk PRIMARY KEY(warehouse_code,loc);

DROP TABLE zone_mst PURGE;
CREATE TABLE zone_mst AS 
SELECT DISTINCT
    warehouse_code
	,ZONE
	,to_char(TRUNC(ABS(dbms_random.VALUE(1,10)),0)) AS zone_kbn
FROM
    loc_mst
;

ALTER TABLE zone_mst ADD CONSTRAINT zone_mst_pk PRIMARY KEY(warehouse_code,ZONE);

DROP TABLE cust_mst PURGE;
CREATE TABLE cust_mst AS 
SELECT
    rpad('c_',6,dbms_random.STRING('X',4)) AS cust_id
	,to_char(TRUNC(ABS(dbms_random.VALUE(1,10)),0)) AS cust_kbn
	,'bas' || lpad(TRUNC(dbms_random.VALUE(1,99),0),2,'0') AS basket_no
	,'car' || lpad(TRUNC(dbms_random.VALUE(1,99),0),2,'0') AS cart_no
FROM
	dual
CONNECT BY
	LEVEL <= &1
;

ALTER TABLE cust_mst ADD  CONSTRAINT cust_mst_pk PRIMARY KEY(cust_id) ;

DROP TABLE basket_mst PURGE;
CREATE TABLE basket_mst AS 
SELECT DISTINCT
    basket_no
	,TRUNC(ABS(dbms_random.VALUE(2000,5000)),0) AS upper_limit_weight
	,TRUNC(ABS(dbms_random.VALUE(2000,4000)),0) AS upper_limit_volume
	,to_char(TRUNC(ABS(dbms_random.VALUE(1,10)),0)) AS basket_kbn
FROM
    cust_mst
;

ALTER TABLE basket_mst ADD  CONSTRAINT basket_mst_pk PRIMARY KEY(basket_no) ;

DROP TABLE cart_mst PURGE;
CREATE TABLE cart_mst AS 
SELECT DISTINCT
    cart_no
	,TRUNC(ABS(dbms_random.VALUE(1,20)),0) AS upper_stack_cnt
	,to_char(TRUNC(ABS(dbms_random.VALUE(1,10)),0)) AS cart_kbn
FROM
    cust_mst
;

ALTER TABLE cart_mst ADD  CONSTRAINT cart_mst_pk PRIMARY KEY(cart_no) ;

DROP TABLE route_mst PURGE;
CREATE TABLE route_mst AS 
SELECT DISTINCT
	warehouse_code
	,rpad('r_',6,dbms_random.STRING('X',4)) AS route_no
	,lpad(TRUNC(dbms_random.VALUE(1,99),0),2,'0') AS barth_no
FROM
    warehouse_mst
;

ALTER TABLE route_mst ADD CONSTRAINT route_mst_pk PRIMARY KEY(warehouse_code,route_no);

DROP TABLE store_mst PURGE;
CREATE TABLE store_mst AS
SELECT DISTINCT
	rpad('s_',6,dbms_random.STRING('X',4)) AS store_no
	,s1.route_no AS route_no
    ,lpad(TRUNC(ABS(dbms_random.VALUE(1,99)),0),2,'0') AS route_order
    ,s2.cust_id AS cust_id
	,to_char(TRUNC(ABS(dbms_random.VALUE(1,10)),0)) AS store_kbn
FROM
    route_mst s1
	,cust_mst s2
;

ALTER TABLE store_mst ADD CONSTRAINT store_mst_pk PRIMARY KEY(store_no);

DROP TABLE stock PURGE;
CREATE TABLE stock AS
WITH make_date AS (
    SELECT
        to_char(A.date_from + ROWNUM - 1, 'YYYYMMDD') AS std_date
    FROM
        (
            SELECT
                TO_DATE(to_char(sysdate, 'YYYY/MM/DD'), 'YYYY/MM/DD') AS date_from
                , TO_DATE(to_char(sysdate + 2, 'YYYY/MM/DD'), 'YYYY/MM/DD') AS date_to
            FROM
                dual
        ) A
    CONNECT BY
        LEVEL <= A.date_to - A.date_from + 1
)
SELECT
    s0.item
    ,s1.warehousing_date AS warehousing_date
    , s2.expiration_date AS expiration_date
    , TRUNC(ABS(dbms_random.VALUE(100, 300)), 0) AS stock_qty
    , TRUNC(ABS(dbms_random.VALUE(10, 90)), 0) AS reserved_stock_qty
FROM
    item_mst s0
    ,(SELECT s1.std_date,to_char(TO_DATE(s1.std_date) - INTERVAL '10' DAY,'YYYYMMDD') AS warehousing_date FROM make_date s1) s1
    , LATERAL(SELECT s2.std_date,to_char(TO_DATE(s2.std_date) + INTERVAL '30' DAY,'YYYYMMDD') AS expiration_date FROM make_date s2 WHERE s1.std_date <= s2.std_date) s2
;

ALTER TABLE stock ADD CONSTRAINT stock_pk PRIMARY KEY(item,warehousing_date,expiration_date);

DROP TABLE sales_trn PURGE;
CREATE TABLE sales_trn AS
WITH make_date AS (
    SELECT
        to_char(A.date_from + ROWNUM - 1, 'YYYYMMDD') AS std_date
    FROM
        (
            SELECT
                TO_DATE(to_char(sysdate, 'YYYY/MM/DD'), 'YYYY/MM/DD') AS date_from
                , TO_DATE(to_char(sysdate + 2, 'YYYY/MM/DD'), 'YYYY/MM/DD') AS date_to
            FROM
                dual
        ) A
    CONNECT BY
        LEVEL <= A.date_to - A.date_from + 1
)
SELECT
    ROWNUM AS seq
    ,s0.item
    ,TRUNC(ABS(dbms_random.VALUE(10,100)),0) AS prov_inst_qty
    ,s1.cust_id
    ,s1.store_no
    ,s2.std_date AS arrive_date
FROM
    item_mst s0
    ,store_mst s1
    ,make_date s2
;

ALTER TABLE sales_trn ADD  CONSTRAINT sales_trn_pk PRIMARY KEY(seq);

cust_mst→store_mst→cart_mst→basket_mstの順

Left-Depp-Join


SELECT /*+ leading(s1 s2 s3 s4) use_hash(s2 s3 s4) */
	s1.*
	,s2.*
	,s3.*
	,s4.*
FROM
	cust_mst s1
	,store_mst s2
	,cart_mst s3
	,basket_mst s4
WHERE
	s1.cust_id = s2.cust_id
AND s1.cart_no = s3.cart_no
AND s1.basket_no = s4.basket_no;

AINE@pdb1> SELECT /*+ leading(s1 s2 s3 s4) use_hash(s2 s3 s4) */
  2  s1.*
  3  ,s2.*
  4  ,s3.*
  5  ,s4.*
  6  FROM
  7  cust_mst s1
  8  ,store_mst s2
  9  ,cart_mst s3
 10  ,basket_mst s4
 11  WHERE
 12  s1.cust_id = s2.cust_id
 13  AND s1.cart_no = s3.cart_no
 14  AND s1.basket_no = s4.basket_no;

9 rows selected.

Elapsed: 00:00:00.03

Execution Plan
----------------------------------------------------------
Plan hash value: 2173346548

-----------------------------------------------------------------------------------
| Id  | Operation            | Name       | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |            |     9 |   666 |    12   (0)| 00:00:01 |
|*  1 |  HASH JOIN           |            |     9 |   666 |    12   (0)| 00:00:01 |
|*  2 |   HASH JOIN          |            |     9 |   522 |     9   (0)| 00:00:01 |
|*  3 |    HASH JOIN         |            |     9 |   423 |     6   (0)| 00:00:01 |
|   4 |     TABLE ACCESS FULL| CUST_MST   |     3 |    63 |     3   (0)| 00:00:01 |
|   5 |     TABLE ACCESS FULL| STORE_MST  |     9 |   234 |     3   (0)| 00:00:01 |
|   6 |    TABLE ACCESS FULL | CART_MST   |     3 |    33 |     3   (0)| 00:00:01 |
|   7 |   TABLE ACCESS FULL  | BASKET_MST |     3 |    48 |     3   (0)| 00:00:01 |
-----------------------------------------------------------------------------------

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

   1 - access("S1"."BASKET_NO"="S4"."BASKET_NO")
   2 - access("S1"."CART_NO"="S3"."CART_NO")
   3 - access("S1"."CUST_ID"="S2"."CUST_ID")


Statistics
----------------------------------------------------------
         15  recursive calls
          0  db block gets
          9  consistent gets
          0  physical reads
          0  redo size
       2313  bytes sent via SQL*Net to client
        624  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          9  rows processed


store_mst→cust_mst→cart_mst→basket_mstの順

Left-Depp-Join
store_mstをBulid表にする
Probe表→NO_SWAP_JOIN_INPUTSヒント
Bulid表→SWAP_JOIN_INPUTSヒント


SELECT /*+ leading(s1 s2 s3 s4) use_hash(s2 s3 s4) swap_join_inputs(s2)*/
	s1.*
	,s2.*
	,s3.*
	,s4.*
FROM
	cust_mst s1
	,store_mst s2
	,cart_mst s3
	,basket_mst s4
WHERE
	s1.cust_id = s2.cust_id
AND s1.cart_no = s3.cart_no
AND s1.basket_no = s4.basket_no;

AINE@pdb1> SELECT /*+ leading(s1 s2 s3 s4) use_hash(s2 s3 s4) swap_join_inputs(s2)*/
  2  s1.*
  3  ,s2.*
  4  ,s3.*
  5  ,s4.*
  6  FROM
  7  cust_mst s1
  8  ,store_mst s2
  9  ,cart_mst s3
 10  ,basket_mst s4
 11  WHERE
 12  s1.cust_id = s2.cust_id
 13  AND s1.cart_no = s3.cart_no
 14  AND s1.basket_no = s4.basket_no;

9 rows selected.

Elapsed: 00:00:00.02

Execution Plan
----------------------------------------------------------
Plan hash value: 3973336579

-----------------------------------------------------------------------------------
| Id  | Operation            | Name       | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |            |     9 |   666 |    12   (0)| 00:00:01 |
|*  1 |  HASH JOIN           |            |     9 |   666 |    12   (0)| 00:00:01 |
|*  2 |   HASH JOIN          |            |     9 |   522 |     9   (0)| 00:00:01 |
|*  3 |    HASH JOIN         |            |     9 |   423 |     6   (0)| 00:00:01 |
|   4 |     TABLE ACCESS FULL| STORE_MST  |     9 |   234 |     3   (0)| 00:00:01 |
|   5 |     TABLE ACCESS FULL| CUST_MST   |     3 |    63 |     3   (0)| 00:00:01 |
|   6 |    TABLE ACCESS FULL | CART_MST   |     3 |    33 |     3   (0)| 00:00:01 |
|   7 |   TABLE ACCESS FULL  | BASKET_MST |     3 |    48 |     3   (0)| 00:00:01 |
-----------------------------------------------------------------------------------

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

   1 - access("S1"."BASKET_NO"="S4"."BASKET_NO")
   2 - access("S1"."CART_NO"="S3"."CART_NO")
   3 - access("S1"."CUST_ID"="S2"."CUST_ID")


Statistics
----------------------------------------------------------
         13  recursive calls
          0  db block gets
          9  consistent gets
          0  physical reads
          0  redo size
       2313  bytes sent via SQL*Net to client
        624  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          9  rows processed

Right-Deep-Join


SELECT /*+ leading(s1 s2 s3 s4) use_hash(s2 s3 s4) swap_join_inputs(s2) swap_join_inputs(s3) swap_join_inputs(s4)*/
	s1.*
	,s2.*
	,s3.*
	,s4.*
FROM
	cust_mst s1
	,store_mst s2
	,cart_mst s3
	,basket_mst s4
WHERE
	s1.cust_id = s2.cust_id
AND s1.cart_no = s3.cart_no
AND s1.basket_no = s4.basket_no;


AINE@pdb1> SELECT /*+ leading(s1 s2 s3 s4) use_hash(s2 s3 s4) swap_join_inputs(s2) swap_join_inputs(s3) swap_join_inputs(s4)*/
  2  s1.*
  3  ,s2.*
  4  ,s3.*
  5  ,s4.*
  6  FROM
  7  cust_mst s1
  8  ,store_mst s2
  9  ,cart_mst s3
 10  ,basket_mst s4
 11  WHERE
 12  s1.cust_id = s2.cust_id
 13  AND s1.cart_no = s3.cart_no
 14  AND s1.basket_no = s4.basket_no;

9 rows selected.

Elapsed: 00:00:00.02

Execution Plan
----------------------------------------------------------
Plan hash value: 3584051765

-----------------------------------------------------------------------------------
| Id  | Operation            | Name       | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |            |     9 |   666 |    12   (0)| 00:00:01 |
|*  1 |  HASH JOIN           |            |     9 |   666 |    12   (0)| 00:00:01 |
|   2 |   TABLE ACCESS FULL  | BASKET_MST |     3 |    48 |     3   (0)| 00:00:01 |
|*  3 |   HASH JOIN          |            |     9 |   522 |     9   (0)| 00:00:01 |
|   4 |    TABLE ACCESS FULL | CART_MST   |     3 |    33 |     3   (0)| 00:00:01 |
|*  5 |    HASH JOIN         |            |     9 |   423 |     6   (0)| 00:00:01 |
|   6 |     TABLE ACCESS FULL| STORE_MST  |     9 |   234 |     3   (0)| 00:00:01 |
|   7 |     TABLE ACCESS FULL| CUST_MST   |     3 |    63 |     3   (0)| 00:00:01 |
-----------------------------------------------------------------------------------

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

   1 - access("S1"."BASKET_NO"="S4"."BASKET_NO")
   3 - access("S1"."CART_NO"="S3"."CART_NO")
   5 - access("S1"."CUST_ID"="S2"."CUST_ID")


Statistics
----------------------------------------------------------
         13  recursive calls
          0  db block gets
          9  consistent gets
          0  physical reads
          0  redo size
       2317  bytes sent via SQL*Net to client
        624  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          9  rows processed

swap_join_inputs&no_swap_join_inputs


SELECT /*+ leading(s1 s2 s3 s4) use_hash(s2 s3 s4) swap_join_inputs(s2) no_swap_join_inputs(s3) no_swap_join_inputs(s4) */
	s1.*
	,s2.*
	,s3.*
	,s4.*
FROM
	cust_mst s1
	,store_mst s2
	,cart_mst s3
	,basket_mst s4
WHERE
	s1.cust_id = s2.cust_id
AND s1.cart_no = s3.cart_no
AND s1.basket_no = s4.basket_no;

AINE@pdb1> SELECT /*+ leading(s1 s2 s3 s4) use_hash(s2 s3 s4) swap_join_inputs(s2) no_swap_join_inputs(s3) no_swap_join_inputs(s4) */
  2  s1.*
  3  ,s2.*
  4  ,s3.*
  5  ,s4.*
  6  FROM
  7  cust_mst s1
  8  ,store_mst s2
  9  ,cart_mst s3
 10  ,basket_mst s4
 11  WHERE
 12  s1.cust_id = s2.cust_id
 13  AND s1.cart_no = s3.cart_no
 14  AND s1.basket_no = s4.basket_no;

9 rows selected.

Elapsed: 00:00:00.02

Execution Plan
----------------------------------------------------------
Plan hash value: 3973336579

-----------------------------------------------------------------------------------
| Id  | Operation            | Name       | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |            |     9 |   666 |    12   (0)| 00:00:01 |
|*  1 |  HASH JOIN           |            |     9 |   666 |    12   (0)| 00:00:01 |
|*  2 |   HASH JOIN          |            |     9 |   522 |     9   (0)| 00:00:01 |
|*  3 |    HASH JOIN         |            |     9 |   423 |     6   (0)| 00:00:01 |
|   4 |     TABLE ACCESS FULL| STORE_MST  |     9 |   234 |     3   (0)| 00:00:01 |
|   5 |     TABLE ACCESS FULL| CUST_MST   |     3 |    63 |     3   (0)| 00:00:01 |
|   6 |    TABLE ACCESS FULL | CART_MST   |     3 |    33 |     3   (0)| 00:00:01 |
|   7 |   TABLE ACCESS FULL  | BASKET_MST |     3 |    48 |     3   (0)| 00:00:01 |
-----------------------------------------------------------------------------------

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

   1 - access("S1"."BASKET_NO"="S4"."BASKET_NO")
   2 - access("S1"."CART_NO"="S3"."CART_NO")
   3 - access("S1"."CUST_ID"="S2"."CUST_ID")


Statistics
----------------------------------------------------------
         13  recursive calls
          0  db block gets
          9  consistent gets
          0  physical reads
          0  redo size
       2313  bytes sent via SQL*Net to client
        624  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          9  rows processed

sql oracle sort-merge

バージョン


[oracle@centos7 ~]$ docker exec -it orcl_18cr3 bash
[oracle@d102981b4117 ~]$ sqlplus aine/ORACLE_PWD@pdb1

SQL*Plus: Release 18.0.0.0.0 - Production on Sun Jan 13 01:41:52 2019
Version 18.3.0.0.0

Copyright (c) 1982, 2018, Oracle.  All rights reserved.

Last Successful login time: Sun Jan 13 2019 01:19:00 +00:00

Connected to:
Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production
Version 18.3.0.0.0

テストデータ


PURGE RECYCLEBIN;

DROP TABLE warehouse_mst PURGE;
CREATE TABLE warehouse_mst AS
SELECT DISTINCT
    rpad('w_',6,dbms_random.STRING('X',4)) AS warehouse_code
	,TRUNC(ABS(dbms_random.VALUE(10,20)),0) AS shipping_permission_over_days
FROM
	dual
CONNECT BY
	LEVEL <= &1
;

ALTER TABLE warehouse_mst ADD CONSTRAINT warehouse_mst_pk PRIMARY KEY(warehouse_code);

DROP TABLE item_mst PURGE;
CREATE TABLE item_mst AS
SELECT DISTINCT
    s1.warehouse_code
    ,rpad('p_',6,dbms_random.STRING('X',4)) AS item
    ,to_char(TRUNC(ABS(dbms_random.VALUE(1,10)),0)) AS item_kbn
    ,dbms_random.STRING('U',2) || lpad(TRUNC(ABS(dbms_random.VALUE(1,10)),0),5,'0') AS main_loc
FROM
    warehouse_mst s1
CONNECT BY
	LEVEL <= &1
;

ALTER TABLE item_mst ADD CONSTRAINT item_mst_pk PRIMARY KEY(warehouse_code,item);

DROP TABLE loc_mst PURGE;
CREATE TABLE loc_mst AS 
SELECT DISTINCT
	warehouse_code
    ,main_loc AS loc
	,lpad(ROWNUM,2,'0') AS ZONE
	,substr(main_loc,1,2) AS BLOCK
	,lpad(TRUNC(ABS(dbms_random.VALUE(1,10)),0),3,'0') AS area
	,lpad(TRUNC(ABS(dbms_random.VALUE(1,10)),0),2,'0') AS LINE
FROM
    item_mst
;

ALTER TABLE loc_mst ADD CONSTRAINT loc_mst_pk PRIMARY KEY(warehouse_code,loc);

DROP TABLE zone_mst PURGE;
CREATE TABLE zone_mst AS 
SELECT DISTINCT
    warehouse_code
	,ZONE
	,to_char(TRUNC(ABS(dbms_random.VALUE(1,10)),0)) AS zone_kbn
FROM
    loc_mst
;

ALTER TABLE zone_mst ADD CONSTRAINT zone_mst_pk PRIMARY KEY(warehouse_code,ZONE);

DROP TABLE cust_mst PURGE;
CREATE TABLE cust_mst AS 
SELECT
    rpad('c_',6,dbms_random.STRING('X',4)) AS cust_id
	,to_char(TRUNC(ABS(dbms_random.VALUE(1,10)),0)) AS cust_kbn
	,'bas' || lpad(TRUNC(dbms_random.VALUE(1,99),0),2,'0') AS basket_no
	,'car' || lpad(TRUNC(dbms_random.VALUE(1,99),0),2,'0') AS cart_no
FROM
	dual
CONNECT BY
	LEVEL <= &1
;

ALTER TABLE cust_mst ADD  CONSTRAINT cust_mst_pk PRIMARY KEY(cust_id) ;

DROP TABLE basket_mst PURGE;
CREATE TABLE basket_mst AS 
SELECT DISTINCT
    basket_no
	,TRUNC(ABS(dbms_random.VALUE(2000,5000)),0) AS upper_limit_weight
	,TRUNC(ABS(dbms_random.VALUE(2000,4000)),0) AS upper_limit_volume
	,to_char(TRUNC(ABS(dbms_random.VALUE(1,10)),0)) AS basket_kbn
FROM
    cust_mst
;

ALTER TABLE basket_mst ADD  CONSTRAINT basket_mst_pk PRIMARY KEY(basket_no) ;

DROP TABLE cart_mst PURGE;
CREATE TABLE cart_mst AS 
SELECT DISTINCT
    cart_no
	,TRUNC(ABS(dbms_random.VALUE(1,20)),0) AS upper_stack_cnt
	,to_char(TRUNC(ABS(dbms_random.VALUE(1,10)),0)) AS cart_kbn
FROM
    cust_mst
;

ALTER TABLE cart_mst ADD  CONSTRAINT cart_mst_pk PRIMARY KEY(cart_no) ;

DROP TABLE route_mst PURGE;
CREATE TABLE route_mst AS 
SELECT DISTINCT
	warehouse_code
	,rpad('r_',6,dbms_random.STRING('X',4)) AS route_no
	,lpad(TRUNC(dbms_random.VALUE(1,99),0),2,'0') AS barth_no
FROM
    warehouse_mst
;

ALTER TABLE route_mst ADD CONSTRAINT route_mst_pk PRIMARY KEY(warehouse_code,route_no);

DROP TABLE store_mst PURGE;
CREATE TABLE store_mst AS
SELECT DISTINCT
	rpad('s_',6,dbms_random.STRING('X',4)) AS store_no
	,s1.route_no AS route_no
    ,lpad(TRUNC(ABS(dbms_random.VALUE(1,99)),0),2,'0') AS route_order
    ,s2.cust_id AS cust_id
	,to_char(TRUNC(ABS(dbms_random.VALUE(1,10)),0)) AS store_kbn
FROM
    route_mst s1
	,cust_mst s2
;

ALTER TABLE store_mst ADD CONSTRAINT store_mst_pk PRIMARY KEY(store_no);

DROP TABLE stock PURGE;
CREATE TABLE stock AS
WITH make_date AS (
    SELECT
        to_char(A.date_from + ROWNUM - 1, 'YYYYMMDD') AS std_date
    FROM
        (
            SELECT
                TO_DATE(to_char(sysdate, 'YYYY/MM/DD'), 'YYYY/MM/DD') AS date_from
                , TO_DATE(to_char(sysdate + 2, 'YYYY/MM/DD'), 'YYYY/MM/DD') AS date_to
            FROM
                dual
        ) A
    CONNECT BY
        LEVEL <= A.date_to - A.date_from + 1
)
SELECT
    s0.item
    ,s1.warehousing_date AS warehousing_date
    , s2.expiration_date AS expiration_date
    , TRUNC(ABS(dbms_random.VALUE(100, 300)), 0) AS stock_qty
    , TRUNC(ABS(dbms_random.VALUE(10, 90)), 0) AS reserved_stock_qty
FROM
    item_mst s0
    ,(SELECT s1.std_date,to_char(TO_DATE(s1.std_date) - INTERVAL '10' DAY,'YYYYMMDD') AS warehousing_date FROM make_date s1) s1
    , LATERAL(SELECT s2.std_date,to_char(TO_DATE(s2.std_date) + INTERVAL '30' DAY,'YYYYMMDD') AS expiration_date FROM make_date s2 WHERE s1.std_date <= s2.std_date) s2
;

ALTER TABLE stock ADD CONSTRAINT stock_pk PRIMARY KEY(item,warehousing_date,expiration_date);

DROP TABLE sales_trn PURGE;
CREATE TABLE sales_trn AS
WITH make_date AS (
    SELECT
        to_char(A.date_from + ROWNUM - 1, 'YYYYMMDD') AS std_date
    FROM
        (
            SELECT
                TO_DATE(to_char(sysdate, 'YYYY/MM/DD'), 'YYYY/MM/DD') AS date_from
                , TO_DATE(to_char(sysdate + 2, 'YYYY/MM/DD'), 'YYYY/MM/DD') AS date_to
            FROM
                dual
        ) A
    CONNECT BY
        LEVEL <= A.date_to - A.date_from + 1
)
SELECT
    ROWNUM AS seq
    ,s0.item
    ,TRUNC(ABS(dbms_random.VALUE(10,100)),0) AS prov_inst_qty
    ,s1.cust_id
    ,s1.store_no
    ,s2.std_date AS arrive_date
FROM
    item_mst s0
    ,store_mst s1
    ,make_date s2
;

ALTER TABLE sales_trn ADD  CONSTRAINT sales_trn_pk PRIMARY KEY(seq);

cust_mst→store_mst→cart_mst→basket_mstの順


SELECT /*+ leading(s1 s2 s3 s4) use_merge(s1 s2 s3) */
	s1.*
	,s2.*
	,s3.*
	,s4.*
FROM
	cust_mst s1
	,store_mst s2
	,cart_mst s3
	,basket_mst s4
WHERE
	s1.cust_id = s2.cust_id
AND s1.cart_no = s3.cart_no
AND s1.basket_no = s4.basket_no
;

AINE@pdb1> SELECT /*+ leading(s1 s2 s3 s4) use_merge(s2 s3 s4) */
  2  s1.*
  3  ,s2.*
  4  ,s3.*
  5  ,s4.*
  6  FROM
  7  cust_mst s1
  8  ,store_mst s2
  9  ,cart_mst s3
 10  ,basket_mst s4
 11  WHERE
 12  s1.cust_id = s2.cust_id
 13  AND s1.cart_no = s3.cart_no
 14  AND s1.basket_no = s4.basket_no
 15  ;

9 rows selected.

Elapsed: 00:00:00.01

Execution Plan
----------------------------------------------------------
Plan hash value: 1357258055

------------------------------------------------------------------------------------------------
| Id  | Operation                        | Name        | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                 |             |     9 |   666 |    16  (32)| 00:00:01 |
|   1 |  MERGE JOIN                      |             |     9 |   666 |    16  (32)| 00:00:01 |
|   2 |   SORT JOIN                      |             |     9 |   522 |    12  (34)| 00:00:01 |
|   3 |    MERGE JOIN                    |             |     9 |   522 |    11  (28)| 00:00:01 |
|   4 |     SORT JOIN                    |             |     9 |   423 |     7  (29)| 00:00:01 |
|   5 |      MERGE JOIN                  |             |     9 |   423 |     6  (17)| 00:00:01 |
|   6 |       TABLE ACCESS BY INDEX ROWID| CUST_MST    |     3 |    63 |     2   (0)| 00:00:01 |
|   7 |        INDEX FULL SCAN           | CUST_MST_PK |     3 |       |     1   (0)| 00:00:01 |
|*  8 |       SORT JOIN                  |             |     9 |   234 |     4  (25)| 00:00:01 |
|   9 |        TABLE ACCESS FULL         | STORE_MST   |     9 |   234 |     3   (0)| 00:00:01 |
|* 10 |     SORT JOIN                    |             |     3 |    33 |     4  (25)| 00:00:01 |
|  11 |      TABLE ACCESS FULL           | CART_MST    |     3 |    33 |     3   (0)| 00:00:01 |
|* 12 |   SORT JOIN                      |             |     3 |    48 |     4  (25)| 00:00:01 |
|  13 |    TABLE ACCESS FULL             | BASKET_MST  |     3 |    48 |     3   (0)| 00:00:01 |
------------------------------------------------------------------------------------------------

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

   8 - access("S1"."CUST_ID"="S2"."CUST_ID")
       filter("S1"."CUST_ID"="S2"."CUST_ID")
  10 - access("S1"."CART_NO"="S3"."CART_NO")
       filter("S1"."CART_NO"="S3"."CART_NO")
  12 - access("S1"."BASKET_NO"="S4"."BASKET_NO")
       filter("S1"."BASKET_NO"="S4"."BASKET_NO")


Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
          8  consistent gets
          0  physical reads
          0  redo size
       2318  bytes sent via SQL*Net to client
        624  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          5  sorts (memory)
          0  sorts (disk)
          9  rows processed

cust_mst→basket_mst→cart_mst→store_mstの順


SELECT /*+ leading(s1 s4 s3 s2) use_merge(s4 s3 s2) */
	s1.*
	,s2.*
	,s3.*
	,s4.*
FROM
	cust_mst s1
	,store_mst s2
	,cart_mst s3
	,basket_mst s4
WHERE
	s1.cust_id = s2.cust_id
AND s1.cart_no = s3.cart_no
AND s1.basket_no = s4.basket_no
;

AINE@pdb1> SELECT /*+ leading(s1 s4 s3 s2) use_merge(s4 s3 s2) */
  2  s1.*
  3  ,s2.*
  4  ,s3.*
  5  ,s4.*
  6  FROM
  7  cust_mst s1
  8  ,store_mst s2
  9  ,cart_mst s3
 10  ,basket_mst s4
 11  WHERE
 12  s1.cust_id = s2.cust_id
 13  AND s1.cart_no = s3.cart_no
 14  AND s1.basket_no = s4.basket_no
 15  ;

9 rows selected.

Elapsed: 00:00:00.01

Execution Plan
----------------------------------------------------------
Plan hash value: 1418257035

--------------------------------------------------------------------------------------
| Id  | Operation               | Name       | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT        |            |     9 |   666 |    18  (34)| 00:00:01 |
|   1 |  MERGE JOIN             |            |     9 |   666 |    18  (34)| 00:00:01 |
|   2 |   SORT JOIN             |            |     3 |   144 |    14  (36)| 00:00:01 |
|   3 |    MERGE JOIN           |            |     3 |   144 |    13  (31)| 00:00:01 |
|   4 |     SORT JOIN           |            |     3 |   111 |     9  (34)| 00:00:01 |
|   5 |      MERGE JOIN         |            |     3 |   111 |     8  (25)| 00:00:01 |
|   6 |       SORT JOIN         |            |     3 |    63 |     4  (25)| 00:00:01 |
|   7 |        TABLE ACCESS FULL| CUST_MST   |     3 |    63 |     3   (0)| 00:00:01 |
|*  8 |       SORT JOIN         |            |     3 |    48 |     4  (25)| 00:00:01 |
|   9 |        TABLE ACCESS FULL| BASKET_MST |     3 |    48 |     3   (0)| 00:00:01 |
|* 10 |     SORT JOIN           |            |     3 |    33 |     4  (25)| 00:00:01 |
|  11 |      TABLE ACCESS FULL  | CART_MST   |     3 |    33 |     3   (0)| 00:00:01 |
|* 12 |   SORT JOIN             |            |     9 |   234 |     4  (25)| 00:00:01 |
|  13 |    TABLE ACCESS FULL    | STORE_MST  |     9 |   234 |     3   (0)| 00:00:01 |
--------------------------------------------------------------------------------------

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

   8 - access("S1"."BASKET_NO"="S4"."BASKET_NO")
       filter("S1"."BASKET_NO"="S4"."BASKET_NO")
  10 - access("S1"."CART_NO"="S3"."CART_NO")
       filter("S1"."CART_NO"="S3"."CART_NO")
  12 - access("S1"."CUST_ID"="S2"."CUST_ID")
       filter("S1"."CUST_ID"="S2"."CUST_ID")


Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
          8  consistent gets
          0  physical reads
          0  redo size
       2324  bytes sent via SQL*Net to client
        624  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          6  sorts (memory)
          0  sorts (disk)
          9  rows processed

sql oracle nested loops

バージョン


[oracle@centos7 ~]$ docker exec -it orcl_18cr3 bash
[oracle@d102981b4117 ~]$ sqlplus aine/ORACLE_PWD@pdb1

SQL*Plus: Release 18.0.0.0.0 - Production on Sun Jan 13 01:41:52 2019
Version 18.3.0.0.0

Copyright (c) 1982, 2018, Oracle.  All rights reserved.

Last Successful login time: Sun Jan 13 2019 01:19:00 +00:00

Connected to:
Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production
Version 18.3.0.0.0

テストデータ


PURGE RECYCLEBIN;

DROP TABLE warehouse_mst PURGE;
CREATE TABLE warehouse_mst AS
SELECT DISTINCT
    rpad('w_',6,dbms_random.STRING('X',4)) AS warehouse_code
	,TRUNC(ABS(dbms_random.VALUE(10,20)),0) AS shipping_permission_over_days
FROM
	dual
CONNECT BY
	LEVEL <= &1
;

ALTER TABLE warehouse_mst ADD CONSTRAINT warehouse_mst_pk PRIMARY KEY(warehouse_code);

DROP TABLE item_mst PURGE;
CREATE TABLE item_mst AS
SELECT DISTINCT
    s1.warehouse_code
    ,rpad('p_',6,dbms_random.STRING('X',4)) AS item
    ,to_char(TRUNC(ABS(dbms_random.VALUE(1,10)),0)) AS item_kbn
    ,dbms_random.STRING('U',2) || lpad(TRUNC(ABS(dbms_random.VALUE(1,10)),0),5,'0') AS main_loc
FROM
    warehouse_mst s1
CONNECT BY
	LEVEL <= &1
;

ALTER TABLE item_mst ADD CONSTRAINT item_mst_pk PRIMARY KEY(warehouse_code,item);

DROP TABLE loc_mst PURGE;
CREATE TABLE loc_mst AS 
SELECT DISTINCT
	warehouse_code
    ,main_loc AS loc
	,lpad(ROWNUM,2,'0') AS ZONE
	,substr(main_loc,1,2) AS BLOCK
	,lpad(TRUNC(ABS(dbms_random.VALUE(1,10)),0),3,'0') AS area
	,lpad(TRUNC(ABS(dbms_random.VALUE(1,10)),0),2,'0') AS LINE
FROM
    item_mst
;

ALTER TABLE loc_mst ADD CONSTRAINT loc_mst_pk PRIMARY KEY(warehouse_code,loc);

DROP TABLE zone_mst PURGE;
CREATE TABLE zone_mst AS 
SELECT DISTINCT
    warehouse_code
	,ZONE
	,to_char(TRUNC(ABS(dbms_random.VALUE(1,10)),0)) AS zone_kbn
FROM
    loc_mst
;

ALTER TABLE zone_mst ADD CONSTRAINT zone_mst_pk PRIMARY KEY(warehouse_code,ZONE);

DROP TABLE cust_mst PURGE;
CREATE TABLE cust_mst AS 
SELECT
    rpad('c_',6,dbms_random.STRING('X',4)) AS cust_id
	,to_char(TRUNC(ABS(dbms_random.VALUE(1,10)),0)) AS cust_kbn
	,'bas' || lpad(TRUNC(dbms_random.VALUE(1,99),0),2,'0') AS basket_no
	,'car' || lpad(TRUNC(dbms_random.VALUE(1,99),0),2,'0') AS cart_no
FROM
	dual
CONNECT BY
	LEVEL <= &1
;

ALTER TABLE cust_mst ADD  CONSTRAINT cust_mst_pk PRIMARY KEY(cust_id) ;

DROP TABLE basket_mst PURGE;
CREATE TABLE basket_mst AS 
SELECT DISTINCT
    basket_no
	,TRUNC(ABS(dbms_random.VALUE(2000,5000)),0) AS upper_limit_weight
	,TRUNC(ABS(dbms_random.VALUE(2000,4000)),0) AS upper_limit_volume
	,to_char(TRUNC(ABS(dbms_random.VALUE(1,10)),0)) AS basket_kbn
FROM
    cust_mst
;

ALTER TABLE basket_mst ADD  CONSTRAINT basket_mst_pk PRIMARY KEY(basket_no) ;

DROP TABLE cart_mst PURGE;
CREATE TABLE cart_mst AS 
SELECT DISTINCT
    cart_no
	,TRUNC(ABS(dbms_random.VALUE(1,20)),0) AS upper_stack_cnt
	,to_char(TRUNC(ABS(dbms_random.VALUE(1,10)),0)) AS cart_kbn
FROM
    cust_mst
;

ALTER TABLE cart_mst ADD  CONSTRAINT cart_mst_pk PRIMARY KEY(cart_no) ;

DROP TABLE route_mst PURGE;
CREATE TABLE route_mst AS 
SELECT DISTINCT
	warehouse_code
	,rpad('r_',6,dbms_random.STRING('X',4)) AS route_no
	,lpad(TRUNC(dbms_random.VALUE(1,99),0),2,'0') AS barth_no
FROM
    warehouse_mst
;

ALTER TABLE route_mst ADD CONSTRAINT route_mst_pk PRIMARY KEY(warehouse_code,route_no);

DROP TABLE store_mst PURGE;
CREATE TABLE store_mst AS
SELECT DISTINCT
	rpad('s_',6,dbms_random.STRING('X',4)) AS store_no
	,s1.route_no AS route_no
    ,lpad(TRUNC(ABS(dbms_random.VALUE(1,99)),0),2,'0') AS route_order
    ,s2.cust_id AS cust_id
	,to_char(TRUNC(ABS(dbms_random.VALUE(1,10)),0)) AS store_kbn
FROM
    route_mst s1
	,cust_mst s2
;

ALTER TABLE store_mst ADD CONSTRAINT store_mst_pk PRIMARY KEY(store_no);

DROP TABLE stock PURGE;
CREATE TABLE stock AS
WITH make_date AS (
    SELECT
        to_char(A.date_from + ROWNUM - 1, 'YYYYMMDD') AS std_date
    FROM
        (
            SELECT
                TO_DATE(to_char(sysdate, 'YYYY/MM/DD'), 'YYYY/MM/DD') AS date_from
                , TO_DATE(to_char(sysdate + 2, 'YYYY/MM/DD'), 'YYYY/MM/DD') AS date_to
            FROM
                dual
        ) A
    CONNECT BY
        LEVEL <= A.date_to - A.date_from + 1
)
SELECT
    s0.item
    ,s1.warehousing_date AS warehousing_date
    , s2.expiration_date AS expiration_date
    , TRUNC(ABS(dbms_random.VALUE(100, 300)), 0) AS stock_qty
    , TRUNC(ABS(dbms_random.VALUE(10, 90)), 0) AS reserved_stock_qty
FROM
    item_mst s0
    ,(SELECT s1.std_date,to_char(TO_DATE(s1.std_date) - INTERVAL '10' DAY,'YYYYMMDD') AS warehousing_date FROM make_date s1) s1
    , LATERAL(SELECT s2.std_date,to_char(TO_DATE(s2.std_date) + INTERVAL '30' DAY,'YYYYMMDD') AS expiration_date FROM make_date s2 WHERE s1.std_date <= s2.std_date) s2
;

ALTER TABLE stock ADD CONSTRAINT stock_pk PRIMARY KEY(item,warehousing_date,expiration_date);

DROP TABLE sales_trn PURGE;
CREATE TABLE sales_trn AS
WITH make_date AS (
    SELECT
        to_char(A.date_from + ROWNUM - 1, 'YYYYMMDD') AS std_date
    FROM
        (
            SELECT
                TO_DATE(to_char(sysdate, 'YYYY/MM/DD'), 'YYYY/MM/DD') AS date_from
                , TO_DATE(to_char(sysdate + 2, 'YYYY/MM/DD'), 'YYYY/MM/DD') AS date_to
            FROM
                dual
        ) A
    CONNECT BY
        LEVEL <= A.date_to - A.date_from + 1
)
SELECT
    ROWNUM AS seq
    ,s0.item
    ,TRUNC(ABS(dbms_random.VALUE(10,100)),0) AS prov_inst_qty
    ,s1.cust_id
    ,s1.store_no
    ,s2.std_date AS arrive_date
FROM
    item_mst s0
    ,store_mst s1
    ,make_date s2
;

ALTER TABLE sales_trn ADD  CONSTRAINT sales_trn_pk PRIMARY KEY(seq);

結合するテーブルが2つ

cust_mst→store_mstの順


SELECT /*+ leading(s1 s2) use_nl(s2) */
	s1.*
	,s2.*
FROM
	cust_mst s1
	,store_mst s2
WHERE
	s1.cust_id = s2.cust_id
;

AINE@pdb1> SELECT /*+ leading(s1 s2) use_nl(s2) */
  2  s1.*
  3  ,s2.*
  4  FROM
  5  cust_mst s1
  6  ,store_mst s2
  7  WHERE
  8  s1.cust_id = s2.cust_id
  9  ;

9 rows selected.

Elapsed: 00:00:00.01

Execution Plan
----------------------------------------------------------
Plan hash value: 755068011

--------------------------------------------------------------------------------
| Id  | Operation          | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |           |     9 |   423 |     8   (0)| 00:00:01 |
|   1 |  NESTED LOOPS      |           |     9 |   423 |     8   (0)| 00:00:01 |
|   2 |   TABLE ACCESS FULL| CUST_MST  |     3 |    63 |     3   (0)| 00:00:01 |
|*  3 |   TABLE ACCESS FULL| STORE_MST |     3 |    78 |     2   (0)| 00:00:01 |
--------------------------------------------------------------------------------

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

   3 - filter("S1"."CUST_ID"="S2"."CUST_ID")


Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
         10  consistent gets
          0  physical reads
          0  redo size
       1577  bytes sent via SQL*Net to client
        624  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          9  rows processed


store_mst→cust_mstの順


SELECT /*+ leading(s2 s1) use_nl(s1) */
	s1.*
	,s2.*
FROM
	cust_mst s1
	,store_mst s2
WHERE
	s1.cust_id = s2.cust_id
;

AINE@pdb1> SELECT /*+ leading(s2 s1) use_nl(s1) */
  2  s1.*
  3  ,s2.*
  4  FROM
  5  cust_mst s1
  6  ,store_mst s2
  7  WHERE
  8  s1.cust_id = s2.cust_id
  9  ;

9 rows selected.

Elapsed: 00:00:00.02

Execution Plan
----------------------------------------------------------
Plan hash value: 217607987

--------------------------------------------------------------------------------------------
| Id  | Operation                    | Name        | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |             |     9 |   423 |    12   (0)| 00:00:01 |
|   1 |  NESTED LOOPS                |             |     9 |   423 |    12   (0)| 00:00:01 |
|   2 |   NESTED LOOPS               |             |     9 |   423 |    12   (0)| 00:00:01 |
|   3 |    TABLE ACCESS FULL         | STORE_MST   |     9 |   234 |     3   (0)| 00:00:01 |
|*  4 |    INDEX UNIQUE SCAN         | CUST_MST_PK |     1 |       |     0   (0)| 00:00:01 |
|   5 |   TABLE ACCESS BY INDEX ROWID| CUST_MST    |     1 |    21 |     1   (0)| 00:00:01 |
--------------------------------------------------------------------------------------------

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

   4 - access("S1"."CUST_ID"="S2"."CUST_ID")


Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
         16  consistent gets
          0  physical reads
          0  redo size
       1626  bytes sent via SQL*Net to client
        624  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          9  rows processed


結合するテーブルが3つ以上

cust_mst→store_mst→cart_mst→basket_mstの順


SELECT /*+ leading(s1 s2 s3 s4) use_nl(s2 s3 s4) */
	s1.*
	,s2.*
	,s3.*
	,s4.*
FROM
	cust_mst s1
	,store_mst s2
	,cart_mst s3
	,basket_mst s4
WHERE
	s1.cust_id = s2.cust_id
AND s1.cart_no = s3.cart_no
AND s1.basket_no = s4.basket_no
;

AINE@pdb1> SELECT /*+ leading(s1 s2 s3 s4) use_nl(s2 s3 s4) */
  2  s1.*
  3  ,s2.*
  4  ,s3.*
  5  ,s4.*
  6  FROM
  7  cust_mst s1
  8  ,store_mst s2
  9  ,cart_mst s3
 10  ,basket_mst s4
 11  WHERE
 12  s1.cust_id = s2.cust_id
 13  AND s1.cart_no = s3.cart_no
 14  AND s1.basket_no = s4.basket_no
 15  ;

9 rows selected.

Elapsed: 00:00:00.01

Execution Plan
----------------------------------------------------------
Plan hash value: 1965907948

------------------------------------------------------------------------------------------------
| Id  | Operation                      | Name          | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT               |               |     9 |   666 |    26   (0)| 00:00:01 |
|   1 |  NESTED LOOPS                  |               |     9 |   666 |    26   (0)| 00:00:01 |
|   2 |   NESTED LOOPS                 |               |     9 |   666 |    26   (0)| 00:00:01 |
|   3 |    NESTED LOOPS                |               |     9 |   522 |    17   (0)| 00:00:01 |
|   4 |     NESTED LOOPS               |               |     9 |   423 |     8   (0)| 00:00:01 |
|   5 |      TABLE ACCESS FULL         | CUST_MST      |     3 |    63 |     3   (0)| 00:00:01 |
|*  6 |      TABLE ACCESS FULL         | STORE_MST     |     3 |    78 |     2   (0)| 00:00:01 |
|   7 |     TABLE ACCESS BY INDEX ROWID| CART_MST      |     1 |    11 |     1   (0)| 00:00:01 |
|*  8 |      INDEX UNIQUE SCAN         | CART_MST_PK   |     1 |       |     0   (0)| 00:00:01 |
|*  9 |    INDEX UNIQUE SCAN           | BASKET_MST_PK |     1 |       |     0   (0)| 00:00:01 |
|  10 |   TABLE ACCESS BY INDEX ROWID  | BASKET_MST    |     1 |    16 |     1   (0)| 00:00:01 |
------------------------------------------------------------------------------------------------

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

   6 - filter("S1"."CUST_ID"="S2"."CUST_ID")
   8 - access("S1"."CART_NO"="S3"."CART_NO")
   9 - access("S1"."BASKET_NO"="S4"."BASKET_NO")


Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
         36  consistent gets
          0  physical reads
          0  redo size
       2317  bytes sent via SQL*Net to client
        624  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          9  rows processed


cust_mst→basket_mst→cart_mst→store_mstの順


SELECT /*+ leading(s1 s4 s3 s2) use_nl(s4 s3 s2) */
	s1.*
	,s2.*
	,s3.*
	,s4.*
FROM
	cust_mst s1
	,store_mst s2
	,cart_mst s3
	,basket_mst s4
WHERE
	s1.cust_id = s2.cust_id
AND s1.cart_no = s3.cart_no
AND s1.basket_no = s4.basket_no
;

AINE@pdb1> SELECT /*+ leading(s1 s4 s3 s2) use_nl(s4 s3 s2) */
  2  s1.*
  3  ,s2.*
  4  ,s3.*
  5  ,s4.*
  6  FROM
  7  cust_mst s1
  8  ,store_mst s2
  9  ,cart_mst s3
 10  ,basket_mst s4
 11  WHERE
 12  s1.cust_id = s2.cust_id
 13  AND s1.cart_no = s3.cart_no
 14  AND s1.basket_no = s4.basket_no
 15  ;

9 rows selected.

Elapsed: 00:00:00.01

Execution Plan
----------------------------------------------------------
Plan hash value: 233170625

------------------------------------------------------------------------------------------------
| Id  | Operation                      | Name          | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT               |               |     9 |   666 |    14   (0)| 00:00:01 |
|   1 |  NESTED LOOPS                  |               |     9 |   666 |    14   (0)| 00:00:01 |
|   2 |   NESTED LOOPS                 |               |     3 |   144 |     9   (0)| 00:00:01 |
|   3 |    NESTED LOOPS                |               |     3 |   111 |     6   (0)| 00:00:01 |
|   4 |     TABLE ACCESS FULL          | CUST_MST      |     3 |    63 |     3   (0)| 00:00:01 |
|   5 |     TABLE ACCESS BY INDEX ROWID| BASKET_MST    |     1 |    16 |     1   (0)| 00:00:01 |
|*  6 |      INDEX UNIQUE SCAN         | BASKET_MST_PK |     1 |       |     0   (0)| 00:00:01 |
|   7 |    TABLE ACCESS BY INDEX ROWID | CART_MST      |     1 |    11 |     1   (0)| 00:00:01 |
|*  8 |     INDEX UNIQUE SCAN          | CART_MST_PK   |     1 |       |     0   (0)| 00:00:01 |
|*  9 |   TABLE ACCESS FULL            | STORE_MST     |     3 |    78 |     2   (0)| 00:00:01 |
------------------------------------------------------------------------------------------------

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

   6 - access("S1"."BASKET_NO"="S4"."BASKET_NO")
   8 - access("S1"."CART_NO"="S3"."CART_NO")
   9 - filter("S1"."CUST_ID"="S2"."CUST_ID")


Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
         20  consistent gets
          0  physical reads
          0  redo size
       2317  bytes sent via SQL*Net to client
        624  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          9  rows processed