당첨되는 재미가 쏠쏠~
정답을 맞추시면 추첨을 통해 기프티콘을 드려요! 
아래 만화는 'Aster와 함께하는 Oracle Event Tour' 중 발췌한 것 입니다. 빈칸에 동일하게 들어가는 알맞은 말을 채워주세요

힌트: 1. 'Aster와 함께하는 Oracle Event Tour' 제26회  Enq:TC-Contention
        2. 만화보러가기

이벤트 기간: 2010년 12월 22일 오전~오후 6시  
당첨자 발표: 2010년 12월 23일 오후 2시 

정답을 댓글로 달아 주시는 분 중 추첨을 통해 열 분께 스타벅스 기프티콘을 
드립니다! 댓글은 비밀댓글로 달아주시고 기프티콘 수신 가능한 전화번호를 기재해 주세요! ^^

연락처를 남기지 않으시면 기프티콘을 드릴수 없습니다. 연락처 잊지 마세요!^^
많은 분들의 참여 기다리겠습니다,

당첨자 발표

장기현, 김봉희, 김창권, 김승훈, 황순혁, 조정빈, 서창희, 장순재, 김중우, 이경혜

이상 10분께 달콤한 스타벅스 기프티콘이 날아갑니다~ 감사합니다!^^
Merry Christmas!

공유하기

글 요소

구독하기 엑셈 이야기

저작자표시

• 카카오스토리
• 트위터
• 페이스북

'이벤트 > 이벤트' 카테고리의 다른 글

[특별이벤트] 10주년 축하 메시지 전하고 기프티콘 받기!  (25)	2011.01.12
[이벤트 둘!/정답자 발표] 당신의 오라클 내공을 보여주세요!  (0)	2010.12.21
[이벤트 하나!] Oracle Event Tour 만화를 채워주세요.  (35)	2010.12.21
[이벤트 둘!] 당신의 오라클 내공을 보여주세요!  (3)	2010.12.01
[이벤트 하나!] Oracle Event Tour 만화를 채워주세요.  (25)	2010.11.23
[이벤트 둘!/당첨자 발표] 당신의 오라클 내공을 보여주세요!  (1)	2010.10.27

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

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

이번 달 문제는 특정 테이블에 단 하나의 로우만 들어가게 하는 방법입니다.

가령 아래와 같이 동작해야 합니다.

SQL> create table t1(c1 number, c2 number);
 
Table created.
 
SQL> -- 여기에서 어떤 작업을 해야 단 하나의!!! 로우만 들어가게 할 수 있을까요?
{ 단 하나의 로우만 들어가도록 어떤 작업을 한다 }
 
Index created.
 
SQL> insert into t1 values(1, 1);
 
1 row created.
 
SQL> insert into t1 values(2, 2);
insert into t1 values(2, 2)
*
ERROR at line 1:
<< Error Message Here >>

 

위의 내용에서 { 단 하나의 로우만 들어가도록 어떤 작업을 한다 }에 들어갈 SQL 문장(들)을 완성하시는 것이 이번 달의 문제입니다.

어떤 세션이 어떤 데이터를 넣어도 단 하나의 로우만 존재해야 합니다. 즉, 첫번째 로우가 들어간 이후로는 더 이상 인서트가 이루어져서는 안됩니다. 그것을 가능하기 위해 필요한 작업을 SQL 문장(들)으로 정리해서 보내주시면 됩니다.

정답제출: quiz@ex-em.com
정답자 발표 : 2010년 12월 3일 금요일 오후 2시
--------------------------------------------------------------------
퀴즈 접수가 마감되었습니다.

당첨자

정** g**@**caretech.com

이벤트에 참여해 주신 모든 분들께 감사드립니다. ^^

퀴즈 정답

테이블에 오직 하나의 로우만 들어가게 하는 가장 간단한 방법은 다음과 같이 Unique Index를 만드는 것입니다.

SQL> create table t1(c1 number, c2 number);

Table created.

SQL> create unique index t1_n1 on t1(1);  -- 상수 값을 가지는 Function Based Index

Index created.

SQL> insert into t1 values(1, 1);

1 row created.

SQL> insert into t1 values(2, 2);
insert into t1 values(2, 2)
*
ERROR at line 1:
ORA-00001: unique constraint (TPACK.T1_N1) violated


SQL> insert into t1 values(null, null);
insert into t1 values(null, null)
*
ERROR at line 1:
ORA-00001: unique constraint (TPACK.T1_N1) violated

