Week1_이진우

조회 수 28176 추천 수 0 2013.09.06 23:29:12
ljw *.229.63.12
제목 작성자 날짜
Week1_이진우 (current) ljw 2013.09.06 23:29:12
복원
<span id="internal-source-marker_0.9205300372559577"><p style="line-height: normal; font-size: medium; "><span id="internal-source-marker_0.9205300372559577"><span style="font-weight: normal; font-size: 15px; font-family: Arial; vertical-align: baseline; white-space: pre-wrap; ">3과목</span><br /><span style="font-weight: normal; font-size: 15px; font-family: Arial; vertical-align: baseline; white-space: pre-wrap; ">1장</span><br /><span style="font-size: 15px; font-family: Arial; vertical-align: baseline; white-space: pre-wrap; ">1]버퍼 블록의 상태 종류중 틀린것은?</span><br /><span style="font-size: 15px; font-family: Arial; vertical-align: baseline; white-space: pre-wrap; ">1)Free 버퍼</span><br /><span style="font-size: 15px; font-family: Arial; vertical-align: baseline; white-space: pre-wrap; ">2)Dirty 버퍼</span><br /><span style="font-size: 15px; font-family: Arial; vertical-align: baseline; white-space: pre-wrap; ">3)Clean 버퍼</span><br /><span style="font-size: 15px; font-family: Arial; vertical-align: baseline; white-space: pre-wrap; ">4)Pinned버퍼</span><br /><span style="font-size: 15px; font-family: Arial; vertical-align: baseline; white-space: pre-wrap; font-weight: normal; font-size: 15px;"></span><br /><span style="font-weight: normal; font-size: 15px; font-family: Arial; vertical-align: baseline; white-space: pre-wrap; ">2]괄호 안에 알맞은 단어를 적으시오(단답형)</span><br /><span style="font-weight: normal; font-size: 15px; font-family: Arial; vertical-align: baseline; white-space: pre-wrap; ">사용자 정의 함수/프로시저는 내장함수처럼 Native 코드로 완전 컴파일된 형태가 아니어서</span><br /><span style="font-weight: normal; font-size: 15px; font-family: Arial; vertical-align: baseline; white-space: pre-wrap; ">가상머신 같은 별도의 실행엔진을 통해 실행된다. 실행 될 때마다 &nbsp;&nbsp;( &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;)이 일어나며,</span><br /><span style="font-weight: normal; font-size: 15px; font-family: Arial; vertical-align: baseline; white-space: pre-wrap; ">이 때문에 내장함수를 호출할 때와 비교해 성능을 상당히 떨어뜨린다.</span><br /><span style="font-size: 15px; font-family: Arial; vertical-align: baseline; white-space: pre-wrap; font-weight: normal; font-size: 15px;"></span></span></p><p style="font-weight: bold; line-height: normal; font-size: medium; "><br /></p><p style="line-height: normal; font-size: medium; ">3]sql형태에 대한 설명중 올바른 것을 고르시오(2개)</p><p style="line-height: normal; font-size: medium; ">1)java언어에서는 static sql문법을 제공한다.</p><p style="line-height: normal; font-size: medium; ">2)dbms입장에서는 static sql과 dynamic sql의 차이가 없다.</p><p style="line-height: normal; font-size: medium; ">3)dynamic sql을 embedded sql이라고도 한다</p><p style="line-height: normal; font-size: medium; ">4)static sql, dynamic sql 구분보다는 바인드 변수 사용이 더 중요하다<br /><b><span style="font-size: 15px; font-family: Arial; vertical-align: baseline; white-space: pre-wrap; font-size: 15px;"></span></b><br /><b><span style="font-size: 15px; font-family: Arial; vertical-align: baseline; white-space: pre-wrap; font-size: 15px;"></span></b><br /><span style="font-size: 15px; font-family: Arial; vertical-align: baseline; white-space: pre-wrap; ">(기출문제) 다음 괄호 안에 들어갈 올바른 용어의 순서로 올바른 것을 선택하세요</span><br /><span style="font-size: 15px; font-family: Arial; vertical-align: baseline; white-space: pre-wrap; font-size: 15px;"></span><br /><span style="font-size: 15px; font-family: Arial; vertical-align: baseline; white-space: pre-wrap; ">Response Time = ( &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;) + Wait Time</span><br /><span style="font-size: 15px; font-family: Arial; vertical-align: baseline; white-space: pre-wrap; "> &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;= ( &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;) + Queue Time</span><br /><span style="font-size: 15px; font-family: Arial; vertical-align: baseline; white-space: pre-wrap; font-size: 15px;"></span><br /><span style="font-size: 15px; font-family: Arial; vertical-align: baseline; white-space: pre-wrap; ">1) Service Time, Cpu Time &nbsp;</span><br /><span style="font-size: 15px; font-family: Arial; vertical-align: baseline; white-space: pre-wrap; ">2) Cpu Time, Service Time</span><br /><span style="font-size: 15px; font-family: Arial; vertical-align: baseline; white-space: pre-wrap; ">3) Idle Time, Service Time</span><br /><span style="font-size: 15px; font-family: Arial; vertical-align: baseline; white-space: pre-wrap; ">4) Elapsed Time, Cpu Time</span><br /><b><span style="font-size: 15px; font-family: Arial; vertical-align: baseline; white-space: pre-wrap; font-size: 15px;"></span></b><br /><b><span style="font-size: 15px; font-family: Arial; vertical-align: baseline; white-space: pre-wrap; font-size: 15px;"></span></b><br /><span style="font-size: 15px; font-family: Arial; vertical-align: baseline; white-space: pre-wrap; ">2장</span><br /><span style="font-size: 15px; font-family: Arial; vertical-align: baseline; white-space: pre-wrap; ">4]괄호안에 알맞은 단어를 적으시오(단답형)</span><br /><span style="font-size: 15px; font-family: Arial; vertical-align: baseline; white-space: pre-wrap; ">( &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;) 이란 관리할 Lock 리소스가 정해진 임계치를 넘으면서 로우 레벨 락이 페이지, 익스텐트, 테이블 레벨 락으로 점점 확장되는 것을 말한다.</span><br /><span style="font-size: 15px; font-family: Arial; vertical-align: baseline; white-space: pre-wrap; font-size: 15px;"></span><br /><span style="font-size: 15px; font-family: Arial; vertical-align: baseline; white-space: pre-wrap; ">5] 괄호안에 알맞은 공통된 단어를 적으시오(단답형)</span><br /><span style="font-size: 15px; font-family: Arial; vertical-align: baseline; white-space: pre-wrap; ">( &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;)은 다른 트랜잭션에 의해 데이터의 추가, 변경, 삭제가 발생하더라도 단일 SQL문 내에서 일관성 있게 값을 읽는 것을 말한다. ( &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;)의 기준 시점은 쿼리 시작 시점이 된다.</span><br /><span style="font-size: 15px; font-family: Arial; vertical-align: baseline; white-space: pre-wrap; font-size: 15px;"></span><br />6]snap shot too old에 대한 설명으로 틀린 것을 고르시오</p><p style="line-height: normal; font-size: medium; ">1)undo 영역의 크기를 증가시킴으로써 snapshot too old 발생을 줄일 수 있다.</p><p style="line-height: normal; font-size: medium; ">2)long run select 쿼리가 snapshot too old를 발생시킨다면 쿼리 튜닝을 통해</p><p style="line-height: normal; font-size: medium; ">&nbsp; &nbsp; snapshot too old발생을 해결할수 있다.</p><p style="line-height: normal; font-size: medium; ">3)대량 업데이트후 해당 테이블을 full scan을 하게되면 block clean out 실패로 인한</p><p style="line-height: normal; font-size: medium; ">&nbsp; &nbsp; snapshot too old 발생을 방지할 수 있다.</p><p style="line-height: normal; font-size: medium; ">4)오라클의 경우 CR 블록을 생성하지 않기 때문에 snapshot too old 에러가 발생한다.</p><p style="line-height: normal; font-size: medium; "><br /><b><span style="font-size: 15px; font-family: Arial; vertical-align: baseline; white-space: pre-wrap; font-size: 15px;"></span></b><br /><span style="font-weight: bold; font-size: 15px; font-family: Arial; vertical-align: baseline; white-space: pre-wrap; ">기출문제 트랜잭션의 특징</span><br /><span style="font-weight: bold; font-size: 15px; font-family: Arial; vertical-align: baseline; white-space: pre-wrap; ">원자성 / 일관성 / 격리성 / 영속성</span><br /><b><span style="font-size: 15px; font-family: Arial; vertical-align: baseline; white-space: pre-wrap; font-size: 15px;"></span></b><br /><b><span style="font-size: 15px; font-family: Arial; vertical-align: baseline; white-space: pre-wrap; font-size: 15px;"></span></b><br /><span style="font-size: 15px; font-family: Arial; vertical-align: baseline; white-space: pre-wrap; ">3장</span><br /><span style="font-size: 15px; font-family: Arial; vertical-align: baseline; white-space: pre-wrap; ">7] 다음 sql 문장을 실행한 결과 emp 테이블을 table full scan하는 실행계획이 나왔다.</span><br /><span style="font-size: 15px; font-family: Arial; vertical-align: baseline; white-space: pre-wrap; ">실행결과와 sql문의 형태만을 보고 유추한 사실중 올바른것 2개를 고르시오</span><br /><span style="font-size: 15px; font-family: Arial; vertical-align: baseline; white-space: pre-wrap; ">select * </span><br /><span style="font-size: 15px; font-family: Arial; vertical-align: baseline; white-space: pre-wrap; "> &nbsp;from emp</span><br /><span style="font-size: 15px; font-family: Arial; vertical-align: baseline; white-space: pre-wrap; ">where job = ‘CLERK’ or deptno=20;</span><br /><span style="font-size: 15px; font-family: Arial; vertical-align: baseline; white-space: pre-wrap; font-size: 15px;"></span><br /><span style="font-size: 15px; font-family: Arial; vertical-align: baseline; white-space: pre-wrap; ">1)emp 테이블에 인덱스가 전혀 없을수도 있다.</span><br /><span style="font-size: 15px; font-family: Arial; vertical-align: baseline; white-space: pre-wrap; ">2)인덱스가 한개만 있더라도 table full scan 실행계획이 나올 수 있다.</span><br /><span style="font-size: 15px; font-family: Arial; vertical-align: baseline; white-space: pre-wrap; ">3)select 절에 * 대신 job, deptno으로 적어주면 table full scan 실행계획이 나타나지 않는다.</span><br /><span style="font-size: 15px; font-family: Arial; vertical-align: baseline; white-space: pre-wrap; ">4)or 조건을 and로 바꿔야 table full scan 실행계획이 사라지고, 결과 값도 동일하다.</span><br /><b><span style="font-size: 15px; font-family: Arial; vertical-align: baseline; white-space: pre-wrap; font-size: 15px;"></span></b><br />8)다음 sql과 실행계획에 대한 설명중 올바른 것을 고르시오</p><p style="font-weight: bold; line-height: normal; font-size: medium; "><span style="color: rgb(68, 68, 68); font-family: monospace; font-size: 12px; line-height: 16px; background-color: rgb(244, 244, 244); ">select deptno, avg_sal&nbsp;</span></p><p style="font-weight: bold; line-height: normal; font-size: medium; "><span style="color: rgb(68, 68, 68); font-family: monospace; font-size: 12px; line-height: 16px; background-color: rgb(244, 244, 244); ">&nbsp; from (select deptno, avg(sal) avg_sal</span></p><p style="font-weight: bold; line-height: normal; font-size: medium; "><span style="color: rgb(68, 68, 68); font-family: monospace; font-size: 12px; line-height: 16px; background-color: rgb(244, 244, 244); ">&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; from emp group by deptno) a</span></p><p style="font-weight: bold; line-height: normal; font-size: medium; "><span style="color: rgb(68, 68, 68); font-family: monospace; font-size: 12px; line-height: 16px; background-color: rgb(244, 244, 244); ">where deptno = 30&nbsp;</span></p><p style="line-height: normal; font-size: medium; "><span style="color: rgb(68, 68, 68); font-family: monospace; font-size: 12px; line-height: 16px; background-color: rgb(244, 244, 244); ">---------------------------------------------------------------------------&nbsp;</span></p><p style="line-height: normal; font-size: medium; "><span style="color: rgb(68, 68, 68); font-family: monospace; font-size: 12px; line-height: 16px; background-color: rgb(244, 244, 244); ">| Id | Operation&nbsp;<span class="Apple-tab-span" style="white-space: pre; "> </span>| Name &nbsp;<span class="Apple-tab-span" style="white-space: pre; "> </span>&nbsp;&nbsp;| Rows| Bytes |&nbsp;</span></p><p style="line-height: normal; font-size: medium; "><span style="color: rgb(68, 68, 68); font-family: monospace; font-size: 12px; line-height: 16px; background-color: rgb(244, 244, 244); ">---------------------------------------------------------------------------</span></p><p style="line-height: normal; font-size: medium; "><span style="color: rgb(68, 68, 68); font-family: monospace; font-size: 12px; line-height: 16px; background-color: rgb(244, 244, 244); ">| 0 | SELECT STATEMENT&nbsp;<span class="Apple-tab-span" style="white-space: pre; "> </span>|&nbsp;<span class="Apple-tab-span" style="white-space: pre; "> </span>&nbsp;&nbsp;| 1&nbsp;<span class="Apple-tab-span" style="white-space: pre; "> </span>| &nbsp; &nbsp;26 |&nbsp;</span></p><p style="line-height: normal; font-size: medium; "><span style="color: rgb(68, 68, 68); font-family: monospace; font-size: 12px; line-height: 16px; background-color: rgb(244, 244, 244); ">| 1 | &nbsp;VIEW&nbsp;<span class="Apple-tab-span" style="white-space: pre; "> </span>|&nbsp;<span class="Apple-tab-span" style="white-space: pre; "> </span>&nbsp;&nbsp;</span><b id="internal-source-marker_0.9205300372559577" style="font-weight: normal; "><b id="internal-source-marker_0.9205300372559577" style="font-weight: normal; "><span style="color: rgb(68, 68, 68); font-family: monospace; font-size: 12px; line-height: 16px; background-color: rgb(244, 244, 244); ">| 1&nbsp;<span class="Apple-tab-span" style="white-space: pre; "> </span>| &nbsp; &nbsp;26 |&nbsp;</span></b></b></p><p></p><p style="font-weight: bold; line-height: normal; font-size: medium; "></p><p style="font-weight: bold; line-height: normal; font-size: medium; "><b style="font-weight: normal; "></b></p><p style="font-weight: bold; line-height: normal; font-size: medium; display: inline !important; "><b style="font-weight: normal; "><span style="color: rgb(68, 68, 68); font-family: monospace; font-size: 12px; line-height: 16px; background-color: rgb(244, 244, 244); ">| 2 | &nbsp; SORT GROUP BY NOSORT&nbsp;<span class="Apple-tab-span" style="white-space: pre; "> </span>|&nbsp;<span class="Apple-tab-span" style="white-space: pre; "> </span>&nbsp;&nbsp;| 1&nbsp;<span class="Apple-tab-span" style="white-space: pre; "> </span>| &nbsp; &nbsp; 7 |&nbsp;</span></b></p><p style="font-weight: bold; line-height: normal; font-size: medium; "></p><p style="font-weight: bold; line-height: normal; font-size: medium; "><b style="font-weight: normal; "></b></p><p style="font-weight: bold; line-height: normal; font-size: medium; display: inline !important; "><b style="font-weight: normal; "><span style="color: rgb(68, 68, 68); font-family: monospace; font-size: 12px; line-height: 16px; background-color: rgb(244, 244, 244); ">| 3 | &nbsp; &nbsp;TABLE ACCESS BY INDEX ROWID&nbsp;<span class="Apple-tab-span" style="white-space: pre; "> </span>| EMP&nbsp;<span class="Apple-tab-span" style="white-space: pre; "> </span>&nbsp;&nbsp;| 6&nbsp;<span class="Apple-tab-span" style="white-space: pre; "> </span>| &nbsp; &nbsp;42 |&nbsp;</span></b></p><p style="font-weight: bold; line-height: normal; font-size: medium; "></p><p style="font-weight: bold; line-height: normal; font-size: medium; "><b style="font-weight: normal; "></b></p><p style="font-weight: bold; line-height: normal; font-size: medium; display: inline !important; "><b style="font-weight: normal; "><span style="color: rgb(68, 68, 68); font-family: monospace; font-size: 12px; line-height: 16px; background-color: rgb(244, 244, 244); ">|* 4 | &nbsp; &nbsp; INDEX RANGE SCAN&nbsp;<span class="Apple-tab-span" style="white-space: pre; "> </span>| EMP_DEPTNO_IDX &nbsp;| 6&nbsp;<span class="Apple-tab-span" style="white-space: pre; "> </span>| &nbsp; &nbsp; &nbsp; |</span></b></p><p style="font-weight: bold; line-height: normal; font-size: medium; "></p><p style="font-weight: bold; line-height: normal; font-size: medium; "><b style="font-weight: normal; "></b></p><p style="font-weight: bold; line-height: normal; font-size: medium; display: inline !important; "><b style="font-weight: normal; "><span style="color: rgb(68, 68, 68); font-family: monospace; font-size: 12px; line-height: 16px; background-color: rgb(244, 244, 244); ">| ------------------------------------------------------------------------&nbsp;</span></b></p><p style="font-weight: bold; line-height: normal; font-size: medium; "></p><p style="font-weight: bold; line-height: normal; font-size: medium; "><b style="font-weight: normal; "></b></p><p style="font-weight: bold; line-height: normal; font-size: medium; display: inline !important; "><b style="font-weight: normal; "><span style="color: rgb(68, 68, 68); font-family: monospace; font-size: 12px; line-height: 16px; background-color: rgb(244, 244, 244); ">Predicate Information (identified by operation id):&nbsp;</span></b></p><p style="font-weight: bold; line-height: normal; font-size: medium; "></p><p style="font-weight: bold; line-height: normal; font-size: medium; "><b style="font-weight: normal; "></b></p><p style="font-weight: bold; line-height: normal; font-size: medium; display: inline !important; "><b style="font-weight: normal; "><span style="color: rgb(68, 68, 68); font-family: monospace; font-size: 12px; line-height: 16px; background-color: rgb(244, 244, 244); ">-------------------------------------------------------------------------&nbsp;</span></b></p><p style="font-weight: bold; line-height: normal; font-size: medium; "></p><p style="font-weight: bold; line-height: normal; font-size: medium; "><b style="font-weight: normal; "></b></p><p style="font-weight: bold; line-height: normal; font-size: medium; display: inline !important; "><b style="font-weight: normal; "><span style="color: rgb(68, 68, 68); font-family: monospace; font-size: 12px; line-height: 16px; background-color: rgb(244, 244, 244); ">4 - access("DEPTNO"=30)</span></b></p><p style="font-weight: bold; line-height: normal; font-size: medium; "></p><p style="font-weight: bold; line-height: normal; font-size: medium; "><br /></p><p style="line-height: normal; font-size: medium; ">1)조건절(Predicate) pullup이 발생했다.</p><p style="line-height: normal; font-size: medium; ">2)emp테이블을 deptno컬럼을 기준으로 그룹함수를 사용하므로</p><p style="line-height: normal; font-size: medium; ">&nbsp; &nbsp;emp 테이블의 모든 데이터를 읽어 효율적이지 못한 쿼리이다.</p><p style="line-height: normal; font-size: medium; ">3)실행계획에 나와있는 sort group by nosort operation을 통해&nbsp;</p><p style="line-height: normal; font-size: medium; ">&nbsp; &nbsp; view 바깥에 있는 deptno=30 조건이 view 안쪽으로 조건절 pushdown된 것을</p><p style="line-height: normal; font-size: medium; ">&nbsp; &nbsp; 알 수 있다.</p><p style="line-height: normal; font-size: medium; ">4)조건절(deptno = 30)에 해당하는 부분의 데이터만 읽는 효율적인 쿼리이다.</p><p style="line-height: normal; font-size: medium; "><span style="font-size: 15px; font-family: Arial; vertical-align: baseline; white-space: pre-wrap; "></span><br /><span style="font-size: 15px; font-family: Arial; vertical-align: baseline; white-space: pre-wrap; ">9] 주관식</span><br /><span style="font-size: 15px; font-family: Arial; vertical-align: baseline; white-space: pre-wrap; ">다음 SQL문의 형태와 인덱스 컬럼 타입을 보고 나타날수 있는 비효율을 서술하고</span><br /><span style="font-size: 15px; font-family: Arial; vertical-align: baseline; white-space: pre-wrap; ">튜닝할수 수단을 서술(인덱스 튜닝 등) / SQL을 재작성 하시오</span><br /><span style="font-size: 15px; font-family: Arial; vertical-align: baseline; white-space: pre-wrap; "></span><br /><span style="font-size: 15px; font-family: Arial; vertical-align: baseline; white-space: pre-wrap; ">-- 인덱스 구성 : n1 ( col2 + col3 + col1)</span><br /><span style="font-size: 15px; font-family: Arial; vertical-align: baseline; white-space: pre-wrap; ">-- 컬럼 타입 : col1, col2, col3 : varchar2</span><br /><span style="font-size: 15px; font-family: Arial; vertical-align: baseline; white-space: pre-wrap; "> &nbsp;&nbsp;&nbsp;</span><br /><span style="font-size: 15px; font-family: Arial; vertical-align: baseline; white-space: pre-wrap; ">select col1, col2, col3</span><br /><span style="font-size: 15px; font-family: Arial; vertical-align: baseline; white-space: pre-wrap; "> from t1</span><br /><span style="font-size: 15px; font-family: Arial; vertical-align: baseline; white-space: pre-wrap; ">where col1 = 'CD111'</span><br /><span style="font-size: 15px; font-family: Arial; vertical-align: baseline; white-space: pre-wrap; "> &nbsp;and to_date(col2, 'YYYYMMDD') between to_date('20120701', 'YYYYMMDD') and to_date('20120731', 'YYYYMMDD')</span><br /><span style="font-size: 15px; font-family: Arial; vertical-align: baseline; white-space: pre-wrap; "> &nbsp;and col3 = 'AA'</span><br /><span style="font-size: 15px; font-family: Arial; vertical-align: baseline; white-space: pre-wrap; "></span><br /><span style="font-size: 15px; font-family: Arial; vertical-align: baseline; white-space: pre-wrap; ">union all</span><br /><span style="font-size: 15px; font-family: Arial; vertical-align: baseline; white-space: pre-wrap; "></span><br /><span style="font-size: 15px; font-family: Arial; vertical-align: baseline; white-space: pre-wrap; ">select col1, col2, col3</span><br /><span style="font-size: 15px; font-family: Arial; vertical-align: baseline; white-space: pre-wrap; "> from t1</span><br /><span style="font-size: 15px; font-family: Arial; vertical-align: baseline; white-space: pre-wrap; ">where col1 = 'CD112'</span><br /><span style="font-size: 15px; font-family: Arial; vertical-align: baseline; white-space: pre-wrap; "> &nbsp;and to_date(col2, 'YYYYMMDD') between to_date('20120701', 'YYYYMMDD') and to_date('20120731', 'YYYYMMDD')</span><br /><span style="font-size: 15px; font-family: Arial; vertical-align: baseline; white-space: pre-wrap; "> &nbsp;and col3 = 'AA'</span><br /><span style="font-size: 15px; font-family: Arial; vertical-align: baseline; white-space: pre-wrap; "></span><br /><span style="font-size: 15px; font-family: Arial; vertical-align: baseline; white-space: pre-wrap; "></span><br /><span style="font-size: 15px; font-family: Arial; vertical-align: baseline; white-space: pre-wrap; "></span><br /><span style="font-size: 15px; font-family: Arial; vertical-align: baseline; white-space: pre-wrap; ">4장</span><br /><span style="font-size: 15px; font-family: Arial; vertical-align: baseline; white-space: pre-wrap; ">10]인덱스에 대한 설명중 틀린것을 고르시오(2개)</span><br /><span style="font-size: 15px; font-family: Arial; vertical-align: baseline; white-space: pre-wrap; ">1)잦은 데이터 삭제로 Index Sparse 현상이 나타날수 있다.</span><br /><span style="font-size: 15px; font-family: Arial; vertical-align: baseline; white-space: pre-wrap; ">2)인덱스에는 null값 자체가 저장될수 없다.</span><br /><span style="font-size: 15px; font-family: Arial; vertical-align: baseline; white-space: pre-wrap; ">3)비트맵 인덱스는 b-tree인덱스에 비해 상대적으로 저장 공간을 적게 차지한다.</span><br /><span style="font-size: 15px; font-family: Arial; vertical-align: baseline; white-space: pre-wrap; ">4)비트맵 인덱스는 주로 값의 종류가 많은 경우에 유리하다.</span><br /><span style="font-size: 15px; font-family: Arial; vertical-align: baseline; white-space: pre-wrap; "></span><br /><span style="font-size: 15px; font-family: Arial; vertical-align: baseline; white-space: pre-wrap; ">11]다음 칸에 알맞은 공통된 단어를 넣으시오</span><br /><span style="font-size: 15px; font-family: Arial; vertical-align: baseline; white-space: pre-wrap; ">Oracle IOT를 개발하면서 두가지 액세스 방식을 모두 사용할 수있도록 설계하였다. IOT 레코드의 위치는 영구적이지 않기 때문에 물리적 주소(ROWID)대신 &nbsp;( &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;)를 사용한다.</span><br /><span style="font-size: 15px; font-family: Arial; vertical-align: baseline; white-space: pre-wrap; ">( &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;)는 PK와 &nbsp;physical guess로 구성된다.</span><br /><span style="font-size: 15px; font-family: Arial; vertical-align: baseline; white-space: pre-wrap; "></span><br />12]인덱스 구성에 대한 설명중 올바른 것을 고르시오</p><p style="line-height: normal; font-size: medium; ">1)unique인덱스는 단일컬럼으로 구성되어야한다.</p><p style="line-height: normal; font-size: medium; ">2)order by, group by를 위한 소트 연산을 줄이기 위해 인덱스에 컬럼을 추가하기도 한다.</p><p style="line-height: normal; font-size: medium; ">3)조건절에 기술된 컬럼에 의해 선택도가 높은 인덱스가 있을경우 table full scan보다 해당</p><p style="line-height: normal; font-size: medium; ">&nbsp; &nbsp;인덱스를 사용하는 것이 좋다.</p><p style="line-height: normal; font-size: medium; ">4)<b id="internal-source-marker_0.9205300372559577" style="font-weight: normal; "><b id="internal-source-marker_0.9205300372559577" style="font-weight: normal; "><b id="internal-source-marker_0.9205300372559577" style="font-weight: normal; ">인덱스 선두 컬럼은 범위조건으로 사용되는 컬럼이 위치하는 것이 인덱스 활용에 유리하다</b></b></b></p><p></p><p style="font-weight: bold; line-height: normal; "></p><b style="font-weight: normal; line-height: normal; "><span style="font-size: 15px; font-family: Arial; vertical-align: baseline; white-space: pre-wrap; font-size: 15px;"></span></b><br /><br /><span style="font-weight: bold; line-height: normal; font-size: 15px; font-family: Arial; vertical-align: baseline; white-space: pre-wrap; ">문자 vs 숫자 : 문자--&gt; 숫자</span><br /><span style="font-weight: bold; line-height: normal; font-size: 15px; font-family: Arial; vertical-align: baseline; white-space: pre-wrap; ">문자 vs 날짜 : 문자--&gt; 날짜</span><br /><b style="font-weight: normal; line-height: normal; "><span style="font-size: 15px; font-family: Arial; vertical-align: baseline; white-space: pre-wrap; font-size: 15px;"></span></b><br /><b style="line-height: normal; "><span style="font-size: 15px; font-family: Arial; vertical-align: baseline; white-space: pre-wrap; font-size: 15px;"></span></b><br /><span style="line-height: normal; font-size: 15px; font-family: Arial; vertical-align: baseline; white-space: pre-wrap; ">5장</span><br /><span style="line-height: normal; font-size: 15px; font-family: Arial; vertical-align: baseline; white-space: pre-wrap; ">13]top-n sql 구현을 통해 소트 연산 횟수를 최소화하여 소트 영역 사용량을 최소화 하려고 한다. 다음 쿼리 문과 sql문을 보고 올바른 설명을 고르시오</span><br /><span style="line-height: normal; font-size: 15px; font-family: Arial; vertical-align: baseline; white-space: pre-wrap; ">(인덱스 구성은 다음과 같다. &nbsp;&nbsp;pk_emp : empno)</span><br /><span style="line-height: normal; "><span style="font-size: 15px; font-family: Arial; vertical-align: baseline; white-space: pre-wrap; font-size: 15px;"></span></span><br /><p style="line-height: normal; "></p><p dir="ltr" style="line-height: normal; margin-top: 0pt; margin-bottom: 0pt; font-size: medium; text-align: justify; "><span style="font-size: 15px; font-family: Arial; background-color: rgb(255, 255, 255); vertical-align: baseline; white-space: pre-wrap; ">select *</span></p><p dir="ltr" style="line-height: normal; margin-top: 0pt; margin-bottom: 0pt; font-size: medium; text-align: justify; "><span style="font-size: 15px; font-family: Arial; background-color: rgb(255, 255, 255); vertical-align: baseline; white-space: pre-wrap; "> from </span></p><p dir="ltr" style="line-height: normal; margin-top: 0pt; margin-bottom: 0pt; font-size: medium; text-align: justify; "><span style="font-size: 15px; font-family: Arial; background-color: rgb(255, 255, 255); vertical-align: baseline; white-space: pre-wrap; "><span class="Apple-tab-span" style="white-space: pre; "> </span>(select rownum rn</span></p><p dir="ltr" style="line-height: normal; margin-top: 0pt; margin-bottom: 0pt; font-size: medium; text-align: justify; "><span style="font-size: 15px; font-family: Arial; background-color: rgb(255, 255, 255); vertical-align: baseline; white-space: pre-wrap; "><span class="Apple-tab-span" style="white-space: pre; "> </span> &nbsp;&nbsp;from </span></p><p dir="ltr" style="line-height: normal; margin-top: 0pt; margin-bottom: 0pt; font-size: medium; text-align: justify; "><span style="font-size: 15px; font-family: Arial; background-color: rgb(255, 255, 255); vertical-align: baseline; white-space: pre-wrap; "><span class="Apple-tab-span" style="white-space: pre; "> </span><span class="Apple-tab-span" style="white-space: pre; "> </span>(select /*+ index (emp pk_emp) */ *</span></p><p dir="ltr" style="line-height: normal; margin-top: 0pt; margin-bottom: 0pt; font-size: medium; text-align: justify; "><span style="font-size: 15px; font-family: Arial; background-color: rgb(255, 255, 255); vertical-align: baseline; white-space: pre-wrap; "><span class="Apple-tab-span" style="white-space: pre; "> </span><span class="Apple-tab-span" style="white-space: pre; "> </span> from emp</span></p><p dir="ltr" style="line-height: normal; margin-top: 0pt; margin-bottom: 0pt; font-size: medium; text-align: justify; "><span style="font-size: 15px; font-family: Arial; background-color: rgb(255, 255, 255); vertical-align: baseline; white-space: pre-wrap; "><span class="Apple-tab-span" style="white-space: pre; "> </span><span class="Apple-tab-span" style="white-space: pre; "> </span>order by empno )</span></p><p dir="ltr" style="line-height: normal; margin-top: 0pt; margin-bottom: 0pt; font-size: medium; text-align: justify; "><span style="font-size: 15px; font-family: Arial; background-color: rgb(255, 255, 255); vertical-align: baseline; white-space: pre-wrap; "><span class="Apple-tab-span" style="white-space: pre; "> </span> &nbsp;)</span></p><p dir="ltr" style="line-height: normal; margin-top: 0pt; margin-bottom: 0pt; font-size: medium; text-align: justify; "><span style="font-size: 15px; font-family: Arial; background-color: rgb(255, 255, 255); vertical-align: baseline; white-space: pre-wrap; "> where &nbsp;rn between 21 and 30;</span></p><p style="line-height: normal; font-size: medium; "><span style="font-size: 15px; font-family: Arial; background-color: rgb(255, 255, 255); vertical-align: baseline; white-space: pre-wrap; "></span><span style="font-size: 15px; font-family: Verdana; background-color: rgb(255, 255, 255); vertical-align: baseline; white-space: pre-wrap; ">
<span id="internal-source-marker_0.9205300372559577" style="font-family: Gulim; white-space: normal; font-size: medium; "><span style="font-size: 15px; font-family: Verdana; vertical-align: baseline; white-space: pre-wrap; ">------------------------------------------</span></span>
| Id &nbsp;| Operation &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<span class="Apple-tab-span" style="white-space: pre; "> </span>
<span id="internal-source-marker_0.9205300372559577" style="font-family: Gulim; white-space: normal; font-size: medium; "><span style="font-size: 15px; font-family: Verdana; vertical-align: baseline; white-space: pre-wrap; ">------------------------------------------</span></span>
| &nbsp;&nbsp;0 | SELECT STATEMENT <br />|* &nbsp;1 | &nbsp;VIEW &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<span class="Apple-tab-span" style="white-space: pre; "> </span><br />| &nbsp;&nbsp;2 | &nbsp;&nbsp;</span><span style="font-size: 15px; font-family: Verdana; background-color: rgb(255, 255, 255); text-decoration: underline; vertical-align: baseline; white-space: pre-wrap; "><b>COUNT</b></span><span style="font-size: 15px; font-family: Verdana; background-color: rgb(255, 255, 255); vertical-align: baseline; white-space: pre-wrap; "><b> &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<span class="Apple-tab-span" style="white-space: pre; "> </span></b><br />| &nbsp;&nbsp;3 | &nbsp;&nbsp;&nbsp;VIEW &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<span class="Apple-tab-span" style="white-space: pre; "> </span><br />| &nbsp;&nbsp;4 | &nbsp;&nbsp;&nbsp;&nbsp;INDEX FULL SCAN( PK_EMP )<br />------------------------------------------<br /> <br />Predicate Information (identified by operation id):<br />---------------------------------------------------<br /> <br /> &nbsp;&nbsp;1 - filter("RN"&lt;=30 AND "RN"&gt;=21)</span><span style="font-size: 15px; font-family: Arial; vertical-align: baseline; white-space: pre-wrap; "></span><br /><span style="font-size: 15px; font-family: Arial; vertical-align: baseline; white-space: pre-wrap; "></span><br /><span style="font-size: 15px; font-family: Arial; vertical-align: baseline; white-space: pre-wrap; ">1)위의 실행계획을 보면 정상적으로 top-n효과가 나타난 것을 확인 할 수 있다.</span><br /><span style="font-size: 15px; font-family: Arial; vertical-align: baseline; white-space: pre-wrap; ">2)인라인 뷰의 중첩된 사용으로 인해 &nbsp;top-n효과가 나타나지 않았다.</span><br /><span style="font-size: 15px; font-family: Arial; vertical-align: baseline; white-space: pre-wrap; ">3)인덱스 사용으로 top-n효과가 나타났다.</span><br /><span style="font-size: 15px; font-family: Arial; vertical-align: baseline; white-space: pre-wrap; ">4)filter predicate을 통해 &nbsp;top-n효과가 정상적으로 나타나지 않은것을 알 수 있다.</span><br /><span style="font-size: 15px; font-family: Arial; vertical-align: baseline; white-space: pre-wrap; "></span><br /><span style="font-size: 15px; font-family: Arial; vertical-align: baseline; white-space: pre-wrap; "></span><br /><span style="font-size: 15px; font-family: Arial; vertical-align: baseline; white-space: pre-wrap; ">14]다음 쿼리에 대해 올바른 설명을 고르시오</span><br /><span style="font-size: 15px; font-family: Arial; vertical-align: baseline; white-space: pre-wrap; ">select /*+ pq_distribute(e, none, none) */ *</span><br /><span style="font-size: 15px; font-family: Arial; vertical-align: baseline; white-space: pre-wrap; "> from dept d, emp e</span><br /><span style="font-size: 15px; font-family: Arial; vertical-align: baseline; white-space: pre-wrap; ">where e.deptno = d.deptno</span><br /><span style="font-size: 15px; font-family: Arial; vertical-align: baseline; white-space: pre-wrap; "></span><br /><span style="font-size: 15px; font-family: Arial; vertical-align: baseline; white-space: pre-wrap; ">1)병렬관련 힌트를 사용하여 emp 테이블을 먼저 읽는 형태(outter table)로 실행된다.</span><br /><span style="font-size: 15px; font-family: Arial; vertical-align: baseline; white-space: pre-wrap; ">2)두번째 파라미터는 inner table의 병렬도를 설정하는 값으로, none 값을 주어 optimizer가</span><br /><span style="font-size: 15px; font-family: Arial; vertical-align: baseline; white-space: pre-wrap; "> &nbsp;&nbsp;&nbsp;스스로 판단하도록 유도 했다.</span><br /><span style="font-size: 15px; font-family: Arial; vertical-align: baseline; white-space: pre-wrap; ">3)세번째 파라미터는 outer 테이블의 distirbution 방식을 지정하는 값으로 none 값을 주어</span><br /><span style="font-size: 15px; font-family: Arial; vertical-align: baseline; white-space: pre-wrap; "> &nbsp;&nbsp;&nbsp;optimizer가 스스로 판단하도록 유도 했다.</span><br /><span style="font-size: 15px; font-family: Arial; vertical-align: baseline; white-space: pre-wrap; ">4)full-partition wise join을 유도한 힌트이다.</span></p><p style="line-height: normal; font-size: medium; "><span style="font-size: 15px; font-family: Arial; vertical-align: baseline; white-space: pre-wrap; "><br /></span></p><p style="line-height: normal; font-size: medium; "><span style="font-size: 15px; font-family: Arial; vertical-align: baseline; white-space: pre-wrap; ">15]다음 sql문에 대한 설명으로 틀린 것을 고르시오</span></p><p style="line-height: normal; font-size: medium; "><span style="font-size: 15px; font-family: Arial; vertical-align: baseline; white-space: pre-wrap; ">(table emp : 사원 테이블)</span></p><p style="line-height: normal; font-size: medium; "><span style="font-size: 15px; font-family: Arial; vertical-align: baseline; white-space: pre-wrap; ">(dept_no 컬럼 : 부서번호 )</span></p><p style="line-height: normal; font-size: medium; "><span style="font-size: 15px; font-family: Arial; vertical-align: baseline; white-space: pre-wrap; "><br /></span></p><p style="line-height: normal; "><font face="Arial"><span style="font-size: 14.857142448425293px; white-space: pre-wrap; ">select dept_no, count(*) cnt,</span></font></p><p style="line-height: normal; "><font face="Arial"><span style="font-size: 14.857142448425293px; white-space: pre-wrap; "> (select count(*) from emp) totalEmp</span></font></p><p style="line-height: normal; "><font face="Arial"><span style="font-size: 14.857142448425293px; white-space: pre-wrap; ">from emp</span></font></p><p style="line-height: normal; "><font face="Arial"><span style="font-size: 14.857142448425293px; white-space: pre-wrap; ">group by dept_no</span></font></p><p style="line-height: normal; "><font face="Arial"><span style="font-size: 14.857142448425293px; white-space: pre-wrap; "><br /></span></font></p><p style="line-height: normal; "><font face="Arial"><span style="font-size: 14.857142448425293px; white-space: pre-wrap; ">1)부서번호, 부서별 사원수, 전체 사원수를 select한다.</span></font></p><p style="line-height: normal; "><font face="Arial"><span style="font-size: 14.857142448425293px; white-space: pre-wrap; ">2)select 절에 기술된 스칼라 서브쿼리의 경우 dept_no수만큼 실행된다.</span></font></p><p style="line-height: normal; "><font face="Arial"><span style="font-size: 14.857142448425293px; white-space: pre-wrap; ">3)일반적인 형태의 sql로 흠잡을데 없는 쿼리이다.</span></font></p><p style="line-height: normal; "><font face="Arial"><span style="font-size: 14.857142448425293px; white-space: pre-wrap; ">4)emp table을 두번 읽게되며 특히 스칼라 서브쿼리의 경우 같은 값을 반복적으로 실행하는 비효율이 발생한다.</span></font></p><p style="line-height: normal; font-size: medium; "><span style="font-size: 15px; font-family: Arial; vertical-align: baseline; white-space: pre-wrap; "><br /></span></p><p style="line-height: normal; font-size: medium; "><span style="font-size: 15px; font-family: Arial; vertical-align: baseline; white-space: pre-wrap; "><br /></span></p><p style="line-height: normal; font-size: medium; "><span style="font-size: 15px; font-family: Arial; vertical-align: baseline; white-space: pre-wrap; ">16] 다음 쿼리를 merge 문 으로 변경하시오</span></p><p style="font-weight: bold; line-height: normal; font-size: medium; "><br /></p><p><span style="vertical-align: baseline; font-size: 15.454545021057129px; line-height: normal; white-space: pre-wrap;"><font face="Arial">UPDATE /*+ BYPASS_UJVC */
(
SELECT A.EMP_ID,
A.TMP_MON,
B.DDCT_MON
FROM TABLE_A A,
TABLE_B B
WHERE A.C_CD = '10'
AND A.C_CD = B.C_CD
AND A.CD = B.PAYROLL_NO
AND A.EMP_ID = B.EMP_ID ) A
SET A.TMP_MON = A.DDCT_MON;</font></span></p><p style="font-weight: bold; line-height: normal; font-size: medium; "><span style="font-size: 15px; font-family: Arial; vertical-align: baseline; white-space: pre-wrap; ">
</span></p></span>
ljw 2012.07.25 10:39:51
복원
<span id="internal-source-marker_0.9205300372559577"><p style="font-weight: bold; line-height: normal; font-size: medium; "><b id="internal-source-marker_0.9205300372559577" style="font-weight: normal; "><span style="font-size: 15px; font-family: Arial; vertical-align: baseline; white-space: pre-wrap; ">3과목</span><br /><span style="font-size: 15px; font-family: Arial; vertical-align: baseline; white-space: pre-wrap; ">1장</span><br /><span style="font-size: 15px; font-family: Arial; vertical-align: baseline; white-space: pre-wrap; ">1]버퍼 블록의 상태 종류중 틀린것은?</span><br /><span style="font-size: 15px; font-family: Arial; vertical-align: baseline; white-space: pre-wrap; ">1)Free 버퍼</span><br /><span style="font-size: 15px; font-family: Arial; vertical-align: baseline; white-space: pre-wrap; ">2)Dirty 버퍼</span><br /><span style="font-size: 15px; font-family: Arial; vertical-align: baseline; white-space: pre-wrap; ">3)Clean 버퍼</span><br /><span style="font-size: 15px; font-family: Arial; vertical-align: baseline; white-space: pre-wrap; ">4)Pinned버퍼</span><br /><span style="font-size: 15px; font-family: Arial; vertical-align: baseline; white-space: pre-wrap; "></span><br /><span style="font-size: 15px; font-family: Arial; vertical-align: baseline; white-space: pre-wrap; ">2]괄호 안에 알맞은 단어를 적으시오(단답형)</span><br /><span style="font-size: 15px; font-family: Arial; vertical-align: baseline; white-space: pre-wrap; ">사용자 정의 함수/프로시저는 내장함수처럼 Native 코드로 완전 컴파일된 형태가 아니어서</span><br /><span style="font-size: 15px; font-family: Arial; vertical-align: baseline; white-space: pre-wrap; ">가상머신 같은 별도의 실행엔진을 통해 실행된다. 실행 될 때마다 &nbsp;&nbsp;( &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;)이 일어나며,</span><br /><span style="font-size: 15px; font-family: Arial; vertical-align: baseline; white-space: pre-wrap; ">이 때문에 내장함수를 호출할 때와 비교해 성능을 상당히 떨어뜨린다.</span><br /><span style="font-size: 15px; font-family: Arial; vertical-align: baseline; white-space: pre-wrap; "></span></b></p><p style="font-weight: bold; line-height: normal; font-size: medium; "><br /></p><p style="font-weight: bold; line-height: normal; font-size: medium; ">3]sql형태에 대한 설명중 올바른 것을 고르시오(2개)</p><p style="font-weight: bold; line-height: normal; font-size: medium; ">1)java언어에서는 static sql문법을 제공한다.</p><p style="font-weight: bold; line-height: normal; font-size: medium; ">2)dbms입장에서는 static sql과 dynamic sql의 차이가 없다.</p><p style="font-weight: bold; line-height: normal; font-size: medium; ">3)dynamic sql을 embedded sql이라고도 한다</p><p style="font-weight: bold; line-height: normal; font-size: medium; ">4)static sql, dynamic sql 구분보다는 바인드 변수 사용이 더 중요하다<br /><span style="font-size: 15px; font-family: Arial; vertical-align: baseline; white-space: pre-wrap; "></span><br /><span style="font-size: 15px; font-family: Arial; vertical-align: baseline; white-space: pre-wrap; "></span><br /><span style="font-size: 15px; font-family: Arial; vertical-align: baseline; white-space: pre-wrap; ">(기출문제) 다음 괄호 안에 들어갈 올바른 용어의 순서로 올바른 것을 선택하세요</span><br /><span style="font-size: 15px; font-family: Arial; vertical-align: baseline; white-space: pre-wrap; "></span><br /><span style="font-size: 15px; font-family: Arial; vertical-align: baseline; white-space: pre-wrap; ">Response Time = ( &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;) + Wait Time</span><br /><span style="font-size: 15px; font-family: Arial; vertical-align: baseline; white-space: pre-wrap; "> &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;= ( &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;) + Queue Time</span><br /><span style="font-size: 15px; font-family: Arial; vertical-align: baseline; white-space: pre-wrap; "></span><br /><span style="font-size: 15px; font-family: Arial; vertical-align: baseline; white-space: pre-wrap; ">1) Service Time, Cpu Time &nbsp;</span><br /><span style="font-size: 15px; font-family: Arial; vertical-align: baseline; white-space: pre-wrap; ">2) Cpu Time, Service Time</span><br /><span style="font-size: 15px; font-family: Arial; vertical-align: baseline; white-space: pre-wrap; ">3) Idle Time, Service Time</span><br /><span style="font-size: 15px; font-family: Arial; vertical-align: baseline; white-space: pre-wrap; ">4) Elapsed Time, Cpu Time</span><br /><span style="font-size: 15px; font-family: Arial; vertical-align: baseline; white-space: pre-wrap; "></span><br /><span style="font-size: 15px; font-family: Arial; vertical-align: baseline; white-space: pre-wrap; "></span><br /><span style="font-size: 15px; font-family: Arial; vertical-align: baseline; white-space: pre-wrap; ">2장</span><br /><span style="font-size: 15px; font-family: Arial; vertical-align: baseline; white-space: pre-wrap; ">4]괄호안에 알맞은 단어를 적으시오(단답형)</span><br /><span style="font-size: 15px; font-family: Arial; vertical-align: baseline; white-space: pre-wrap; ">( &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;) 이란 관리할 Lock 리소스가 정해진 임계치를 넘으면서 로우 레벨 락이 페이지, 익스텐트, 테이블 레벨 락으로 점점 확장되는 것을 말한다.</span><br /><span style="font-size: 15px; font-family: Arial; vertical-align: baseline; white-space: pre-wrap; "></span><br /><span style="font-size: 15px; font-family: Arial; vertical-align: baseline; white-space: pre-wrap; ">5] 괄호안에 알맞은 공통된 단어를 적으시오(단답형)</span><br /><span style="font-size: 15px; font-family: Arial; vertical-align: baseline; white-space: pre-wrap; ">( &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;)은 다른 트랜잭션에 의해 데이터의 추가, 변경, 삭제가 발생하더라도 단일 SQL문 내에서 일관성 있게 값을 읽는 것을 말한다. ( &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;)의 기준 시점은 쿼리 시작 시점이 된다.</span><br /><span style="font-size: 15px; font-family: Arial; vertical-align: baseline; white-space: pre-wrap; "></span><br />6]snap shot too old에 대한 설명으로 틀린 것을 고르시오</p><p style="font-weight: bold; line-height: normal; font-size: medium; ">1)undo 영역의 크기를 증가시킴으로써 snapshot too old 발생을 줄일 수 있다.</p><p style="font-weight: bold; line-height: normal; font-size: medium; ">2)long run select 쿼리가 snapshot too old를 발생시킨다면 쿼리 튜닝을 통해</p><p style="font-weight: bold; line-height: normal; font-size: medium; ">&nbsp; &nbsp; snapshot too old발생을 해결할수 있다.</p><p style="font-weight: bold; line-height: normal; font-size: medium; ">3)대량 업데이트후 해당 테이블을 full scan을 하게되면 block clean out 실패로 인한</p><p style="font-weight: bold; line-height: normal; font-size: medium; ">&nbsp; &nbsp; snapshot too old 발생을 방지할 수 있다.</p><p style="font-weight: bold; line-height: normal; font-size: medium; ">4)오라클의 경우 CR 블록을 생성하지 않기 때문에 snapshot too old 에러가 발생한다.</p><p style="font-weight: bold; line-height: normal; font-size: medium; "><br /><span style="font-size: 15px; font-family: Arial; vertical-align: baseline; white-space: pre-wrap; "></span><br /><span style="font-size: 15px; font-family: Arial; vertical-align: baseline; white-space: pre-wrap; ">기출문제 트랜잭션의 특징</span><br /><span style="font-size: 15px; font-family: Arial; vertical-align: baseline; white-space: pre-wrap; ">원자성 / 일관성 / 격리성 / 영속성</span><br /><span style="font-size: 15px; font-family: Arial; vertical-align: baseline; white-space: pre-wrap; "></span><br /><span style="font-size: 15px; font-family: Arial; vertical-align: baseline; white-space: pre-wrap; "></span><br /><span style="font-size: 15px; font-family: Arial; vertical-align: baseline; white-space: pre-wrap; ">3장</span><br /><span style="font-size: 15px; font-family: Arial; vertical-align: baseline; white-space: pre-wrap; ">7] 다음 sql 문장을 실행한 결과 emp 테이블을 table full scan하는 실행계획이 나왔다.</span><br /><span style="font-size: 15px; font-family: Arial; vertical-align: baseline; white-space: pre-wrap; ">실행결과와 sql문의 형태만을 보고 유추한 사실중 올바른것 2개를 고르시오</span><br /><span style="font-size: 15px; font-family: Arial; vertical-align: baseline; white-space: pre-wrap; ">select * </span><br /><span style="font-size: 15px; font-family: Arial; vertical-align: baseline; white-space: pre-wrap; "> &nbsp;from emp</span><br /><span style="font-size: 15px; font-family: Arial; vertical-align: baseline; white-space: pre-wrap; ">where job = ‘CLERK’ or deptno=20;</span><br /><span style="font-size: 15px; font-family: Arial; vertical-align: baseline; white-space: pre-wrap; "></span><br /><span style="font-size: 15px; font-family: Arial; vertical-align: baseline; white-space: pre-wrap; ">1)emp 테이블에 인덱스가 전혀 없을수도 있다.</span><br /><span style="font-size: 15px; font-family: Arial; vertical-align: baseline; white-space: pre-wrap; ">2)인덱스가 한개만 있더라도 table full scan 실행계획이 나올 수 있다.</span><br /><span style="font-size: 15px; font-family: Arial; vertical-align: baseline; white-space: pre-wrap; ">3)select 절에 * 대신 job, deptno으로 적어주면 table full scan 실행계획이 나타나지 않는다.</span><br /><span style="font-size: 15px; font-family: Arial; vertical-align: baseline; white-space: pre-wrap; ">4)or 조건을 and로 바꿔야 table full scan 실행계획이 사라지고, 결과 값도 동일하다.</span><br /><span style="font-size: 15px; font-family: Arial; vertical-align: baseline; white-space: pre-wrap; "></span><br />8)다음 sql과 실행계획에 대한 설명중 올바른 것을 고르시오</p><p style="font-weight: bold; line-height: normal; font-size: medium; "><span style="color: rgb(68, 68, 68); font-family: monospace; font-size: 12px; line-height: 16px; background-color: rgb(244, 244, 244); ">select deptno, avg_sal&nbsp;</span></p><p style="font-weight: bold; line-height: normal; font-size: medium; "><span style="color: rgb(68, 68, 68); font-family: monospace; font-size: 12px; line-height: 16px; background-color: rgb(244, 244, 244); ">&nbsp; from (select deptno, avg(sal) avg_sal</span></p><p style="font-weight: bold; line-height: normal; font-size: medium; "><span style="color: rgb(68, 68, 68); font-family: monospace; font-size: 12px; line-height: 16px; background-color: rgb(244, 244, 244); ">&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; from emp group by deptno) a</span></p><p style="font-weight: bold; line-height: normal; font-size: medium; "><span style="color: rgb(68, 68, 68); font-family: monospace; font-size: 12px; line-height: 16px; background-color: rgb(244, 244, 244); ">where deptno = 30&nbsp;</span></p><p style="font-weight: bold; line-height: normal; font-size: medium; "><span style="color: rgb(68, 68, 68); font-family: monospace; font-size: 12px; line-height: 16px; background-color: rgb(244, 244, 244); ">---------------------------------------------------------------------------&nbsp;</span></p><p style="font-weight: bold; line-height: normal; font-size: medium; "><span style="color: rgb(68, 68, 68); font-family: monospace; font-size: 12px; line-height: 16px; background-color: rgb(244, 244, 244); ">| Id | Operation&nbsp;<span class="Apple-tab-span" style="white-space: pre; "> </span>| Name &nbsp;<span class="Apple-tab-span" style="white-space: pre; "> </span>&nbsp;&nbsp;| Rows| Bytes |&nbsp;</span></p><p style="font-weight: bold; line-height: normal; font-size: medium; "><span style="color: rgb(68, 68, 68); font-family: monospace; font-size: 12px; line-height: 16px; background-color: rgb(244, 244, 244); ">---------------------------------------------------------------------------</span></p><p style="font-weight: bold; line-height: normal; font-size: medium; "><span style="color: rgb(68, 68, 68); font-family: monospace; font-size: 12px; line-height: 16px; background-color: rgb(244, 244, 244); ">| 0 | SELECT STATEMENT&nbsp;<span class="Apple-tab-span" style="white-space: pre; "> </span>|&nbsp;<span class="Apple-tab-span" style="white-space: pre; "> </span>&nbsp;&nbsp;| 1&nbsp;<span class="Apple-tab-span" style="white-space: pre; "> </span>| &nbsp; &nbsp;26 |&nbsp;</span></p><p style="font-weight: bold; line-height: normal; font-size: medium; "><span style="color: rgb(68, 68, 68); font-family: monospace; font-size: 12px; line-height: 16px; background-color: rgb(244, 244, 244); ">| 1 | &nbsp;VIEW&nbsp;<span class="Apple-tab-span" style="white-space: pre; "> </span>|&nbsp;<span class="Apple-tab-span" style="white-space: pre; "> </span>&nbsp;&nbsp;</span><b id="internal-source-marker_0.9205300372559577" style="font-weight: normal; "><p style="display: inline !important; "><b id="internal-source-marker_0.9205300372559577" style="font-weight: normal; "><span style="color: rgb(68, 68, 68); font-family: monospace; font-size: 12px; line-height: 16px; background-color: rgb(244, 244, 244); ">| 1&nbsp;<span class="Apple-tab-span" style="white-space: pre; "> </span>| &nbsp; &nbsp;26 |&nbsp;</span></b></p></b></p><p style="font-weight: bold; line-height: normal; font-size: medium; "></p><p style="font-weight: bold; line-height: normal; font-size: medium; "><b style="font-weight: normal; "></b></p><p style="font-weight: bold; line-height: normal; font-size: medium; display: inline !important; "><b style="font-weight: normal; "><span style="color: rgb(68, 68, 68); font-family: monospace; font-size: 12px; line-height: 16px; background-color: rgb(244, 244, 244); ">| 2 | &nbsp; SORT GROUP BY NOSORT&nbsp;<span class="Apple-tab-span" style="white-space: pre; "> </span>|&nbsp;<span class="Apple-tab-span" style="white-space: pre; "> </span>&nbsp;&nbsp;| 1&nbsp;<span class="Apple-tab-span" style="white-space: pre; "> </span>| &nbsp; &nbsp; 7 |&nbsp;</span></b></p><p style="font-weight: bold; line-height: normal; font-size: medium; "></p><p style="font-weight: bold; line-height: normal; font-size: medium; "><b style="font-weight: normal; "></b></p><p style="font-weight: bold; line-height: normal; font-size: medium; display: inline !important; "><b style="font-weight: normal; "><span style="color: rgb(68, 68, 68); font-family: monospace; font-size: 12px; line-height: 16px; background-color: rgb(244, 244, 244); ">| 3 | &nbsp; &nbsp;TABLE ACCESS BY INDEX ROWID&nbsp;<span class="Apple-tab-span" style="white-space: pre; "> </span>| EMP&nbsp;<span class="Apple-tab-span" style="white-space: pre; "> </span>&nbsp;&nbsp;| 6&nbsp;<span class="Apple-tab-span" style="white-space: pre; "> </span>| &nbsp; &nbsp;42 |&nbsp;</span></b></p><p style="font-weight: bold; line-height: normal; font-size: medium; "></p><p style="font-weight: bold; line-height: normal; font-size: medium; "><b style="font-weight: normal; "></b></p><p style="font-weight: bold; line-height: normal; font-size: medium; display: inline !important; "><b style="font-weight: normal; "><span style="color: rgb(68, 68, 68); font-family: monospace; font-size: 12px; line-height: 16px; background-color: rgb(244, 244, 244); ">|* 4 | &nbsp; &nbsp; INDEX RANGE SCAN&nbsp;<span class="Apple-tab-span" style="white-space: pre; "> </span>| EMP_DEPTNO_IDX &nbsp;| 6&nbsp;<span class="Apple-tab-span" style="white-space: pre; "> </span>| &nbsp; &nbsp; &nbsp; |</span></b></p><p style="font-weight: bold; line-height: normal; font-size: medium; "></p><p style="font-weight: bold; line-height: normal; font-size: medium; "><b style="font-weight: normal; "></b></p><p style="font-weight: bold; line-height: normal; font-size: medium; display: inline !important; "><b style="font-weight: normal; "><span style="color: rgb(68, 68, 68); font-family: monospace; font-size: 12px; line-height: 16px; background-color: rgb(244, 244, 244); ">| ------------------------------------------------------------------------&nbsp;</span></b></p><p style="font-weight: bold; line-height: normal; font-size: medium; "></p><p style="font-weight: bold; line-height: normal; font-size: medium; "><b style="font-weight: normal; "></b></p><p style="font-weight: bold; line-height: normal; font-size: medium; display: inline !important; "><b style="font-weight: normal; "><span style="color: rgb(68, 68, 68); font-family: monospace; font-size: 12px; line-height: 16px; background-color: rgb(244, 244, 244); ">Predicate Information (identified by operation id):&nbsp;</span></b></p><p style="font-weight: bold; line-height: normal; font-size: medium; "></p><p style="font-weight: bold; line-height: normal; font-size: medium; "><b style="font-weight: normal; "></b></p><p style="font-weight: bold; line-height: normal; font-size: medium; display: inline !important; "><b style="font-weight: normal; "><span style="color: rgb(68, 68, 68); font-family: monospace; font-size: 12px; line-height: 16px; background-color: rgb(244, 244, 244); ">-------------------------------------------------------------------------&nbsp;</span></b></p><p style="font-weight: bold; line-height: normal; font-size: medium; "></p><p style="font-weight: bold; line-height: normal; font-size: medium; "><b style="font-weight: normal; "></b></p><p style="font-weight: bold; line-height: normal; font-size: medium; display: inline !important; "><b style="font-weight: normal; "><span style="color: rgb(68, 68, 68); font-family: monospace; font-size: 12px; line-height: 16px; background-color: rgb(244, 244, 244); ">4 - access("DEPTNO"=30)</span></b></p><p style="font-weight: bold; line-height: normal; font-size: medium; "></p><p style="font-weight: bold; line-height: normal; font-size: medium; "><br /></p><p style="font-weight: bold; line-height: normal; font-size: medium; ">1)조건절(Predicate) pullup이 발생했다.</p><p style="font-weight: bold; line-height: normal; font-size: medium; ">2)emp테이블을 deptno컬럼을 기준으로 그룹함수를 사용하므로</p><p style="font-weight: bold; line-height: normal; font-size: medium; ">&nbsp; &nbsp;emp 테이블의 모든 데이터를 읽어 효율적이지 못한 쿼리이다.</p><p style="font-weight: bold; line-height: normal; font-size: medium; ">3)실행계획에 나와있는 sort group by nosort operation을 통해&nbsp;</p><p style="font-weight: bold; line-height: normal; font-size: medium; ">&nbsp; &nbsp; view 바깥에 있는 deptno=30 조건이 view 안쪽으로 조건절 pushdown된 것을</p><p style="font-weight: bold; line-height: normal; font-size: medium; ">&nbsp; &nbsp; 알 수 있다.</p><p style="font-weight: bold; line-height: normal; font-size: medium; ">4)조건절(deptno = 30)에 해당하는 부분의 데이터만 읽는 효율적인 쿼리이다.</p><p style="font-weight: bold; line-height: normal; font-size: medium; "><span style="font-size: 15px; font-family: Arial; vertical-align: baseline; white-space: pre-wrap; "></span><br /><span style="font-size: 15px; font-family: Arial; vertical-align: baseline; white-space: pre-wrap; ">9] 주관식</span><br /><span style="font-size: 15px; font-family: Arial; vertical-align: baseline; white-space: pre-wrap; ">다음 SQL문의 형태와 인덱스 컬럼 타입을 보고 나타날수 있는 비효율을 서술하고</span><br /><span style="font-size: 15px; font-family: Arial; vertical-align: baseline; white-space: pre-wrap; ">튜닝할수 수단을 서술(인덱스 튜닝 등) / SQL을 재작성 하시오</span><br /><span style="font-size: 15px; font-family: Arial; vertical-align: baseline; white-space: pre-wrap; "></span><br /><span style="font-size: 15px; font-family: Arial; vertical-align: baseline; white-space: pre-wrap; ">-- 인덱스 구성 : n1 ( col2 + col3 + col1)</span><br /><span style="font-size: 15px; font-family: Arial; vertical-align: baseline; white-space: pre-wrap; ">-- 컬럼 타입 : col1, col2, col3 : varchar2</span><br /><span style="font-size: 15px; font-family: Arial; vertical-align: baseline; white-space: pre-wrap; "> &nbsp;&nbsp;&nbsp;</span><br /><span style="font-size: 15px; font-family: Arial; vertical-align: baseline; white-space: pre-wrap; ">select col1, col2, col3</span><br /><span style="font-size: 15px; font-family: Arial; vertical-align: baseline; white-space: pre-wrap; "> from t1</span><br /><span style="font-size: 15px; font-family: Arial; vertical-align: baseline; white-space: pre-wrap; ">where col1 = 'CD111'</span><br /><span style="font-size: 15px; font-family: Arial; vertical-align: baseline; white-space: pre-wrap; "> &nbsp;and to_date(col2, 'YYYYMMDD') between to_date('20120701', 'YYYYMMDD') and to_date('20120731', 'YYYYMMDD')</span><br /><span style="font-size: 15px; font-family: Arial; vertical-align: baseline; white-space: pre-wrap; "> &nbsp;and col3 = 'AA'</span><br /><span style="font-size: 15px; font-family: Arial; vertical-align: baseline; white-space: pre-wrap; "></span><br /><span style="font-size: 15px; font-family: Arial; vertical-align: baseline; white-space: pre-wrap; ">union all</span><br /><span style="font-size: 15px; font-family: Arial; vertical-align: baseline; white-space: pre-wrap; "></span><br /><span style="font-size: 15px; font-family: Arial; vertical-align: baseline; white-space: pre-wrap; ">select col1, col2, col3</span><br /><span style="font-size: 15px; font-family: Arial; vertical-align: baseline; white-space: pre-wrap; "> from t1</span><br /><span style="font-size: 15px; font-family: Arial; vertical-align: baseline; white-space: pre-wrap; ">where col1 = 'CD112'</span><br /><span style="font-size: 15px; font-family: Arial; vertical-align: baseline; white-space: pre-wrap; "> &nbsp;and to_date(col2, 'YYYYMMDD') between to_date('20120701', 'YYYYMMDD') and to_date('20120731', 'YYYYMMDD')</span><br /><span style="font-size: 15px; font-family: Arial; vertical-align: baseline; white-space: pre-wrap; "> &nbsp;and col3 = 'AA'</span><br /><span style="font-size: 15px; font-family: Arial; vertical-align: baseline; white-space: pre-wrap; "></span><br /><span style="font-size: 15px; font-family: Arial; vertical-align: baseline; white-space: pre-wrap; "></span><br /><span style="font-size: 15px; font-family: Arial; vertical-align: baseline; white-space: pre-wrap; "></span><br /><span style="font-size: 15px; font-family: Arial; vertical-align: baseline; white-space: pre-wrap; ">4장</span><br /><span style="font-size: 15px; font-family: Arial; vertical-align: baseline; white-space: pre-wrap; ">10]인덱스에 대한 설명중 틀린것을 고르시오(2개)</span><br /><span style="font-size: 15px; font-family: Arial; vertical-align: baseline; white-space: pre-wrap; ">1)잦은 데이터 삭제로 Index Sparse 현상이 나타날수 있다.</span><br /><span style="font-size: 15px; font-family: Arial; vertical-align: baseline; white-space: pre-wrap; ">2)인덱스에는 null값 자체가 저장될수 없다.</span><br /><span style="font-size: 15px; font-family: Arial; vertical-align: baseline; white-space: pre-wrap; ">3)비트맵 인덱스는 b-tree인덱스에 비해 상대적으로 저장 공간을 적게 차지한다.</span><br /><span style="font-size: 15px; font-family: Arial; vertical-align: baseline; white-space: pre-wrap; ">4)비트맵 인덱스는 주로 값의 종류가 많은 경우에 유리하다.</span><br /><span style="font-size: 15px; font-family: Arial; vertical-align: baseline; white-space: pre-wrap; "></span><br /><span style="font-size: 15px; font-family: Arial; vertical-align: baseline; white-space: pre-wrap; ">11]다음 칸에 알맞은 공통된 단어를 넣으시오</span><br /><span style="font-size: 15px; font-family: Arial; vertical-align: baseline; white-space: pre-wrap; ">Oracle IOT를 개발하면서 두가지 액세스 방식을 모두 사용할 수있도록 설계하였다. IOT 레코드의 위치는 영구적이지 않기 때문에 물리적 주소(ROWID)대신 &nbsp;( &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;)를 사용한다.</span><br /><span style="font-size: 15px; font-family: Arial; vertical-align: baseline; white-space: pre-wrap; ">( &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;)는 PK와 &nbsp;physical guess로 구성된다.</span><br /><span style="font-size: 15px; font-family: Arial; vertical-align: baseline; white-space: pre-wrap; "></span><br />12]인덱스 구성에 대한 설명중 올바른 것을 고르시오</p><p style="font-weight: bold; line-height: normal; font-size: medium; ">1)unique인덱스는 단일컬럼으로 구성되어야한다.</p><p style="font-weight: bold; line-height: normal; font-size: medium; ">2)order by, group by를 위한 소트 연산을 줄이기 위해 인덱스에 컬럼을 추가하기도 한다.</p><p style="font-weight: bold; line-height: normal; font-size: medium; ">3)조건절에 기술된 컬럼에 의해 선택도가 높은 인덱스가 있을경우 table full scan보다 해당</p><p style="font-weight: bold; line-height: normal; font-size: medium; ">&nbsp; &nbsp;인덱스를 사용하는 것이 좋다.</p><p style="font-weight: bold; line-height: normal; font-size: medium; ">4)<b id="internal-source-marker_0.9205300372559577" style="font-weight: normal; "><p style="display: inline !important; "><b id="internal-source-marker_0.9205300372559577" style="font-weight: normal; "><b id="internal-source-marker_0.9205300372559577" style="font-weight: normal; ">인덱스 선두 컬럼은 범위조건으로 사용되는 컬럼이 위치하는 것이 인덱스 활용에 유리하다</b></b></p></b></p><p style="font-weight: bold; line-height: normal; "></p><b style="font-weight: normal; line-height: normal; "><span style="font-size: 15px; font-family: Arial; vertical-align: baseline; white-space: pre-wrap; font-size: 15px;"></span></b><br /><br /><span style="font-weight: bold; line-height: normal; font-size: 15px; font-family: Arial; vertical-align: baseline; white-space: pre-wrap; ">문자 vs 숫자 : 문자--&gt; 숫자</span><br /><span style="font-weight: bold; line-height: normal; font-size: 15px; font-family: Arial; vertical-align: baseline; white-space: pre-wrap; ">문자 vs 날짜 : 문자--&gt; 날짜</span><br /><b style="font-weight: normal; line-height: normal; "><span style="font-size: 15px; font-family: Arial; vertical-align: baseline; white-space: pre-wrap; font-size: 15px;"></span></b><br /><b style="font-weight: normal; line-height: normal; "><span style="font-size: 15px; font-family: Arial; vertical-align: baseline; white-space: pre-wrap; font-size: 15px;"></span></b><br /><span style="font-weight: bold; line-height: normal; font-size: 15px; font-family: Arial; vertical-align: baseline; white-space: pre-wrap; ">5장</span><br /><span style="font-weight: bold; line-height: normal; font-size: 15px; font-family: Arial; vertical-align: baseline; white-space: pre-wrap; ">13]top-n sql 구현을 통해 소트 연산 횟수를 최소화하여 소트 영역 사용량을 최소화 하려고 한다. 다음 쿼리 문과 sql문을 보고 올바른 설명을 고르시오</span><br /><span style="font-weight: bold; line-height: normal; font-size: 15px; font-family: Arial; vertical-align: baseline; white-space: pre-wrap; ">(인덱스 구성은 다음과 같다. &nbsp;&nbsp;pk_emp : empno)</span><br /><b style="font-weight: normal; line-height: normal; "><span style="font-size: 15px; font-family: Arial; vertical-align: baseline; white-space: pre-wrap; font-size: 15px;"></span></b><br /><p style="font-weight: bold; line-height: normal; "></p><p dir="ltr" style="font-weight: bold; line-height: normal; margin-top: 0pt; margin-bottom: 0pt; font-size: medium; text-align: justify; "><span style="font-size: 15px; font-family: Arial; background-color: rgb(255, 255, 255); vertical-align: baseline; white-space: pre-wrap; ">select *</span></p><p dir="ltr" style="font-weight: bold; line-height: normal; margin-top: 0pt; margin-bottom: 0pt; font-size: medium; text-align: justify; "><span style="font-size: 15px; font-family: Arial; background-color: rgb(255, 255, 255); vertical-align: baseline; white-space: pre-wrap; "> from </span></p><p dir="ltr" style="font-weight: bold; line-height: normal; margin-top: 0pt; margin-bottom: 0pt; font-size: medium; text-align: justify; "><span style="font-size: 15px; font-family: Arial; background-color: rgb(255, 255, 255); vertical-align: baseline; white-space: pre-wrap; "><span class="Apple-tab-span" style="white-space: pre; "> </span>(select rownum rn</span></p><p dir="ltr" style="font-weight: bold; line-height: normal; margin-top: 0pt; margin-bottom: 0pt; font-size: medium; text-align: justify; "><span style="font-size: 15px; font-family: Arial; background-color: rgb(255, 255, 255); vertical-align: baseline; white-space: pre-wrap; "><span class="Apple-tab-span" style="white-space: pre; "> </span> &nbsp;&nbsp;from </span></p><p dir="ltr" style="font-weight: bold; line-height: normal; margin-top: 0pt; margin-bottom: 0pt; font-size: medium; text-align: justify; "><span style="font-size: 15px; font-family: Arial; background-color: rgb(255, 255, 255); vertical-align: baseline; white-space: pre-wrap; "><span class="Apple-tab-span" style="white-space: pre; "> </span><span class="Apple-tab-span" style="white-space: pre; "> </span>(select /*+ index (emp pk_emp) */ *</span></p><p dir="ltr" style="font-weight: bold; line-height: normal; margin-top: 0pt; margin-bottom: 0pt; font-size: medium; text-align: justify; "><span style="font-size: 15px; font-family: Arial; background-color: rgb(255, 255, 255); vertical-align: baseline; white-space: pre-wrap; "><span class="Apple-tab-span" style="white-space: pre; "> </span><span class="Apple-tab-span" style="white-space: pre; "> </span> from emp</span></p><p dir="ltr" style="font-weight: bold; line-height: normal; margin-top: 0pt; margin-bottom: 0pt; font-size: medium; text-align: justify; "><span style="font-size: 15px; font-family: Arial; background-color: rgb(255, 255, 255); vertical-align: baseline; white-space: pre-wrap; "><span class="Apple-tab-span" style="white-space: pre; "> </span><span class="Apple-tab-span" style="white-space: pre; "> </span>order by empno )</span></p><p dir="ltr" style="font-weight: bold; line-height: normal; margin-top: 0pt; margin-bottom: 0pt; font-size: medium; text-align: justify; "><span style="font-size: 15px; font-family: Arial; background-color: rgb(255, 255, 255); vertical-align: baseline; white-space: pre-wrap; "><span class="Apple-tab-span" style="white-space: pre; "> </span> &nbsp;)</span></p><p dir="ltr" style="font-weight: bold; line-height: normal; margin-top: 0pt; margin-bottom: 0pt; font-size: medium; text-align: justify; "><span style="font-size: 15px; font-family: Arial; background-color: rgb(255, 255, 255); vertical-align: baseline; white-space: pre-wrap; "> where &nbsp;rn between 21 and 30;</span></p><p style="font-weight: bold; line-height: normal; font-size: medium; "><span style="font-size: 15px; font-family: Arial; background-color: rgb(255, 255, 255); vertical-align: baseline; white-space: pre-wrap; "></span><span style="font-size: 15px; font-family: Verdana; background-color: rgb(255, 255, 255); vertical-align: baseline; white-space: pre-wrap; ">
<b id="internal-source-marker_0.9205300372559577" style="font-family: Gulim; white-space: normal; font-size: medium; font-weight: normal; "><span style="font-size: 15px; font-family: Verdana; vertical-align: baseline; white-space: pre-wrap; ">------------------------------------------</span></b>
| Id &nbsp;| Operation &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<span class="Apple-tab-span" style="white-space: pre; "> </span>
<b id="internal-source-marker_0.9205300372559577" style="font-family: Gulim; white-space: normal; font-size: medium; font-weight: normal; "><span style="font-size: 15px; font-family: Verdana; vertical-align: baseline; white-space: pre-wrap; ">------------------------------------------</span></b>
| &nbsp;&nbsp;0 | SELECT STATEMENT <br />|* &nbsp;1 | &nbsp;VIEW &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<span class="Apple-tab-span" style="white-space: pre; "> </span><br />| &nbsp;&nbsp;2 | &nbsp;&nbsp;</span><span style="font-size: 15px; font-family: Verdana; background-color: rgb(255, 255, 255); font-weight: bold; text-decoration: underline; vertical-align: baseline; white-space: pre-wrap; ">COUNT</span><span style="font-size: 15px; font-family: Verdana; background-color: rgb(255, 255, 255); vertical-align: baseline; white-space: pre-wrap; "> &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<span class="Apple-tab-span" style="white-space: pre; "> </span><br />| &nbsp;&nbsp;3 | &nbsp;&nbsp;&nbsp;VIEW &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<span class="Apple-tab-span" style="white-space: pre; "> </span><br />| &nbsp;&nbsp;4 | &nbsp;&nbsp;&nbsp;&nbsp;INDEX FULL SCAN( PK_EMP )<br />------------------------------------------<br /> <br />Predicate Information (identified by operation id):<br />---------------------------------------------------<br /> <br /> &nbsp;&nbsp;1 - filter("RN"&lt;=30 AND "RN"&gt;=21)</span><span style="font-size: 15px; font-family: Arial; vertical-align: baseline; white-space: pre-wrap; "></span><br /><span style="font-size: 15px; font-family: Arial; vertical-align: baseline; white-space: pre-wrap; "></span><br /><span style="font-size: 15px; font-family: Arial; vertical-align: baseline; white-space: pre-wrap; ">1)위의 실행계획을 보면 정상적으로 top-n효과가 나타난 것을 확인 할 수 있다.</span><br /><span style="font-size: 15px; font-family: Arial; vertical-align: baseline; white-space: pre-wrap; ">2)인라인 뷰의 중첩된 사용으로 인해 &nbsp;top-n효과가 나타나지 않았다.</span><br /><span style="font-size: 15px; font-family: Arial; vertical-align: baseline; white-space: pre-wrap; ">3)인덱스 사용으로 top-n효과가 나타났다.</span><br /><span style="font-size: 15px; font-family: Arial; vertical-align: baseline; white-space: pre-wrap; ">4)filter predicate을 통해 &nbsp;top-n효과가 정상적으로 나타나지 않은것을 알 수 있다.</span><br /><span style="font-size: 15px; font-family: Arial; vertical-align: baseline; white-space: pre-wrap; "></span><br /><span style="font-size: 15px; font-family: Arial; vertical-align: baseline; white-space: pre-wrap; "></span><br /><span style="font-size: 15px; font-family: Arial; vertical-align: baseline; white-space: pre-wrap; ">14]다음 쿼리에 대해 올바른 설명을 고르시오</span><br /><span style="font-size: 15px; font-family: Arial; vertical-align: baseline; white-space: pre-wrap; ">select /*+ pq_distribute(e, none, none) */ *</span><br /><span style="font-size: 15px; font-family: Arial; vertical-align: baseline; white-space: pre-wrap; "> from dept d, emp e</span><br /><span style="font-size: 15px; font-family: Arial; vertical-align: baseline; white-space: pre-wrap; ">where e.deptno = d.deptno</span><br /><span style="font-size: 15px; font-family: Arial; vertical-align: baseline; white-space: pre-wrap; "></span><br /><span style="font-size: 15px; font-family: Arial; vertical-align: baseline; white-space: pre-wrap; ">1)병렬관련 힌트를 사용하여 emp 테이블을 먼저 읽는 형태(outter table)로 실행된다.</span><br /><span style="font-size: 15px; font-family: Arial; vertical-align: baseline; white-space: pre-wrap; ">2)두번째 파라미터는 inner table의 병렬도를 설정하는 값으로, none 값을 주어 optimizer가</span><br /><span style="font-size: 15px; font-family: Arial; vertical-align: baseline; white-space: pre-wrap; "> &nbsp;&nbsp;&nbsp;스스로 판단하도록 유도 했다.</span><br /><span style="font-size: 15px; font-family: Arial; vertical-align: baseline; white-space: pre-wrap; ">3)세번째 파라미터는 outer 테이블의 distirbution 방식을 지정하는 값으로 none 값을 주어</span><br /><span style="font-size: 15px; font-family: Arial; vertical-align: baseline; white-space: pre-wrap; "> &nbsp;&nbsp;&nbsp;optimizer가 스스로 판단하도록 유도 했다.</span><br /><span style="font-size: 15px; font-family: Arial; vertical-align: baseline; white-space: pre-wrap; ">4)full-partition wise join을 유도한 힌트이다.</span></p><p style="font-weight: bold; line-height: normal; font-size: medium; "><span style="font-size: 15px; font-family: Arial; vertical-align: baseline; white-space: pre-wrap; "><br /></span></p><p style="font-weight: bold; line-height: normal; font-size: medium; "><span style="font-size: 15px; font-family: Arial; vertical-align: baseline; white-space: pre-wrap; ">15]다음 sql문에 대한 설명으로 틀린 것을 고르시오</span></p><p style="font-weight: bold; line-height: normal; font-size: medium; "><span style="font-size: 15px; font-family: Arial; vertical-align: baseline; white-space: pre-wrap; ">(table emp : 사원 테이블)</span></p><p style="font-weight: bold; line-height: normal; font-size: medium; "><span style="font-size: 15px; font-family: Arial; vertical-align: baseline; white-space: pre-wrap; ">(dept_no 컬럼 : 부서번호 )</span></p><p style="font-weight: bold; line-height: normal; font-size: medium; "><span style="font-size: 15px; font-family: Arial; vertical-align: baseline; white-space: pre-wrap; "><br /></span></p><p style="font-weight: bold; line-height: normal; "><font face="Arial"><span style="font-size: 14.857142448425293px; white-space: pre-wrap; ">select dept_no, count(*) cnt,</span></font></p><p style="font-weight: bold; line-height: normal; "><font face="Arial"><span style="font-size: 14.857142448425293px; white-space: pre-wrap; "> (select count(*) from emp) totalEmp</span></font></p><p style="font-weight: bold; line-height: normal; "><font face="Arial"><span style="font-size: 14.857142448425293px; white-space: pre-wrap; ">from emp</span></font></p><p style="font-weight: bold; line-height: normal; "><font face="Arial"><span style="font-size: 14.857142448425293px; white-space: pre-wrap; ">group by dept_no</span></font></p><p style="font-weight: bold; line-height: normal; "><font face="Arial"><span style="font-size: 14.857142448425293px; white-space: pre-wrap; "><br /></span></font></p><p style="font-weight: bold; line-height: normal; "><font face="Arial"><span style="font-size: 14.857142448425293px; white-space: pre-wrap; ">1)부서번호, 부서별 사원수, 전체 사원수를 select한다.</span></font></p><p style="font-weight: bold; line-height: normal; "><font face="Arial"><span style="font-size: 14.857142448425293px; white-space: pre-wrap; ">2)select 절에 기술된 스칼라 서브쿼리의 경우 dept_no수만큼 실행된다.</span></font></p><p style="font-weight: bold; line-height: normal; "><font face="Arial"><span style="font-size: 14.857142448425293px; white-space: pre-wrap; ">3)일반적인 형태의 sql로 흠잡을데 없는 쿼리이다.</span></font></p><p style="font-weight: bold; line-height: normal; "><font face="Arial"><span style="font-size: 14.857142448425293px; white-space: pre-wrap; ">4)emp table을 두번 읽게되며 특히 스칼라 서브쿼리의 경우 같은 값을 반복적으로 실행하는 비효율이 발생한다.</span></font></p><p style="font-weight: bold; line-height: normal; font-size: medium; "><span style="font-size: 15px; font-family: Arial; vertical-align: baseline; white-space: pre-wrap; "><br /></span></p><p style="font-weight: bold; line-height: normal; font-size: medium; "><span style="font-size: 15px; font-family: Arial; vertical-align: baseline; white-space: pre-wrap; "><br /></span></p><p style="font-weight: bold; line-height: normal; font-size: medium; "><span style="font-size: 15px; font-family: Arial; vertical-align: baseline; white-space: pre-wrap; ">16] 다음 쿼리를 merge 문 으로 변경하시오</span></p><p style="font-weight: bold; line-height: normal; font-size: medium; "><br /></p><p><span style="vertical-align: baseline; font-size: 15.454545021057129px; line-height: normal; white-space: pre-wrap;"><font face="Arial">UPDATE /*+ BYPASS_UJVC */
(
SELECT A.EMP_ID,
A.TMP_MON,
B.DDCT_MON
FROM TABLE_A A,
TABLE_B B
WHERE A.C_CD = '10'
AND A.C_CD = B.C_CD
AND A.CD = B.PAYROLL_NO
AND A.EMP_ID = B.EMP_ID ) A
SET A.TMP_MON = A.DDCT_MON;</font></span></p><p style="font-weight: bold; line-height: normal; font-size: medium; "><span style="font-size: 15px; font-family: Arial; vertical-align: baseline; white-space: pre-wrap; ">
</span></p></span>
ljw 2012.07.25 10:35:59
복원
<b id="internal-source-marker_0.9205300372559577" style="line-height: normal; font-weight: normal; "><p style="font-size: medium; "><b id="internal-source-marker_0.9205300372559577" style="font-weight: normal; "><span style="font-size: 15px; font-family: Arial; vertical-align: baseline; white-space: pre-wrap; ">3과목</span><br /><span style="font-size: 15px; font-family: Arial; vertical-align: baseline; white-space: pre-wrap; ">1장</span><br /><span style="font-size: 15px; font-family: Arial; vertical-align: baseline; white-space: pre-wrap; ">1]버퍼 블록의 상태 종류중 틀린것은?</span><br /><span style="font-size: 15px; font-family: Arial; vertical-align: baseline; white-space: pre-wrap; ">1)Free 버퍼</span><br /><span style="font-size: 15px; font-family: Arial; vertical-align: baseline; white-space: pre-wrap; ">2)Dirty 버퍼</span><br /><span style="font-size: 15px; font-family: Arial; vertical-align: baseline; white-space: pre-wrap; ">3)Clean 버퍼</span><br /><span style="font-size: 15px; font-family: Arial; vertical-align: baseline; white-space: pre-wrap; ">4)Pinned버퍼</span><br /><span style="font-size: 15px; font-family: Arial; vertical-align: baseline; white-space: pre-wrap; "></span><br /><span style="font-size: 15px; font-family: Arial; vertical-align: baseline; white-space: pre-wrap; ">2]괄호 안에 알맞은 단어를 적으시오(단답형)</span><br /><span style="font-size: 15px; font-family: Arial; vertical-align: baseline; white-space: pre-wrap; ">사용자 정의 함수/프로시저는 내장함수처럼 Native 코드로 완전 컴파일된 형태가 아니어서</span><br /><span style="font-size: 15px; font-family: Arial; vertical-align: baseline; white-space: pre-wrap; ">가상머신 같은 별도의 실행엔진을 통해 실행된다. 실행 될 때마다 &nbsp;&nbsp;( &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;)이 일어나며,</span><br /><span style="font-size: 15px; font-family: Arial; vertical-align: baseline; white-space: pre-wrap; ">이 때문에 내장함수를 호출할 때와 비교해 성능을 상당히 떨어뜨린다.</span><br /><span style="font-size: 15px; font-family: Arial; vertical-align: baseline; white-space: pre-wrap; "></span></b></p><p style="font-size: medium; "><br /></p><p style="font-size: medium; ">3]sql형태에 대한 설명중 올바른 것을 고르시오(2개)</p><p style="font-size: medium; ">1)java언어에서는 static sql문법을 제공한다.</p><p style="font-size: medium; ">2)dbms입장에서는 static sql과 dynamic sql의 차이가 없다.</p><p style="font-size: medium; ">3)dynamic sql을 embedded sql이라고도 한다</p><p style="font-size: medium; ">4)static sql, dynamic sql 구분보다는 바인드 변수 사용이 더 중요하다<br /><span style="font-size: 15px; font-family: Arial; vertical-align: baseline; white-space: pre-wrap; "></span><br /><span style="font-size: 15px; font-family: Arial; vertical-align: baseline; white-space: pre-wrap; "></span><br /><span style="font-size: 15px; font-family: Arial; vertical-align: baseline; white-space: pre-wrap; ">(기출문제) 다음 괄호 안에 들어갈 올바른 용어의 순서로 올바른 것을 선택하세요</span><br /><span style="font-size: 15px; font-family: Arial; vertical-align: baseline; white-space: pre-wrap; "></span><br /><span style="font-size: 15px; font-family: Arial; vertical-align: baseline; white-space: pre-wrap; ">Response Time = ( &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;) + Wait Time</span><br /><span style="font-size: 15px; font-family: Arial; vertical-align: baseline; white-space: pre-wrap; "> &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;= ( &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;) + Queue Time</span><br /><span style="font-size: 15px; font-family: Arial; vertical-align: baseline; white-space: pre-wrap; "></span><br /><span style="font-size: 15px; font-family: Arial; vertical-align: baseline; white-space: pre-wrap; ">1) Service Time, Cpu Time &nbsp;</span><br /><span style="font-size: 15px; font-family: Arial; vertical-align: baseline; white-space: pre-wrap; ">2) Cpu Time, Service Time</span><br /><span style="font-size: 15px; font-family: Arial; vertical-align: baseline; white-space: pre-wrap; ">3) Idle Time, Service Time</span><br /><span style="font-size: 15px; font-family: Arial; vertical-align: baseline; white-space: pre-wrap; ">4) Elapsed Time, Cpu Time</span><br /><span style="font-size: 15px; font-family: Arial; vertical-align: baseline; white-space: pre-wrap; "></span><br /><span style="font-size: 15px; font-family: Arial; vertical-align: baseline; white-space: pre-wrap; "></span><br /><span style="font-size: 15px; font-family: Arial; vertical-align: baseline; white-space: pre-wrap; ">2장</span><br /><span style="font-size: 15px; font-family: Arial; vertical-align: baseline; white-space: pre-wrap; ">4]괄호안에 알맞은 단어를 적으시오(단답형)</span><br /><span style="font-size: 15px; font-family: Arial; vertical-align: baseline; white-space: pre-wrap; ">( &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;) 이란 관리할 Lock 리소스가 정해진 임계치를 넘으면서 로우 레벨 락이 페이지, 익스텐트, 테이블 레벨 락으로 점점 확장되는 것을 말한다.</span><br /><span style="font-size: 15px; font-family: Arial; vertical-align: baseline; white-space: pre-wrap; "></span><br /><span style="font-size: 15px; font-family: Arial; vertical-align: baseline; white-space: pre-wrap; ">5] 괄호안에 알맞은 공통된 단어를 적으시오(단답형)</span><br /><span style="font-size: 15px; font-family: Arial; vertical-align: baseline; white-space: pre-wrap; ">( &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;)은 다른 트랜잭션에 의해 데이터의 추가, 변경, 삭제가 발생하더라도 단일 SQL문 내에서 일관성 있게 값을 읽는 것을 말한다. ( &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;)의 기준 시점은 쿼리 시작 시점이 된다.</span><br /><span style="font-size: 15px; font-family: Arial; vertical-align: baseline; white-space: pre-wrap; "></span><br />6]snap shot too old에 대한 설명으로 틀린 것을 고르시오</p><p style="font-size: medium; ">1)undo 영역의 크기를 증가시킴으로써 snapshot too old 발생을 줄일 수 있다.</p><p style="font-size: medium; ">2)long run select 쿼리가 snapshot too old를 발생시킨다면 쿼리 튜닝을 통해</p><p style="font-size: medium; ">&nbsp; &nbsp; snapshot too old발생을 해결할수 있다.</p><p style="font-size: medium; ">3)대량 업데이트후 해당 테이블을 full scan을 하게되면 block clean out 실패로 인한</p><p style="font-size: medium; ">&nbsp; &nbsp; snapshot too old 발생을 방지할 수 있다.</p><p style="font-size: medium; ">4)오라클의 경우 CR 블록을 생성하지 않기 때문에 snapshot too old 에러가 발생한다.</p><p style="font-size: medium; "><br /><span style="font-size: 15px; font-family: Arial; vertical-align: baseline; white-space: pre-wrap; "></span><br /><span style="font-size: 15px; font-family: Arial; vertical-align: baseline; white-space: pre-wrap; ">기출문제 트랜잭션의 특징</span><br /><span style="font-size: 15px; font-family: Arial; vertical-align: baseline; white-space: pre-wrap; ">원자성 / 일관성 / 격리성 / 영속성</span><br /><span style="font-size: 15px; font-family: Arial; vertical-align: baseline; white-space: pre-wrap; "></span><br /><span style="font-size: 15px; font-family: Arial; vertical-align: baseline; white-space: pre-wrap; "></span><br /><span style="font-size: 15px; font-family: Arial; vertical-align: baseline; white-space: pre-wrap; ">3장</span><br /><span style="font-size: 15px; font-family: Arial; vertical-align: baseline; white-space: pre-wrap; ">7] 다음 sql 문장을 실행한 결과 emp 테이블을 table full scan하는 실행계획이 나왔다.</span><br /><span style="font-size: 15px; font-family: Arial; vertical-align: baseline; white-space: pre-wrap; ">실행결과와 sql문의 형태만을 보고 유추한 사실중 올바른것 2개를 고르시오</span><br /><span style="font-size: 15px; font-family: Arial; vertical-align: baseline; white-space: pre-wrap; ">select * </span><br /><span style="font-size: 15px; font-family: Arial; vertical-align: baseline; white-space: pre-wrap; "> &nbsp;from emp</span><br /><span style="font-size: 15px; font-family: Arial; vertical-align: baseline; white-space: pre-wrap; ">where job = ‘CLERK’ or deptno=20;</span><br /><span style="font-size: 15px; font-family: Arial; vertical-align: baseline; white-space: pre-wrap; "></span><br /><span style="font-size: 15px; font-family: Arial; vertical-align: baseline; white-space: pre-wrap; ">1)emp 테이블에 인덱스가 전혀 없을수도 있다.</span><br /><span style="font-size: 15px; font-family: Arial; vertical-align: baseline; white-space: pre-wrap; ">2)인덱스가 한개만 있더라도 table full scan 실행계획이 나올 수 있다.</span><br /><span style="font-size: 15px; font-family: Arial; vertical-align: baseline; white-space: pre-wrap; ">3)select 절에 * 대신 job, deptno으로 적어주면 table full scan 실행계획이 나타나지 않는다.</span><br /><span style="font-size: 15px; font-family: Arial; vertical-align: baseline; white-space: pre-wrap; ">4)or 조건을 and로 바꿔야 table full scan 실행계획이 사라지고, 결과 값도 동일하다.</span><br /><span style="font-size: 15px; font-family: Arial; vertical-align: baseline; white-space: pre-wrap; "></span><br />8)다음 sql과 실행계획에 대한 설명중 올바른 것을 고르시오</p><p style="font-size: medium; "><span style="color: rgb(68, 68, 68); font-family: monospace; font-size: 12px; line-height: 16px; background-color: rgb(244, 244, 244); ">select deptno, avg_sal&nbsp;</span></p><p style="font-size: medium; "><span style="color: rgb(68, 68, 68); font-family: monospace; font-size: 12px; line-height: 16px; background-color: rgb(244, 244, 244); ">&nbsp; from (select deptno, avg(sal) avg_sal</span></p><p style="font-size: medium; "><span style="color: rgb(68, 68, 68); font-family: monospace; font-size: 12px; line-height: 16px; background-color: rgb(244, 244, 244); ">&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; from emp group by deptno) a</span></p><p style="font-size: medium; "><span style="color: rgb(68, 68, 68); font-family: monospace; font-size: 12px; line-height: 16px; background-color: rgb(244, 244, 244); ">where deptno = 30&nbsp;</span></p><p style="font-size: medium; "><span style="color: rgb(68, 68, 68); font-family: monospace; font-size: 12px; line-height: 16px; background-color: rgb(244, 244, 244); ">---------------------------------------------------------------------------&nbsp;</span></p><p style="font-size: medium; "><span style="color: rgb(68, 68, 68); font-family: monospace; font-size: 12px; line-height: 16px; background-color: rgb(244, 244, 244); ">| Id | Operation&nbsp;<span class="Apple-tab-span" style="white-space: pre; "> </span>| Name &nbsp;<span class="Apple-tab-span" style="white-space: pre; "> </span>&nbsp;&nbsp;| Rows| Bytes |&nbsp;</span></p><p style="font-size: medium; "><span style="color: rgb(68, 68, 68); font-family: monospace; font-size: 12px; line-height: 16px; background-color: rgb(244, 244, 244); ">---------------------------------------------------------------------------</span></p><p style="font-size: medium; "><span style="color: rgb(68, 68, 68); font-family: monospace; font-size: 12px; line-height: 16px; background-color: rgb(244, 244, 244); ">| 0 | SELECT STATEMENT&nbsp;<span class="Apple-tab-span" style="white-space: pre; "> </span>|&nbsp;<span class="Apple-tab-span" style="white-space: pre; "> </span>&nbsp;&nbsp;| 1&nbsp;<span class="Apple-tab-span" style="white-space: pre; "> </span>| &nbsp; &nbsp;26 |&nbsp;</span></p><p style="font-size: medium; "><span style="color: rgb(68, 68, 68); font-family: monospace; font-size: 12px; line-height: 16px; background-color: rgb(244, 244, 244); ">| 1 | &nbsp;VIEW&nbsp;<span class="Apple-tab-span" style="white-space: pre; "> </span>|&nbsp;<span class="Apple-tab-span" style="white-space: pre; "> </span>&nbsp;&nbsp;</span><b id="internal-source-marker_0.9205300372559577" style="font-weight: normal; "></b></p><p style="font-size: medium; display: inline !important; "><b id="internal-source-marker_0.9205300372559577" style="font-weight: normal; "><span style="color: rgb(68, 68, 68); font-family: monospace; font-size: 12px; line-height: 16px; background-color: rgb(244, 244, 244); ">| 1&nbsp;<span class="Apple-tab-span" style="white-space: pre; "> </span>| &nbsp; &nbsp;26 |&nbsp;</span></b></p><p style="font-size: medium; "></p><p style="font-size: medium; "><b style="font-weight: normal; "></b></p><p style="font-size: medium; display: inline !important; "><b style="font-weight: normal; "><span style="color: rgb(68, 68, 68); font-family: monospace; font-size: 12px; line-height: 16px; background-color: rgb(244, 244, 244); ">| 2 | &nbsp; SORT GROUP BY NOSORT&nbsp;<span class="Apple-tab-span" style="white-space: pre; "> </span>|&nbsp;<span class="Apple-tab-span" style="white-space: pre; "> </span>&nbsp;&nbsp;| 1&nbsp;<span class="Apple-tab-span" style="white-space: pre; "> </span>| &nbsp; &nbsp; 7 |&nbsp;</span></b></p><p style="font-size: medium; "></p><p style="font-size: medium; "><b style="font-weight: normal; "></b></p><p style="font-size: medium; display: inline !important; "><b style="font-weight: normal; "><span style="color: rgb(68, 68, 68); font-family: monospace; font-size: 12px; line-height: 16px; background-color: rgb(244, 244, 244); ">| 3 | &nbsp; &nbsp;TABLE ACCESS BY INDEX ROWID&nbsp;<span class="Apple-tab-span" style="white-space: pre; "> </span>| EMP&nbsp;<span class="Apple-tab-span" style="white-space: pre; "> </span>&nbsp;&nbsp;| 6&nbsp;<span class="Apple-tab-span" style="white-space: pre; "> </span>| &nbsp; &nbsp;42 |&nbsp;</span></b></p><p style="font-size: medium; "></p><p style="font-size: medium; "><b style="font-weight: normal; "></b></p><p style="font-size: medium; display: inline !important; "><b style="font-weight: normal; "><span style="color: rgb(68, 68, 68); font-family: monospace; font-size: 12px; line-height: 16px; background-color: rgb(244, 244, 244); ">|* 4 | &nbsp; &nbsp; INDEX RANGE SCAN&nbsp;<span class="Apple-tab-span" style="white-space: pre; "> </span>| EMP_DEPTNO_IDX &nbsp;| 6&nbsp;<span class="Apple-tab-span" style="white-space: pre; "> </span>| &nbsp; &nbsp; &nbsp; |</span></b></p><p style="font-size: medium; "></p><p style="font-size: medium; "><b style="font-weight: normal; "></b></p><p style="font-size: medium; display: inline !important; "><b style="font-weight: normal; "><span style="color: rgb(68, 68, 68); font-family: monospace; font-size: 12px; line-height: 16px; background-color: rgb(244, 244, 244); ">| ------------------------------------------------------------------------&nbsp;</span></b></p><p style="font-size: medium; "></p><p style="font-size: medium; "><b style="font-weight: normal; "></b></p><p style="font-size: medium; display: inline !important; "><b style="font-weight: normal; "><span style="color: rgb(68, 68, 68); font-family: monospace; font-size: 12px; line-height: 16px; background-color: rgb(244, 244, 244); ">Predicate Information (identified by operation id):&nbsp;</span></b></p><p style="font-size: medium; "></p><p style="font-size: medium; "><b style="font-weight: normal; "></b></p><p style="font-size: medium; display: inline !important; "><b style="font-weight: normal; "><span style="color: rgb(68, 68, 68); font-family: monospace; font-size: 12px; line-height: 16px; background-color: rgb(244, 244, 244); ">-------------------------------------------------------------------------&nbsp;</span></b></p><p style="font-size: medium; "></p><p style="font-size: medium; "><b style="font-weight: normal; "></b></p><p style="font-size: medium; display: inline !important; "><b style="font-weight: normal; "><span style="color: rgb(68, 68, 68); font-family: monospace; font-size: 12px; line-height: 16px; background-color: rgb(244, 244, 244); ">4 - access("DEPTNO"=30)</span></b></p><p style="font-size: medium; "></p><p style="font-size: medium; "><br /></p><p style="font-size: medium; ">1)조건절(Predicate) pullup이 발생했다.</p><p style="font-size: medium; ">2)emp테이블을 deptno컬럼을 기준으로 그룹함수를 사용하므로</p><p style="font-size: medium; ">&nbsp; &nbsp;emp 테이블의 모든 데이터를 읽어 효율적이지 못한 쿼리이다.</p><p style="font-size: medium; ">3)실행계획에 나와있는 sort group by nosort operation을 통해&nbsp;</p><p style="font-size: medium; ">&nbsp; &nbsp; view 바깥에 있는 deptno=30 조건이 view 안쪽으로 조건절 pushdown된 것을</p><p style="font-size: medium; ">&nbsp; &nbsp; 알 수 있다.</p><p style="font-size: medium; ">4)조건절(deptno = 30)에 해당하는 부분의 데이터만 읽는 효율적인 쿼리이다.</p><p style="font-size: medium; "><span style="font-size: 15px; font-family: Arial; vertical-align: baseline; white-space: pre-wrap; "></span><br /><span style="font-size: 15px; font-family: Arial; vertical-align: baseline; white-space: pre-wrap; ">9] 주관식</span><br /><span style="font-size: 15px; font-family: Arial; vertical-align: baseline; white-space: pre-wrap; ">다음 SQL문의 형태와 인덱스 컬럼 타입을 보고 나타날수 있는 비효율을 서술하고</span><br /><span style="font-size: 15px; font-family: Arial; vertical-align: baseline; white-space: pre-wrap; ">튜닝할수 수단을 서술(인덱스 튜닝 등) / SQL을 재작성 하시오</span><br /><span style="font-size: 15px; font-family: Arial; vertical-align: baseline; white-space: pre-wrap; "></span><br /><span style="font-size: 15px; font-family: Arial; vertical-align: baseline; white-space: pre-wrap; ">-- 인덱스 구성 : n1 ( col2 + col3 + col1)</span><br /><span style="font-size: 15px; font-family: Arial; vertical-align: baseline; white-space: pre-wrap; ">-- 컬럼 타입 : col1, col2, col3 : varchar2</span><br /><span style="font-size: 15px; font-family: Arial; vertical-align: baseline; white-space: pre-wrap; "> &nbsp;&nbsp;&nbsp;</span><br /><span style="font-size: 15px; font-family: Arial; vertical-align: baseline; white-space: pre-wrap; ">select col1, col2, col3</span><br /><span style="font-size: 15px; font-family: Arial; vertical-align: baseline; white-space: pre-wrap; "> from t1</span><br /><span style="font-size: 15px; font-family: Arial; vertical-align: baseline; white-space: pre-wrap; ">where col1 = 'CD111'</span><br /><span style="font-size: 15px; font-family: Arial; vertical-align: baseline; white-space: pre-wrap; "> &nbsp;and to_date(col2, 'YYYYMMDD') between to_date('20120701', 'YYYYMMDD') and to_date('20120731', 'YYYYMMDD')</span><br /><span style="font-size: 15px; font-family: Arial; vertical-align: baseline; white-space: pre-wrap; "> &nbsp;and col3 = 'AA'</span><br /><span style="font-size: 15px; font-family: Arial; vertical-align: baseline; white-space: pre-wrap; "></span><br /><span style="font-size: 15px; font-family: Arial; vertical-align: baseline; white-space: pre-wrap; ">union all</span><br /><span style="font-size: 15px; font-family: Arial; vertical-align: baseline; white-space: pre-wrap; "></span><br /><span style="font-size: 15px; font-family: Arial; vertical-align: baseline; white-space: pre-wrap; ">select col1, col2, col3</span><br /><span style="font-size: 15px; font-family: Arial; vertical-align: baseline; white-space: pre-wrap; "> from t1</span><br /><span style="font-size: 15px; font-family: Arial; vertical-align: baseline; white-space: pre-wrap; ">where col1 = 'CD112'</span><br /><span style="font-size: 15px; font-family: Arial; vertical-align: baseline; white-space: pre-wrap; "> &nbsp;and to_date(col2, 'YYYYMMDD') between to_date('20120701', 'YYYYMMDD') and to_date('20120731', 'YYYYMMDD')</span><br /><span style="font-size: 15px; font-family: Arial; vertical-align: baseline; white-space: pre-wrap; "> &nbsp;and col3 = 'AA'</span><br /><span style="font-size: 15px; font-family: Arial; vertical-align: baseline; white-space: pre-wrap; "></span><br /><span style="font-size: 15px; font-family: Arial; vertical-align: baseline; white-space: pre-wrap; "></span><br /><span style="font-size: 15px; font-family: Arial; vertical-align: baseline; white-space: pre-wrap; "></span><br /><span style="font-size: 15px; font-family: Arial; vertical-align: baseline; white-space: pre-wrap; ">4장</span><br /><span style="font-size: 15px; font-family: Arial; vertical-align: baseline; white-space: pre-wrap; ">10]인덱스에 대한 설명중 틀린것을 고르시오(2개)</span><br /><span style="font-size: 15px; font-family: Arial; vertical-align: baseline; white-space: pre-wrap; ">1)잦은 데이터 삭제로 Index Sparse 현상이 나타날수 있다.</span><br /><span style="font-size: 15px; font-family: Arial; vertical-align: baseline; white-space: pre-wrap; ">2)인덱스에는 null값 자체가 저장될수 없다.</span><br /><span style="font-size: 15px; font-family: Arial; vertical-align: baseline; white-space: pre-wrap; ">3)비트맵 인덱스는 b-tree인덱스에 비해 상대적으로 저장 공간을 적게 차지한다.</span><br /><span style="font-size: 15px; font-family: Arial; vertical-align: baseline; white-space: pre-wrap; ">4)비트맵 인덱스는 주로 값의 종류가 많은 경우에 유리하다.</span><br /><span style="font-size: 15px; font-family: Arial; vertical-align: baseline; white-space: pre-wrap; "></span><br /><span style="font-size: 15px; font-family: Arial; vertical-align: baseline; white-space: pre-wrap; ">11]다음 칸에 알맞은 공통된 단어를 넣으시오</span><br /><span style="font-size: 15px; font-family: Arial; vertical-align: baseline; white-space: pre-wrap; ">Oracle IOT를 개발하면서 두가지 액세스 방식을 모두 사용할 수있도록 설계하였다. IOT 레코드의 위치는 영구적이지 않기 때문에 물리적 주소(ROWID)대신 &nbsp;( &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;)를 사용한다.</span><br /><span style="font-size: 15px; font-family: Arial; vertical-align: baseline; white-space: pre-wrap; ">( &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;)는 PK와 &nbsp;physical guess로 구성된다.</span><br /><span style="font-size: 15px; font-family: Arial; vertical-align: baseline; white-space: pre-wrap; "></span><br />12]인덱스 구성에 대한 설명중 올바른 것을 고르시오</p><p style="font-size: medium; ">1)unique인덱스는 단일컬럼으로 구성되어야한다.</p><p style="font-size: medium; ">2)order by, group by를 위한 소트 연산을 줄이기 위해 인덱스에 컬럼을 추가하기도 한다.</p><p style="font-size: medium; ">3)조건절에 기술된 컬럼에 의해 선택도가 높은 인덱스가 있을경우 table full scan보다 해당</p><p style="font-size: medium; ">&nbsp; &nbsp;인덱스를 사용하는 것이 좋다.</p><p style="font-size: medium; ">4)<b id="internal-source-marker_0.9205300372559577" style="font-weight: normal; "></b></p><p style="display: inline !important; "><b id="internal-source-marker_0.9205300372559577" style="font-weight: normal; "><b id="internal-source-marker_0.9205300372559577" style="font-weight: normal; "></b></b></p><p style="display: inline !important; "><b id="internal-source-marker_0.9205300372559577" style="font-weight: normal; "><b id="internal-source-marker_0.9205300372559577" style="font-weight: normal; ">인덱스 선두 컬럼은 범위조건으로 사용되는 컬럼이 위치하는 것이 인덱스 활용에 유리하다</b></b></p><p></p><span style="font-size: 15px; font-family: Arial; vertical-align: baseline; white-space: pre-wrap; "></span><br /><br /><span style="font-size: 15px; font-family: Arial; vertical-align: baseline; white-space: pre-wrap; ">문자 vs 숫자 : 문자--&gt; 숫자</span><br /><span style="font-size: 15px; font-family: Arial; vertical-align: baseline; white-space: pre-wrap; ">문자 vs 날짜 : 문자--&gt; 날짜</span><br /><span style="font-size: 15px; font-family: Arial; vertical-align: baseline; white-space: pre-wrap; "></span><br /><span style="font-size: 15px; font-family: Arial; vertical-align: baseline; white-space: pre-wrap; "></span><br /><span style="font-size: 15px; font-family: Arial; vertical-align: baseline; white-space: pre-wrap; ">5장</span><br /><span style="font-size: 15px; font-family: Arial; vertical-align: baseline; white-space: pre-wrap; ">13]top-n sql 구현을 통해 소트 연산 횟수를 최소화하여 소트 영역 사용량을 최소화 하려고 한다. 다음 쿼리 문과 sql문을 보고 올바른 설명을 고르시오</span><br /><span style="font-size: 15px; font-family: Arial; vertical-align: baseline; white-space: pre-wrap; ">(인덱스 구성은 다음과 같다. &nbsp;&nbsp;pk_emp : empno)</span><br /><span style="font-size: 15px; font-family: Arial; vertical-align: baseline; white-space: pre-wrap; "></span><br /><p></p><p dir="ltr" style="margin-top: 0pt; margin-bottom: 0pt; font-size: medium; text-align: justify; "><span style="font-size: 15px; font-family: Arial; background-color: rgb(255, 255, 255); vertical-align: baseline; white-space: pre-wrap; ">select *</span></p><p dir="ltr" style="margin-top: 0pt; margin-bottom: 0pt; font-size: medium; text-align: justify; "><span style="font-size: 15px; font-family: Arial; background-color: rgb(255, 255, 255); vertical-align: baseline; white-space: pre-wrap; "> from </span></p><p dir="ltr" style="margin-top: 0pt; margin-bottom: 0pt; font-size: medium; text-align: justify; "><span style="font-size: 15px; font-family: Arial; background-color: rgb(255, 255, 255); vertical-align: baseline; white-space: pre-wrap; "><span class="Apple-tab-span" style="white-space: pre; "> </span>(select rownum rn</span></p><p dir="ltr" style="margin-top: 0pt; margin-bottom: 0pt; font-size: medium; text-align: justify; "><span style="font-size: 15px; font-family: Arial; background-color: rgb(255, 255, 255); vertical-align: baseline; white-space: pre-wrap; "><span class="Apple-tab-span" style="white-space: pre; "> </span> &nbsp;&nbsp;from </span></p><p dir="ltr" style="margin-top: 0pt; margin-bottom: 0pt; font-size: medium; text-align: justify; "><span style="font-size: 15px; font-family: Arial; background-color: rgb(255, 255, 255); vertical-align: baseline; white-space: pre-wrap; "><span class="Apple-tab-span" style="white-space: pre; "> </span><span class="Apple-tab-span" style="white-space: pre; "> </span>(select /*+ index (emp pk_emp) */ *</span></p><p dir="ltr" style="margin-top: 0pt; margin-bottom: 0pt; font-size: medium; text-align: justify; "><span style="font-size: 15px; font-family: Arial; background-color: rgb(255, 255, 255); vertical-align: baseline; white-space: pre-wrap; "><span class="Apple-tab-span" style="white-space: pre; "> </span><span class="Apple-tab-span" style="white-space: pre; "> </span> from emp</span></p><p dir="ltr" style="margin-top: 0pt; margin-bottom: 0pt; font-size: medium; text-align: justify; "><span style="font-size: 15px; font-family: Arial; background-color: rgb(255, 255, 255); vertical-align: baseline; white-space: pre-wrap; "><span class="Apple-tab-span" style="white-space: pre; "> </span><span class="Apple-tab-span" style="white-space: pre; "> </span>order by empno )</span></p><p dir="ltr" style="margin-top: 0pt; margin-bottom: 0pt; font-size: medium; text-align: justify; "><span style="font-size: 15px; font-family: Arial; background-color: rgb(255, 255, 255); vertical-align: baseline; white-space: pre-wrap; "><span class="Apple-tab-span" style="white-space: pre; "> </span> &nbsp;)</span></p><p dir="ltr" style="margin-top: 0pt; margin-bottom: 0pt; font-size: medium; text-align: justify; "><span style="font-size: 15px; font-family: Arial; background-color: rgb(255, 255, 255); vertical-align: baseline; white-space: pre-wrap; "> where &nbsp;rn between 21 and 30;</span></p><p style="font-size: medium; "><span style="font-size: 15px; font-family: Arial; background-color: rgb(255, 255, 255); vertical-align: baseline; white-space: pre-wrap; "></span><span style="font-size: 15px; font-family: Verdana; background-color: rgb(255, 255, 255); vertical-align: baseline; white-space: pre-wrap; ">
<b id="internal-source-marker_0.9205300372559577" style="font-family: Gulim; white-space: normal; font-size: medium; font-weight: normal; "><span style="font-size: 15px; font-family: Verdana; vertical-align: baseline; white-space: pre-wrap; ">------------------------------------------</span></b>
| Id &nbsp;| Operation &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<span class="Apple-tab-span" style="white-space: pre; "> </span>
<b id="internal-source-marker_0.9205300372559577" style="font-family: Gulim; white-space: normal; font-size: medium; font-weight: normal; "><span style="font-size: 15px; font-family: Verdana; vertical-align: baseline; white-space: pre-wrap; ">------------------------------------------</span></b>
| &nbsp;&nbsp;0 | SELECT STATEMENT <br />|* &nbsp;1 | &nbsp;VIEW &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<span class="Apple-tab-span" style="white-space: pre; "> </span><br />| &nbsp;&nbsp;2 | &nbsp;&nbsp;</span><span style="font-size: 15px; font-family: Verdana; background-color: rgb(255, 255, 255); font-weight: bold; text-decoration: underline; vertical-align: baseline; white-space: pre-wrap; ">COUNT</span><span style="font-size: 15px; font-family: Verdana; background-color: rgb(255, 255, 255); vertical-align: baseline; white-space: pre-wrap; "> &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<span class="Apple-tab-span" style="white-space: pre; "> </span><br />| &nbsp;&nbsp;3 | &nbsp;&nbsp;&nbsp;VIEW &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<span class="Apple-tab-span" style="white-space: pre; "> </span><br />| &nbsp;&nbsp;4 | &nbsp;&nbsp;&nbsp;&nbsp;INDEX FULL SCAN( PK_EMP )<br />------------------------------------------<br /> <br />Predicate Information (identified by operation id):<br />---------------------------------------------------<br /> <br /> &nbsp;&nbsp;1 - filter("RN"&lt;=30 AND "RN"&gt;=21)</span><span style="font-size: 15px; font-family: Arial; vertical-align: baseline; white-space: pre-wrap; "></span><br /><span style="font-size: 15px; font-family: Arial; vertical-align: baseline; white-space: pre-wrap; "></span><br /><span style="font-size: 15px; font-family: Arial; vertical-align: baseline; white-space: pre-wrap; ">1)위의 실행계획을 보면 정상적으로 top-n효과가 나타난 것을 확인 할 수 있다.</span><br /><span style="font-size: 15px; font-family: Arial; vertical-align: baseline; white-space: pre-wrap; ">2)인라인 뷰의 중첩된 사용으로 인해 &nbsp;top-n효과가 나타나지 않았다.</span><br /><span style="font-size: 15px; font-family: Arial; vertical-align: baseline; white-space: pre-wrap; ">3)인덱스 사용으로 top-n효과가 나타났다.</span><br /><span style="font-size: 15px; font-family: Arial; vertical-align: baseline; white-space: pre-wrap; ">4)filter predicate을 통해 &nbsp;top-n효과가 정상적으로 나타나지 않은것을 알 수 있다.</span><br /><span style="font-size: 15px; font-family: Arial; vertical-align: baseline; white-space: pre-wrap; "></span><br /><span style="font-size: 15px; font-family: Arial; vertical-align: baseline; white-space: pre-wrap; "></span><br /><span style="font-size: 15px; font-family: Arial; vertical-align: baseline; white-space: pre-wrap; ">14]다음 쿼리에 대해 올바른 설명을 고르시오</span><br /><span style="font-size: 15px; font-family: Arial; vertical-align: baseline; white-space: pre-wrap; ">select /*+ pq_distribute(e, none, none) */ *</span><br /><span style="font-size: 15px; font-family: Arial; vertical-align: baseline; white-space: pre-wrap; "> from dept d, emp e</span><br /><span style="font-size: 15px; font-family: Arial; vertical-align: baseline; white-space: pre-wrap; ">where e.deptno = d.deptno</span><br /><span style="font-size: 15px; font-family: Arial; vertical-align: baseline; white-space: pre-wrap; "></span><br /><span style="font-size: 15px; font-family: Arial; vertical-align: baseline; white-space: pre-wrap; ">1)병렬관련 힌트를 사용하여 emp 테이블을 먼저 읽는 형태(outter table)로 실행된다.</span><br /><span style="font-size: 15px; font-family: Arial; vertical-align: baseline; white-space: pre-wrap; ">2)두번째 파라미터는 inner table의 병렬도를 설정하는 값으로, none 값을 주어 optimizer가</span><br /><span style="font-size: 15px; font-family: Arial; vertical-align: baseline; white-space: pre-wrap; "> &nbsp;&nbsp;&nbsp;스스로 판단하도록 유도 했다.</span><br /><span style="font-size: 15px; font-family: Arial; vertical-align: baseline; white-space: pre-wrap; ">3)세번째 파라미터는 outer 테이블의 distirbution 방식을 지정하는 값으로 none 값을 주어</span><br /><span style="font-size: 15px; font-family: Arial; vertical-align: baseline; white-space: pre-wrap; "> &nbsp;&nbsp;&nbsp;optimizer가 스스로 판단하도록 유도 했다.</span><br /><span style="font-size: 15px; font-family: Arial; vertical-align: baseline; white-space: pre-wrap; ">4)full-partition wise join을 유도한 힌트이다.</span></p><p style="font-size: medium; "><span style="font-size: 15px; font-family: Arial; vertical-align: baseline; white-space: pre-wrap; "><br /></span></p><p style="font-size: medium; "><span style="font-size: 15px; font-family: Arial; vertical-align: baseline; white-space: pre-wrap; ">15]다음 sql문에 대한 설명으로 틀린 것을 고르시오</span></p><p style="font-size: medium; "><span style="font-size: 15px; font-family: Arial; vertical-align: baseline; white-space: pre-wrap; ">(table emp : 사원 테이블)</span></p><p style="font-size: medium; "><span style="font-size: 15px; font-family: Arial; vertical-align: baseline; white-space: pre-wrap; ">(dept_no 컬럼 : 부서번호 )</span></p><p style="font-size: medium; "><span style="font-size: 15px; font-family: Arial; vertical-align: baseline; white-space: pre-wrap; "><br /></span></p><p><font face="Arial"><span style="font-size: 14.857142448425293px; white-space: pre-wrap; ">select dept_no, count(*) cnt,</span></font></p><p><font face="Arial"><span style="font-size: 14.857142448425293px; white-space: pre-wrap; "> (select count(*) from emp) totalEmp</span></font></p><p><font face="Arial"><span style="font-size: 14.857142448425293px; white-space: pre-wrap; ">from emp</span></font></p><p><font face="Arial"><span style="font-size: 14.857142448425293px; white-space: pre-wrap; ">group by dept_no</span></font></p><p><font face="Arial"><span style="font-size: 14.857142448425293px; white-space: pre-wrap; "><br /></span></font></p><p><font face="Arial"><span style="font-size: 14.857142448425293px; white-space: pre-wrap; ">1)부서번호, 부서별 사원수, 전체 사원수를 select한다.</span></font></p><p><font face="Arial"><span style="font-size: 14.857142448425293px; white-space: pre-wrap; ">2)select 절에 기술된 스칼라 서브쿼리의 경우 dept_no수만큼 실행된다.</span></font></p><p><font face="Arial"><span style="font-size: 14.857142448425293px; white-space: pre-wrap; ">3)일반적인 형태의 sql로 흠잡을데 없는 쿼리이다.</span></font></p><p><font face="Arial"><span style="font-size: 14.857142448425293px; white-space: pre-wrap; ">4)emp table을 두번 읽게되며 특히 스칼라 서브쿼리의 경우 같은 값을 반복적으로 실행하는 비효율이 발생한다.</span></font></p><p style="font-size: medium; "><span style="font-size: 15px; font-family: Arial; vertical-align: baseline; white-space: pre-wrap; "><br /></span></p></b>
ljw 2012.07.25 10:26:29
복원
<b id="internal-source-marker_0.9205300372559577" style="font-size: medium; line-height: normal; font-weight: normal; "><span style="font-size: 15px; font-family: Arial; vertical-align: baseline; white-space: pre-wrap; ">3과목</span><br /><span style="font-size: 15px; font-family: Arial; vertical-align: baseline; white-space: pre-wrap; ">1장</span><br /><span style="font-size: 15px; font-family: Arial; vertical-align: baseline; white-space: pre-wrap; ">1]버퍼 블록의 상태 종류중 틀린것은?</span><br /><span style="font-size: 15px; font-family: Arial; vertical-align: baseline; white-space: pre-wrap; ">1)Free 버퍼</span><br /><span style="font-size: 15px; font-family: Arial; vertical-align: baseline; white-space: pre-wrap; ">2)Dirty 버퍼</span><br /><span style="font-size: 15px; font-family: Arial; vertical-align: baseline; white-space: pre-wrap; ">3)Clean 버퍼</span><br /><span style="font-size: 15px; font-family: Arial; vertical-align: baseline; white-space: pre-wrap; ">4)Pinned버퍼</span><br /><span style="font-size: 15px; font-family: Arial; vertical-align: baseline; white-space: pre-wrap; "></span><br /><span style="font-size: 15px; font-family: Arial; vertical-align: baseline; white-space: pre-wrap; ">2]괄호 안에 알맞은 단어를 적으시오(단답형)</span><br /><span style="font-size: 15px; font-family: Arial; vertical-align: baseline; white-space: pre-wrap; ">사용자 정의 함수/프로시저는 내장함수처럼 Native 코드로 완전 컴파일된 형태가 아니어서</span><br /><span style="font-size: 15px; font-family: Arial; vertical-align: baseline; white-space: pre-wrap; ">가상머신 같은 별도의 실행엔진을 통해 실행된다. 실행 될 때마다 &nbsp;&nbsp;( &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;)이 일어나며,</span><br /><span style="font-size: 15px; font-family: Arial; vertical-align: baseline; white-space: pre-wrap; ">이 때문에 내장함수를 호출할 때와 비교해 성능을 상당히 떨어뜨린다.</span><br /><span style="font-size: 15px; font-family: Arial; vertical-align: baseline; white-space: pre-wrap; "></span><br /><span style="font-size: 15px; font-family: Arial; vertical-align: baseline; white-space: pre-wrap; "></span><br /><span style="font-size: 15px; font-family: Arial; vertical-align: baseline; white-space: pre-wrap; "></span><br /><span style="font-size: 15px; font-family: Arial; vertical-align: baseline; white-space: pre-wrap; ">(기출문제) 다음 괄호 안에 들어갈 올바른 용어의 순서로 올바른 것을 선택하세요</span><br /><span style="font-size: 15px; font-family: Arial; vertical-align: baseline; white-space: pre-wrap; "></span><br /><span style="font-size: 15px; font-family: Arial; vertical-align: baseline; white-space: pre-wrap; ">Response Time = ( &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;) + Wait Time</span><br /><span style="font-size: 15px; font-family: Arial; vertical-align: baseline; white-space: pre-wrap; "> &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;= ( &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;) + Queue Time</span><br /><span style="font-size: 15px; font-family: Arial; vertical-align: baseline; white-space: pre-wrap; "></span><br /><span style="font-size: 15px; font-family: Arial; vertical-align: baseline; white-space: pre-wrap; ">1) Service Time, Cpu Time &nbsp;</span><br /><span style="font-size: 15px; font-family: Arial; vertical-align: baseline; white-space: pre-wrap; ">2) Cpu Time, Service Time</span><br /><span style="font-size: 15px; font-family: Arial; vertical-align: baseline; white-space: pre-wrap; ">3) Idle Time, Service Time</span><br /><span style="font-size: 15px; font-family: Arial; vertical-align: baseline; white-space: pre-wrap; ">4) Elapsed Time, Cpu Time</span><br /><span style="font-size: 15px; font-family: Arial; vertical-align: baseline; white-space: pre-wrap; "></span><br /><span style="font-size: 15px; font-family: Arial; vertical-align: baseline; white-space: pre-wrap; "></span><br /><span style="font-size: 15px; font-family: Arial; vertical-align: baseline; white-space: pre-wrap; ">2장</span><br /><span style="font-size: 15px; font-family: Arial; vertical-align: baseline; white-space: pre-wrap; ">3]괄호안에 알맞은 단어를 적으시오(단답형)</span><br /><span style="font-size: 15px; font-family: Arial; vertical-align: baseline; white-space: pre-wrap; ">( &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;) 이란 관리할 Lock 리소스가 정해진 임계치를 넘으면서 로우 레벨 락이 페이지, 익스텐트, 테이블 레벨 락으로 점점 확장되는 것을 말한다.</span><br /><span style="font-size: 15px; font-family: Arial; vertical-align: baseline; white-space: pre-wrap; "></span><br /><span style="font-size: 15px; font-family: Arial; vertical-align: baseline; white-space: pre-wrap; ">4] 괄호안에 알맞은 공통된 단어를 적으시오(단답형)</span><br /><span style="font-size: 15px; font-family: Arial; vertical-align: baseline; white-space: pre-wrap; ">( &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;)은 다른 트랜잭션에 의해 데이터의 추가, 변경, 삭제가 발생하더라도 단일 SQL문 내에서 일관성 있게 값을 읽는 것을 말한다. ( &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;)의 기준 시점은 쿼리 시작 시점이 된다.</span><br /><span style="font-size: 15px; font-family: Arial; vertical-align: baseline; white-space: pre-wrap; "></span><br /><span style="font-size: 15px; font-family: Arial; vertical-align: baseline; white-space: pre-wrap; "></span><br /><span style="font-size: 15px; font-family: Arial; vertical-align: baseline; white-space: pre-wrap; ">기출문제 트랜잭션의 특징</span><br /><span style="font-size: 15px; font-family: Arial; vertical-align: baseline; white-space: pre-wrap; ">원자성 / 일관성 / 격리성 / 영속성</span><br /><span style="font-size: 15px; font-family: Arial; vertical-align: baseline; white-space: pre-wrap; "></span><br /><span style="font-size: 15px; font-family: Arial; vertical-align: baseline; white-space: pre-wrap; "></span><br /><span style="font-size: 15px; font-family: Arial; vertical-align: baseline; white-space: pre-wrap; ">3장</span><br /><span style="font-size: 15px; font-family: Arial; vertical-align: baseline; white-space: pre-wrap; ">5] 다음 sql 문장을 실행한 결과 emp 테이블을 table full scan하는 실행계획이 나왔다.</span><br /><span style="font-size: 15px; font-family: Arial; vertical-align: baseline; white-space: pre-wrap; ">실행결과와 sql문의 형태만을 보고 유추한 사실중 올바른것 2개를 고르시오</span><br /><span style="font-size: 15px; font-family: Arial; vertical-align: baseline; white-space: pre-wrap; ">select * </span><br /><span style="font-size: 15px; font-family: Arial; vertical-align: baseline; white-space: pre-wrap; "> &nbsp;from emp</span><br /><span style="font-size: 15px; font-family: Arial; vertical-align: baseline; white-space: pre-wrap; ">where job = ‘CLERK’ or deptno=20;</span><br /><span style="font-size: 15px; font-family: Arial; vertical-align: baseline; white-space: pre-wrap; "></span><br /><span style="font-size: 15px; font-family: Arial; vertical-align: baseline; white-space: pre-wrap; ">1)emp 테이블에 인덱스가 전혀 없을수도 있다.</span><br /><span style="font-size: 15px; font-family: Arial; vertical-align: baseline; white-space: pre-wrap; ">2)인덱스가 한개만 있더라도 table full scan 실행계획이 나올 수 있다.</span><br /><span style="font-size: 15px; font-family: Arial; vertical-align: baseline; white-space: pre-wrap; ">3)select 절에 * 대신 job, deptno으로 적어주면 table full scan 실행계획이 나타나지 않는다.</span><br /><span style="font-size: 15px; font-family: Arial; vertical-align: baseline; white-space: pre-wrap; ">4)or 조건을 and로 바꿔야 table full scan 실행계획이 사라지고, 결과 값도 동일하다.</span><br /><span style="font-size: 15px; font-family: Arial; vertical-align: baseline; white-space: pre-wrap; "></span><br /><span style="font-size: 15px; font-family: Arial; vertical-align: baseline; white-space: pre-wrap; "></span><br /><span style="font-size: 15px; font-family: Arial; vertical-align: baseline; white-space: pre-wrap; ">6] 주관식</span><br /><span style="font-size: 15px; font-family: Arial; vertical-align: baseline; white-space: pre-wrap; ">다음 SQL문의 형태와 인덱스 컬럼 타입을 보고 나타날수 있는 비효율을 서술하고</span><br /><span style="font-size: 15px; font-family: Arial; vertical-align: baseline; white-space: pre-wrap; ">튜닝할수 수단을 서술(인덱스 튜닝 등) / SQL을 재작성 하시오</span><br /><span style="font-size: 15px; font-family: Arial; vertical-align: baseline; white-space: pre-wrap; "></span><br /><span style="font-size: 15px; font-family: Arial; vertical-align: baseline; white-space: pre-wrap; ">-- 인덱스 구성 : n1 ( col2 + col3 + col1)</span><br /><span style="font-size: 15px; font-family: Arial; vertical-align: baseline; white-space: pre-wrap; ">-- 컬럼 타입 : col1, col2, col3 : varchar2</span><br /><span style="font-size: 15px; font-family: Arial; vertical-align: baseline; white-space: pre-wrap; "> &nbsp;&nbsp;&nbsp;</span><br /><span style="font-size: 15px; font-family: Arial; vertical-align: baseline; white-space: pre-wrap; ">select col1, col2, col3</span><br /><span style="font-size: 15px; font-family: Arial; vertical-align: baseline; white-space: pre-wrap; "> from t1</span><br /><span style="font-size: 15px; font-family: Arial; vertical-align: baseline; white-space: pre-wrap; ">where col1 = 'CD111'</span><br /><span style="font-size: 15px; font-family: Arial; vertical-align: baseline; white-space: pre-wrap; "> &nbsp;and to_date(col2, 'YYYYMMDD') between to_date('20120701', 'YYYYMMDD') and to_date('20120731', 'YYYYMMDD')</span><br /><span style="font-size: 15px; font-family: Arial; vertical-align: baseline; white-space: pre-wrap; "> &nbsp;and col3 = 'AA'</span><br /><span style="font-size: 15px; font-family: Arial; vertical-align: baseline; white-space: pre-wrap; "></span><br /><span style="font-size: 15px; font-family: Arial; vertical-align: baseline; white-space: pre-wrap; ">union all</span><br /><span style="font-size: 15px; font-family: Arial; vertical-align: baseline; white-space: pre-wrap; "></span><br /><span style="font-size: 15px; font-family: Arial; vertical-align: baseline; white-space: pre-wrap; ">select col1, col2, col3</span><br /><span style="font-size: 15px; font-family: Arial; vertical-align: baseline; white-space: pre-wrap; "> from t1</span><br /><span style="font-size: 15px; font-family: Arial; vertical-align: baseline; white-space: pre-wrap; ">where col1 = 'CD112'</span><br /><span style="font-size: 15px; font-family: Arial; vertical-align: baseline; white-space: pre-wrap; "> &nbsp;and to_date(col2, 'YYYYMMDD') between to_date('20120701', 'YYYYMMDD') and to_date('20120731', 'YYYYMMDD')</span><br /><span style="font-size: 15px; font-family: Arial; vertical-align: baseline; white-space: pre-wrap; "> &nbsp;and col3 = 'AA'</span><br /><span style="font-size: 15px; font-family: Arial; vertical-align: baseline; white-space: pre-wrap; "></span><br /><span style="font-size: 15px; font-family: Arial; vertical-align: baseline; white-space: pre-wrap; "></span><br /><span style="font-size: 15px; font-family: Arial; vertical-align: baseline; white-space: pre-wrap; "></span><br /><span style="font-size: 15px; font-family: Arial; vertical-align: baseline; white-space: pre-wrap; ">4장</span><br /><span style="font-size: 15px; font-family: Arial; vertical-align: baseline; white-space: pre-wrap; ">7]인덱스에 대한 설명중 틀린것을 고르시오(2개)</span><br /><span style="font-size: 15px; font-family: Arial; vertical-align: baseline; white-space: pre-wrap; ">1)잦은 데이터 삭제로 Index Sparse 현상이 나타날수 있다.</span><br /><span style="font-size: 15px; font-family: Arial; vertical-align: baseline; white-space: pre-wrap; ">2)인덱스에는 null값 자체가 저장될수 없다.</span><br /><span style="font-size: 15px; font-family: Arial; vertical-align: baseline; white-space: pre-wrap; ">3)비트맵 인덱스는 b-tree인덱스에 비해 상대적으로 저장 공간을 적게 차지한다.</span><br /><span style="font-size: 15px; font-family: Arial; vertical-align: baseline; white-space: pre-wrap; ">4)비트맵 인덱스는 주로 값의 종류가 많은 경우에 유리하다.</span><br /><span style="font-size: 15px; font-family: Arial; vertical-align: baseline; white-space: pre-wrap; "></span><br /><span style="font-size: 15px; font-family: Arial; vertical-align: baseline; white-space: pre-wrap; ">8]다음 칸에 알맞은 공통된 단어를 넣으시오</span><br /><span style="font-size: 15px; font-family: Arial; vertical-align: baseline; white-space: pre-wrap; ">Oracle IOT를 개발하면서 두가지 액세스 방식을 모두 사용할 수있도록 설계하였다. IOT 레코드의 위치는 영구적이지 않기 때문에 물리적 주소(ROWID)대신 &nbsp;( &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;)를 사용한다.</span><br /><span style="font-size: 15px; font-family: Arial; vertical-align: baseline; white-space: pre-wrap; ">( &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;)는 PK와 &nbsp;physical guess로 구성된다.</span><br /><span style="font-size: 15px; font-family: Arial; vertical-align: baseline; white-space: pre-wrap; "></span><br /><span style="font-size: 15px; font-family: Arial; vertical-align: baseline; white-space: pre-wrap; "></span><br /><span style="font-size: 15px; font-family: Arial; vertical-align: baseline; white-space: pre-wrap; ">문자 vs 숫자 : 문자--&gt; 숫자</span><br /><span style="font-size: 15px; font-family: Arial; vertical-align: baseline; white-space: pre-wrap; ">문자 vs 날짜 : 문자--&gt; 날짜</span><br /><span style="font-size: 15px; font-family: Arial; vertical-align: baseline; white-space: pre-wrap; "></span><br /><span style="font-size: 15px; font-family: Arial; vertical-align: baseline; white-space: pre-wrap; "></span><br /><span style="font-size: 15px; font-family: Arial; vertical-align: baseline; white-space: pre-wrap; ">5장</span><br /><span style="font-size: 15px; font-family: Arial; vertical-align: baseline; white-space: pre-wrap; ">9]top-n sql 구현을 통해 소트 연산 횟수를 최소화하여 소트 영역 사용량을 최소화 하려고 한다. 다음 쿼리 문과 sql문을 보고 올바른 설명을 고르시오</span><br /><span style="font-size: 15px; font-family: Arial; vertical-align: baseline; white-space: pre-wrap; ">(인덱스 구성은 다음과 같다. &nbsp;&nbsp;pk_emp : empno)</span><br /><span style="font-size: 15px; font-family: Arial; vertical-align: baseline; white-space: pre-wrap; "></span><br /><p dir="ltr" style="text-align: justify; margin-top: 0pt; margin-bottom: 0pt; "><span style="font-size: 15px; font-family: Arial; background-color: rgb(255, 255, 255); vertical-align: baseline; white-space: pre-wrap; ">select *</span></p><p dir="ltr" style="text-align: justify; margin-top: 0pt; margin-bottom: 0pt; "><span style="font-size: 15px; font-family: Arial; background-color: rgb(255, 255, 255); vertical-align: baseline; white-space: pre-wrap; "> from </span></p><p dir="ltr" style="text-align: justify; margin-top: 0pt; margin-bottom: 0pt; "><span style="font-size: 15px; font-family: Arial; background-color: rgb(255, 255, 255); vertical-align: baseline; white-space: pre-wrap; "><span class="Apple-tab-span" style="white-space: pre; "> </span>(select rownum rn</span></p><p dir="ltr" style="text-align: justify; margin-top: 0pt; margin-bottom: 0pt; "><span style="font-size: 15px; font-family: Arial; background-color: rgb(255, 255, 255); vertical-align: baseline; white-space: pre-wrap; "><span class="Apple-tab-span" style="white-space: pre; "> </span> &nbsp;&nbsp;from </span></p><p dir="ltr" style="text-align: justify; margin-top: 0pt; margin-bottom: 0pt; "><span style="font-size: 15px; font-family: Arial; background-color: rgb(255, 255, 255); vertical-align: baseline; white-space: pre-wrap; "><span class="Apple-tab-span" style="white-space: pre; "> </span><span class="Apple-tab-span" style="white-space: pre; "> </span>(select /*+ index (emp pk_emp) */ *</span></p><p dir="ltr" style="text-align: justify; margin-top: 0pt; margin-bottom: 0pt; "><span style="font-size: 15px; font-family: Arial; background-color: rgb(255, 255, 255); vertical-align: baseline; white-space: pre-wrap; "><span class="Apple-tab-span" style="white-space: pre; "> </span><span class="Apple-tab-span" style="white-space: pre; "> </span> from emp</span></p><p dir="ltr" style="text-align: justify; margin-top: 0pt; margin-bottom: 0pt; "><span style="font-size: 15px; font-family: Arial; background-color: rgb(255, 255, 255); vertical-align: baseline; white-space: pre-wrap; "><span class="Apple-tab-span" style="white-space: pre; "> </span><span class="Apple-tab-span" style="white-space: pre; "> </span>order by empno )</span></p><p dir="ltr" style="text-align: justify; margin-top: 0pt; margin-bottom: 0pt; "><span style="font-size: 15px; font-family: Arial; background-color: rgb(255, 255, 255); vertical-align: baseline; white-space: pre-wrap; "><span class="Apple-tab-span" style="white-space: pre; "> </span> &nbsp;)</span></p><p dir="ltr" style="text-align: justify; margin-top: 0pt; margin-bottom: 0pt; "><span style="font-size: 15px; font-family: Arial; background-color: rgb(255, 255, 255); vertical-align: baseline; white-space: pre-wrap; "> where &nbsp;rn between 21 and 30;</span></p><span style="font-size: 15px; font-family: Arial; background-color: rgb(255, 255, 255); vertical-align: baseline; white-space: pre-wrap; "></span><span style="font-size: 15px; font-family: Verdana; background-color: rgb(255, 255, 255); vertical-align: baseline; white-space: pre-wrap; ">
<b id="internal-source-marker_0.9205300372559577" style="font-family: Gulim; white-space: normal; font-size: medium; font-weight: normal; "><span style="font-size: 15px; font-family: Verdana; vertical-align: baseline; white-space: pre-wrap; ">------------------------------------------</span></b>
| Id &nbsp;| Operation &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<span class="Apple-tab-span" style="white-space:pre"> </span>
<b id="internal-source-marker_0.9205300372559577" style="font-family: Gulim; white-space: normal; font-size: medium; font-weight: normal; "><span style="font-size: 15px; font-family: Verdana; vertical-align: baseline; white-space: pre-wrap; ">------------------------------------------</span></b>
| &nbsp;&nbsp;0 | SELECT STATEMENT <br />|* &nbsp;1 | &nbsp;VIEW &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<span class="Apple-tab-span" style="white-space:pre"> </span><br />| &nbsp;&nbsp;2 | &nbsp;&nbsp;</span><span style="font-size: 15px; font-family: Verdana; background-color: rgb(255, 255, 255); font-weight: bold; text-decoration: underline; vertical-align: baseline; white-space: pre-wrap; ">COUNT</span><span style="font-size: 15px; font-family: Verdana; background-color: rgb(255, 255, 255); vertical-align: baseline; white-space: pre-wrap; "> &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<span class="Apple-tab-span" style="white-space:pre"> </span><br />| &nbsp;&nbsp;3 | &nbsp;&nbsp;&nbsp;VIEW &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<span class="Apple-tab-span" style="white-space:pre"> </span><br />| &nbsp;&nbsp;4 | &nbsp;&nbsp;&nbsp;&nbsp;INDEX FULL SCAN( PK_EMP )<br />------------------------------------------<br /> <br />Predicate Information (identified by operation id):<br />---------------------------------------------------<br /> <br /> &nbsp;&nbsp;1 - filter("RN"&lt;=30 AND "RN"&gt;=21)</span><span style="font-size: 15px; font-family: Arial; vertical-align: baseline; white-space: pre-wrap; "></span><br /><span style="font-size: 15px; font-family: Arial; vertical-align: baseline; white-space: pre-wrap; "></span><br /><span style="font-size: 15px; font-family: Arial; vertical-align: baseline; white-space: pre-wrap; ">1)위의 실행계획을 보면 정상적으로 top-n효과가 나타난 것을 확인 할 수 있다.</span><br /><span style="font-size: 15px; font-family: Arial; vertical-align: baseline; white-space: pre-wrap; ">2)인라인 뷰의 중첩된 사용으로 인해 &nbsp;top-n효과가 나타나지 않았다.</span><br /><span style="font-size: 15px; font-family: Arial; vertical-align: baseline; white-space: pre-wrap; ">3)인덱스 사용으로 top-n효과가 나타났다.</span><br /><span style="font-size: 15px; font-family: Arial; vertical-align: baseline; white-space: pre-wrap; ">4)filter predicate을 통해 &nbsp;top-n효과가 정상적으로 나타나지 않은것을 알 수 있다.</span><br /><span style="font-size: 15px; font-family: Arial; vertical-align: baseline; white-space: pre-wrap; "></span><br /><span style="font-size: 15px; font-family: Arial; vertical-align: baseline; white-space: pre-wrap; "></span><br /><span style="font-size: 15px; font-family: Arial; vertical-align: baseline; white-space: pre-wrap; ">10]다음 쿼리에 대해 올바른 설명을 고르시오</span><br /><span style="font-size: 15px; font-family: Arial; vertical-align: baseline; white-space: pre-wrap; ">select /*+ pq_distribute(e, none, none) */ *</span><br /><span style="font-size: 15px; font-family: Arial; vertical-align: baseline; white-space: pre-wrap; "> from dept d, emp e</span><br /><span style="font-size: 15px; font-family: Arial; vertical-align: baseline; white-space: pre-wrap; ">where e.deptno = d.deptno</span><br /><span style="font-size: 15px; font-family: Arial; vertical-align: baseline; white-space: pre-wrap; "></span><br /><span style="font-size: 15px; font-family: Arial; vertical-align: baseline; white-space: pre-wrap; ">1)병렬관련 힌트를 사용하여 emp 테이블을 먼저 읽는 형태(outter table)로 실행된다.</span><br /><span style="font-size: 15px; font-family: Arial; vertical-align: baseline; white-space: pre-wrap; ">2)두번째 파라미터는 inner table의 병렬도를 설정하는 값으로, none 값을 주어 optimizer가</span><br /><span style="font-size: 15px; font-family: Arial; vertical-align: baseline; white-space: pre-wrap; "> &nbsp;&nbsp;&nbsp;스스로 판단하도록 유도 했다.</span><br /><span style="font-size: 15px; font-family: Arial; vertical-align: baseline; white-space: pre-wrap; ">3)세번째 파라미터는 outer 테이블의 distirbution 방식을 지정하는 값으로 none 값을 주어</span><br /><span style="font-size: 15px; font-family: Arial; vertical-align: baseline; white-space: pre-wrap; "> &nbsp;&nbsp;&nbsp;optimizer가 스스로 판단하도록 유도 했다.</span><br /><span style="font-size: 15px; font-family: Arial; vertical-align: baseline; white-space: pre-wrap; ">4)full-partition wise join을 유도한 힌트이다.</span></b>
ljw 2012.07.23 09:30:17