by-nc-sa     개발자, DBA가 함께 만들어가는 구루비 지식창고!

7. 조인을 내포한 DML 튜닝




07 조인을 내포한 DML 튜닝

(1) 수정 가능 조인 뷰 활용

TEST 준비 스크립트


--고객 100만
CREATE TABLE 고객 AS
SELECT LEVEL AS 고객번호
     , SYSDATE - 365 AS 최종거래일시
     , 0 AS 최근거래횟수
     , 0 AS 최근거래금액
  FROM DUAL
CONNECT BY LEVEL <= 1000000

ALTER TABLE 고객 ADD CONSTRAINT IDX_고객_PK PRIMARY KEY(고객번호)

--거래 1000만
CREATE TABLE 거래 AS
SELECT CEIL(LEVEL / 1000000) 고객번호
     , ADD_MONTHS(SYSDATE,-4) + FLOOR( DBMS_RANDOM.VALUE(1,120) ) AS 거래일시
     , (FLOOR( DBMS_RANDOM.VALUE(1,13) )*100) + 500 AS 거래금액
  FROM DUAL 
CONNECT BY LEVEL <= 10000000

CREATE INDEX IDX_거래_01 ON 거래(거래일시, 고객번호)
CREATE INDEX IDX_거래_02 ON 거래(고객번호, 거래일시)

EXEC DBMS_STATS.GATHER_TABLE_STATS(USER, '고객')
EXEC DBMS_STATS.GATHER_TABLE_STATS(USER,'거래')
|
(딴애기) IDX_거래_01 =_=
SQL> SELECT /*+ GATHER_PLAN_STATISTICS INDEX(거래 IDX_거래_01)*/MAX(거래일시)
  2    FROM 거래
  3   WHERE 고객번호 = 1
  4     AND 거래일시 >= TRUNC(ADD_MONTHS(SYSDATE, -1))
  5  ;

