[이벤트 둘!/당첨자 발표] 당신의 오라클 내공을 보여주세요!

Oracle ACE "Dion Cho" 가 직접 출제하는 주옥같은 문제!
당신의 오라클 내공을 보여주세요~
가장 좋은 정답을 제출한 분께 5만원 상품권을 드립니다!

지금 바로 도전하세요~! 지난달 당첨자 및 정답 발표

아래 결과는 Oracle 10gR2 이상에서 동작합니다. 그리고 정확한 일량(Logical Reads)는 환경에 따라 다를 수 있습니다.

1. 우선 다음과 같이 테이블 T1을 만듭니다. 컬럼 C1은 항상 "1"의 값입니다.

SQL> create table t1
  2  as
  3  select
  4  	1 as c1,
  5  	rpad('x',10) as c2
  6  from dual
  7  connect by level <= 10000
  8  ;

Table created.

2. 그리고 인덱스 T1_N1을 만듭니다.

SQL> create index t1_n1 on t1(c1, c2);

Index created.

3. 아래와 같이 인덱스 T1_N1을 이용하면 C1 = 1을 만족하는 첫번째 로우의 값을 최소의 일량으로 구할 수 있습니다.

SQL> select c1, count(*)
  2  from t1
  3  group by c1
  4  ;

        C1   COUNT(*)
---------- ----------
         1      10000

SQL> select /*+ gather_plan_statistics
  2  			index(t1) */
  3  	*
  4  from
  5  	t1
  6  where
  7  	c1 = 1
  8  	and rownum = 1
  9  ;

        C1 C2
---------- ----------
         1 x

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

----------------------------------------------------------------------------
| Id  | Operation         | Name  | Starts | A-Rows |   A-Time   | Buffers |
----------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |       |      1 |      1 |00:00:00.01 |       2 |
|*  1 |  COUNT STOPKEY    |       |      1 |      1 |00:00:00.01 |       2 |
|*  2 |   INDEX RANGE SCAN| T1_N1 |      1 |      1 |00:00:00.01 |       2 |
----------------------------------------------------------------------------

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

   1 - filter(ROWNUM=1)
   2 - access("C1"=1)

4. 이제 아래의 작업을 수행합니다.

SQL> -- do what?
SQL> { 여기에 어떤 SQL 문장이 들어갈까요? }

5. 그리고 동일한 방법으로 인덱스 T1_N1을 이용해서 C1 = 1 을 만족하는 첫번째 로우를 얻습니다. 하지만 이번에는 일량이 36으로 크게 증가했습니다.

SQL> select c1, count(*)
  2  from t1
  3  group by c1
  4  ;

        C1   COUNT(*)
---------- ----------
         1      10000

SQL> select /*+ gather_plan_statistics
  2  			index(t1) */
  3  	*
  4  from
  5  	t1
  6  where
  7  	c1 = 1
  8  	and rownum = 1
  9  ;

        C1 C2
---------- ----------
         1 x

----------------------------------------------------------------------------
| Id  | Operation         | Name  | Starts | A-Rows |   A-Time   | Buffers |
----------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |       |      1 |      1 |00:00:00.01 |      36 |
|*  1 |  COUNT STOPKEY    |       |      1 |      1 |00:00:00.01 |      36 |
|*  2 |   INDEX RANGE SCAN| T1_N1 |      1 |      1 |00:00:00.01 |      36 |
----------------------------------------------------------------------------

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

   1 - filter(ROWNUM=1)
   2 - access("C1"=1)

컬럼 C1은 항상 1의 값을 가지기 때문에 C1 = 1 조건을 인덱스를 이용해서 경유하면 최소의 일량만으로 원하는 로우를 얻을 수 있어야 합니다. 하지만 { 여기에 어떤 SQL 문장이 들어갈까요? }에 해당하는 작업을 수행하면 일량이 갑자기 증가합니다. 어떤 SQL 문장을 수행해야 위와 같이 일량이 증가하는 결과가 나올까요? 이것이 이번 문제입니다.

힌트를 드리면 다음과 같습니다.

정답은 하나의 DML 문장입니다. 즉, INSERT, UPDATE, DELETE 중 하나입니다.
위의 테스트 과정을 보시면 C1 = 1 에 해당하는 로우 수는 1,000개로 항상 일정합니다. 그리고 테이블의 로우 수도 1,000개입니다.

