1. HOME
  2. [종료]대용량 데이터베이스 스터디
  3. 2010년 하반기 대용량데이터베이스 스터디
  4. 7. 조인을 내포한 DML 튜닝

7. 조인을 내포한 DML 튜닝 (by ljh1148) [2010.09.01]

07 조인을 내포한 DML 튜닝

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

TEST 준비 스크립트
{CODE:SQL}

--고객 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,'거래')

{CODE}(딴애기) IDX_거래_01 =_=
{CODE:SQL}
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'));

-

IdOperationNameStartsE-RowsA-RowsA-TimeBuffersReads

-

1SORT AGGREGATE11100:00:00.99775517

PLAN_TABLE_OUTPUT

  • 2
INDEX SKIP SCANIDX_거래_01243K235K00:00:00.48775517

-

Predicate Information (identified by operation id):

---

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

{CODE}(딴애기) IDX_거래_02
{CODE:SQL}
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'));

IdOperationNameStartsE-RowsA-RowsA-TimeBuffersReads

1SORT AGGREGATE11100:00:00.0532

PLAN_TABLE_OUTPUT

2FIRST ROW1243K100:00:00.0532
  • 3
INDEX RANGE SCAN (MIN/MAX)IDX_거래_01243K100:00:00.0532

Predicate Information (identified by operation id):

---

3 - access("고객번호"=1 AND "거래일시" IS NOT NULL AND "거래일시">=TRUNC(ADD_MONTHS(SYSDATE@!,-1)

{CODE}전통적인 방식의 UPDATE : IDX_거래_02 ( CREATE INDEX IDX_거래_02 ON 거래(고객번호, 거래일시)
{CODE:SQL}
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


IdOperationNameStartsE-RowsA-RowsA-TimeBuffersOMem1Mem

1UPDATE고객1000:00:09.34755K
  • 2
HASH JOIN SEMI111000:00:06.583372247M4218K61M (0)3TABLE ACCESS FULL고객1992K1000K00:00:00.013045
  • 4
INDEX FULL SCANIDX_거래_012438K2351K00:00:56.8830677
5SORT AGGREGATE1011000:00:00.0122
6FIRST ROW10243K1000:00:00.0122
  • 7
INDEX RANGE SCAN (MIN/MAX)IDX_거래_010243K1000:00:00.0122
8SORT AGGREGATE1011000:00:00.427235

PLAN_TABLE_OUTPUT

  • 9
INDEX RANGE SCANIDX_거래_010243K2351K00:00:00.017235
10SORT AGGREGATE1011000:00:02.21714K
11TABLE ACCESS BY INDEX ROWID거래10243K2351K00:00:02.35714K
  • 12
INDEX RANGE SCANIDX_거래_010248K2351K00:00:00.017235

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

{CODE}전통적인 방식의 UPDATE : IDX_거래_01 (CREATE INDEX IDX_거래_01 ON 거래(거래일시, 고객번호)
{CODE:SQL}
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


IdOperationNameStartsE-RowsA-RowsA-TimeBuffersOMem1Mem

1UPDATE고객1000:00:07.10741K
  • 2
HASH JOIN SEMI111000:00:00.571026247M4218K61M (0)3TABLE ACCESS FULL고객1992K1000K00:00:00.013045
  • 4
INDEX RANGE SCANIDX_거래_012438K2351K00:00:00.017217
5SORT AGGREGATE1011000:00:01.377958
  • 6
INDEX SKIP SCANIDX_거래_010243K2351K00:00:00.017958
7SORT AGGREGATE1011000:00:01.287958
  • 8
INDEX SKIP SCANIDX_거래_010243K2351K00:00:00.017958

PLAN_TABLE_OUTPUT

9SORT AGGREGATE1011000:00:03.22714K
10TABLE ACCESS BY INDEX ROWID거래10243K2351K00:00:02.35714K
  • 11
INDEX SKIP SCANIDX_거래_010245K2351K00:00:00.017958

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)

{CODE}위에 쿼리 튜닝
{CODE: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)*/'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'));

IdOperationNameStartsE-RowsA-RowsA-TimeBuffersOMem1Mem

PLAN_TABLE_OUTPUT


1UPDATE고객1000:00:04.68699K
  • 2
HASH JOIN SEMI111000:00:00.651000247M4218K61M (0)3TABLE ACCESS FULL고객1992K1000K00:00:00.013045
  • 4
INDEX RANGE SCANIDX_거래_012353K2267K00:00:00.016957
5SORT AGGREGATE1011000:00:03.39689K
6TABLE ACCESS BY INDEX ROWID거래10235K2267K00:00:02.27689K
  • 7
INDEX SKIP SCANIDX_거래_010236K2267K00:00:00.017670

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)