MAX(거래
--------
10/08/28

SQL> select * from table( dbms_xplan.display_cursor( null, null, 'ALLSTATS LAST'));

-------------------------------------------------------------------------------------------------
| Id  | Operation        | Name      | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Reads  |
-------------------------------------------------------------------------------------------------
|   1 |  SORT AGGREGATE  |           |      1 |      1 |      1 |00:00:00.99 |     775 |    517 |

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
|*  2 |   INDEX SKIP SCAN| IDX_거래_0|      1 |    243K|    235K|00:00:00.48 |     775 |    517 |
-------------------------------------------------------------------------------------------------

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

   2 - access("거래일시">=TRUNC(ADD_MONTHS(SYSDATE@!,-1)) AND "고객번호"=1 AND "거래일시" IS NOT
              NULL)
       filter("고객번호"=1)
|
(딴애기) IDX_거래_02
SQL> SELECT /*+ GATHER_PLAN_STATISTICS INDEX(거래 IDX_거래_02)*/MAX(거래일시)
  2    FROM 거래
  3   WHERE 고객번호 = 1
  4     AND 거래일시 >= TRUNC(ADD_MONTHS(SYSDATE, -1))
  5  ;

MAX(거래
--------
10/08/28

SQL> select * from table( dbms_xplan.display_cursor( null, null, 'ALLSTATS LAST'));

----------------------------------------------------------------------------------------------------
| Id  | Operation                    | Name      | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Reads  |
----------------------------------------------------------------------------------------------------
|   1 |  SORT AGGREGATE              |           |      1 |      1 |      1 |00:00:00.05 |       3 |      2 |

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
|   2 |   FIRST ROW                  |           |      1 |    243K|      1 |00:00:00.05 |       3 |      2 |
|*  3 |    INDEX RANGE SCAN (MIN/MAX)| IDX_거래_0|      1 |    243K|      1 |00:00:00.05 |       3 |      2 |
----------------------------------------------------------------------------------------------------

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

   3 - access("고객번호"=1 AND "거래일시" IS NOT NULL AND "거래일시">=TRUNC(ADD_MONTHS(SYSDATE@!,-1)
|
전통적인 방식의 UPDATE : IDX_거래_02 ( CREATE INDEX IDX_거래_02 ON 거래(고객번호, 거래일시)
SQL> UPDATE /*+ GATHER_PLAN_STATISTICS */고객 C
  2     SET 최종거래일시 = (SELECT /*+ INDEX(거래 IDX_거래_02)*/MAX(거래일시) 
  3                           FROM 거래                                                                
  4                          WHERE 고객번호 = C.고객번호                                                        
  5                            AND 거래일시 >= TRUNC(ADD_MONTHS(SYSDATE, -1)) )    
  6       , 최근거래횟수 =  (SELECT /*+ INDEX(거래 IDX_거래_02)*/COUNT(*) 
  7                            FROM 거래                                                                
  8                           WHERE 고객번호 = C.고객번호                                                          
  9                             AND 거래일시 >= TRUNC(ADD_MONTHS(SYSDATE, -1)) )
 10       , 최근거래금액 =  (SELECT /*+ INDEX(거래 IDX_거래_02)*/SUM(거래금액) 
 11                            FROM 거래                                                                
 12                           WHERE 고객번호 = C.고객번호                                                          
 13                             AND 거래일시 >= TRUNC(ADD_MONTHS(SYSDATE, -1)) ) 
 14   WHERE EXISTS (SELECT /*+ INDEX(거래 IDX_거래_02)*/'X'
 15                   FROM 거래 
 16                  WHERE 고객번호 = C.고객번호 
 17                    AND 거래일시 >= TRUNC(ADD_MONTHS(SYSDATE, -1)) )
 18  ;

10 행이 갱신되었습니다.

SQL> select * from table( dbms_xplan.display_cursor( null, null, 'ALLSTATS LAST'));

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------------------
| Id  | Operation                     | Name      | Starts | E-Rows | A-Rows |   A-Time   | Buffers |  OMem |  1Mem |
----------------------------------------------------------------------------------------------------
|   1 |  UPDATE                       | 고객      |      1 |        |      0 |00:00:09.34 |     755K|       |       |          |
|*  2 |   HASH JOIN SEMI              |           |      1 |      1 |     10 |00:00:06.58 |   33722 |    47M|  4218K|   61M (0)
|   3 |    TABLE ACCESS FULL          | 고객      |      1 |    992K|   1000K|00:00:00.01 |    3045 |       |       |    
|*  4 |    INDEX FULL SCAN            | IDX_거래_0|      1 |   2438K|   2351K|00:00:56.88 |   30677 |       |      
|   5 |   SORT AGGREGATE              |           |     10 |      1 |     10 |00:00:00.01 |      22 |       |       |          |
|   6 |    FIRST ROW                  |           |     10 |    243K|     10 |00:00:00.01 |      22 |       |       |          |
|*  7 |     INDEX RANGE SCAN (MIN/MAX)| IDX_거래_0|     10 |    243K|     10 |00:00:00.01 |      22 |       |       |
|   8 |   SORT AGGREGATE              |           |     10 |      1 |     10 |00:00:00.42 |    7235 |       |       |          |

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
|*  9 |    INDEX RANGE SCAN           | IDX_거래_0|     10 |    243K|   2351K|00:00:00.01 |    7235 |       |       |
|  10 |   SORT AGGREGATE              |           |     10 |      1 |     10 |00:00:02.21 |     714K|       |       |          |
|  11 |    TABLE ACCESS BY INDEX ROWID| 거래      |     10 |    243K|   2351K|00:00:02.35 |     714K|       |       |     
|* 12 |     INDEX RANGE SCAN          | IDX_거래_0|     10 |    248K|   2351K|00:00:00.01 |    7235 |       |       
----------------------------------------------------------------------------------------------------

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

   2 - access("고객번호"="C"."고객번호")
   4 - access("거래일시">=TRUNC(ADD_MONTHS(SYSDATE@!,-1)))

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
       filter("거래일시">=TRUNC(ADD_MONTHS(SYSDATE@!,-1)))
   7 - access("고객번호"=:B1 AND "거래일시" IS NOT NULL AND "거래일시">=TRUNC(ADD_MONTHS(SYSDATE@!,-
   9 - access("고객번호"=:B1 AND "거래일시">=TRUNC(ADD_MONTHS(SYSDATE@!,-1)) AND "거래일시" IS NOT N
  12 - access("고객번호"=:B1 AND "거래일시">=TRUNC(ADD_MONTHS(SYSDATE@!,-1)) AND "거래일시" IS NOT N
|
전통적인 방식의 UPDATE : IDX_거래_01 (CREATE INDEX IDX_거래_01 ON 거래(거래일시, 고객번호)
SQL> UPDATE /*+ GATHER_PLAN_STATISTICS */고객 C
  2     SET 최종거래일시 = (SELECT /*+ INDEX(거래 IDX_거래_01)*/MAX(거래일시) 
  3                           FROM 거래                                                                
  4                          WHERE 고객번호 = C.고객번호                                                        
  5                            AND 거래일시 >= TRUNC(ADD_MONTHS(SYSDATE, -1)) )    
  6       , 최근거래횟수 =  (SELECT /*+ INDEX(거래 IDX_거래_01)*/COUNT(*) 
  7                            FROM 거래                                                                
  8                           WHERE 고객번호 = C.고객번호                                                          
  9                             AND 거래일시 >= TRUNC(ADD_MONTHS(SYSDATE, -1)) )
 10       , 최근거래금액 =  (SELECT /*+ INDEX(거래 IDX_거래_01)*/SUM(거래금액) 
 11                            FROM 거래                                                                
 12                           WHERE 고객번호 = C.고객번호                                                          
 13                             AND 거래일시 >= TRUNC(ADD_MONTHS(SYSDATE, -1)) ) 
 14   WHERE EXISTS (SELECT /*+ INDEX(거래 IDX_거래_01)*/'X'
 15                   FROM 거래 
 16                  WHERE 고객번호 = C.고객번호 
 17                    AND 거래일시 >= TRUNC(ADD_MONTHS(SYSDATE, -1)) );

10 행이 갱신되었습니다.

SQL> select * from table( dbms_xplan.display_cursor( null, null, 'ALLSTATS LAST'));

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------------------
| Id  | Operation                     | Name      | Starts | E-Rows | A-Rows |   A-Time   | Buffers |  OMem |  1Mem |
----------------------------------------------------------------------------------------------------
|   1 |  UPDATE                       | 고객      |      1 |        |      0 |00:00:07.10 |     741K|       |       |          |
|*  2 |   HASH JOIN SEMI              |           |      1 |      1 |     10 |00:00:00.57 |   10262 |    47M|  4218K|   61M (0)
|   3 |    TABLE ACCESS FULL          | 고객      |      1 |    992K|   1000K|00:00:00.01 |    3045 |       |       |    
|*  4 |    INDEX RANGE SCAN           | IDX_거래_0|      1 |   2438K|   2351K|00:00:00.01 |    7217 |       |     
|   5 |   SORT AGGREGATE              |           |     10 |      1 |     10 |00:00:01.37 |    7958 |       |       |          |
|*  6 |    INDEX SKIP SCAN            | IDX_거래_0|     10 |    243K|   2351K|00:00:00.01 |    7958 |       |       |   
|   7 |   SORT AGGREGATE              |           |     10 |      1 |     10 |00:00:01.28 |    7958 |       |       |          |
|*  8 |    INDEX SKIP SCAN            | IDX_거래_0|     10 |    243K|   2351K|00:00:00.01 |    7958 |       |       |   

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
|   9 |   SORT AGGREGATE              |           |     10 |      1 |     10 |00:00:03.22 |     714K|       |       |          |
|  10 |    TABLE ACCESS BY INDEX ROWID| 거래      |     10 |    243K|   2351K|00:00:02.35 |     714K|       |       |     
|* 11 |     INDEX SKIP SCAN           | IDX_거래_0|     10 |    245K|   2351K|00:00:00.01 |    7958 |       |       |
----------------------------------------------------------------------------------------------------

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

   2 - access("고객번호"="C"."고객번호")
   4 - access("거래일시">=TRUNC(ADD_MONTHS(SYSDATE@!,-1)) AND "거래일시" IS NOT NULL)
   6 - access("거래일시">=TRUNC(ADD_MONTHS(SYSDATE@!,-1)) AND "고객번호"=:B1 AND "거래일시" IS NOT N

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
       filter("고객번호"=:B1)
   8 - access("거래일시">=TRUNC(ADD_MONTHS(SYSDATE@!,-1)) AND "고객번호"=:B1 AND "거래일시" IS NOT N
       filter("고객번호"=:B1)
  11 - access("거래일시">=TRUNC(ADD_MONTHS(SYSDATE@!,-1)) AND "고객번호"=:B1 AND "거래일시" IS NOT N
       filter("고객번호"=:B1)
|
위에 쿼리 튜닝
SQL> UPDATE /*+ GATHER_PLAN_STATISTICS */고객 C  
  2     SET (최종거래일시, 최근거래횟수, 최근거래금액) = 
  3     (SELECT /*+ INDEX(거래 IDX_거래_01)*/MAX(거래일시),COUNT(*),SUM(거래금액)       
  4        FROM 거래                                                         
  5       WHERE 고객번호 = C.고객번호                                        
  6         AND 거래일시 >= TRUNC(ADD_MONTHS(SYSDATE, -1)) )                               
  7   WHERE EXISTS (SELECT /*+ INDEX(거래 IDX_거래_01)*/'X'                       
  8                   FROM 거래                                                   
  9                  WHERE 고객번호 = C.고객번호                                                
 10                    AND 거래일시 >= TRUNC(ADD_MONTHS(SYSDATE, -1)) ); 

10 행이 갱신되었습니다.

SQL> select * from table( dbms_xplan.display_cursor( null, null, 'ALLSTATS LAST'));

----------------------------------------------------------------------------------------------------
| Id  | Operation                     | Name      | Starts | E-Rows | A-Rows |   A-Time   | Buffers |  OMem |  1Mem |

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------------------
|   1 |  UPDATE                       | 고객      |      1 |        |      0 |00:00:04.68 |     699K|       |       |          |
|*  2 |   HASH JOIN SEMI              |           |      1 |      1 |     10 |00:00:00.65 |   10002 |    47M|  4218K|   61M (0)
|   3 |    TABLE ACCESS FULL          | 고객      |      1 |    992K|   1000K|00:00:00.01 |    3045 |       |       |    
|*  4 |    INDEX RANGE SCAN           | IDX_거래_0|      1 |   2353K|   2267K|00:00:00.01 |    6957 |       |     
|   5 |   SORT AGGREGATE              |           |     10 |      1 |     10 |00:00:03.39 |     689K|       |       |          |
|   6 |    TABLE ACCESS BY INDEX ROWID| 거래      |     10 |    235K|   2267K|00:00:02.27 |     689K|       |       |     
|*  7 |     INDEX SKIP SCAN           | IDX_거래_0|     10 |    236K|   2267K|00:00:00.01 |    7670 |       |       |
----------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
---------------------------------------------------

   2 - access("고객번호"="C"."고객번호")
   4 - access("거래일시">=TRUNC(ADD_MONTHS(SYSDATE@!,-1)) AND "거래일시" IS NOT NULL)
   7 - access("거래일시">=TRUNC(ADD_MONTHS(SYSDATE@!,-1)) AND "고객번호"=:B1 AND "거래일시" IS NOT N
       filter("고객번호"=:B1)
|
  • 비효율 발생 : 한 달 이내 거래가 있던 고객을 두번 조회 하기 때문에 총 고객수와 한 달 이내 거래가 발생한 고객 수에 따라 성능이 좌우..
(딴짓) NO_UNNEST
SQL> UPDATE /*+ GATHER_PLAN_STATISTICS ) */고객 C  
  2     SET (최종거래일시, 최근거래횟수, 최근거래금액) = 
  3     (SELECT /*+ INDEX(거래 IDX_거래_01)*/MAX(거래일시),COUNT(*),SUM(거래금액)       
  4        FROM 거래                                                         
  5       WHERE 고객번호 = C.고객번호                                        
  6         AND 거래일시 >= TRUNC(ADD_MONTHS(SYSDATE, -1)) )                               
  7   WHERE EXISTS (SELECT /*+ INDEX(거래 IDX_거래_01) NO_UNNEST*/'X'                       
  8                   FROM 거래                                                   
  9                  WHERE 고객번호 = C.고객번호                                                
 10                    AND 거래일시 >= TRUNC(ADD_MONTHS(SYSDATE, -1)) );    

10 행이 갱신되었습니다.

SQL> select * from table( dbms_xplan.display_cursor( null, null, 'ALLSTATS LAST'));

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
| Id  | Operation                     | Name      | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
----------------------------------------------------------------------------------------------------
|   1 |  UPDATE                       | 고객      |      1 |        |      0 |00:04:16.62 |      50M|
|*  2 |   FILTER                      |           |      1 |        |     10 |00:00:00.01 |      50M|
|   3 |    TABLE ACCESS FULL          | 고객      |      1 |    992K|   1000K|00:00:00.01 |    3045 |
|*  4 |    INDEX SKIP SCAN            | IDX_거래_0|   1000K|    235K|     10 |00:07:00.69 |      49M|
|   5 |   SORT AGGREGATE              |           |     10 |      1 |     10 |00:00:03.24 |     689K|
|   6 |    TABLE ACCESS BY INDEX ROWID| 거래      |     10 |    235K|   2267K|00:00:02.27 |     689K|
|*  7 |     INDEX SKIP SCAN           | IDX_거래_0|     10 |    236K|   2267K|00:00:00.01 |    7670 |
----------------------------------------------------------------------------------------------------

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

   2 - filter( IS NOT NULL)
   4 - access("거래일시">=TRUNC(ADD_MONTHS(SYSDATE@!,-1)) AND "고객번호"=:B1 AND "거래일시" IS NOT N
       filter("고객번호"=:B1)
   7 - access("거래일시">=TRUNC(ADD_MONTHS(SYSDATE@!,-1)) AND "고객번호"=:B1 AND "거래일시" IS NOT N
       filter("고객번호"=:B1)
실수로 거래 테이블에 고객 번호를 100만번 고객까지만 넣었어요.. 걍 참고하세요

-- 고객 : 1000만 
SQL> DROP TABLE 고객 PURGE
SQL> DROP TABLE 거래 PURGE
SQL> CREATE TABLE 고객 AS
  2  SELECT LEVEL AS 고객번호
  3       , SYSDATE - 365 AS 최종거래일시
  4       , 0 AS 최근거래횟수
  5       , 0 AS 최근거래금액
  6    FROM DUAL
  7  CONNECT BY LEVEL <= 10000000;

SQL> ALTER TABLE 고객 ADD CONSTRAINT IDX_고객_PK PRIMARY KEY(고객번호); 
-- 거래 : 1000만 (실수로 거래 테이블의 고객번호를 100만까지만 만들었음 =_= 걍 참고하셔요..)
SQL> CREATE TABLE 거래 AS
  2  SELECT CEIL(LEVEL / 1000000) 고객번호  <-- 실수한 부분
  3       , ADD_MONTHS(SYSDATE,-4) + FLOOR( DBMS_RANDOM.VALUE(1,120) ) AS 거래일시
  4       , (FLOOR( DBMS_RANDOM.VALUE(1,13) )*100) + 500 AS 거래금액
  5    FROM DUAL 
  6  CONNECT BY LEVEL <= 10000000;

SQL> CREATE INDEX IDX_거래_01 ON 거래(거래일시, 고객번호);

SQL> EXEC DBMS_STATS.GATHER_TABLE_STATS(USER, '고객')
SQL> EXEC DBMS_STATS.GATHER_TABLE_STATS(USER,'거래')

SQL> UPDATE /*+ GATHER_PLAN_STATISTICS */고객 C                                       
  2     SET (최종거래일시, 최근거래횟수, 최근거래금액) =                              
  3     (SELECT /*+ INDEX(거래 IDX_거래_01)*/MAX(거래일시),COUNT(*),SUM(거래금액)     
  4        FROM 거래                                                                  
  5       WHERE 고객번호 = C.고객번호                                                 
  6         AND 거래일시 >= TRUNC(ADD_MONTHS(SYSDATE, -1)) )                          
  7   WHERE EXISTS (SELECT /*+ INDEX(거래 IDX_거래_01) */'X'                          
  8                   FROM 거래                                                       
  9                  WHERE 고객번호 = C.고객번호                                      
 10                    AND 거래일시 >= TRUNC(ADD_MONTHS(SYSDATE, -1)) );              

10 행이 갱신되었습니다.

SQL> 
SQL> select * from table( dbms_xplan.display_cursor( null, null, 'ALLSTATS LAST'));

----------------------------------------------------------------------------------------------------
| Id  | Operation                     | Name      | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Reads  |  OMem |

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------------------
|   1 |  UPDATE                       | 고객      |      1 |        |      0 |00:00:07.14 |     722K|  10630 |       |       |          |
|   2 |   NESTED LOOPS                |           |      1 |      1 |     10 |00:00:01.67 |    7237 |   4582 |       |       |          
|   3 |    SORT UNIQUE                |           |      1 |   2405K|     10 |00:00:01.66 |    7215 |   4580 |  9216 |  9216 
|*  4 |     INDEX RANGE SCAN          | IDX_거래_0|      1 |   2405K|   2351K|00:00:02.37 |    7215 |   4580 |  
|*  5 |    INDEX UNIQUE SCAN          | IDX_고객_P|     10 |      1 |     10 |00:00:00.01 |      22 |      2 |       |  
|   6 |   SORT AGGREGATE              |           |     10 |      1 |     10 |00:00:05.45 |     714K|   6048 |       |       |          |
|   7 |    TABLE ACCESS BY INDEX ROWID| 거래      |     10 |    240K|   2351K|00:00:04.70 |     714K|   6048 |       |  
|*  8 |     INDEX SKIP SCAN           | IDX_거래_0|     10 |    240K|   2351K|00:00:00.01 |    7956 |     13 |      
----------------------------------------------------------------------------------------------------


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

   4 - access("거래일시">=TRUNC(ADD_MONTHS(SYSDATE@!,-1)) AND "거래일시" IS NOT NULL)
   5 - access("고객번호"="C"."고객번호")
   8 - access("거래일시">=TRUNC(ADD_MONTHS(SYSDATE@!,-1)) AND "고객번호"=:B1 AND "거래일시" IS NOT N
       filter("고객번호"=:B1)

SQL> UPDATE /*+ GATHER_PLAN_STATISTICS */고객 C                                    
  2     SET (최종거래일시, 최근거래횟수, 최근거래금액) =                           
  3     (SELECT /*+ INDEX(거래 IDX_거래_01)*/MAX(거래일시),COUNT(*),SUM(거래금액)  
  4        FROM 거래                                                               
  5       WHERE 고객번호 = C.고객번호                                              
  6         AND 거래일시 >= TRUNC(ADD_MONTHS(SYSDATE, -1)) )                       
  7   WHERE EXISTS (SELECT /*+ INDEX(거래 IDX_거래_01) UNNSET HASH_SJ*/'X'         
  8                   FROM 거래                                                    
  9                  WHERE 고객번호 = C.고객번호                                   
 10                    AND 거래일시 >= TRUNC(ADD_MONTHS(SYSDATE, -1)) );           

10 행이 갱신되었습니다.

SQL> 
SQL> select * from table( dbms_xplan.display_cursor( null, null, 'ALLSTATS LAST'));

----------------------------------------------------------------------------------------------------
| Id  | Operation                     | Name      | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Reads  | Writes
----------------------------------------------------------------------------------------------------

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
|   1 |  UPDATE                       | 고객      |      1 |        |      0 |00:00:12.08 |     753K|  21168 |   7719 |       |    
|*  2 |   HASH JOIN RIGHT SEMI        |           |      1 |      1 |     10 |00:00:02.57 |   38929 |  14048 |   7719 
|*  3 |    INDEX RANGE SCAN           | IDX_거래_0|      1 |   2405K|   2351K|00:00:00.01 |    7215 |      0 |   
|   4 |    TABLE ACCESS FULL          | 고객      |      1 |   9929K|     10M|00:00:00.01 |   31714 |   8003 |     
|   5 |   SORT AGGREGATE              |           |     10 |      1 |     10 |00:00:05.40 |     714K|   7120 |      0 |       |   
|   6 |    TABLE ACCESS BY INDEX ROWID| 거래      |     10 |    240K|   2351K|00:00:04.72 |     714K|   7120 |      0 |
|*  7 |     INDEX SKIP SCAN           | IDX_거래_0|     10 |    240K|   2351K|00:00:00.01 |    7956 |      0 |      
----------------------------------------------------------------------------------------------------

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

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------

   2 - access("고객번호"="C"."고객번호")
   3 - access("거래일시">=TRUNC(ADD_MONTHS(SYSDATE@!,-1)) AND "거래일시" IS NOT NULL)
   7 - access("거래일시">=TRUNC(ADD_MONTHS(SYSDATE@!,-1)) AND "고객번호"=:B1 AND "거래일시" IS NOT N
       filter("고객번호"=:B1)
|
총 고객 수가 아주 많다면 Exists 서브 쿼리를 아래와 같이 해시 세미 조인으로 유도하는 것을 고려할 수 있다.
SQL> SELECT /*+ INDEX(거래 IDX_거래_01) */count(*)         
  2    FROM 거래                                      
  3   WHERE  거래일시 >= TRUNC(ADD_MONTHS(SYSDATE, -1))
  4  ;

  COUNT(*)
----------
   2354462

SQL> DROP TABLE 고객 PURGE
SQL> DROP TABLE 거래 PURGE
--고객 500만건
SQL> CREATE TABLE 고객 AS                   
  2  SELECT LEVEL AS 고객번호               
  3       , SYSDATE - 365 AS 최종거래일시   
  4       , 0 AS 최근거래횟수               
  5       , 0 AS 최근거래금액               
  6    FROM DUAL                            
  7  CONNECT BY LEVEL <= 5000000;    
  
SQL> ALTER TABLE 고객 ADD CONSTRAINT IDX_고객_PK PRIMARY KEY(고객번호); 
--거래 1000만건
SQL> CREATE TABLE 거래 AS                                                               
  2  SELECT FLOOR(DBMS_RANDOM.VALUE(1,5000000)) 고객번호                                
  3       , ADD_MONTHS(SYSDATE,-4) + FLOOR( DBMS_RANDOM.VALUE(1,120) ) AS 거래일시      
  4       , (FLOOR( DBMS_RANDOM.VALUE(1,13) )*100) + 500 AS 거래금액                    
  5    FROM DUAL                                                                        
  6  CONNECT BY LEVEL <= 10000000;                                                      

SQL> CREATE INDEX IDX_거래_01 ON 거래(거래일시, 고객번호)

SQL> EXEC DBMS_STATS.GATHER_TABLE_STATS(USER, '고객');
SQL> EXEC DBMS_STATS.GATHER_TABLE_STATS(USER,'거래');
SQL> UPDATE /*+ GATHER_PLAN_STATISTICS */고객 C                                  
  2     SET (최종거래일시, 최근거래횟수, 최근거래금액) =                         
  3     (SELECT /*+ INDEX(거래 IDX_거래_01)*/MAX(거래일시),COUNT(*),SUM(거래금액)
  4        FROM 거래                                                             
  5       WHERE 고객번호 = C.고객번호                                            
  6         AND 거래일시 >= TRUNC(ADD_MONTHS(SYSDATE, -1)) )                     
  7   WHERE EXISTS (SELECT /*+ INDEX(거래 IDX_거래_01) */'X'                     
  8                   FROM 거래                                                  
  9                  WHERE 고객번호 = C.고객번호                                 
 10                    AND 거래일시 >= TRUNC(ADD_MONTHS(SYSDATE, -1)) );         

1876184 행이 갱신되었습니다.

SQL> select * from table( dbms_xplan.display_cursor( null, null, 'ALLSTATS LAST'));

----------------------------------------------------------------------------------------------------
| Id  | Operation                     | Name      | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Reads  | Writes
----------------------------------------------------------------------------------------------------

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
|   1 |  UPDATE                       | 고객      |      1 |        |      0 |00:11:12.50 |     156M|  59555 |  12183 |       |  
|*  2 |   HASH JOIN RIGHT SEMI        |           |      1 |   2045K|   1876K|00:00:23.66 |   23918 |  32803 |  121
|*  3 |    INDEX RANGE SCAN           | IDX_거래_0|      1 |   2419K|   2351K|00:00:04.72 |    8129 |   5380 |   
|   4 |    TABLE ACCESS FULL          | 고객      |      1 |   4977K|   5000K|00:00:00.01 |   15789 |  15240 |   
|   5 |   SORT AGGREGATE              |           |   1876K|      1 |   1876K|00:27:58.26 |     152M|  26735 |      0 |     
|   6 |    TABLE ACCESS BY INDEX ROWID| 거래      |   1876K|      1 |   2351K|00:26:19.12 |     152M|  26735 |   
|*  7 |     INDEX SKIP SCAN           | IDX_거래_0|   1876K|      1 |   2351K|00:22:05.46 |     149M|     15 |      
----------------------------------------------------------------------------------------------------

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

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------

   2 - access("고객번호"="C"."고객번호")
   3 - access("거래일시">=TRUNC(ADD_MONTHS(SYSDATE@!,-1)) AND "거래일시" IS NOT NULL)
   7 - access("거래일시">=TRUNC(ADD_MONTHS(SYSDATE@!,-1)) AND "고객번호"=:B1 AND "거래일시" IS NOT N
       filter("고객번호"=:B1)

--이게 조금 더 빠르게 보이나... 소프트 파싱때문에.. 빨리 보이는듯.. 쿼리는 똑같이 풀린것 같음.. 맞나요?? =_=
SQL> UPDATE /*+ GATHER_PLAN_STATISTICS */고객 C                                   
  2     SET (최종거래일시, 최근거래횟수, 최근거래금액) =                          
  3     (SELECT /*+ INDEX(거래 IDX_거래_01)*/MAX(거래일시),COUNT(*),SUM(거래금액) 
  4        FROM 거래                                                              
  5       WHERE 고객번호 = C.고객번호                                             
  6         AND 거래일시 >= TRUNC(ADD_MONTHS(SYSDATE, -1)) )                      
  7   WHERE EXISTS (SELECT /*+ INDEX(거래 IDX_거래_01) UNNSET HASH_SJ*/'X'        
  8                   FROM 거래                                                   
  9                  WHERE 고객번호 = C.고객번호                                  
 10                    AND 거래일시 >= TRUNC(ADD_MONTHS(SYSDATE, -1)) );          

1876184 행이 갱신되었습니다.
                                                                            
SQL>  select * from table( dbms_xplan.display_cursor( null, null, 'ALLSTATS LAST'));

----------------------------------------------------------------------------------------------------
| Id  | Operation                     | Name      | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Reads  | Writes
----------------------------------------------------------------------------------------------------

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
|   1 |  UPDATE                       | 고객      |      1 |        |      0 |00:23:56.13 |     156M|  12493 |  12493 |       |    
|*  2 |   HASH JOIN RIGHT SEMI        |           |      1 |   2045K|   1876K|00:00:09.72 |   23918 |  12493 |  124
|*  3 |    INDEX RANGE SCAN           | IDX_거래_0|      1 |   2419K|   2351K|00:00:00.01 |    8129 |      0 |   
|   4 |    TABLE ACCESS FULL          | 고객      |      1 |   4977K|   5000K|00:00:00.01 |   15789 |      0 |     
|   5 |   SORT AGGREGATE              |           |   1876K|      1 |   1876K|00:22:46.50 |     152M|      0 |      0 |       
|   6 |    TABLE ACCESS BY INDEX ROWID| 거래      |   1876K|      1 |   2351K|00:21:12.54 |     152M|      0 |     
|*  7 |     INDEX SKIP SCAN           | IDX_거래_0|   1876K|      1 |   2351K|00:21:04.07 |     149M|      0 |      
----------------------------------------------------------------------------------------------------

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

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------

   2 - access("고객번호"="C"."고객번호")
   3 - access("거래일시">=TRUNC(ADD_MONTHS(SYSDATE@!,-1)) AND "거래일시" IS NOT NULL)
   7 - access("거래일시">=TRUNC(ADD_MONTHS(SYSDATE@!,-1)) AND "고객번호"=:B1 AND "거래일시" IS NOT N
       filter("고객번호"=:B1)
위부분 오타가 나서 다시 작업합니다.(테이블도 새로 만들어서 업데이트 한 회수가 틀립니다.)
SQL>  UPDATE /*+ GATHER_PLAN_STATISTICS */고객 C                                  
  2         SET (최종거래일시, 최근거래횟수, 최근거래금액) =                         
  3         (SELECT /*+ INDEX(거래 IDX_거래_01)*/MAX(거래일시),COUNT(*),SUM(거래금액)
  4            FROM 거래                                                             
  5           WHERE 고객번호 = C.고객번호                                            
  6             AND 거래일시 >= TRUNC(ADD_MONTHS(SYSDATE, -1)) )                     
  7       WHERE EXISTS (SELECT /*+ INDEX(거래 IDX_거래_01) */'X'                     
  8                       FROM 거래                                                  
  9                      WHERE 고객번호 = C.고객번호                                 
 10                       AND 거래일시 >= TRUNC(ADD_MONTHS(SYSDATE, -1)) );            

1876844 행이 갱신되었습니다.

SQL> SELECT SUBSTR(SQL_TEXT, 1, 30) SQL_TEXT,
  2              SQL_ID, CHILD_NUMBER
  3       FROM V$SQL
  4       WHERE SQL_TEXT LIKE ' UPDATE /*+ GATHER_PLAN_STATISTICS */고객 %';

SQL_TEXT                                                     SQL_ID        CHILD_NUMBER
------------------------------------------------------------ ------------- ------------
 UPDATE /*+ GATHER_PLAN_STATIS                               3fq85ar1rrsp3            0

SQL> select * from table( dbms_xplan.display_cursor( '3fq85ar1rrsp3', null, 'ALLSTATS LAST'));


----------------------------------------------------------------------------------------------------
| Id  | Operation                     | Name      | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Reads  | Writes

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------------------
|   1 |  UPDATE                       | 고객      |      1 |        |      0 |00:23:32.54 |     156M|  25100 |  12183 |       |    
|*  2 |   HASH JOIN RIGHT SEMI        |           |      1 |   2035K|   1876K|00:00:12.59 |   23926 |  25096 |  121
|*  3 |    INDEX RANGE SCAN           | IDX_거래_0|      1 |   2405K|   2353K|00:00:00.03 |    8137 |   5192 |   
|   4 |    TABLE ACCESS FULL          | 고객      |      1 |   5002K|   5000K|00:00:00.01 |   15789 |   7721 |     
|   5 |   SORT AGGREGATE              |           |   1876K|      1 |   1876K|00:22:09.51 |     152M|      1 |      0 |       
|   6 |    TABLE ACCESS BY INDEX ROWID| 거래      |   1876K|      1 |   2353K|00:20:38.68 |     152M|      1 |     
|*  7 |     INDEX SKIP SCAN           | IDX_거래_0|   1876K|      1 |   2353K|00:20:31.88 |     149M|      1 |      
----------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
---------------------------------------------------

   2 - access("고객번호"="C"."고객번호")
   3 - access("거래일시">=TRUNC(ADD_MONTHS(SYSDATE@!,-1)) AND "거래일시" IS NOT NULL)
   7 - access("거래일시">=TRUNC(ADD_MONTHS(SYSDATE@!,-1)) AND "고객번호"=:B1 AND "거래일시" IS NOT N
       filter("고객번호"=:B1)

SQL> 
SQL> UPDATE /*+ GATHER_PLAN_STATISTICS */고객 C                                    
  2     SET (최종거래일시, 최근거래횟수, 최근거래금액) =                           
  3     (SELECT /*+ INDEX(거래 IDX_거래_01)*/MAX(거래일시),COUNT(*),SUM(거래금액)  
  4        FROM 거래                                                               
  5       WHERE 고객번호 = C.고객번호                                              
  6         AND 거래일시 >= TRUNC(ADD_MONTHS(SYSDATE, -1)) )                       
  7   WHERE EXISTS (SELECT /*+ INDEX(거래 IDX_거래_01) UNNEST HASH_SJ*/'X'         
  8                   FROM 거래                                                    
  9                  WHERE 고객번호 = C.고객번호                                   
 10                    AND 거래일시 >= TRUNC(ADD_MONTHS(SYSDATE, -1)) );           

1876844 행이 갱신되었습니다.

SQL>  select * from table( dbms_xplan.display_cursor( null, null, 'ALLSTATS LAST'));

----------------------------------------------------------------------------------------------------
| Id  | Operation                     | Name      | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Reads  | Writes
----------------------------------------------------------------------------------------------------

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
|   1 |  UPDATE                       | 고객      |      1 |        |      0 |00:10:37.91 |     156M|  12493 |  12493 |       |    
|*  2 |   HASH JOIN RIGHT SEMI        |           |      1 |   2035K|   1876K|00:00:07.52 |   23926 |  12493 |  124
|*  3 |    INDEX RANGE SCAN           | IDX_거래_0|      1 |   2405K|   2353K|00:00:00.01 |    8137 |      0 |   
|   4 |    TABLE ACCESS FULL          | 고객      |      1 |   5002K|   5000K|00:00:00.01 |   15789 |      0 |     
|   5 |   SORT AGGREGATE              |           |   1876K|      1 |   1876K|00:22:02.49 |     152M|      0 |      0 |       
|   6 |    TABLE ACCESS BY INDEX ROWID| 거래      |   1876K|      1 |   2353K|00:20:32.22 |     152M|      0 |     
|*  7 |     INDEX SKIP SCAN           | IDX_거래_0|   1876K|      1 |   2353K|00:20:25.57 |     149M|      0 |      
----------------------------------------------------------------------------------------------------

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

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------

   2 - access("고객번호"="C"."고객번호")
   3 - access("거래일시">=TRUNC(ADD_MONTHS(SYSDATE@!,-1)) AND "거래일시" IS NOT NULL)
   7 - access("거래일시">=TRUNC(ADD_MONTHS(SYSDATE@!,-1)) AND "고객번호"=:B1 AND "거래일시" IS NOT N
       filter("고객번호"=:B1)
한달 이내 거래를 발생시킨 고객킨 고객이 많아 update 발생량이 많다면 아래와 같이 변경하는 것을 고려할 수 있다.
하지만 모든 고객 레코드에 lock이 발생함은 물론, 이전관 같은 값은로 갱신되는 비중이 높을수로 Redo 로그 발생량이 증가해 오히려
비효율적일 수 있다.
위 쿼리 처리량은 : 1876184 행 (토탈 고객 : 500만명)
SQL> UPDATE /*+ GATHER_PLAN_STATISTICS ) */고객 C  
  2     SET (최종거래일시, 최근거래횟수, 최근거래금액) = 
  3     (SELECT /*+ INDEX(거래 IDX_거래_01)*/
  4            NVL( MAX(거래일시), C.최종거래일시)
  5    , DECODE( COUNT(*), 0, C.최근거래횟수, COUNT(*) )
  6    , NVL( SUM(거래금액), C.최근거래금액)
  7        FROM 거래                                                         
  8       WHERE 고객번호 = C.고객번호                                        
  9         AND 거래일시 >= TRUNC(ADD_MONTHS(SYSDATE, -1)) )   
 10  ;

5000000 행이 갱신되었습니다.

SQL> select * from table( dbms_xplan.display_cursor( null, null, 'ALLSTATS LAST'));

Plan hash value: 58125775

----------------------------------------------------------------------------------------------------
| Id  | Operation                     | Name      | Starts | E-Rows | A-Rows |   A-Time   | Buffers |

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------------------
|   1 |  UPDATE                       | 고객      |      1 |        |      0 |01:00:18.50 |     412M|
|   2 |   TABLE ACCESS FULL           | 고객      |      1 |   4977K|   5000K|00:00:05.00 |   15789 |
|   3 |   SORT AGGREGATE              |           |   5000K|      1 |   5000K|01:00:04.31 |     401M|
|   4 |    TABLE ACCESS BY INDEX ROWID| 거래      |   5000K|      1 |   2351K|00:58:23.29 |     401M|
|*  5 |     INDEX SKIP SCAN           | IDX_거래_0|   5000K|      1 |   2351K|00:58:10.77 |     399M|
----------------------------------------------------------------------------------------------------

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


PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
   5 - access("거래일시">=TRUNC(ADD_MONTHS(SYSDATE@!,-1)) AND "고객번호"=:B1 AND "거래일시" IS NOT N
       filter("고객번호"=:B1)

수정 가능 조인 뷰

  • 조인 뷰 : from절에 두 개 이상 테이블을 가진 뷰를 가리키며..
  • 수정 가능 조인 뷰 : 말 그대로 입력, 수정, 삭제가 허용되는 조인 뷰를 말한다.( 단. 한쪽 집합과 조인되는 M집합에만 입력, 수정, 삭제가 허용된다.)
고객 100만건, 거래 1000만건 다시 준비.. (속도 및 맨첨에 한쿼리랑 비교 할려고.)

--준비
SQL> DROP TABLE 고객 PURGE;
SQL> DROP TABLE 거래 PURGE;
--100만
SQL> CREATE TABLE 고객 AS
  2  SELECT LEVEL AS 고객번호
  3       , SYSDATE - 365 AS 최종거래일시
  4       , 0 AS 최근거래횟수
  5       , 0 AS 최근거래금액
  6    FROM DUAL
  7  CONNECT BY LEVEL <= 1000000;

SQL> ALTER TABLE 고객 ADD CONSTRAINT IDX_고객_PK PRIMARY KEY(고객번호);
-- 1000만
SQL> CREATE TABLE 거래 AS
  2  SELECT CEIL(LEVEL / 1000000) 고객번호
  3       , ADD_MONTHS(SYSDATE,-4) + FLOOR( DBMS_RANDOM.VALUE(1,120) ) AS 거래일시
  4       , (FLOOR( DBMS_RANDOM.VALUE(1,13) )*100) + 500 AS 거래금액
  5    FROM DUAL 
  6  CONNECT BY LEVEL <= 10000000;

SQL> CREATE INDEX IDX_거래_01 ON 거래(거래일시, 고객번호);
SQL> CREATE INDEX IDX_거래_02 ON 거래(고객번호, 거래일시);

SQL> EXEC DBMS_STATS.GATHER_TABLE_STATS(USER, '고객');
SQL> EXEC DBMS_STATS.GATHER_TABLE_STATS(USER,'거래');

--잠시 딴짓
SQL> SELECT /*+ GATHER_PLAN_STATISTICS ORDERED USE_HASH(C)*/
  2         C.최종거래일시, C.최근거래횟수, C.최근거래금액
  3       , T.거래일시, T.거래횟수, T.거래금액
  4    FROM (SELECT /*+ INDEX(A IDX_거래_01 )*/
  5                 고객번호, MAX(거래일시) 거래일시, COUNT(*) 거래횟수, SUM(거래금액) 거래금액
  6            FROM 거래 A
  7           WHERE 거래일시 >= TRUNC( ADD_MONTHS( SYSDATE, -1))
  8           GROUP BY 고객번호
  9         )T
 10       , 고객 C
 11   WHERE C.고객번호 = T.고객번호
 12  ;

최종거래 최근거래횟수 최근거래금액 거래일시   거래횟수   거래금액
-------- ------------ ------------ -------- ---------- ----------
09/09/02            0            0 10/08/29     235045  270258800
09/09/02            0            0 10/08/29     235318  270715000
09/09/02            0            0 10/08/29     235456  270746300
09/09/02            0            0 10/08/29     235166  270655400
09/09/02            0            0 10/08/29     235626  270961700
09/09/02            0            0 10/08/29     234245  269209800
09/09/02            0            0 10/08/29     234885  270176400
09/09/02            0            0 10/08/29     235609  270941700
09/09/02            0            0 10/08/29     235325  270638300
09/09/02            0            0 10/08/29     234872  269879200

10 개의 행이 선택되었습니다.

SQL> select * from table( dbms_xplan.display_cursor( null, null, 'ALLSTATS LAST'));
-----------------------------------------------------------------------------------------------
| Id  | Operation                      | Name      | Starts | E-Rows | A-Rows |   A-Time   | Buffers |  OMem |  1Mem 
----------------------------------------------------------------------------------------------------

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
|*  1 |  HASH JOIN                     |           |      1 |      1 |     10 |00:00:03.38 |     717K|   812K|   812K| 1018K (0)|
|   2 |   VIEW                         |           |      1 |     10 |     10 |00:00:03.38 |     714K|       |       |          |
|   3 |    HASH GROUP BY               |           |      1 |     10 |     10 |00:00:03.38 |     714K|       |       |          |
|   4 |     TABLE ACCESS BY INDEX ROWID| 거래      |      1 |   2474K|   2351K|00:00:02.35 |     714K|       |   
|*  5 |      INDEX RANGE SCAN          | IDX_거래_0|      1 |   2474K|   2351K|00:00:00.01 |    7216 |       |   
|   6 |   TABLE ACCESS FULL            | 고객      |      1 |   1007K|   1000K|00:00:00.01 |    3046 |       |       
----------------------------------------------------------------------------------------------------

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


PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
   1 - access("C"."고객번호"="T"."고객번호")
   5 - access("거래일시">=TRUNC(ADD_MONTHS(SYSDATE@!,-1)) AND "거래일시" IS NOT NULL)
수정 가능 조인 뷰
SQL> UPDATE /*+ GATHER_PLAN_STATISTICS bypass_ujvc */
  2  (
  3  SELECT /*+  ORDERED USE_HASH(C)*/                                   
  4         C.최종거래일시, C.최근거래횟수, C.최근거래금액                                     
  5       , T.거래일시, T.거래횟수, T.거래금액                                                 
  6    FROM (SELECT /*+ INDEX(A IDX_거래_01 )*/                                                
  7                 고객번호, MAX(거래일시) 거래일시, COUNT(*) 거래횟수, SUM(거래금액) 거래금액
  8            FROM 거래 A                                                                     
  9           WHERE 거래일시 >= TRUNC( ADD_MONTHS( SYSDATE, -1))                               
 10           GROUP BY 고객번호                                                                
 11         )T                                                                                 
 12       , 고객 C                                                                             
 13   WHERE C.고객번호 = T.고객번호                                                            
 14   )
 15   SET 최종거래일시 = 거래일시
 16     , 최근거래횟수 = 거래횟수
 17     , 최근거래금액 = 거래금액;

10 행이 갱신되었습니다.

SQL> select * from table( dbms_xplan.display_cursor( null, null, 'ALLSTATS LAST'));

----------------------------------------------------------------------------------------------------
| Id  | Operation                       | Name      | Starts | E-Rows | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | U

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------------------
|   1 |  UPDATE                         | 고객      |      1 |        |      0 |00:00:03.34 |     717K|       |       |          |
|*  2 |   HASH JOIN                     |           |      1 |      1 |     10 |00:00:03.15 |     717K|   812K|   812K| 1027K (0)
|   3 |    VIEW                         |           |      1 |     10 |     10 |00:00:03.15 |     714K|       |       |          |
|   4 |     SORT GROUP BY               |           |      1 |     10 |     10 |00:00:03.15 |     714K| 73728 | 73728 |          |
|   5 |      TABLE ACCESS BY INDEX ROWID| 거래      |      1 |   2474K|   2351K|00:00:02.35 |     714K|   
|*  6 |       INDEX RANGE SCAN          | IDX_거래_0|      1 |   2474K|   2351K|00:00:00.01 |    7216 |       |  
|   7 |    TABLE ACCESS FULL            | 고객      |      1 |   1007K|   1000K|00:00:00.01 |    3045 |       |     
----------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
---------------------------------------------------

   2 - access("C"."고객번호"="T"."고객번호")
   6 - access("거래일시">=TRUNC(ADD_MONTHS(SYSDATE@!,-1)) AND "거래일시" IS NOT NULL)
JOIN VIEW 통한 업데이트
SQL> CONN /AS SYSDBA
SQL> CREATE TABLE EMP AS SELECT * FROM SCOTT.EMP;
SQL> CREATE TABLE DEPT AS SELECT * FROM SCOTT.DEPT;

SQL> CREATE VIEW EMP_DEPT_VIEW AS
  2  SELECT E.ROWID EMP_RID, E.*, D.ROWID DEPT_RID, D.DNAME, D.LOC   
  3    FROM EMP E, DEPT D                                            
  4   WHERE E.DEPTNO = D.DEPTNO;                                     

SQL> SELECT EMPNO, ENAME, JOB, SAL, DEPTNO, DNAME, LOC
  2    FROM EMP_DEPT_VIEW
  3   ORDER BY JOB,DEPTNO;

     EMPNO ENAME      JOB              SAL     DEPTNO DNAME          LOC
---------- ---------- --------- ---------- ---------- -------------- -------------
      7902 FORD       ANALYST         3000         20 RESEARCH       DALLAS
      7788 SCOTT      ANALYST         3000         20 RESEARCH       DALLAS
      7934 MILLER     CLERK           1300         10 ACCOUNTING     NEW YORKS  <--
      7369 SMITH      CLERK            800         20 RESEARCH       DALLAS	<--
      7876 ADAMS      CLERK           1100         20 RESEARCH       DALLAS	<--
      7900 JAMES      SELE            950         30 SALES          CHICAG	<--
      7782 CLARK      MANAGER         2450         10 ACCOUNTING     NEW YORK
      7566 JONES      MANAGER         2975         20 RESEARCH       DALLAS
      7698 BLAKE      MANAGER         2850         30 SALES          CHICAGO
      7839 KING       PRESIDENT       5000         10 ACCOUNTING     NEW YORK
      7654 MARTIN     SALESMAN        1250         30 SALES          CHICAGO

     EMPNO ENAME      JOB              SAL     DEPTNO DNAME          LOC
---------- ---------- --------- ---------- ---------- -------------- -------------
      7844 TURNER     SALESMAN        1500         30 SALES          CHICAGO
      7521 WARD       SALESMAN        1250         30 SALES          CHICAGO
      7499 ALLEN      SALESMAN        1600         30 SALES          CHICAGO

14 개의 행이 선택되었습니다.

SQL> SELECT * FROM DEPT;

    DEPTNO DNAME          LOC
---------- -------------- -------------
        10 ACCOUNTING     NEW YORK
        20 RESEARCH       DALLAS
        30 SALES          CHICAGO
        40 OPERATIONS     BOSTON

UPDATE EMP_DEPT_VIEW SET LOC = 'SEOUL' WHERE JOB = 'CLERK'
                         *
1행에 오류:
ORA-01779: 키-보존된것이 아닌 테이블로 대응한 열을 수정할 수 없습니다

UPDATE EMP_DEPT_VIEW SET COMM = NVL(COMM, 0) +(SAL * 0.1 ) WHERE SAL <= 1500
                         *
1행에 오류:
ORA-01779: 키-보존된것이 아닌 테이블로 대응한 열을 수정할 수 없습니다

DELETE FROM EMP_DEPT_VIEW WHERE JOB = 'CLERK'
            *
1행에 오류:
ORA-01752: 뷰으로 부터 정확하게 하나의 키-보전된 테이블 없이 삭제할 수 없습니다

INSERT INTO EMP_DEPT_VIEW (DEPTNO, DNAME, LOC) VALUES(50,'TEST','TEST')
                           *
1행에 오류:
ORA-01779: 키-보존된것이 아닌 테이블로 대응한 열을 수정할 수 없습니다
  • DEPT 테이블에 UNIQUE 이덱스를 생성하지 않았기 때문에 생긴 에러이다.
  • 옵티마이저 입장에서 어느 쪽이 1집합인지 알 수 없었던 것이고 지금 상태에서 DELETE 문장도 허용 되지 않는다.(INSERT ?? =_=)
SQL> ALTER TABLE DEPT ADD CONSTRAINT DEPT_PK PRIMARY KEY(DEPTNO);

테이블이 변경되었습니다.

SQL> UPDATE EMP_DEPT_VIEW SET COMM = NVL(COMM, 0) +(SAL * 0.1 ) WHERE SAL <= 1500;

7 행이 갱신되었습니다.
  • 위와 같이 PK 제약을 설정하면 EMP 테이블은 '키-보존 테이블(Key-Preserved Table)'이 되고
  • dept 테이블은 '비 키-보존 테이블(Non Key-Preserved Table)'로 남는다.

키 보존 테이블이란.?

  • 키 보존 테이블이란 ? 조인된 결과 집합을 통해서도 중복 값이 없이 Unique하게 식별이 가능한 테이블을 말한다.
  • Unique한 1쪽 집합과 조인되는 테이블이어야 조인된 결과 집합을 통한 식별이 가능하다.
    앞서 생선한 EMP_DEPT_VIEW에서 rowid를 함께 출력해 보자
    SQL> SELECT ROWID, EMP_RID, DEPT_RID, EMPNO, DEPTNO FROM EMP_DEPT_VIEW;
    
    ROWID              EMP_RID            DEPT_RID                EMPNO     DEPTNO
    ------------------ ------------------ ------------------ ---------- ----------
    AAANZcAABAAASJqAAA AAANZcAABAAASJqAAA AAANZdAABAAASJyAAB       7369         20
    AAANZcAABAAASJqAAB AAANZcAABAAASJqAAB AAANZdAABAAASJyAAC       7499         30
    AAANZcAABAAASJqAAC AAANZcAABAAASJqAAC AAANZdAABAAASJyAAC       7521         30
    AAANZcAABAAASJqAAD AAANZcAABAAASJqAAD AAANZdAABAAASJyAAB       7566         20
    AAANZcAABAAASJqAAE AAANZcAABAAASJqAAE AAANZdAABAAASJyAAC       7654         30
    AAANZcAABAAASJqAAF AAANZcAABAAASJqAAF AAANZdAABAAASJyAAC       7698         30
    AAANZcAABAAASJqAAG AAANZcAABAAASJqAAG AAANZdAABAAASJyAAA       7782         10
    AAANZcAABAAASJqAAH AAANZcAABAAASJqAAH AAANZdAABAAASJyAAB       7788         20
    AAANZcAABAAASJqAAI AAANZcAABAAASJqAAI AAANZdAABAAASJyAAA       7839         10
    AAANZcAABAAASJqAAJ AAANZcAABAAASJqAAJ AAANZdAABAAASJyAAC       7844         30
    AAANZcAABAAASJqAAK AAANZcAABAAASJqAAK AAANZdAABAAASJyAAB       7876         20
    
    ROWID              EMP_RID            DEPT_RID                EMPNO     DEPTNO
    ------------------ ------------------ ------------------ ---------- ----------
    AAANZcAABAAASJqAAL AAANZcAABAAASJqAAL AAANZdAABAAASJyAAC       7900         30
    AAANZcAABAAASJqAAM AAANZcAABAAASJqAAM AAANZdAABAAASJyAAB       7902         20
    AAANZcAABAAASJqAAN AAANZcAABAAASJqAAN AAANZdAABAAASJyAAA       7934         10
    
    14 개의 행이 선택되었습니다.
    |
  • DEPT_RID에 중복 값이 나타나고 있다. EMP_RID에는 중복 값이 없으며 뷰의 ROWID와 일차한다.
    단적으로 말해 '키 보존 테이블'이란 뷰에 [ROWID를 제공하는 테이블]을 말한다
    그럼 DEPT PK를 드랍 하고 EMP PK를 생성하면..??
    SQL> ALTER TABLE  DEPT DROP CONSTRAINT DEPT_PK;
    
    SQL> ALTER TABLE EMP ADD CONSTRAINT EMP_PK PRIMARY KEY(EMPNO);
    
    SQL> UPDATE EMP_DEPT_VIEW SET COMM = NVL(COMM, 0) +(SAL * 0.1 ) WHERE SAL <= 1500;
    UPDATE EMP_DEPT_VIEW SET COMM = NVL(COMM, 0) +(SAL * 0.1 ) WHERE SAL <= 1500
    
    SQL> SELECT ROWID, EMP_RID, DEPT_RID, EMPNO, DEPTNO FROM EMP_DEPT_VIEW;
    SELECT ROWID, EMP_RID, DEPT_RID, EMPNO, DEPTNO FROM EMP_DEPT_VIEW
    
    1행에 오류:
    ORA-01445: 키 보존 테이블이 없는 조인 뷰에서 ROWID를 선택할 수 없음
    
    SQL> ALTER TABLE EMP DROP PRIMARY KEY;
    
    SQL> SELECT ROWID, EMP_RID, DEPT_RID, EMPNO, DEPTNO FROM EMP_DEPT_VIEW;
    SELECT ROWID, EMP_RID, DEPT_RID, EMPNO, DEPTNO FROM EMP_DEPT_VIEW
                                                        *
    1행에 오류:
    ORA-01445: 키 보존 테이블이 없는 조인 뷰에서 ROWID를 선택할 수 없음
    

_UPDATABLE_COLUMNS 뷰 참조

아래와 같이 수정 가능 조인 뷰를 통해 Insert를 시도했지만 실패하였다.
SQL> ALTER TABLE DEPT ADD CONSTRAINT DEPT_PK PRIMARY KEY(DEPTNO);

SQL> INSERT INTO EMP_DEPT_VIEW
  2         (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO, LOC)
  3   SELECT EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO, LOC
  4    FROM EMP_DEPT_VIEW;
       (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO, LOC)
                                                             *
2행에 오류:
ORA-01776: 조인 뷰에 의하여 하나 이상의 기본 테이블을 수정할 수 없습니다.
그럼 EMP_PK를 생성하면 LOC에 인서트가 가능한가.??(추론은 EMP TABLE이 키 보존 테이블이라 DEPT에는 인서트가 안될듯..)

-- 테이블을 다시 만든이유는 기존 테이블은 EMP에 인서트를 해서. EMP_PK가 생성이 불가능해서 다시 만듬.
SQL> DROP VIEW EMP_DEPT_VIEW ;
SQL> DROP TABLE EMP PURGE;
SQL> DROP TABLE DEPT PURGE;

SQL> CREATE TABLE EMP AS SELECT * FROM SCOTT.EMP;
SQL> CREATE TABLE DEPT AS SELECT * FROM SCOTT.DEPT;

SQL>  CREATE VIEW EMP_DEPT_VIEW AS
  2      SELECT E.ROWID EMP_RID, E.*, D.ROWID DEPT_RID, D.DNAME, D.LOC   
  3        FROM EMP E, DEPT D                                            
  4       WHERE E.DEPTNO = D.DEPTNO;   

SQL> ALTER TABLE DEPT ADD CONSTRAINT DEPT_PK PRIMARY KEY(DEPTNO);
SQL> ALTER TABLE EMP ADD CONSTRAINT EMP_PK PRIMARY KEY(EMPNO);

SQL>  INSERT INTO EMP_DEPT_VIEW                                       
  2        (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO, LOC) 
  3  SELECT EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO, LOC  
  4   FROM EMP_DEPT_VIEW;                                             
      (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO, LOC)
                                                            *
2행에 오류:
ORA-01776: 조인 뷰에 의하여 하나 이상의 기본 테이블을 수정할 수 없습니다.
참고로 테스트 하다가 수정 가능한 조인 뷰에서 DELETE두 되는군요 =_=
SQL> SELECT * FROM EMP;

     EMPNO ENAME      JOB              MGR HIREDATE        SAL       COMM     DEPTNO
---------- ---------- --------- ---------- -------- ---------- ---------- ----------
      7369 SMITH      CLERK           7902 80/12/17        800                    20
      7499 ALLEN      SALESMAN        7698 81/02/20       1600        300         30
      7521 WARD       SALESMAN        7698 81/02/22       1250        500         30
      7566 JONES      MANAGER         7839 81/04/02       2975                    20
      7654 MARTIN     SALESMAN        7698 81/09/28       1250       1400         30
      7698 BLAKE      MANAGER         7839 81/05/01       2850                    30
      7782 CLARK      MANAGER         7839 81/06/09       2450                    10
      7788 SCOTT      ANALYST         7566 87/04/19       3000                    20
      7839 KING       PRESIDENT            81/11/17       5000                    10
      7844 TURNER     SALESMAN        7698 81/09/08       1500          0         30
      7876 ADAMS      CLERK           7788 87/05/23       1100                    20

     EMPNO ENAME      JOB              MGR HIREDATE        SAL       COMM     DEPTNO
---------- ---------- --------- ---------- -------- ---------- ---------- ----------
      7900 JAMES      CLERK           7698 81/12/03        950                    30
      7902 FORD       ANALYST         7566 81/12/03       3000                    20
      7934 MILLER     CLERK           7782 82/01/23       1300                    10

14 개의 행이 선택되었습니다.

SQL> DELETE
  2  (
  3  SELECT EMPNO
  4    FROM EMP
  5   WHERE EMPNO = 7369
  6  )
  7  ;

1 행이 삭제되었습니다.
실패한 원인을 *_UPDATABLE_COLUMNS 뷰를 통해 쉽게 확인할 수 있다.
SQL> SELECT COLUMN_NAME, INSERTABLE, UPDATABLE, DELETABLE
  2    FROM USER_UPDATABLE_COLUMNS
  3   WHERE TABLE_NAME = 'EMP_DEPT_VIEW'
  4  ;

COLUMN_NAME                    INS UPD DEL
------------------------------ --- --- ---
EMP_RID                        YES YES YES
EMPNO                          YES YES YES
ENAME                          YES YES YES
JOB                            YES YES YES
MGR                            YES YES YES
HIREDATE                       YES YES YES
SAL                            YES YES YES
COMM                           YES YES YES
DEPTNO                         YES YES YES
DEPT_RID                       NO  NO  NO
DNAME                          NO  NO  NO

COLUMN_NAME                    INS UPD DEL
------------------------------ --- --- ---
LOC                            NO  NO  NO
|
  • DEPT_RID, DNAME, LOC 컬럼에는 INSERT, UPDATE, DELETE가 허용되지 않는다고 표시돼있는데,
    모두 '비-키 보존 테이블(Non Key-Preserved Table)'(DEPT)로 부터 온 컬럼이다.
    LOC 컬럼을 빼고 다시 INSERT 해 보면 아래와 같이 정상적으로 처리 된다.
SQL> INSERT INTO EMP_DEPT_VIEW                                     
  2         (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO) 
  3   SELECT EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO 
  4     FROM EMP_DEPT_VIEW;

14 개의 행이 만들어졌습니다.

수정가능 조인 뷰 제약 회피

  • 아래와 같이 부서별 평균 급여를 저장할 컬럼을 DEPT 테이블에 추가하고, EMP테이블에서 집계한 값을 반영하려고 하자 에러가 발생
  • DEPTNO로 GROUP BY한 결과는 UNIQUE하기 때문에 이 집합과 조인되는 DEPT 테이블은 키가 보존됨에도 옵티마이저가 불필요한 제약을 가한 것이다.
    Updatable Join View Check
    SQL> ALTER TABLE DEPT ADD AVG_SAL NUMBER(7,2);
    
    SQL>  UPDATE
      2   (SELECT D.DEPTNO, D.AVG_SAL AS D_AVG_SAL, E.AVG_SAL AS E_AVG_SAL
      3     FROM (SELECT DEPTNO, ROUND(AVG(SAL), 2) AVG_SAL FROM EMP GROUP BY DEPTNO) E
      4        , DEPT D
      5    WHERE D.DEPTNO = E.DEPTNO)
      6      SET D_AVG_SAL = E_AVG_SAL;
        SET D_AVG_SAL = E_AVG_SAL
            *
    6행에 오류:
    ORA-01779: 키-보존된것이 아닌 테이블로 대응한 열을 수정할 수 없습니다
    |
  • BYPASS_UJVC : 옵티마이저에게 Updatable Join View Check를 생략하라는 힌트
    BYPASS_UJVC(Updatable Join View Check를 생략힌트)
    SQL> SELECT D.DEPTNO, D.AVG_SAL AS D_AVG_SAL, E.AVG_SAL AS E_AVG_SAL
      2     FROM (SELECT DEPTNO, ROUND(AVG(SAL), 2) AVG_SAL FROM EMP GROUP BY DEPTNO) E
      3        , DEPT D
      4    WHERE D.DEPTNO = E.DEPTNO;
    
        DEPTNO  D_AVG_SAL  E_AVG_SAL
    ---------- ---------- ----------
            30               1566.67
            20                  2175
            10               2916.67
    
    SQL> UPDATE /*+ GATHER_PLAN_STATISTICS BYPASS_UJVC */                               
      2  (SELECT D.DEPTNO, D.AVG_SAL AS D_AVG_SAL, E.AVG_SAL AS E_AVG_SAL               
      3    FROM (SELECT DEPTNO, ROUND(AVG(SAL), 2) AVG_SAL FROM EMP GROUP BY DEPTNO) E  
      4       , DEPT D                                                                  
      5   WHERE D.DEPTNO = E.DEPTNO)                                                    
      6     SET D_AVG_SAL = E_AVG_SAL;                                                  
    
    3 행이 갱신되었습니다.
    
    SQL> select * from table( dbms_xplan.display_cursor( null, null, 'ALLSTATS LAST'));
    
    ----------------------------------------------------------------------------------------------------
    | Id  | Operation             | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | U
    ----------------------------------------------------------------------------------------------------
    
    PLAN_TABLE_OUTPUT
    ----------------------------------------------------------------------------------------------------
    |   1 |  UPDATE               | DEPT |      1 |        |      0 |00:00:00.01 |       9 |       |       |          |
    |*  2 |   HASH JOIN           |      |      1 |      3 |      3 |00:00:00.01 |       6 |   951K|   951K|  
    |   3 |    VIEW               |      |      1 |      3 |      3 |00:00:00.01 |       3 |       |       |          |
    |   4 |     SORT GROUP BY     |      |      1 |      3 |      3 |00:00:00.01 |       3 | 73728 | 73728
    |   5 |      TABLE ACCESS FULL| EMP  |      1 |     28 |     28 |00:00:00.01 |       3 |       |      
    |   6 |    TABLE ACCESS FULL  | DEPT |      1 |      4 |      4 |00:00:00.01 |       3 |       |      
    ----------------------------------------------------------------------------------------------------
    
    Predicate Information (identified by operation id):
    ---------------------------------------------------
    
    
    PLAN_TABLE_OUTPUT
    ----------------------------------------------------------------------------------------------------
       2 - access("D"."DEPTNO"="E"."DEPTNO")
    
    SQL> SELECT * FROM DEPT;
    
        DEPTNO DNAME          LOC              AVG_SAL
    ---------- -------------- ------------- ----------
            10 ACCOUNTING     NEW YORK         2916.67
            20 RESEARCH       DALLAS              2175
            30 SALES          CHICAGO          1566.67
            40 OPERATIONS     BOSTON
    
    
  • [UPDATE를 위해 참조하는 집합에 중복 레코드가 없을 때만 이 힌트를 사용해야 한다.].... 중복 레코드가 아니면 어떻게 되는데..?? =_=
  • 10g부터는 Merge Into구문에도 활용 가능함.
    중복 레코드가 아니면 어떻게 되는데..?? =_=
    SQL> ALTER TABLE DEPT ADD TEST_SAL NUMBER;
    
    SQL> SELECT D.DEPTNO, D.TEST_SAL, T.SAL
      2    FROM (SELECT 10 DEPTNO
      3               , 100 SAL
      4            FROM DUAL
      5           UNION ALL
      6          SELECT 10 DEPTNO
      7               , 200 SAL
      8            FROM DUAL
      9          )T
     10        , DEPT D
     11    WHERE T.DEPTNO = D.DEPTNO;
    
        DEPTNO   TEST_SAL        SAL
    ---------- ---------- ----------
            10        100        200
            10        100        100
    
    SQL> UPDATE /*+ GATHER_PLAN_STATISTICS BYPASS_UJVC */
      2  (                                               
      3    SELECT D.DEPTNO, D.TEST_SAL, T.SAL            
      4      FROM (SELECT 10 DEPTNO                      
      5                 , 100 SAL                        
      6              FROM DUAL                           
      7             UNION ALL                            
      8            SELECT 10 DEPTNO                      
      9                 , 200 SAL                        
     10              FROM DUAL                           
     11            )T                                    
     12          , DEPT D                                
     13      WHERE T.DEPTNO = D.DEPTNO                   
     14  )                                               
     15    SET TEST_SAL = SAL                            
     16  ;                                               
    
    2 행이 갱신되었습니다.
    
    SQL> select * from table( dbms_xplan.display_cursor( null, null, 'ALLSTATS LAST'));
    
    ----------------------------------------------------------------------------------------------------
    | Id  | Operation           | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-
    
    PLAN_TABLE_OUTPUT
    ----------------------------------------------------------------------------------------------------
    ----------------------------------------------------------------------------------------------------
    |   1 |  UPDATE             | DEPT |      3 |        |      1 |00:00:00.01 |      15 |       |       |          |
    |*  2 |   HASH JOIN         |      |      3 |      2 |      6 |00:00:00.01 |       9 |   968K|   968K|  287K (0)|
    |   3 |    VIEW             |      |      3 |      2 |      6 |00:00:00.01 |       0 |       |       |          |
    |   4 |     UNION-ALL       |      |      3 |        |      6 |00:00:00.01 |       0 |       |       |          |
    |   5 |      FAST DUAL      |      |      3 |      1 |      3 |00:00:00.01 |       0 |       |       |          |
    |   6 |      FAST DUAL      |      |      3 |      1 |      3 |00:00:00.01 |       0 |       |       |          |
    |   7 |    TABLE ACCESS FULL| DEPT |      3 |      4 |      9 |00:00:00.01 |       9 |       |       |          |
    ----------------------------------------------------------------------------------------------------
    
    Predicate Information (identified by operation id):
    
    PLAN_TABLE_OUTPUT
    ----------------------------------------------------------------------------------------------------
    ---------------------------------------------------
    
       2 - access("T"."DEPTNO"="D"."DEPTNO")
    
    SQL> SELECT * FROM DEPT;
    
        DEPTNO DNAME          LOC              AVG_SAL   TEST_SAL
    ---------- -------------- ------------- ---------- ----------
            10 ACCOUNTING     NEW YORK         2916.67        100
            20 RESEARCH       DALLAS              2175
            30 SALES          CHICAGO          1566.67
            40 OPERATIONS     BOSTON
    

(2) Merge문 활용

  • DW에서 가증 흔히 발생하는 오퍼레이션은, 기간계 시스템에서 가져온 신규 트랜잭션 데이터를 반영함으로써 두 시스템 간 테이터를 동기화 시키는 작업이다.
    1. 전일 발생한 변경 테이터를 기간계 시스템으로부터 추출(Extraction)
    SQL> SELECT TO_CHAR(TRUNC(SYSDATE) -1, 'YYYYMMDDHH24MISS'), TO_CHAR( TRUNC( SYSDATE ) -1 / 86400, 'Y
    YYYMMDDHH24MISS') FROM DUAL;
    
    TO_CHAR(TRUNC( TO_CHAR(TRUNC(
    -------------- --------------
    20100901000000 20100901235959
    
    CREATE TABLE CUSTOMER_DELTA
    AS
    SELECT * FROM CUSTOMER
     WHERE MOD_DT BETWEEN TRUNC( SYSDATE)-1 AND TRUNC( SYSDATE)-1/86400;
    
    2. CUSTOMER_DELTA 테이블을 DW 시스템으로 전송(Transportation)
    3. DW 시스템으로 적재(Loading)
    MERGE INTO CUSTOMER T USING CUSTOMER_DELTA S ON (T.CUST_ID = S.CUST_ID)
     WHEN MATCHED THEN UPDATE
      SET T.CUST_ID = S.CUST_ID
        , T.CUST_NM = S.CUST_NM
        , T.EMAIL = S.EMAIL
        , ....
     WHEN NOT MATCHED THEN INSERT
       (CUST_ID, CUST_NM, EMAIL, TEL_NO, REGION, ADDR, REG_DT) VALUES
       (S.CUST_ID, S.CUST_NM, S.EMAIL, S.TEL_NO, S.REGION, S.ADDR, S.REG_DT);
    
  • 기본 Merge문

Optional Clauses

  • 10g부터사용가능 update와 insert를 선택적으로 처리 가능 ....
    준비
    
    --200만건
    SQL>  CREATE TABLE CUSTOMER AS
      2   SELECT LEVEL AS CUST_ID 
      3        , TO_CHAR(LEVEL) AS CUST_NAME
      4        , TO_CHAR(LEVEL)||'@'||DECODE(FLOOR(DBMS_RANDOM.VALUE(1,3)),1,'naver.com',2,'hanmail.net',3,'gmail.com') AS EMAIL
      5     FROM DUAL
      6  CONNECT BY LEVEL <= 2000000;
    
    SQL> ALTER TABLE CUSTOMER ADD CONSTRAINT CUSTOMER_PK PRIMARY KEY(CUST_ID);
    --300만건
    SQL>  CREATE TABLE CUSTOMER_DELTA AS
      2   SELECT LEVEL AS CUST_ID 
      3        , TO_CHAR(LEVEL) AS CUST_NAME
      4        , TO_CHAR(LEVEL)||'@'||DECODE(FLOOR(DBMS_RANDOM.VALUE(1,3)),1,'naver.com',2,'hanmail.net',3,'gmail.com') AS EMAIL
      5     FROM DUAL
      6  CONNECT BY LEVEL <= 3000000;
    
    SQL> ALTER TABLE CUSTOMER_DELTA ADD CONSTRAINT CUSTOMER_DELTA_PK PRIMARY KEY(CUST_ID);
    
    SQL> EXEC DBMS_STATS.GATHER_TABLE_STATS(USER, 'CUSTOMER');
    SQL> EXEC DBMS_STATS.GATHER_TABLE_STATS(USER, 'CUSTOMER_DELTA');
    
    MERGE UPDATE
    SQL> MERGE /*+ GATHER_PLAN_STATISTICS */INTO CUSTOMER T USING CUSTOMER_DELTA S ON(T.CUST_ID = S.CUST_ID)
      2   WHEN MATCHED THEN UPDATE
      3    SET T.CUST_NAME = S.CUST_NAME||S.CUST_NAME;
    
    2000000 행이 병합되었습니다.
    
    SQL> select * from table( dbms_xplan.display_cursor( null, null, 'ALLSTATS LAST'));
    
    ----------------------------------------------------------------------------------------------------
    | Id  | Operation            | Name           | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Reads  | Wri
    ----------------------------------------------------------------------------------------------------
    |   1 |  MERGE               | CUSTOMER       |      1 |        |      1 |00:05:22.65 |    2970K|  47491 |  19375 |
    
    PLAN_TABLE_OUTPUT
    ----------------------------------------------------------------------------------------------------
    |   2 |   VIEW               |                |      1 |        |   2000K|00:00:43.40 |   25420 |  37423 |  19375 |       |       |
    |*  3 |    HASH JOIN         |                |      1 |   1996K|   2000K|00:00:43.40 |   25420 |  37423 |  193
    |   4 |     TABLE ACCESS FULL| CUSTOMER       |      1 |   1996K|   2000K|00:00:10.02 |   10080 |   
    |   5 |     TABLE ACCESS FULL| CUSTOMER_DELTA |      1 |   3000K|   3000K|00:00:15.00 |   15340 |   
    ----------------------------------------------------------------------------------------------------
    
    Predicate Information (identified by operation id):
    ---------------------------------------------------
    
       3 - access("T"."CUST_ID"="S"."CUST_ID")
    
    수정가능한 조인 뷰
     SQL> UPDATE /*+ GATHER_PLAN_STATISTICS */
      2  (
      3  SELECT 
      4        T.CUST_NAME, S.CUST_NAME||S.CUST_NAME AS S_CUST_NAME
      5    FROM CUSTOMER T
      6       , CUSTOMER_DELTA S
      7   WHERE T.CUST_ID = S.CUST_ID
      8  )
      9     SET CUST_NAME = S_CUST_NAME;
    
    2000000 행이 갱신되었습니다.
    
    SQL> select * from table( dbms_xplan.display_cursor( null, null, 'ALLSTATS LAST'));
    
    ----------------------------------------------------------------------------------------------------
    | Id  | Operation           | Name           | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Reads  | Writes
    ----------------------------------------------------------------------------------------------------
    |   1 |  UPDATE             | CUSTOMER       |      1 |        |      0 |00:01:52.58 |    2515K|  27922 |  112
    
    PLAN_TABLE_OUTPUT
    ----------------------------------------------------------------------------------------------------
    |*  2 |   HASH JOIN         |                |      1 |   1996K|   2000K|00:00:05.87 |   26904 |  26582 |  11253 | 
    |   3 |    TABLE ACCESS FULL| CUSTOMER       |      1 |   1996K|   2000K|00:00:00.01 |   11564 |      0
    |   4 |    TABLE ACCESS FULL| CUSTOMER_DELTA |      1 |   3000K|   3000K|00:00:00.01 |   15340 |  1532
    ----------------------------------------------------------------------------------------------------
    
    Predicate Information (identified by operation id):
    ---------------------------------------------------
    
       2 - access("T"."CUST_ID"="S"."CUST_ID")
    
    MERGE INSERT
    SQL> MERGE /*+ GATHER_PLAN_STATISTICS */INTO CUSTOMER T USING CUSTOMER_DELTA S ON(T.CUST_ID = S.CUST_ID)
      2   WHEN NOT MATCHED THEN INSERT
      3        (CUST_ID, CUST_NAME) VALUES
      4        (S.CUST_ID, S.CUST_NAME||S.CUST_NAME);
    
    1000000 행이 병합되었습니다.
    
    SQL>  select * from table( dbms_xplan.display_cursor( null, null, 'ALLSTATS LAST'));
    
    ----------------------------------------------------------------------------------------------------
    | Id  | Operation               | Name           | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Reads  | Writes |  OMem 
    ----------------------------------------------------------------------------------------------------
    |   1 |  MERGE                  | CUSTOMER       |      1 |        |      1 |00:02:27.52 |    4165K|  27627 |  22382 |       |       |
    
    PLAN_TABLE_OUTPUT
    ----------------------------------------------------------------------------------------------------
    |   2 |   VIEW                  |                |      1 |        |   3000K|00:00:10.66 |   26904 |  27627 |  22382 |       |       |          |       
    |*  3 |    HASH JOIN RIGHT OUTER|                |      1 |   3000K|   3000K|00:00:10.66 |   26904 |  27627 |  22382 |
    |   4 |     TABLE ACCESS FULL   | CUSTOMER       |      1 |   1996K|   2000K|00:00:00.01 |   11564 |      0 |      0 
    |   5 |     TABLE ACCESS FULL   | CUSTOMER_DELTA |      1 |   3000K|   3000K|00:00:00.01 |   15340 |   5245
    ----------------------------------------------------------------------------------------------------
    
    Predicate Information (identified by operation id):
    ---------------------------------------------------
    
       3 - access("T"."CUST_ID"="S"."CUST_ID")
    
    NOT EXISTS INSERT
    SQL> ROLLBACK;
    SQL> INSERT /*+ GATHER_PLAN_STATISTICS */INTO CUSTOMER(CUST_ID, CUST_NAME)
      2  SELECT CUST_ID, CUST_NAME||CUST_NAME AS CUST_NAME
      3    FROM CUSTOMER_DELTA T
      4   WHERE NOT EXISTS( SELECT 'X'
      5                       FROM CUSTOMER
      6               WHERE CUST_ID = T.CUST_ID);
    
    1000000 개의 행이 만들어졌습니다.
    
    SQL> select * from table( dbms_xplan.display_cursor( null, null, 'ALLSTATS LAST'));
    
    ----------------------------------------------------------------------------------------------------
    | Id  | Operation             | Name           | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Reads  |  
    ----------------------------------------------------------------------------------------------------
    |*  1 |  HASH JOIN RIGHT ANTI |                |      1 |   1004K|   1000K|00:00:05.11 |   23240 |  15329 |
    
    PLAN_TABLE_OUTPUT
    ----------------------------------------------------------------------------------------------------
    |   2 |   INDEX FAST FULL SCAN| CUSTOMER_PK    |      1 |   1996K|   2000K|00:00:00.01 |    7900 |      0
    |   3 |   TABLE ACCESS FULL   | CUSTOMER_DELTA |      1 |   3000K|   3000K|00:00:00.01 |   15340 |  
    ----------------------------------------------------------------------------------------------------
    
    Predicate Information (identified by operation id):
    ---------------------------------------------------
    
       1 - access("CUST_ID"="T"."CUST_ID")
    
    
    그럼 이 기능이 왜있는가?? Optional Clauses Updatable Join View기능을 대체 할 수 있게되었다.(bypass_ujvc힌트가 필요할때 아래와같이 풀수있다.)
    SQL> MERGE /*+ GATHER_PLAN_STATISTICS */INTO DEPT D
      2  USING (SELECT DEPTNO, ROUND(AVG(SAL), 2) AVG_SAL FROM EMP GROUP BY DEPTNO) E
      3     ON (D.DEPTNO = E.DEPTNO)
      4   WHEN MATCHED THEN UPDATE SET D.AVG_SAL = E.AVG_SAL;
    
    3 행이 병합되었습니다.
    
    SQL> select * from table( dbms_xplan.display_cursor( null, null, 'ALLSTATS LAST'));
    
    ----------------------------------------------------------------------------------------------------
    | Id  | Operation              | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Reads  |  OMem |
    ----------------------------------------------------------------------------------------------------
    |   1 |  MERGE                 | DEPT |      1 |        |      1 |00:00:00.01 |       9 |      2 |       |       |          |
    
    PLAN_TABLE_OUTPUT
    ----------------------------------------------------------------------------------------------------
    |   2 |   VIEW                 |      |      1 |        |      3 |00:00:00.01 |       6 |      2 |       |       |          |
    |*  3 |    HASH JOIN           |      |      1 |      3 |      3 |00:00:00.01 |       6 |      2 |   95
    |   4 |     VIEW               |      |      1 |      3 |      3 |00:00:00.01 |       3 |      2 |       |       |        
    |   5 |      SORT GROUP BY     |      |      1 |      3 |      3 |00:00:00.01 |       3 |      2 | 7
    |   6 |       TABLE ACCESS FULL| EMP  |      1 |     28 |     28 |00:00:00.01 |       3 |      2 |       
    |   7 |     TABLE ACCESS FULL  | DEPT |      1 |      4 |      4 |00:00:00.01 |       3 |      0 |       
    ----------------------------------------------------------------------------------------------------
    
    Predicate Information (identified by operation id):
    ---------------------------------------------------
    
    
    PLAN_TABLE_OUTPUT
    ----------------------------------------------------------------------------------------------------
       3 - access("D"."DEPTNO"="E"."DEPTNO")
    
    |

Conditional Operations

  • 10g에서는 on절에 기술한 조인문 외에 아래와 같이 추가로 조건절을 기술 할 수있다.
    준비
    MERGE INTO CUSTOMER T USING CUSTOMER_DELTA S ON (T.CUST_ID = S.CUST_ID)
     WHEN MATCHED THEN UPDATE
      SET T.CUST_ID = S.CUST_ID, T.CUST_NM = S.CUST_NM, T.EMAIL = S.EMAIL,....
    WHERE REG_DT >= TO_DATE('20100101','YYYYMMDD')
     WHEN NOT MATCHED THEN INSERT
      (CUST_ID, CUST_NM, EMAIL, TEL_NO, REGION, ADDR, REG_DT) VALUES
      (S.CUST_ID, S.CUST_NM, S.EMAIL, S.TEL_NO, S.REGION, S.ADDR, S.REG_DT)
    WHERE REG_DT < TRUNC(SYSDATE);
    

Delete Clause

  • 10g에서 제공되기 시작한 또 한가지 확장 기능은, 이미 저장된 테이터를 조건에 따라 지우는 것이다.? (=_=)
    
    -- 이거 테스트 해봐야하는데요.. 시간이 없어서 =_=
    MERGE INTO CUSTOMER T USING CUSTOMER_DELTA S ON (T.CUST_ID = S.CUST_ID)
     WHEN MATCHED THEN
      UPDATE SET T.CUST_ID = S.CUST_ID
               , T.CUST_NM = S.CUST_NM
    	   ... 
      DELETE WHERE T.WITHDRAW_DT IS NOT NULL --탈퇴일시가 null이 아닌 레코드 삭제
     WHEN NO MATCHED THEN INSERT
      ....
    
  • 탈퇴일시가 NULL이 아니었어도 Merge문을 수행한 결과가 Null이면 삭제된다.
  • 그럼 DELETE -> INSERT -> DELETE 이런 식으로 되는건가요?? =_=

Merge Into 활용 - 1

항상 두번씩 실행
SELECT COUNT(*) INTO : CNT FROM DEPT HWERE DEPTNO = :VAL;
IF :CNT = 0 THEN
INSERT INTO DEPT(DEPTNO, DNAME, LOC) VALUES(:VAL1, :VAL2, :VAL3);
END IF;
최대 두번 실행
UPDATE DEPT SET DNAME = :VAL2, LOC = :VAL3 WHERE DEPTNO = :VAL1;

IF(SQL%ROWCOUNT = 0 THEN
INSERT INTO DEPT(DEPTNO, DNAME, LOC)VALUES(:VAL1, :VAL2, :VAL3)
END IF;
한번 만 실행
SQL> SELECT * FROM DEPT;

    DEPTNO DNAME          LOC              AVG_SAL   TEST_SAL
---------- -------------- ------------- ---------- ----------
        10 ACCOUNTING     NEW YORK         2916.67
        20 RESEARCH       DALLAS              2175
        30 SALES          CHICAGO          1566.67
        40 OPERATIONS     BOSTON

SQL> MERGE /*+ GATHER_PLAN_STATISTICS */ INTO DEPT A
  2  USING (SELECT 50 DEPTNO, '열공' DNAME, '수색역' LOC FROM DUAL) B
  3     ON (B.DEPTNO = A.DEPTNO)
  4   WHEN MATCHED THEN
  5     UPDATE SET DNAME = B.DNAME, LOC = B.LOC
  6   WHEN NOT MATCHED THEN
  7     INSERT (A.DEPTNO, A.DNAME, A.LOC) VALUES (B.DEPTNO, B.DNAME, B.LOC);

1 행이 병합되었습니다.

SQL> select * from table( dbms_xplan.display_cursor( null, null, 'ALLSTATS LAST'));

----------------------------------------------------------------------------------------------------
| Id  | Operation                      | Name    | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Reads  |
----------------------------------------------------------------------------------------------------

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
|   1 |  MERGE                         | DEPT    |      1 |        |      2 |00:00:00.03 |      12 |      1 |
|   2 |   VIEW                         |         |      1 |        |      1 |00:00:00.01 |       1 |      1 |
|   3 |    NESTED LOOPS OUTER          |         |      1 |      1 |      1 |00:00:00.01 |       1 |      1 |
|   4 |     FAST DUAL                  |         |      1 |      1 |      1 |00:00:00.01 |       0 |      0 |
|   5 |     TABLE ACCESS BY INDEX ROWID| DEPT    |      1 |      1 |      0 |00:00:00.01 |       1 |      1 |
|*  6 |      INDEX UNIQUE SCAN         | DEPT_PK |      1 |      1 |      0 |00:00:00.01 |       1 |      1 |
----------------------------------------------------------------------------------------------------

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


PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
   6 - access("A"."DEPTNO"=50)

Merge Into 활용 - 2

준비 1
SQL> CREATE TABLE 청구일반내역(일련번호,청구년월, 청구생성번호,  청구일련번호, 보류구분, 의료급여종별구분)AS
  2  SELECT   LEVEL AS 일련번호
  3       , '2010'||TO_CHAR(CEIL(ROWNUM / 100000),'FM09') AS 청구년월
  4       , 'A'||TO_CHAR(FLOOR(DBMS_RANDOM.VALUE(1,10)),'FM09') AS 청구생성번호
  5       , 0 AS 청구일련번호
  6       , DECODE(FLOOR(DBMS_RANDOM.VALUE(1,10)), 1, 'Y','N') AS 보류구분 --Y:보류
  7       , CHR(FLOOR(DBMS_RANDOM.VALUE(1,10))+ 64) AS 의료급여종별구분 -- A ~ G
  8    FROM DUAL
  9  CONNECT BY LEVEL <= 1200000;

SQL> ALTER TABLE 청구일반내역 ADD CONSTRAINT  PK_청구일반내역 PRIMARY KEY (청구년월, 청구생성번호,일련번호);

SQL> CREATE INDEX IDX_01_청구일반내역 ON 청구일반내역(청구년월, 청구생성번호, 청구일련번호);

SQL> EXEC DBMS_STATS.GATHER_TABLE_STATS(USER,'청구일반내역');

준비 2
SQL> SELECT /*+ GATHER_PLAN_STATISTICS */COUNT(*)
  2    FROM 청구일반내역 C
  3   WHERE C.청구년월 = '201001'
  4     AND C.청구생성번호 = 'A01'
  5     AND C.보류구분 = 'N';

  COUNT(*)
----------
      9881

SQL> select * from table( dbms_xplan.display_cursor( null, null, 'ALLSTATS LAST'));

----------------------------------------------------------------------------------------------------
| Id  | Operation                    | Name          | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
----------------------------------------------------------------------------------------------------
|   1 |  SORT AGGREGATE              |               |      1 |      1 |      1 |00:00:00.02 |     412 |

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
|*  2 |   TABLE ACCESS BY INDEX ROWID| 청구일반내역  |      1 |   5568 |   9881 |00:00:00.02 |     412
|*  3 |    INDEX RANGE SCAN          | IDX_01_청구일 |      1 |  11137 |  11121 |00:00:00.01 |      40 |
----------------------------------------------------------------------------------------------------

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

   2 - filter("C"."보류구분"='N')
   3 - access("C"."청구년월"='201001' AND "C"."청구생성번호"='A01')
UPDATE
SQL> UPDATE  /*+ GATHER_PLAN_STATISTICS */청구일반내역 A
  2     SET A.청구일련번호 = (
  3     SELECT RNUM
  4       FROM (SELECT 청구년월, 청구생성번호, 일련번호, ROWNUM RNUM
  5               FROM (SELECT /*+ INDEX(C PK_청구일반내역) */청구년월, 청구생성번호, 일련번호,C.의료급여종별구분
  6                       FROM 청구일반내역 C
  7                       WHERE C.청구년월 = '201001'
  8                         AND C.청구생성번호 = 'A01'
  9                         AND C.보류구분 = 'N'
 10                       ORDER BY C.의료급여종별구분
 11                      )
 12             )B
 13      WHERE B.청구년월 = A.청구년월
 14        AND B.청구생성번호 =  A.청구생성번호
 15        AND B.일련번호 =  A.일련번호)
 16   WHERE A.청구년월 = '201001'
 17     AND A.청구생성번호 = 'A01'
 18     AND A.보류구분 = 'N';

9881 행이 갱신되었습니다.

SQL> select * from table( dbms_xplan.display_cursor( null, null, 'ALLSTATS LAST'));

----------------------------------------------------------------------------------------------------
| Id  | Operation                        | Name          | Starts | E-Rows | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-
----------------------------------------------------------------------------------------------------
|   1 |  UPDATE                          | 청구일반내역  |      1 |        |      0 |00:01:40.38 |    4123K|       |       |          |
|*  2 |   TABLE ACCESS BY INDEX ROWID    | 청구일반내역  |      1 |   5568 |   9881 |00:00:00.03 |     412 
|*  3 |    INDEX RANGE SCAN              | IDX_01_청구일 |      1 |  11137 |  11121 |00:00:00.01 |      40 |       |       |      
|*  4 |   VIEW                           |               |   9881 |   5568 |   9881 |00:02:25.27 |    4100K|       |       |          |
|   5 |    COUNT                         |               |   9881 |        |     97M|00:01:30.59 |    4100K|       |       |          |
|   6 |     VIEW                         |               |   9881 |   5568 |     97M|00:01:30.58 |    4100K|       |       |          |
|   7 |      SORT ORDER BY               |               |   9881 |   5568 |     97M|00:01:30.57 |    4100K|   549K|   457K|  4
|*  8 |       TABLE ACCESS BY INDEX ROWID| 청구일반내역  |   9881 |   5568 |     97M|00:00:00.14 |    4
|*  9 |        INDEX RANGE SCAN          | PK_청구일반내 |   9881 |  11137 |    109M|00:00:00.10 |     424K|     

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

   2 - filter("A"."보류구분"='N')
   3 - access("A"."청구년월"='201001' AND "A"."청구생성번호"='A01')
   4 - filter(("B"."일련번호"=:B1 AND "B"."청구생성번호"=:B2 AND "B"."청구년월"=:B3))
   8 - filter("C"."보류구분"='N')
   9 - access("C"."청구년월"='201001' AND "C"."청구생성번호"='A01')

--AUTOTRACE
Execution Plan
----------------------------------------------------------
Plan hash value: 1806610301

--------------------------------------------------------------------------------------------------
| Id  | Operation                        | Name          | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------------
|   0 | UPDATE STATEMENT                 |               |  5568 |   114K|   418   (1)| 00:00:06 |
|   1 |  UPDATE                          | 청구일반내역  |       |       |            |          |
|*  2 |   TABLE ACCESS BY INDEX ROWID    | 청구일반내역  |  5568 |   114K|   418   (1)| 00:00:06 |
|*  3 |    INDEX RANGE SCAN              | IDX_01_청구일 | 11137 |       |    40   (0)| 00:00:01 |
|*  4 |   VIEW                           |               |  5568 |   190K|   423   (1)| 00:00:06 |
|   5 |    COUNT                         |               |       |       |            |          |
|   6 |     VIEW                         |               |  5568 |   119K|   423   (1)| 00:00:06 |
|   7 |      SORT ORDER BY               |               |  5568 |   114K|   423   (1)| 00:00:06 |
|*  8 |       TABLE ACCESS BY INDEX ROWID| 청구일반내역  |  5568 |   114K|   422   (1)| 00:00:06 |
|*  9 |        INDEX RANGE SCAN          | PK_청구일반내 | 11137 |       |    43   (0)| 00:00:01 |
--------------------------------------------------------------------------------------------------

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

   2 - filter("A"."보류구분"='N')
   3 - access("A"."청구년월"='201001' AND "A"."청구생성번호"='A01')
   4 - filter("B"."일련번호"=:B1 AND "B"."청구생성번호"=:B2 AND "B"."청구년월"=:B3)
   8 - filter("C"."보류구분"='N')
   9 - access("C"."청구년월"='201001' AND "C"."청구생성번호"='A01')


Statistics
----------------------------------------------------------
          1  recursive calls
      19972  db block gets
    4104329  consistent gets
          0  physical reads
    2380024  redo size
        692  bytes sent via SQL*Net to client
       1276  bytes received via SQL*Net from client
          3  SQL*Net roundtrips to/from client
       9883  sorts (memory)
          0  sorts (disk)
       9881  rows processed

--10043 trace
UPDATE  /*+ GATHER_PLAN_STATISTICS */청구일반내역 A
   SET A.청구일련번호 = (
   SELECT RNUM
     FROM (SELECT 청구년월, 청구생성번호, 일련번호, ROWNUM RNUM
             FROM (SELECT /*+ INDEX(C PK_청구일반내역) */청구년월, 청구생성번호, 일련번호,C.의료급여종별구분
                     FROM 청구일반내역 C
                     WHERE C.청구년월 = '201001'
                       AND C.청구생성번호 = 'A01'
                       AND C.보류구분 = 'N'
                     ORDER BY C.의료급여종별구분
                    )
           )B
    WHERE B.청구년월 = A.청구년월
      AND B.청구생성번호 =  A.청구생성번호
      AND B.일련번호 =  A.일련번호)
 WHERE A.청구년월 = '201001'
   AND A.청구생성번호 = 'A01'
   AND A.보류구분 = 'N'

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      1    150.29     155.10          0    4104330      19985        9881
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        2    150.29     155.10          0    4104330      19985        9881

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: SYS

Rows     Row Source Operation
-------  ---------------------------------------------------
      0  UPDATE  청구일반내역 (cr=4104334 pr=0 pw=0 time=155098240 us)
   9881   TABLE ACCESS BY INDEX ROWID 청구일반내역 (cr=3714 pr=0 pw=0 time=51325 us)
  11121    INDEX RANGE SCAN IDX_01_청구일반내역 (cr=102 pr=0 pw=0 time=11155 us)(object id 54969)
   9881   VIEW  (cr=4100615 pr=0 pw=0 time=149457935 us)
97634161    COUNT  (cr=4100615 pr=0 pw=0 time=96475752 us)
97634161     VIEW  (cr=4100615 pr=0 pw=0 time=96459832 us)
97634161      SORT ORDER BY (cr=4100615 pr=0 pw=0 time=96450318 us)
97634161       TABLE ACCESS BY INDEX ROWID 청구일반내역 (cr=4100615 pr=0 pw=0 time=149576 us)
109886601        INDEX RANGE SCAN PK_청구일반내역 (cr=424883 pr=0 pw=0 time=108680 us)(object id 54968)

********************************************************************************
Merge Into
SQL> ROLLBACK;
SQL> MERGE /*+ GATHER_PLAN_STATISTICS */INTO 청구일반내역 A
  2  USING (SELECT 청구년월, 청구생성번호, 일련번호
  3              , ROW_NUMBER() OVER(ORDER BY C.의료급여종별구분) 청구일련번호
  4           FROM 청구일반내역 C
  5          WHERE C.청구년월 = '201001'
  6            AND C.청구생성번호 = 'A01'
  7            AND C.보류구분 = 'N')B
  8     ON (B.청구년월 = A.청구년월 AND
  9         B.청구생성번호 = B.청구생성번호 AND
 10         B.일련번호 = A.일련번호)
 11   WHEN MATCHED THEN UPDATE
 12    SET A.청구일련번호 = B.청구일련번호  ;

9881 행이 병합되었습니다.

SQL> select * from table( dbms_xplan.display_cursor( null, null, 'ALLSTATS LAST'));

----------------------------------------------------------------------------------------------------
| Id  | Operation                        | Name          | Starts | E-Rows | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------------------
|   1 |  MERGE                           | 청구일반내역  |      1 |        |      1 |00:00:05.54 |   18336 |       |       |          |
|   2 |   VIEW                           |               |      1 |        |   9881 |00:00:00.16 |    8228 |       |       |          |
|*  3 |    HASH JOIN                     |               |      1 |   5568 |   9881 |00:00:00.15 |    8228 |  1031K|  1031K| 1377K (0)
|   4 |     VIEW                         |               |      1 |   5568 |   9881 |00:00:00.06 |    3714 |       |       |          |
|   5 |      WINDOW SORT                 |               |      1 |   5568 |   9881 |00:00:00.05 |    3714 |   549K|   457K|  487K 
|*  6 |       TABLE ACCESS BY INDEX ROWID| 청구일반내역  |      1 |   5568 |   9881 |00:00:00.04 |    371
|*  7 |        INDEX RANGE SCAN          | IDX_01_청구일 |      1 |  11137 |  11121 |00:00:00.01 |     102 |       
|   8 |     TABLE ACCESS FULL            | 청구일반내역  |      1 |   1202K|   1200K|00:00:00.01 |    4514 |       |    
----------------------------------------------------------------------------------------------------


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

   3 - access("B"."청구년월"="A"."청구년월" AND "B"."일련번호"="A"."일련번호")
   6 - filter("C"."보류구분"='N')
   7 - access("C"."청구년월"='201001' AND "C"."청구생성번호"='A01')

--10046 TRACE
MERGE INTO 청구일반내역 A
USING (SELECT 청구년월, 청구생성번호, 일련번호
            , ROW_NUMBER() OVER(ORDER BY C.의료급여종별구분) 청구일련번호
         FROM 청구일반내역 C
        WHERE C.청구년월 = '201001'
          AND C.청구생성번호 = 'A01'
          AND C.보류구분 = 'N')B
   ON (B.청구년월 = A.청구년월 AND
       B.청구생성번호 = B.청구생성번호 AND
       B.일련번호 = A.일련번호)
 WHEN MATCHED THEN UPDATE
  SET A.청구일련번호 = B.청구일련번호  

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.02          0          2          0           0
Execute      1      3.26       7.30          0       5003      70022        9881
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        2      3.26       7.32          0       5005      70022        9881

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: SYS

Rows     Row Source Operation
-------  ---------------------------------------------------
      1  MERGE  청구일반내역 (cr=5063 pr=0 pw=0 time=7270734 us)
   9881   VIEW  (cr=4988 pr=0 pw=0 time=161679 us)
   9881    HASH JOIN  (cr=4988 pr=0 pw=0 time=151796 us)
   9881     VIEW  (cr=474 pr=0 pw=0 time=69563 us)
   9881      WINDOW SORT (cr=474 pr=0 pw=0 time=59680 us)
   9881       TABLE ACCESS BY INDEX ROWID 청구일반내역 (cr=474 pr=0 pw=0 time=29705 us)
  11121        INDEX RANGE SCAN IDX_01_청구일반내역 (cr=102 pr=0 pw=0 time=11164 us)(object id 54969)
1200000     TABLE ACCESS FULL 청구일반내역 (cr=4514 pr=0 pw=0 time=37 us)

다중 테이블 Insert 활용

  • 오라클 9i부터 제공
  • 기본 사용법은 오라클 메뉴얼 참조.
    야간 배치 프로그램
    
    --Query 1
    INSERT INTO 청구보험당사자( 당사자ID, 접수일자,접수순번, 담보구분, 청구수번, ...)
    SELECT ...
     FROM 청구보험당사자_임시 A, 거래당사자 B
    WHERE A.당사자ID = B.당사자ID
    --Query 2
    INSERT INTO 자동차사고접수당사자( 당사자ID, 접수일자,접수순번, 담보구분, 청구수번, ...)
    SELECT ...
     FROM 가사고접수당사자_임시 A, 거래당사자 B
    WHERE B.당사자구분 NOT IN('4', '5', '6')
      AND A.당사자ID = B.당사자ID
    
  • '청구보험당사자_임시'와 '가사고접수당사자_임시'는 10만 건 미만이지만 '거래당사자'는 수천만건에 이르는 대용량 테이블이라고 하자.
  • 그럴때 아래와 같이 다중 테이블 Insert문을 활용하면 대용량 거래당사자 테이블을 한번만 읽고 처리할수있다.
    다중 테이블 Insert문
    INSERT FIRST
      WHEN 구분 = 'A' THEN
      INTO 청구보험당사자(당사자ID, 접수일자, 접수순번, 담보구분, 청구순번....)
     VALUES (당사자ID, 접수일자, 접수순번, 담보구분, 청구순번...)
      WHEN 구분 = 'B' THEN
      INTO 자동차사고접수당사자( 당사자ID, 접수일자,접수순번, 담보구분, 청구수번, ...) 
      VALUES (당사자ID, 접수일자, 접수순번, 담보구분, 청구순번...)
     SELECT A.구분, A.당사자ID, A.접수일자, A.접수순번, A.담보구분, A.청구순번, .....
       FROM (
          SELECT 'A' 구분
            FROM 청구보험당사자_임시
           UNION AL
          SELECT 'B' 구분
            FROM 가사고접수당사자_임시
           WHERE 당사자구분 NOT IN ('4', '5', '6')
           )A. 거래당사자 B
      WHERE A.당사자ID = B.당사자ID;
    

ALTER TABLE EMP ADD CONSTRAINT EMP_PK PRIMARY KEY(EMPNO);

ALTER TABLE EMP DROP CONSTRAIN EMP_PK PRIMARY KEY

ALTER TABLE EMP DROP CONSTRAINT EMP_PK;

DELETE
(
SELECT EMPNO
FROM EMP
WHERE EMPNO = 7369
)

CREATE VIEW EMP_DEPT_VIEW AS
SELECT E.ROWID EMP_RID, E.*, D.ROWID DEPT_RID, D.DNAME, D.LOC
FROM EMP E, DEPT D
WHERE E.DEPTNO = D.DEPTNO;

SELECT EMPNO, ENAME, JOB, SAL, DEPTNO, DNAME, LOC
FROM EMP_DEPT_VIEW
ORDER BY JOB,DEPTNO;

SQL> INSERT INTO EMP_DEPT_VIEW
2 (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO, LOC)
3 SELECT EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO, LOC
4 FROM EMP_DEPT_VIEW;
(EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO, LOC)

문서정보

Enter labels to add to this page:
Please wait 
Looking for a label? Just start typing.