여러가지 방법을 생각할 수 있겠지만 가장 간단한 방법이 정답으로 선정된 것으로 이해하시면 되겠습니다.

당첨되는 재미가 쏠쏠~
정답을 맞추시면 추첨을 통해 기프티콘을 드려요! 
아래 만화는 'Aster와 함께하는 Oracle Event Tour' 중 발췌한 것 입니다. 빈칸에 동일하게 들어가는 알맞은 말을 채워주세요

힌트: 1. 'Aster와 함께하는 Oracle Event Tour' 제25회 SGA: allocation forcing Component growth      
        2. 만화보러가기

이벤트 기간: 2010년 11월 24일 오전~오후 6시  
당첨자 발표: 2010년 11월 25일 오전 10시 

정답을 댓글로 달아 주시는 분 중 추첨을 통해 열 분께 스타벅스 기프티콘을 
드립니다! 댓글은 비밀댓글로 달아주시고 기프티콘 수신 가능한 전화번호를 기재해 주세요! ^^

연락처를 남기지 않으시면 기프티콘을 드릴수 없습니다. 연락처 잊지 마세요!^^
많은 분들의 참여 기다리겠습니다!

당첨자 발표

고갱, 이준구, 지창현, 조석, 조수경, 최범진, 최성재,이원기, 한만용, 한은경

이상 열분 축하드립니다~^^  이벤트에 참여해 주신 모든분들께 감사드립니다.

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

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

당첨되는 재미가 쏠쏠~
정답을 맞추시면 추첨을 통해 기프티콘을 드려요! 
아래 만화는 'Aster와 함께하는 Oracle Event Tour' 중 발췌한 것 입니다. 빈칸에 동일하게 들어가는 알맞은 말을 채워주세요

힌트: 1. 'Aster와 함께하는 Oracle Event Tour' 제22회 Enq:HW-Contention   
        2. 만화보러가기

이벤트 기간: 2010년 10월 20일 오전~오후 6시  
당첨자 발표: 2010년 10월 21일 오전 10시 

정답을 댓글로 달아 주시는 분 중 추첨을 통해 열 분께 스타벅스 기프티콘을 
드립니다! 댓글은 비밀댓글로 달아주시고 기프티콘 수신 가능한 전화번호를 기재해 주세요! ^^

연락처를 남기지 않으시면 기프티콘을 드릴수 없습니다. 연락처 잊지 마세요!^^
많은 분들의 참여 기다리겠습니다!

<당첨자 발표>

파라다이스, 장순재, 남해연, 박모연, 김태원, 정영주, 이원기, 박기종, 임정윤, 박애라

이상 열분께 스타벅스 기프티콘이 날아갑니다~ 오늘 중으로 받지 못하신 분은
sunshy12@ex-em.com 으로 연락부탁드립니다! 감사합니다.

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

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

다음과 같이 테이블 T1을 만들고, Unique 인덱스 T1_N1을 만듭니다.

SQL> create table t1  2  as  3  select level as c1, level as c2  4  from dual  5  connect by level <= 1000;Table created.SQL> create unique index t1_n1 on t1(c1);Index created.

그리고 다음과 같은 작업을 수행합니다.

SQL> { Unique 인덱스 T1_n1을 경유해 테이블 T1을 업데이트하는 어떤 작업 };

위에서 수행한 작업에 대해 TKPROF 리포트를 만들면 다음과 같은 결과가 나옵니다.

UPDATE T1 SET C2 = :B1 +1WHERE C1 = :B2call     count       cpu    elapsed       disk      query    current      rows------- ------  -------- ---------- ---------- ---------- ----------  --------Parse        1      0.00       0.00          0          0          0         0Execute      1      0.03       0.03          0       1011       1022      1000Fetch        0      0.00       0.00          0          0          0         0------- ------  -------- ---------- ---------- ---------- ----------  --------total        2      0.03       0.03          0       1011       1022      1000

위의 결과를 보면 UPDATE 문은 분명히 한번(Execute=1) 수행되었지만 총 1,000건(rows=1000)이 변경된 것으로 보고되고 있습니다. 어떻게 이것이 가능할까요?

{ Unique 인덱스 T1_n1을 경유해 테이블 T1을 업데이트하는 어떤 작업 }에 가장 알맞은 코드를 작성해서 보내주시는 것이 이번 퀴즈의 문제입니다. 단, 실제로 동작하는 코드라는 것을 확인하기 위해서 반드시 SQL*Plus의 SPOOL 결과를 보내주신 경우에만 정답으로 인정하겠습니다.

