NL 조인 튜닝 예시

테스트 데이터 환경 아래 스크립트 참조  * 튜닝 포인트 : SQL수정 or 인덱스 변경가능 OLTP에 최적화    T_고객23        - 총건수               : 2만건       - 고객성향코드 = '920' : 101건       - 고객성향코드 종류    : 200종류             - 인덱스 : PK_T_고객23 (고객번호)    T_주문23       - 총 건수: 200만건       - 아래 조건의 결과 : 10,000건         O.주문일자 LIKE '201701%' AND O.상품코드 = 'P103'          - 인덱스 : PK_T_주문23 (주문번호)   */

DROP   TABLE test.T_cust_KM;
CREATE TABLE test.T_cust_KM
  (custno      VARCHAR2(7),
   custname        VARCHAR2(50),
   custcode  VARCHAR2(3),
   C1            VARCHAR2(30),
   C2            VARCHAR2(30),
   C3            VARCHAR2(30),
   C4            VARCHAR2(30),
   C5            VARCHAR2(30),
   CONSTRAINT PK_T_cust_KM PRIMARY KEY (custno)
  );

CREATE PUBLIC SYNONYM T_cust_KM FOR test.T_cust_KM;

INSERT /*+ APPEND */ INTO T_cust_KM
SELECT LPAD(TO_CHAR(ROWNUM), 7, '0')                                    custno
     , RPAD(TO_CHAR(ROUND(DBMS_RANDOM.VALUE(1, 65000))), 10, '0')       custname
     , LPAD(TO_CHAR(ROUND(DBMS_RANDOM.VALUE(1, 200))) || '0', 3, '0')   custcode
     , 'ABCDEFGHIJKLMNOPQRSTUVWXYZ'                                     C1
     , 'ABCDEFGHIJKLMNOPQRSTUVWXYZ'                                     C2
     , 'ABCDEFGHIJKLMNOPQRSTUVWXYZ'                                     C3
     , 'ABCDEFGHIJKLMNOPQRSTUVWXYZ'                                     C4
     , 'ABCDEFGHIJKLMNOPQRSTUVWXYZ'                                     C5
FROM DUAL
CONNECT BY LEVEL <= 20000
ORDER BY DBMS_RANDOM.RANDOM();;

COMMIT;

DROP   TABLE test.T_DATE23;
CREATE TABLE test.T_DATE23 AS
SELECT TO_CHAR(TO_DATE('20170101', 'YYYYMMDD') + LEVEL, 'YYYYMMDD') WORK_DATE
FROM DUAL
CONNECT BY LEVEL <= 100
ORDER BY DBMS_RANDOM.RANDOM();

CREATE PUBLIC SYNONYM T_DATE23 FOR test.T_DATE23;

DROP TABLE  test.T_order_KM ;
CREATE TABLE test.T_order_KM AS
SELECT  'O' || LPAD(TO_CHAR(ROWNUM), 7, '0')                                    orderno
      ,  C.custno
      , 'P' || LPAD(TO_CHAR(ROUND(DBMS_RANDOM.VALUE(1, 200))) || '0', 3, '0')   code
      ,  D.WORK_DATE                                                            orderDT
      , ROUND(DBMS_RANDOM.VALUE(1, 3))                                          orderCNT        
FROM T_cust_KM C, T_DATE23 D
ORDER BY DBMS_RANDOM.RANDOM();;

CREATE PUBLIC SYNONYM T_order_KM FOR test.T_order_KM;

ALTER TABLE test.T_order_KM 
ADD CONSTRAINT PK_T_order_KM PRIMARY KEY(orderno)
;

EXECUTE DBMS_STATS.GATHER_TABLE_STATS('test', 'T_cust_KM');
EXECUTE DBMS_STATS.GATHER_TABLE_STATS('test', 'T_order_KM');​

 

[AS-IS 쿼리]

- 조건절에 있는 인덱스가 없어 드라이빙테이블 기준으로 hash join 발생하고 있다.

SQL> select * from table(dbms_xplan.display_cursor(null, null, 'allstats last'));

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  8z6gaw801qx46, child number 0
-------------------------------------
SELECT C.custno, C.custname, C.C1, O.orderno, O.code, O.orderDT, O.orderCNT 
FROM T_CUST_KM C, T_ORDER_KM O 
WHERE C.custcode = '920'  
AND  O.custno     = C.custno  
AND  O.orderdt    LIKE '201701%'  
AND  O.code     = 'P103'

Plan hash value: 2736319789

-----------------------------------------------------------------------------
| Id  | Operation          | Name       | E-Rows |  OMem |  1Mem | Used-Mem |
-----------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |            |        |       |       |          |
|*  1 |  HASH JOIN         |            |    114 |  1021K|  1021K| 1264K (0)|
|*  2 |   TABLE ACCESS FULL| T_CUST_KM  |    106 |       |       |          |
|*  3 |   TABLE ACCESS FULL| T_ORDER_KM |   3015 |       |       |          |
-----------------------------------------------------------------------------

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

   1 - access("O"."CUSTNO"="C"."CUSTNO")
   2 - filter("C"."CUSTCODE"='920')
   3 - filter(("O"."CODE"='P103' AND "O"."ORDERDT" LIKE '201701%'))

 

 

[TO_BE 쿼리]

-  인덱스 구성을 아래와 같이 추가
> T_CUST_KM에 custcode 인덱스 추가
> T_ORDER_KM에 custno, code, orderdt로 인덱스 추가

create index T_CUST_KM_x1 on T_CUST_KM (custcode);
create index T_ORDER_KM_x1 on T_ORDER_KM (custno,code, orderdt);

-  조건절 변경

> O.orderdt     LIKE '201701%'  조건을 O.orderdt    >='20170101' AND O.orderdt  <='20170131' 변경함

 

>> *+ ordered use_nl(O) */ 힌트 유도하지 않아도 옵티마이저가 새로 생성된 인덱스에 의해 nested loop 조인으로 품

수행속도  00:00:00:23  > 00:00:00:02  로 감소

SQL> select * from table(dbms_xplan.display_cursor(null,null,'allstats last'));

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  25ydzn0wpt854, child number 0
-------------------------------------
SELECT C.custno, C.custname, C.C1, O.orderno, O.code, O.orderDT, O.orderCNT 
FROM T_CUST_KM C, T_ORDER_KM O 
WHERE C.custcode = '920'  --드라이빙조건
AND  O.custno     = C.custno  --드라이빙조건
AND  O.orderdt    >='20170101' AND O.orderdt  <='20170131'  --드라이빙조건
AND  O.code     = 'P103' --드라이빙조건

Plan hash value: 3399044312

------------------------------------------------------------------------
| Id  | Operation                             | Name          | E-Rows |
------------------------------------------------------------------------
|   0 | SELECT STATEMENT                      |               |        |
|   1 |  NESTED LOOPS                         |               |    109 |
|   2 |   NESTED LOOPS                        |               |    109 |
|   3 |    TABLE ACCESS BY INDEX ROWID BATCHED| T_CUST_KM     |    106 |
|*  4 |     INDEX RANGE SCAN                  | T_CUST_KM_X1  |    106 |
|*  5 |    INDEX RANGE SCAN                   | T_ORDER_KM_X1 |      1 |
|   6 |   TABLE ACCESS BY INDEX ROWID         | T_ORDER_KM    |      1 |
------------------------------------------------------------------------

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

   4 - access("C"."CUSTCODE"='920')
   5 - access("O"."CUSTNO"="C"."CUSTNO" AND "O"."CODE"='P103' AND
              "O"."ORDERDT">='20170101' AND "O"."ORDERDT"<='20170131')