태터데스크 관리자

도움말
닫기
적용하기   첫페이지 만들기

태터데스크 메시지

저장하였습니다.

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

유머/이벤트 2010.10.27 13:40

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
인데스의 특징을 알 수 있는 문제였다고 생각됩니다.
저작자 표시
신고
  • 미플 2010.11.03 12:08 신고 ADDR 수정/삭제 답글

    헐~~문제가 잘못 되었던 건가요? 5번에서 C2는 'x'를 리턴하는데 어떻게된건지요...
    원리는 알고 있는데 리턴 결과를 'x'로 맞출 수 없어서 응모 포기했었는디...



티스토리 툴바