{CODE}
* 비효율 발생 : 한 달 이내 거래가 있던 고객을 두번 조회 하기 때문에 총 고객수와 한 달 이내 거래가 발생한 고객 수에 따라 성능이 좌우..		(딴짓) NO_UNNEST
{CODE: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) 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

IdOperationNameStartsE-RowsA-RowsA-TimeBuffers

1UPDATE고객1000:04:16.6250M
  • 2
FILTER11000:00:00.0150M
3TABLE ACCESS FULL고객1992K1000K00:00:00.013045
  • 4
INDEX SKIP SCANIDX_거래_01000K235K1000:07:00.6949M
5SORT AGGREGATE1011000:00:03.24689K
6TABLE ACCESS BY INDEX ROWID거래10235K2267K00:00:02.27689K
  • 7
INDEX SKIP SCANIDX_거래_010236K2267K00:00:00.017670

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)
{CODE}

실수로 거래 테이블에 고객 번호를 100만번 고객까지만 넣었어요.. 걍 참고하세요
{CODE:SQL}
-- 고객 : 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,'거래')

{CODE}

{CODE: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) */'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'));

IdOperationNameStartsE-RowsA-RowsA-TimeBuffersReadsOMem

PLAN_TABLE_OUTPUT


1UPDATE고객1000:00:07.14722K10630
2NESTED LOOPS111000:00:01.6772374582
3SORT UNIQUE12405K1000:00:01.667215458092169216
  • 4
INDEX RANGE SCANIDX_거래_012405K2351K00:00:02.3772154580
  • 5
INDEX UNIQUE SCANIDX_고객_P1011000:00:00.01222
6SORT AGGREGATE1011000:00:05.45714K6048
7TABLE ACCESS BY INDEX ROWID거래10240K2351K00:00:04.70714K6048
  • 8
INDEX SKIP SCANIDX_거래_010240K2351K00:00:00.01795613

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)

{CODE}

{CODE: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) 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'));

IdOperationNameStartsE-RowsA-RowsA-TimeBuffersReadsWrites

PLAN_TABLE_OUTPUT

1UPDATE고객1000:00:12.08753K211687719
  • 2
HASH JOIN RIGHT SEMI111000:00:02.5738929140487719
  • 3
INDEX RANGE SCANIDX_거래_012405K2351K00:00:00.0172150
4TABLE ACCESS FULL고객19929K10M00:00:00.01317148003
5SORT AGGREGATE1011000:00:05.40714K71200
6TABLE ACCESS BY INDEX ROWID거래10240K2351K00:00:04.72714K71200
  • 7
INDEX SKIP SCANIDX_거래_010240K2351K00:00:00.0179560

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)

{CODE}총 고객 수가 아주 많다면 Exists 서브 쿼리를 아래와 같이 해시 세미 조인으로 유도하는 것을 고려할 수 있다.
{CODE:SQL}
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,'거래');
{CODE}

{CODE: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) */'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'));

IdOperationNameStartsE-RowsA-RowsA-TimeBuffersReadsWrites

PLAN_TABLE_OUTPUT

1UPDATE고객1000:11:12.50156M5955512183
  • 2
HASH JOIN RIGHT SEMI12045K1876K00:00:23.662391832803121
  • 3
INDEX RANGE SCANIDX_거래_012419K2351K00:00:04.7281295380
4TABLE ACCESS FULL고객14977K5000K00:00:00.011578915240
5SORT AGGREGATE1876K11876K00:27:58.26152M267350
6TABLE ACCESS BY INDEX ROWID거래1876K12351K00:26:19.12152M26735
  • 7