위의 힌트를 고려하시면 정답이 보이실 것입니다.

정답발표

정답제출: quiz@ex-em.com
정답자 발표 : 2010년 10월 27일 수요일 오후 2시

퀴즈 정답 접수가 마감되었습니다.

<퀴즈 당첨자>

김** no****@nate.com

많은 분들이 정답을 보내주셨습니다~
참여해주신 모든 분들께 진심으로 감사드립니다.
정답이 아래에 공개됩니다!

<퀴즈 정답>

이번 퀴즈의 정답자가 보내주신 문장은 아래와 같습니다.

SQL> update t1
  2  set c2 = 'xxxxxxxxxx';

문제는 왜? 위와 같이 UPDATE문을 수행하고 나면 Logical Reads가 증가하느냐입니다.

인덱스 관점에서 UPDATE는 DELETE 후 (커밋없이) INSERT와 동일합니다. 인덱스의 고유의 특징 때문에 현재 트랜잭션이 삭제한 공간이라고 하더라도 커밋이 이루어지지 않는 한 재사용이 불가능합니다.

위의 UPDATE 문장을 수행한 후 인덱스 트리 덤프(Index Tree Dump)를 수행해보면 아래와 같습니다.

alter session set events 'immediate trace name treedump level { data object id of the index }';