정답발표

정답제출: quiz@ex-em.com
정답자 발표 : 2010년 10월 5일 화요일 오후 2시
--------------------------------------------------------------------
퀴즈 정답 접수가 마감되었습니다. 

<퀴즈 당첨자>

구**  sa***@gmail.com

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

<퀴즈 정답> 

이번 달은 문제가 조금 애매했던 관계로 도전자가 조금 적었습니다. 하지만 출제자의 의도를 완벽하게 파악해서 답변을 제시해주신 분이 있습니다.

이번 문제의 정답은 배치 DML입니다. 아래와 같이 FORALL 구문을 이용해서 UPDATE 문을 수행합니다.

SQL> ALTER SESSION SET SQL_TRACE = TRUE;

세션이 변경되었습니다.

SQL> DECLARE
  2  
  3  CURSOR CUR IS
  4  SELECT C1 FROM HSKOO.T1;
  5  
  6  TYPE T1_C1 IS TABLE OF HSKOO.T1.C1%TYPE;
  7  
  8  T1_C1S T1_C1;
  9  
 10  BEGIN
 11      OPEN CUR;
 12      FETCH CUR BULK COLLECT INTO T1_C1S;
 13      CLOSE CUR;
 14  
 15      FORALL X IN T1_C1S.FIRST..T1_C1S.LAST
 16      UPDATE HSKOO.T1
 17         SET C2 = T1_C1S(X) + 1
 18       WHERE C1 = T1_C1S(X);
 19  
 20  END;
 21  /

PL/SQL 처리가 정상적으로 완료되었습니다.

SQL> SPOOL OFF

FORALL 구문을 사용하면 오라클은 내부적으로 배열 프로세싱(Array Processing)을 사용합니다. 따라서 아래와 같이 단 한건만 UPDATE하는 구문임에도 불구하고 한번 실행에서 여러 개(여기서는 1,000개)의 로우를 한번에 UPDATE하는 것으로 보고되는 것입니다.

UPDATE HSKOO.T1 SET C2 = :B1 + 1 
WHERE
 C1 = :B2 


call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      1      0.06       0.36          0       1002       1024        1000
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        2      0.06       0.36          0       1002       1024        1000

 

정답을 맞추신 분께 다시 한번 축하말씀드리며, 다음 번에 보다 단순하면서도 재미있는 문제를 준비하도록 하겠습니다. 참여해 주신 모든 분께 감사드립니다.^^

당첨되는 재미가 쏠쏠~
정답을 맞추시면 추첨을 통해 기프티콘을 드려요! 

아래 만화는 'Aster와 함께하는 Oracle Event Tour' 중 발췌한 것 입니다. 빈칸에 동일하게 들어가는 알맞은 말을 채워주세요

힌트: 1. 'Aster와 함께하는 Oracle Event Tour' 제23회 gc buffer busy   
        2. 만화보러가기

이벤트 기간: 2010년 9월 16일 오전~오후 6시  
당첨자 발표: 2010년 9월 17일 오전 10시 

정답을 댓글로 달아 주시는 분 중 추첨을 통해 열 분께 스타벅스 기프티콘을 
드립니다! 댓글은 비밀댓글로 달아주시고 기프티콘 수신 가능한 전화번호를 기재해 주세요! ^^

연락처를 남기지 않으시면 기프티콘을 드릴수 없습니다. 연락처 잊지 마세요!^^
많은 분들의 참여 기다리겠습니다!

당첨자 발표
김성열, 남군, 두계웅, 윤태준, 김은혜, 김신연, 윤미지, 윤윤선, 이원기, 태욱

이상 열분께 스타벅스 기프티콘이 날아갑니다~ 오늘 중으로 받지 못하신 분은
sunshy12@ex-em.com 으로 연락부탁드립니다! 감사합니다.

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

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

이번 문제는 아래와 같습니다.

SQL> create table t1(c1 number);

Table created.

SQL> insert into t1
  2  select 1 from dual connect by level <= 10000
  3  union all
  4  select null from dual connect by level <= 10;

10010 rows created.

SQL> create index t1_n1 on t1(c1);

Index created.

SQL> exec dbms_stats.gather_table_stats(user, 'T1', no_invalidate=>false);

PL/SQL procedure successfully completed.

SQ> -- WHERE C1 IS NULL 은 반드시 Table Full Scan!
SQL> explain plan for
  2  select * from t1
  3  where c1 is null;

Explained.

SQL> select * from table(dbms_xplan.display(format=>'basic'));