INDEX SKIP SCANIDX_거래_01876K12351K00:22:05.46149M15

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)
{CODE}

{CODE: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) 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'));

IdOperationNameStartsE-RowsA-RowsA-TimeBuffersReadsWrites

PLAN_TABLE_OUTPUT

1UPDATE고객1000:23:56.13156M1249312493
  • 2
HASH JOIN RIGHT SEMI12045K1876K00:00:09.722391812493124
  • 3
INDEX RANGE SCANIDX_거래_012419K2351K00:00:00.0181290
4TABLE ACCESS FULL고객14977K5000K00:00:00.01157890
5SORT AGGREGATE1876K11876K00:22:46.50152M00
6TABLE ACCESS BY INDEX ROWID거래1876K12351K00:21:12.54152M0
  • 7
INDEX SKIP SCANIDX_거래_01876K12351K00:21:04.07149M0

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)
{CODE}

위부분 오타가 나서 다시 작업합니다.(테이블도 새로 만들어서 업데이트 한 회수가 틀립니다.)
{CODE: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) */'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'));

IdOperationNameStartsE-RowsA-RowsA-TimeBuffersReadsWrites

PLAN_TABLE_OUTPUT


1UPDATE고객1000:23:32.54156M2510012183
  • 2
HASH JOIN RIGHT SEMI12035K1876K00:00:12.592392625096121
  • 3
INDEX RANGE SCANIDX_거래_012405K2353K00:00:00.0381375192
4TABLE ACCESS FULL고객15002K5000K00:00:00.01157897721
5SORT AGGREGATE1876K11876K00:22:09.51152M10
6TABLE ACCESS BY INDEX ROWID거래1876K12353K00:20:38.68152M1
  • 7
INDEX SKIP SCANIDX_거래_01876K12353K00:20:31.88149M1

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'));

IdOperationNameStartsE-RowsA-RowsA-TimeBuffersReadsWrites

PLAN_TABLE_OUTPUT

1UPDATE고객1000:10:37.91156M1249312493
  • 2
HASH JOIN RIGHT SEMI12035K1876K00:00:07.522392612493124
  • 3
INDEX RANGE SCANIDX_거래_012405K2353K00:00:00.0181370
4TABLE ACCESS FULL고객15002K5000K00:00:00.01157890
5SORT AGGREGATE1876K11876K00:22:02.49152M00
6TABLE ACCESS BY INDEX ROWID거래1876K12353K00:20:32.22152M0
  • 7
INDEX SKIP SCANIDX_거래_01876K12353K00:20:25.57149M0

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)
{CODE}

한달 이내 거래를 발생시킨 고객킨 고객이 많아 update 발생량이 많다면 아래와 같이 변경하는 것을 고려할 수 있다.
하지만 모든 고객 레코드에 lock이 발생함은 물론, 이전관 같은 값은로 갱신되는 비중이 높을수로 Redo 로그 발생량이 증가해 오히려
비효율적일 수 있다.
위 쿼리 처리량은 : 1876184 행 (토탈 고객 : 500만명)
{CODE:SQL}
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

IdOperationNameStartsE-RowsA-RowsA-TimeBuffers

PLAN_TABLE_OUTPUT


1UPDATE고객1001:00:18.50412M
2TABLE ACCESS FULL고객14977K5000K00:00:05.0015789
3SORT AGGREGATE5000K15000K01:00:04.31401M
4TABLE ACCESS BY INDEX ROWID거래5000K12351K00:58:23.29401M
  • 5
INDEX SKIP SCANIDX_거래_05000K12351K00:58:10.77399M

Predicate Information (identified by operation id):

---

PLAN_TABLE_OUTPUT


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

수정 가능 조인 뷰

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

{CODE:SQL}
--잠시 딴짓
SQL> SELECT /*+ GATHER_PLAN_STATISTICS ORDERED USE_HASH©*/
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'));

---

IdOperationNameStartsE-RowsA-RowsA-TimeBuffersOMem1Mem

PLAN_TABLE_OUTPUT

  • 1
HASH JOIN111000:00:03.38717K812K812K1018K (0)
2VIEW1101000:00:03.38714K
3HASH GROUP BY1101000:00:03.38714K
4TABLE ACCESS BY INDEX ROWID거래12474K2351K00:00:02.35714K
  • 5