-- 트레이스파일
----- begin tree dump
branch: 0x10005ab 16778667 (0: nrow: 67, level: 1)
   leaf: 0x10005ac 16778668 (-1: nrow: 287 rrow: 0)
   leaf: 0x10005ad 16778669 (0: nrow: 287 rrow: 0)
   leaf: 0x10005ae 16778670 (1: nrow: 287 rrow: 0)
   leaf: 0x10005af 16778671 (2: nrow: 287 rrow: 0)
   leaf: 0x10005e8 16778728 (3: nrow: 287 rrow: 0)
   leaf: 0x10005e9 16778729 (4: nrow: 287 rrow: 0)
   leaf: 0x10005ea 16778730 (5: nrow: 287 rrow: 0)
   leaf: 0x10005eb 16778731 (6: nrow: 287 rrow: 0)
   leaf: 0x10005ec 16778732 (7: nrow: 287 rrow: 0)
   leaf: 0x10005ed 16778733 (8: nrow: 287 rrow: 0)
   leaf: 0x10005ee 16778734 (9: nrow: 287 rrow: 0)
   leaf: 0x10005ef 16778735 (10: nrow: 287 rrow: 0)
   leaf: 0x10005f1 16778737 (11: nrow: 287 rrow: 0)
   leaf: 0x10005f2 16778738 (12: nrow: 287 rrow: 0)
   leaf: 0x10005f3 16778739 (13: nrow: 287 rrow: 0)
   leaf: 0x10005f4 16778740 (14: nrow: 287 rrow: 0)
   leaf: 0x10005f5 16778741 (15: nrow: 287 rrow: 0)
   leaf: 0x10005f6 16778742 (16: nrow: 287 rrow: 0)
   leaf: 0x10005f7 16778743 (17: nrow: 287 rrow: 0)
   leaf: 0x10005f8 16778744 (18: nrow: 287 rrow: 0)
   leaf: 0x10005f9 16778745 (19: nrow: 287 rrow: 0)
   leaf: 0x10005fa 16778746 (20: nrow: 287 rrow: 0)
   leaf: 0x10005fb 16778747 (21: nrow: 287 rrow: 0)
   leaf: 0x10005fc 16778748 (22: nrow: 287 rrow: 0)
   leaf: 0x10005fd 16778749 (23: nrow: 287 rrow: 0)
   leaf: 0x10005fe 16778750 (24: nrow: 287 rrow: 0)
   leaf: 0x10005ff 16778751 (25: nrow: 287 rrow: 0)
   leaf: 0x1000c89 16780425 (26: nrow: 287 rrow: 0)
   leaf: 0x1000c8a 16780426 (27: nrow: 287 rrow: 0)
   leaf: 0x1000c8b 16780427 (28: nrow: 287 rrow: 0)
   leaf: 0x1000c8c 16780428 (29: nrow: 287 rrow: 0)
   leaf: 0x1000c8d 16780429 (30: nrow: 287 rrow: 0)
   leaf: 0x1000c8e 16780430 (31: nrow: 287 rrow: 0)
   leaf: 0x1000c8f 16780431 (32: nrow: 287 rrow: 0)
   leaf: 0x1000c90 16780432 (33: nrow: 320 rrow: 78)
   leaf: 0x1000c91 16780433 (34: nrow: 320 rrow: 320)
   leaf: 0x1000c92 16780434 (35: nrow: 320 rrow: 320)
   leaf: 0x1000c95 16780437 (36: nrow: 320 rrow: 320)
   leaf: 0x1000c96 16780438 (37: nrow: 320 rrow: 320)
   leaf: 0x1000c97 16780439 (38: nrow: 320 rrow: 320)
   leaf: 0x1000c93 16780435 (39: nrow: 320 rrow: 320)
   leaf: 0x1000c94 16780436 (40: nrow: 320 rrow: 320)
   leaf: 0x1000c99 16780441 (41: nrow: 320 rrow: 320)
   leaf: 0x1000c9a 16780442 (42: nrow: 320 rrow: 320)
   leaf: 0x1000c9d 16780445 (43: nrow: 320 rrow: 320)
   leaf: 0x1000c9e 16780446 (44: nrow: 320 rrow: 320)
   leaf: 0x1000c9f 16780447 (45: nrow: 320 rrow: 320)
   leaf: 0x1000c9b 16780443 (46: nrow: 320 rrow: 320)
   leaf: 0x1000c9c 16780444 (47: nrow: 320 rrow: 320)
   leaf: 0x1000ca5 16780453 (48: nrow: 320 rrow: 320)
   leaf: 0x1000ca6 16780454 (49: nrow: 320 rrow: 320)
   leaf: 0x1000ca7 16780455 (50: nrow: 320 rrow: 320)
   leaf: 0x1000ca0 16780448 (51: nrow: 320 rrow: 320)
   leaf: 0x1000ca1 16780449 (52: nrow: 320 rrow: 320)
   leaf: 0x1000ca2 16780450 (53: nrow: 320 rrow: 320)
   leaf: 0x1000ca3 16780451 (54: nrow: 320 rrow: 320)
   leaf: 0x1000ca4 16780452 (55: nrow: 320 rrow: 320)
   leaf: 0x1000ca9 16780457 (56: nrow: 320 rrow: 320)
   leaf: 0x1000caa 16780458 (57: nrow: 320 rrow: 320)
   leaf: 0x1000cad 16780461 (58: nrow: 320 rrow: 320)
   leaf: 0x1000cae 16780462 (59: nrow: 320 rrow: 320)
   leaf: 0x1000caf 16780463 (60: nrow: 320 rrow: 320)
   leaf: 0x1000cab 16780459 (61: nrow: 320 rrow: 320)
   leaf: 0x1000cac 16780460 (62: nrow: 320 rrow: 320)
   leaf: 0x1000cb5 16780469 (63: nrow: 320 rrow: 320)
   leaf: 0x1000cb6 16780470 (64: nrow: 320 rrow: 320)
   leaf: 0x1000cb7 16780471 (65: nrow: 2 rrow: 2)
----- end tree dump

DELETE 후 INSERT 때문에 리프 노드 왼쪽 블록들이 모두 비어있습니다. 즉, 삭제된 공간을 재활용하지 않은 것입니다. 이때문에 최초의 로우를 하나 읽는데 아래만큼 블록을 읽어야 합니다. (브랜츠 노드 1개 + 리프 노드 35개)

SQL> select 40 - 5 + 1 from dual;

    40 5+1
----------
        36

인데스의 특징을 알 수 있는 문제였다고 생각됩니다.

저작자표시

'왕푸짐 이벤트' 카테고리의 다른 글

[이벤트 둘!] 당신의 오라클 내공을 보여주세요! (3)	2010.12.01
[이벤트 하나!] Oracle Event Tour 만화를 채워주세요. (25)	2010.11.23
[이벤트 하나!/당첨자발표] Oracle Event Tour 만화를 채워주세요. (17)	2010.10.19
[이벤트 둘!/정답 및 당첨자 발표] 당신의 오라클 내공을 보여주세요! (0)	2010.10.05
[이벤트 하나!/당첨자 발표] Oracle Event Tour 만화를 채워주세요. (31)	2010.09.17