PLAN_TABLE_OUTPUT
-----------------------------------
Plan hash value: 3617692013

----------------------------------
| Id  | Operation         | Name |
----------------------------------
|   0 | SELECT STATEMENT  |      |
|   1 |  TABLE ACCESS FULL| T1   |
----------------------------------

8 rows selected.

SQL> -- 하지만 아래와 같이 인덱스를 만들면 Index Range Scan 가능!
SQL> { 여기에 들어갈 SQL 문장을 완성해주세요 };

Index created.

SQL> explain plan for
  2  select * from t1
  3  where c1 is null;

Explained.

SQL> select * from table(dbms_xplan.display(format=>'basic'));

PLAN_TABLE_OUTPUT
------------------------------------
Plan hash value: 677322570

----------------------------------
| Id  | Operation        | Name  |
----------------------------------
|   0 | SELECT STATEMENT |       |
|   1 |  INDEX RANGE SCAN| T1_N2 |
----------------------------------

8 rows selected.

 

위의 테스트 케이스에서 { 여기에 들어갈 SQL 문장을 완성해주세요 } 부분에 들어갈 적절한 SQL 문장을 보내주시면 됩니다. 해답을 보내주신 분들 중 다음과 같은 기준으로 정답자 한 분을 선정합니다.

• 가장 먼저
• 가장 정확한 정답을
• 기타 재미있는 아이디어가 있으면 가산점!

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

많은 참여 부탁드립니다^^
------------------------------------------------------------------------------------------------------
퀴즈 정답 접수가 마감되었습니다. 

<퀴즈 정답> 

 

1. 퀴즈의 정답은 아래와 같이 무의미한 Dummy 키를 추가한 일종의 복합 인덱스를 만드는 것입니다.

SQL> create index t1_n2 on t1(c1, '');

Index created.

SQL> 
SQL> explain plan for
  2  select * from t1
  3  where c1 is null;

Explained.

SQL> -- 아래의 문법은 Oracle 11g에서만 동작합니다. 
SQL> -- 10g까지는 select * from table(dbms_xplan.display) 로 호출하면 됩니다.
SQL> select * from table(dbms_xplan.display(format=>'basic'));

----------------------------------
| Id  | Operation        | Name  |
----------------------------------
|   0 | SELECT STATEMENT |       |
|   1 |  INDEX RANGE SCAN| T1_N2 |
----------------------------------

NULL 값을 다루는 몇가지 다른 방법을 볼까요?

2. 우선 비트맵 인덱스는 NULL 값을 포함합니다. 단, 이것은 이론적인 설명으로 실제 운영환경에서 NULL 값을 인덱싱하기 위한 목적으로 비트맵 인덱스를 함부로 사용해선 안됩니다.

SQL> create bitmap index t1_n1 on t1(c1);

Index created.

SQL> 
SQL> explain plan for
  2  select * from t1
  3  where c1 is null;

----------------------------------------------
| Id  | Operation                    | Name  |
----------------------------------------------
|   0 | SELECT STATEMENT             |       |
|   1 |  BITMAP CONVERSION TO ROWIDS |       |
|   2 |   BITMAP INDEX FAST FULL SCAN| T1_N1 |
----------------------------------------------

3. IS NULL 조건을 변경할 수 있다면 아래와 같이 NVL과 같은 함수를 이용한 Function Based Index도 사용할 수 있습니다.

SQL> create index t1_n1 on t1(nvl(c1,-1));

Index created.

SQL> 
SQL> explain plan for
  2  select * from t1
  3  where nvl(c1,-1) = -1;

---------------------------------------------
| Id  | Operation                   | Name  |
---------------------------------------------
|   0 | SELECT STATEMENT            |       |
|   1 |  TABLE ACCESS BY INDEX ROWID| T1    |
|   2 |   INDEX RANGE SCAN          | T1_N1 |
---------------------------------------------

4. 또 하나의 방법은 NAN 값을 이용하는 것입니다. NAN은 Not-A-Number의 약자로 Number는 아니지만 Null도 아닌 값을 의미합니다. 아래와 같이 사용할 수 있습니다. 단, Number 타입에 대해서만 지원된다는 제약이 있습니다.

SQL> create table t1(c1 binary_float);

Table created.

SQL> 
SQL> insert into t1
  2  select 1 from dual connect by level <= 10000
  3  union all
  4  select binary_float_nan from dual connect by level <= 10;

10010 rows created.

SQL> create index t1_n1 on t1(c1);

Index created.