INDEX RANGE SCANIDX_거래_012474K2351K00:00:00.017216
6TABLE ACCESS FULL고객11007K1000K00:00:00.013046

Predicate Information (identified by operation id):

---

PLAN_TABLE_OUTPUT


1 - access("C"."고객번호"="T"."고객번호")
5 - access("거래일시">=TRUNC(ADD_MONTHS(SYSDATE@!,-1)) AND "거래일시" IS NOT NULL)
{CODE}

수정 가능 조인 뷰
{CODE:SQL}
SQL> UPDATE /*+ GATHER_PLAN_STATISTICS bypass_ujvc */
2 (
3 SELECT /*+ ORDERED USE_HASH©*/
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'));

IdOperationNameStartsE-RowsA-RowsA-TimeBuffersOMem1MemU

PLAN_TABLE_OUTPUT


1UPDATE고객1000:00:03.34717K
  • 2
HASH JOIN111000:00:03.15717K812K812K1027K (0)3VIEW1101000:00:03.15714K
4SORT GROUP BY1101000:00:03.15714K7372873728
5TABLE ACCESS BY INDEX ROWID거래12474K2351K00:00:02.35714K
  • 6
INDEX RANGE SCANIDX_거래_012474K2351K00:00:00.017216
7TABLE ACCESS FULL고객11007K1000K00:00:00.013045

Predicate Information (identified by operation id):

PLAN_TABLE_OUTPUT


---

2 - access("C"."고객번호"="T"."고객번호")
6 - access("거래일시">=TRUNC(ADD_MONTHS(SYSDATE@!,-1)) AND "거래일시" IS NOT NULL)
{CODE}

JOIN VIEW 통한 업데이트
{CODE:SQL}
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
{CODE}

{CODE:SQL}

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: 키-보존된것이 아닌 테이블로 대응한 열을 수정할 수 없습니다
{CODE}

  • DEPT 테이블에 UNIQUE 이덱스를 생성하지 않았기 때문에 생긴 에러이다.
  • 옵티마이저 입장에서 어느 쪽이 1집합인지 알 수 없었던 것이고 지금 상태에서 DELETE 문장도 허용 되지 않는다.(INSERT ?? =_=)
{CODE:SQL}
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 행이 갱신되었습니다.
{CODE}

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

키 보존 테이블이란.?

  • 키 보존 테이블이란 ? 조인된 결과 집합을 통해서도 중복 값이 없이 Unique하게 식별이 가능한 테이블을 말한다.
  • Unique한 1쪽 집합과 조인되는 테이블이어야 조인된 결과 집합을 통한 식별이 가능하다.
앞서 생선한 EMP_DEPT_VIEW에서 rowid를 함께 출력해 보자
{CODE:SQL}
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 개의 행이 선택되었습니다.

{CODE}
* DEPT_RID에 중복 값이 나타나고 있다. EMP_RID에는 중복 값이 없으며 뷰의 ROWID와 일차한다.
단적으로 말해 '키 보존 테이블'이란 뷰에 ROWID를 제공하는 테이블을 말한다		그럼 DEPT PK를 드랍 하고 EMP PK를 생성하면..??
{CODE:SQL}
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를 선택할 수 없음
{CODE}

_UPDATABLE_COLUMNS 뷰 참조

아래와 같이 수정 가능 조인 뷰를 통해 Insert를 시도했지만 실패하였다.
{CODE:SQL}
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: 조인 뷰에 의하여 하나 이상의 기본 테이블을 수정할 수 없습니다.
{CODE}

그럼 EMP_PK를 생성하면 LOC에 인서트가 가능한가.??(추론은 EMP TABLE이 키 보존 테이블이라 DEPT에는 인서트가 안될듯..)
{CODE:SQL}
-- 테이블을 다시 만든이유는 기존 테이블은 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: 조인 뷰에 의하여 하나 이상의 기본 테이블을 수정할 수 없습니다.
{CODE}

참고로 테스트 하다가 수정 가능한 조인 뷰에서 DELETE두 되는군요 =_=
{CODE:SQL}
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 행이 삭제되었습니다.
{CODE}

실패한 원인을 *_UPDATABLE_COLUMNS 뷰를 통해 쉽게 확인할 수 있다.
{CODE:SQL}
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

{CODE}
* DEPT_RID, DNAME, LOC 컬럼에는 INSERT, UPDATE, DELETE가 허용되지 않는다고 표시돼있는데,
모두 '비-키 보존 테이블(Non Key-Preserved Table)'(DEPT)로 부터 온 컬럼이다.
LOC 컬럼을 빼고 다시 INSERT 해 보면 아래와 같이 정상적으로 처리 된다.		{CODE:SQL}
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 개의 행이 만들어졌습니다.
{CODE}

수정가능 조인 뷰 제약 회피

  • 아래와 같이 부서별 평균 급여를 저장할 컬럼을 DEPT 테이블에 추가하고, EMP테이블에서 집계한 값을 반영하려고 하자 에러가 발생
  • DEPTNO로 GROUP BY한 결과는 UNIQUE하기 때문에 이 집합과 조인되는 DEPT 테이블은 키가 보존됨에도 옵티마이저가 불필요한 제약을 가한 것이다.
Updatable Join View Check
{CODE:SQL}
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: 키-보존된것이 아닌 테이블로 대응한 열을 수정할 수 없습니다

{CODE}
* BYPASS_UJVC : 옵티마이저에게 Updatable Join View Check를 생략하라는 힌트		BYPASS_UJVC(Updatable Join View Check를 생략힌트)
{CODE:SQL}
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'));

IdOperationNameStartsE-RowsA-RowsA-TimeBuffersOMem1MemU

PLAN_TABLE_OUTPUT

1UPDATEDEPT1000:00:00.019
  • 2
HASH JOIN13300:00:00.016951K951K
3VIEW13300:00:00.013
4SORT GROUP BY13300:00:00.01373728737285TABLE ACCESS FULLEMP1282800:00:00.013
6TABLE ACCESS FULLDEPT14400:00:00.013

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

{CODE}

중복 레코드가 아니면 어떻게 되는데..?? =_=
{CODE:SQL}
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'));

IdOperationNameStartsE-RowsA-RowsA-TimeBuffersOMem1MemUsed-

PLAN_TABLE_OUTPUT


1UPDATEDEPT3100:00:00.0115
  • 2
HASH JOIN32600:00:00.019968K968K287K (0)
3VIEW32600:00:00.010
4UNION-ALL3600:00:00.010
5FAST DUAL31300:00:00.010
6FAST DUAL31300:00:00.010
7TABLE ACCESS FULLDEPT34900:00:00.019

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
{CODE}

(2) Merge문 활용

  • DW에서 가증 흔히 발생하는 오퍼레이션은, 기간계 시스템에서 가져온 신규 트랜잭션 데이터를 반영함으로써 두 시스템 간 테이터를 동기화 시키는 작업이다.
1. 전일 발생한 변경 테이터를 기간계 시스템으로부터 추출(Extraction)
{CODE:SQL}
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;
{CODE}

2. CUSTOMER_DELTA 테이블을 DW 시스템으로 전송(Transportation)
3. DW 시스템으로 적재(Loading)
{CODE:SQL}
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);
{CODE}
* 기본 Merge문

