Let's have a cup of coffee.

1. 아래의 SQL을 튜닝하시오

SELECT COUNT(*)
  FROM 구매내역 A, 결제내역 B
 WHERE A.주문번호 = B.주문번호
   AND B.주문코드 = 'O801'
   AND B.주문일시 BETWEEN :FROM ||'000000'
                     AND :TO ||'235959'
   AND A.주문번호 IN
      ( SELECT 주문번호
          FROM 구매내역
         MINUS 
        SELECT 주문번호
          FROM 구매취소내역
         WHERE 판매센터ID = :CENTER_ID
       )
;

-------------------------------------------------------------------------------------------
|Id|Operation                   |Name           |Starts|E-Rows|A-Rows|  A-Time   |Buffers |
-------------------------------------------------------------------------------------------
| 0|SELECT STATEMENT            |               |     1|      |     1|00:00:00.53|    3042|
| 1| SORT AGGREGATE             |		|     1|     1|	    1|00:00:00.53|    3042|
| 2|  FILTER                    |		|     1|      | 11405|00:00:00.47|    3042|
| 3|   HASH JOIN                |		|     1| 11681| 11405|00:00:00.46|    3042|
| 4|    HASH JOIN               |		|     1| 11681| 11412|00:00:00.08|    1559|
| 5|     INDEX RANGE SCAN       |결제내역_IX3   |     1| 11482| 11412|00:00:00.01|      78|
| 6|     INDEX FAST FULL SCAN   |구매내역_PK    |     1|  459K|  459K|00:00:00.01|    1481|
| 7|    VIEW                    |VW_NSO_1       |     1|  459K|  458K|00:00:00.21|    1483|
| 8|     MINUS                  |               |     1|  458K|  458K|00:00:00.21|    1483|
| 9|      SORT UNIQUE           |	        |     1|  459K|  459K|00:00:00.21|    1481|
|10|       INDEX FAST FULL SCAN |구매내역_PK    |     1|  459K|  459K|00:00:00.01|    1481|
|11|      SORT UNIQUE           |               |     1|   115|   174|00:00:00.01|       2|
|12|       INDEX RANGE SCAN     |구매취소내역_IX2|     1|   115|   178|00:00:00.01|       2|
-------------------------------------------------------------------------------------------

2. 위의 서브쿼리는 _________ 로 변형되었다.

1. 아래 SQL은 인덱스 컬럼을 가공했는데, 어떻게 인덱스를 Range Scan 하나요?

주문상세_IX1 : 주문일자 + 주문구분코드 + 주문회차 + 주문번호

select * 
  from 주문상세
 where 주문일자 = :order_date
   and substr(주문구분코드, 1, 4) = :order_gbcd
   and 주문회차 = :order_th
   and 주문번호 = :order_no

Execution Plan
----------------------------------------------------------------------
 0      SELECT STATEMENT Optimizer=ALL_ROWS
 1   0    TABLE ACCESS (BY INDEX ROWID) OF '주문상세' (TABLE)
 2   1      INDEX (RANGE SCAN) OF '주문상세_IX1' (INDEX)

2. EMP테이블에서 ENAME = ‘FORD’ 의 최상위 관리자부터 시작하여 하위 직원(관리자)을 차례대로 출력 하시오.

쿼리를 작성할 때 ‘FORT’의 최상위 상사가 몇명인지 모른다고 가정하고 작성한다.

최상위 관리자의 MGR컬럼의 값은 NULL이다. 출력 할 때 최상위 관리자는 모든 레코드에 표시한다.

--

alter session set nls_Date_format='RR/MM/DD';
drop table emp;
drop table dept;


CREATE TABLE DEPT
       (DEPTNO number(10),
        DNAME VARCHAR2(14),
        LOC VARCHAR2(13) );


INSERT INTO DEPT VALUES (10, 'ACCOUNTING', 'NEW YORK');
INSERT INTO DEPT VALUES (20, 'RESEARCH',   'DALLAS');
INSERT INTO DEPT VALUES (30, 'SALES',      'CHICAGO');
INSERT INTO DEPT VALUES (40, 'OPERATIONS', 'BOSTON');

CREATE TABLE EMP (
 EMPNO               NUMBER(4) NOT NULL,
 ENAME               VARCHAR2(10),
 JOB                 VARCHAR2(9),
 MGR                 NUMBER(4) ,
 HIREDATE            DATE,
 SAL                 NUMBER(7,2),
 COMM                NUMBER(7,2),
 DEPTNO              NUMBER(2) );


INSERT INTO EMP VALUES (7839,'KING','PRESIDENT',NULL,'81-11-17',5000,NULL,10);
INSERT INTO EMP VALUES (7698,'BLAKE','MANAGER',7839,'81-05-01',2850,NULL,30);
INSERT INTO EMP VALUES (7782,'CLARK','MANAGER',7839,'81-05-09',2450,NULL,10);
INSERT INTO EMP VALUES (7566,'JONES','MANAGER',7839,'81-04-01',2975,NULL,20);
INSERT INTO EMP VALUES (7654,'MARTIN','SALESMAN',7698,'81-09-10',1250,1400,30);
INSERT INTO EMP VALUES (7499,'ALLEN','SALESMAN',7698,'81-02-11',1600,300,30);
INSERT INTO EMP VALUES (7844,'TURNER','SALESMAN',7698,'81-08-21',1500,0,30);
INSERT INTO EMP VALUES (7900,'JAMES','CLERK',7698,'81-12-11',950,NULL,30);
INSERT INTO EMP VALUES (7521,'WARD','SALESMAN',7698,'81-02-23',1250,500,30);
INSERT INTO EMP VALUES (7902,'FORD','ANALYST',7566,'81-12-11',3000,NULL,20);
INSERT INTO EMP VALUES (7369,'SMITH','CLERK',7902,'80-12-09',800,NULL,20);
INSERT INTO EMP VALUES (7788,'SCOTT','ANALYST',7566,'82-12-22',3000,NULL,20);
INSERT INTO EMP VALUES (7876,'ADAMS','CLERK',7788,'83-01-15',1100,NULL,20);
INSERT INTO EMP VALUES (7934,'MILLER','CLERK',7782,'82-01-11',1300,NULL,10);


commit;

1. 아래 SQL의 GROUP BY의 기준은 무엇일까?

1)

select deptno, sum(sal)
from emp
group by rollup(deptno);

2)

select deptno, job, sum(sal)
from emp
group by rollup(deptno, job);

3)

select deptno, job, empno, sum(sal)
from emp
group by rollup(deptno, job, empno);

4)

select deptno, sum(sal)
from emp
group by cube(deptno);

5)

select deptno, job, sum(sal)
from emp
group by cube(deptno, job);

6)

select deptno, job, empno, sum(sal)
from emp
group by cube(deptno, job, empno);

2. 아래 SQL의 결과를 동일하게 grouping sets를 사용하여 출력하시오

1)

select deptno, job, sum(sal)
from emp
group by rollup(deptno, job);

2)

select deptno, job, sum(sal)
from emp
group by cube(deptno, job);