SQL> explain plan for
  2  select /*+ index(t1) */ * from t1
  3  where c1 = binary_float_nan;

----------------------------------
| Id  | Operation        | Name  |
----------------------------------
|   0 | SELECT STATEMENT |       |
|   1 |  INDEX RANGE SCAN| T1_N1 |
----------------------------------

역시 1번 정답이 최고의 방법이군요!

당첨되는 재미가 쏠쏠~
정답을 맞추시면 추첨을 통해 기프티콘을 드려요! 

아래 만화는 'Aster와 함께하는 Oracle Event Tour' 중 발췌한 것 입니다. 빈칸에 들어가는 알맞은 말을 채워주세요!


힌트: 1. 'Aster와 함께하는 Oracle Event Tour' 제21회 log file switch completion  
        2. 만화보러가기

이벤트 기간: 2010년 8월 25일 오전~오후 6시  
당첨자 발표: 2010년 8월 26일 오전 10시 

정답을 댓글로 달아 주시는 분 중 추첨을 통해 열 분께 스타벅스 기프티콘을 
드립니다! 댓글은 비밀댓글로 달아주시고 기프티콘 수신 가능한 전화번호를 기재해 주세요! ^^

연락처를 남기지 않으시면 기프티콘을 드릴수 없습니다. 연락처 잊지 마세요!^^

당첨자

김여름, 김창권, 홍택민, 변대범, 윤광희, 정지원, 박애라, 강진수, 강현식,
김종익

당첨자분들께는 스타벅스 기프티쇼가 날아갑니다. 금일(8/26) 중으로 기프티쇼를 수령하지 못하신 분은 sunshy12@ex-em.com 으로 문의 주시기 바랍니다.

이벤트에 참여해 주신 모든 분들께 감사드립니다.

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

지금 바로 도전하세요~!

1. 아래와 같이 2 개의 Unique Constraint가 있습니다.

SQL> select constraint_name, constraint_type
  2  from user_constraints
  3  where table_name = 'T1';

CONSTRAINT_NAME C
--------------- -
T1_N1           U
T1_N2           U

2. 각 Constraint를 구성하는 컬럼은 다음과 같습니다.

SQL> col constraint_name format a15
SQL> col table_name format a15
SQL> col column_name format a15
SQL> 
SQL> select constraint_name, table_name, column_name
  2  from user_cons_columns
  3  where table_name = 'T1';

CONSTRAINT_NAME TABLE_NAME      COLUMN_NAME
--------------- --------------- ---------------
T1_N1           T1              C1
T1_N2           T1              C2

3. 각 컬럼을 구성하는 인덱스는 다음과 같습니다.

SQL> select i.index_name, c.column_name, i.uniqueness
  2  from user_indexes i, user_ind_columns c
  3  where i.table_name = 'T1'
  4  	  and i.index_name = c.index_name
  5  ;

INDEX_NAME           COLUMN_NAME     UNIQUENES
-------------------- --------------- ---------
T1_N1                C1              UNIQUE
T1_N2                C2              NONUNIQUE

여기서 문제!

왜 동일한 Unique Constraint 임에도 불구하고 인덱스 T1_N1은 Unique 인덱스이고, 인덱스 T1_N2는 Non-Unique 인덱스일까요?

최고의 답변을 선택하는 기준은 다음과 같습니다.

• 얼마나 빨리 답변하는가?
• 얼마나 정확하게 답변하는가?
• 재현 가능한 테스트 케이스를 제공하는가? (가산점)

답변은 quiz@ex-em.com 으로 보내주시면 됩니다.( 댓글은 금지되어있습니다^^;)

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

--------------------------------------------------------------------

<퀴즈 당첨자>

임**  ons***@gmail.com

본 퀴즈의 정답은 2개 입니다. 하지만 많은 분들께서 정답을 하나만 보내주셨는데요,^^; 그래도 하나의 정답을 가장 먼저 보내주신 분께 당첨의 영광을  드리도록 하겠습니다!

엑셈의 이벤트에 참여해주신 모든 분들께 진심으로 감사드립니다. 

2개의 정답이 아래에 공개됩니다!  

<퀴즈 정답>

이번 문제의 정답은 2개입니다.  

1. 이미 Non Unique 인덱스가 존재하는 컬럼에 대해 Unique Constraint를 부여하는 경우
2. Deferrable Constraint를 선언하는 경우

자세한 설명은 예제를 통해 하겠습니다.

1. Non Unique 인덱스를 먼저 만들고 Unique Constraint를 생성하는 경우입니다.