Optional Clauses

  • 10g부터사용가능 update와 insert를 선택적으로 처리 가능 ....
준비
{CODE:SQL}
--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');
{CODE}

MERGE UPDATE
{CODE:SQL}
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'));

IdOperationNameStartsE-RowsA-RowsA-TimeBuffersReadsWri
1MERGECUSTOMER1100:05:22.652970K4749119375

PLAN_TABLE_OUTPUT

2VIEW12000K00:00:43.40254203742319375
  • 3
HASH JOIN11996K2000K00:00:43.4025420374231934TABLE ACCESS FULLCUSTOMER11996K2000K00:00:10.0210080
5TABLE ACCESS FULLCUSTOMER_DELTA13000K3000K00:00:15.0015340

Predicate Information (identified by operation id):

---

3 - access("T"."CUST_ID"="S"."CUST_ID")
{CODE}

수정가능한 조인 뷰
{CODE:SQL}
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'));

IdOperationNameStartsE-RowsA-RowsA-TimeBuffersReadsWrites
1UPDATECUSTOMER1000:01:52.582515K27922112

PLAN_TABLE_OUTPUT

  • 2
HASH JOIN11996K2000K00:00:05.87269042658211253
3TABLE ACCESS FULLCUSTOMER11996K2000K00:00:00.011156404TABLE ACCESS FULLCUSTOMER_DELTA13000K3000K00:00:00.01153401532

Predicate Information (identified by operation id):

---

2 - access("T"."CUST_ID"="S"."CUST_ID")
{CODE}

MERGE INSERT
{CODE:SQL}
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'));

IdOperationNameStartsE-RowsA-RowsA-TimeBuffersReadsWritesOMem
1MERGECUSTOMER1100:02:27.524165K2762722382

PLAN_TABLE_OUTPUT

2VIEW13000K00:00:10.66269042762722382
  • 3
HASH JOIN RIGHT OUTER13000K3000K00:00:10.66269042762722382
4TABLE ACCESS FULLCUSTOMER11996K2000K00:00:00.0111564005TABLE ACCESS FULLCUSTOMER_DELTA13000K3000K00:00:00.01153405245

Predicate Information (identified by operation id):

---

3 - access("T"."CUST_ID"="S"."CUST_ID")
{CODE}

NOT EXISTS INSERT
{CODE:SQL}
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'));

IdOperationNameStartsE-RowsA-RowsA-TimeBuffersReads

  • 1
HASH JOIN RIGHT ANTI11004K1000K00:00:05.112324015329

PLAN_TABLE_OUTPUT

2INDEX FAST FULL SCANCUSTOMER_PK11996K2000K00:00:00.01790003TABLE ACCESS FULLCUSTOMER_DELTA13000K3000K00:00:00.0115340

Predicate Information (identified by operation id):

---

1 - access("CUST_ID"="T"."CUST_ID")

{CODE}

그럼 이 기능이 왜있는가?? Optional Clauses Updatable Join View기능을 대체 할 수 있게되었다.(bypass_ujvc힌트가 필요할때 아래와같이 풀수있다.)
{CODE:SQL}
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'));

IdOperationNameStartsE-RowsA-RowsA-TimeBuffersReadsOMem

1MERGEDEPT1100:00:00.0192

PLAN_TABLE_OUTPUT

2VIEW1300:00:00.0162
  • 3
HASH JOIN13300:00:00.0162954VIEW13300:00:00.0132
5SORT GROUP BY13300:00:00.013276TABLE ACCESS FULLEMP1282800:00:00.0132
7TABLE ACCESS FULLDEPT14400:00:00.0130

Predicate Information (identified by operation id):

---

PLAN_TABLE_OUTPUT


3 - access("D"."DEPTNO"="E"."DEPTNO")

{CODE}

Conditional Operations

  • 10g에서는 on절에 기술한 조인문 외에 아래와 같이 추가로 조건절을 기술 할 수있다.
준비
{CODE:SQL}
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);
{CODE}

Delete Clause

  • 10g에서 제공되기 시작한 또 한가지 확장 기능은, 이미 저장된 테이터를 조건에 따라 지우는 것이다.? (=_=)
{CODE:SQL}
-- 이거 테스트 해봐야하는데요.. 시간이 없어서 =_=
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
....
{CODE}
* 탈퇴일시가 NULL이 아니었어도 Merge문을 수행한 결과가 Null이면 삭제된다.
* 그럼 DELETE -> INSERT -> DELETE 이런 식으로 되는건가요?? =_=

Merge Into 활용 - 1

항상 두번씩 실행
{CODE:SQL}
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;
{CODE}		최대 두번 실행
{CODE:SQL}
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;
{CODE}

한번 만 실행
{CODE:SQL}
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'));

IdOperationNameStartsE-RowsA-RowsA-TimeBuffersReads

PLAN_TABLE_OUTPUT

1MERGEDEPT1200:00:00.03121
2VIEW1100:00:00.0111
3NESTED LOOPS OUTER11100:00:00.0111
4FAST DUAL11100:00:00.0100
5TABLE ACCESS BY INDEX ROWIDDEPT11000:00:00.0111
  • 6