SQL> create table t1(c1 number);

Table created.

SQL> create index t1_n1 on t1(c1);

Index created.

SQL> alter table t1 add constraint t1_un unique(c1) using index t1_n1;

Table altered.

-- 또는 이렇게도 할 수 있습니다. 
SQL> alter table t1 add constraint t1_un unique(c1) using index(create index t1_n1 on t1(c1));

SQL> select index_name, uniqueness from user_indexes where table_name = 'T1';

INDEX_NAME           UNIQUENES
-------------------- ---------
T1_N1                NONUNIQUE

2. Unique Constraint를 Deferrable로 선언하는 경우입니다.

SQL> create table t1(c1 number);

Table created.

SQL> alter table t1 add constraint t1_un unique(c1) deferrable;

Table altered.

SQL> select index_name, uniqueness from user_indexes where table_name = 'T1';

INDEX_NAME           UNIQUENES
-------------------- ---------
T1_UN                NONUNIQUE

Deferrable이 가능하려면 Unique Index가 존재해서는 안됩니다. 따라서 어쩔 수 없이 Non Unique 인덱스를 만드는 것입니다. 단, Deferrable Constraint라고 하더라도 아직 Deferred된 상태는 아니므로 Constraint는 정상적으로 동작합니다.

SQL> insert into t1 values(1);

1 row created.

SQL> insert into t1 values(1);
insert into t1 values(1)
*
ERROR at line 1:
ORA-00001: unique constraint (TPACK.T1_UN) violated

다음과 같이 SET CONSTRAINT 명령을 통해서 명시적으로 Deferred 시킬 수 있습니다.(물론 Constraint 생성시에 Deferred 상태를 지정할 수도 있습니다)

SQL> set constraint t1_un deferred;

Constraint set.

SQL> insert into t1 values(1);

1 row created.

Commit할 때 에러가 발생합니다.

SQL> commit;
commit
*
ERROR at line 1:
ORA-02091: transaction rolled back
ORA-00001: unique constraint (TPACK.T1_UN) violated

Constraint를 생성할 때 NOVALIDATE 옵션을 부여하면 기존의 데이터들에 대해서는 Constraint를 확인하지 않습니다. 하지만 다음과 같이 Unique Constraint를 NOVALIDATE로 생성해도 Duplicate Key에러가 발생합니다.

SQL> create table t1(c1 number);

Table created.

SQL> insert into t1 values(1);

1 row created.

-- 중복값 추가!
SQL> insert into t1 values(1); 

1 row created.

SQL> commit;

Commit complete.

SQL> alter table t1 add constraint t1_un unique(c1) enable novalidate;
alter table t1 add constraint t1_un unique(c1) enable novalidate
                              *
ERROR at line 1:
ORA-02299: cannot validate (TPACK.T1_UN) - duplicate keys found

왜 그럴까요? 힌트는 위에서 이미 제시되었습니다. Unique Constraint를 생성할 때 오라클은 가능한 한 Unique 인덱스를 만듭니다. 즉 위의 에러는 Unique 인덱스를 만드는 과정에서 생기는 것으로 볼 수 있습니다. 따라서 Unique 인덱스가 생기지 않게 하면 위의 에러를 피할 수 있을 것입니다.

즉 다음과 같이 Deferrable 속성을 부여하면 됩니다.

SQL> alter table t1 add constraint t1_un unique(c1) deferrable enable novalidate;

Table altered.

한가지 주의할 것은 이런 경우 Constraint을 지워도 인덱스는 남아있다는 것입니다.

SQL> alter table t1 drop constraint t1_un;

Table altered.

SQL> select index_name, uniqueness from user_indexes where table_name = 'T1';

INDEX_NAME           UNIQUENES
-------------------- ---------
T1_UN                NONUNIQUE

또는 다음과 같이 USING INDEX 구문을 이용해서 명시적으로 Non Unique 인덱스를 생성하면 됩니다.

SQL> drop index t1_un;

Index dropped.

SQL> alter table t1 add constraint t1_un unique(c1) using index(create index t1_n1 on t1(c1)) enable novalidate ;

Table altered.

기존의 중복값들은 그대로 남아있고(NOVALIDATE), 새롭게 추가되는 데이터에 대해서만 Unique Constraint가 적용됩니다.

SQL> insert into t1 values(1);
insert into t1 values(1)
*
ERROR at line 1:
ORA-00001: unique constraint (TPACK.T1_UN) violated


SQL> select * from t1;

        C1
----------
         1
         1