INDEX UNIQUE SCANDEPT_PK11000:00:00.0111

Predicate Information (identified by operation id):

---

PLAN_TABLE_OUTPUT


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

{CODE}

Merge Into 활용 - 2

준비 1
{CODE:SQL}
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,'청구일반내역');

{CODE}

준비 2
{CODE:SQL}
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'));

IdOperationNameStartsE-RowsA-RowsA-TimeBuffers

1SORT AGGREGATE11100:00:00.02412

PLAN_TABLE_OUTPUT

  • 2
TABLE ACCESS BY INDEX ROWID청구일반내역15568988100:00:00.02412
  • 3
INDEX RANGE SCANIDX_01_청구일1111371112100:00:00.0140

Predicate Information (identified by operation id):

---

2 - filter("C"."보류구분"='N')
3 - access("C"."청구년월"='201001' AND "C"."청구생성번호"='A01')
{CODE}

UPDATE
{CODE:SQL}
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'));

IdOperationNameStartsE-RowsA-RowsA-TimeBuffersOMem1MemUsed-
1UPDATE청구일반내역1000:01:40.384123K
  • 2
TABLE ACCESS BY INDEX ROWID청구일반내역15568988100:00:00.03412
  • 3
INDEX RANGE SCANIDX_01_청구일1111371112100:00:00.0140
  • 4
VIEW98815568988100:02:25.274100K
5COUNT988197M00:01:30.594100K
6VIEW9881556897M00:01:30.584100K
7SORT ORDER BY9881556897M00:01:30.574100K549K457K4
  • 8
TABLE ACCESS BY INDEX ROWID청구일반내역9881556897M00:00:00.144
  • 9
INDEX RANGE SCANPK_청구일반내988111137109M00:00:00.10424K

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

--

IdOperationNameRowsBytesCost (%CPU)Time

--

0UPDATE STATEMENT5568114K418 (1)00:00:06
1UPDATE청구일반내역
  • 2
TABLE ACCESS BY INDEX ROWID청구일반내역5568114K418 (1)00:00:06
  • 3
INDEX RANGE SCANIDX_01_청구일1113740 (0)00:00:01
  • 4
VIEW5568190K423 (1)00:00:06
5COUNT
6VIEW5568119K423 (1)00:00:06
7SORT ORDER BY5568114K423 (1)00:00:06
  • 8
TABLE ACCESS BY INDEX ROWID청구일반내역5568114K422 (1)00:00:06
  • 9
INDEX RANGE SCANPK_청구일반내1113743 (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)

********************************************************************************
{CODE}

Merge Into
{CODE:SQL}
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'));

IdOperationNameStartsE-RowsA-RowsA-TimeBuffersOMem1MemUsed-

PLAN_TABLE_OUTPUT


1MERGE청구일반내역1100:00:05.5418336
2VIEW1988100:00:00.168228
  • 3
HASH JOIN15568988100:00:00.1582281031K1031K1377K (0)4VIEW15568988100:00:00.063714
5WINDOW SORT15568988100:00:00.053714549K457K487K
  • 6
TABLE ACCESS BY INDEX ROWID청구일반내역15568988100:00:00.04371
  • 7
INDEX RANGE SCANIDX_01_청구일1111371112100:00:00.01102
8TABLE ACCESS FULL청구일반내역11202K1200K00:00:00.014514

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)

{CODE}

다중 테이블 Insert 활용

  • 오라클 9i부터 제공
  • 기본 사용법은 오라클 메뉴얼 참조.
야간 배치 프로그램
{CODE:SQL}
--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
{CODE}
* '청구보험당사자_임시'와 '가사고접수당사자_임시'는 10만 건 미만이지만 '거래당사자'는 수천만건에 이르는 대용량 테이블이라고 하자.
* 그럴때 아래와 같이 다중 테이블 Insert문을 활용하면 대용량 거래당사자 테이블을 한번만 읽고 처리할수있다.		다중 테이블 Insert문
{CODE:SQL}
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;
{CODE}

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)

  1. HOME
  2. [종료]대용량 데이터베이스 스터디
  3. 2010년 하반기 대용량데이터베이스 스터디
  4. 7. 조인을 내포한 DML 튜닝