Let's have a cup of coffee.

문제. 전체사원수, 부서개수, 전체부서의평균사원수, 최대사원수, 최소사원수, 최소사원수인 부서, 최대사원수인 부서를 구하시오

답안.

# 1) keep
select sum(cnt) 전체사원수, count(*) 부서개수
       , round(sum(cnt)/count(*),2) 부서별평균사원수
       , max(cnt) , min(cnt)
       , max(deptno) keep(dense_rank first order by cnt) 최소사원수인부서
       , max(deptno) keep(dense_rank first order by cnt desc) 최대사원수인부서
from (select deptno, count(*) cnt
      from emp
      group by deptno);


# 2) first_value
select sum(cnt) 전체사원수, count(*) 부서개수
     , round(sum(cnt)/count(*),2) 부서별평균사원수
     , max(cnt) , min(cnt)
     , max(min_cnt) 최소사원수인부서
     , max(max_cnt) 최대사원수인부서
from (select deptno, count(*) cnt
             ,first_value(deptno) over(order by count(*)) min_cnt
             ,first_value(deptno) over(order by count(*) desc) max_cnt
      from emp
      group by deptno);


전체사원수 부서개수 부서별평균사원수 MAX(CNT) MIN(CNT) 최소사원수인부서 최대사원수인부서
--------- --------- -------------- -------- --------- --------------- ----------------
   14          3             4.67       6          3              10              30

문제. 아래SQL의 비효율을 튜닝하시오 필요하다면 인덱스를 생성하시오

• 1.1억건의 조인연산을 exists로 변환

select distinct b.code, c.name
 from order12 a, product b, menu c
where a.orderdate > '20090101'
   and a.prod_id = b.prod_id
   and b.code = c.code;

1. 사전작업 

--서브쿼리테스트
--사전작업
--menu table 생성
drop table menu;
create table menu
as
select rownum code, DBMS_RANDOM.STRING('U',5) name from dual connect by level<=500;

alter table menu
add constraint menu_pk primary key(code);

--product table 생성
drop table product;
create table product
as
select rownum prod_id , decode(mod(rownum,500),0,500,mod(rownum,500)) code 
from dual connect by level<=10000;

alter table product
add constraint product_pk primary key(prod_id);

--order table 생성
drop table order1;
create table order1
as
select rownum prod_id, '20090101' orderdate 
from dual connect by level<=10000;

drop table order2;
create table order2
as
select rownum prod_id, '20090102' orderdate 
from dual connect by level<=10000;
 
create table order12
as
select * from order1 where 1=2;

--10m
alter session set sort_area_size = 10000000; 
--병렬쿼리 지원여부
select * from v$option where parameter = 'Parallel execution';
alter session enable parallel dml;

insert /*+ append parallel(order12 4)*/into order12 nologging
select /*+ full(order1) parallel(order1 4)*/  prod_id, orderdate
from order1, (select rownum from dual connect by level<=9000);
commit;

insert /*+ append parallel(order12 4)*/ into order12 nologging
select /*+ full(order2) parallel(order2 4)*/ prod_id, orderdate
from order2, (select rownum from dual connect by level<=2000);
commit;
 
create index order_idx01 on order12(orderdate) nologging parallel 4;
alter session disable parallel dml;

2. 문제풀이

튜닝전SQL:

select /*+ ordered use_nl(b) use_nl(c)*/distinct b.code, c.name
from order12 a, product b, menu c
where a.orderdate > '20090101'
and a.prod_id = b.prod_id
and b.code = c.code;


Call     Count CPU Time Elapsed Time       Disk      Query    Current       Rows
------- ------ -------- ------------ ---------- ---------- ---------- ----------
Parse        1    0.109        0.102         10        396          0          0
Execute      1    0.000        0.000          0          0          0          0
Fetch        6   86.563       88.679     273519   40994941          0        500
------- ------ -------- ------------ ---------- ---------- ---------- ----------
Total        8   86.672       88.782     273529   40995337          0        500

Misses in library cache during parse   : 1
Optimizer Goal : ALL_ROWS
Parsing user : SCOTT (ID=84)


Rows     Row Source Operation
-------  -----------------------------------------------------------------------
    500  HASH UNIQUE (cr=40994941 pr=273519 pw=0 time=166 us cost=51196586 size=43388334720 card=21021480)
20000000   NESTED LOOPS  (cr=40994941 pr=273519 pw=0 time=81775744 us)
20000000    NESTED LOOPS  (cr=20994941 pr=273519 pw=0 time=60707584 us cost=42137979 size=43388334720 card=21021480)
20000000     NESTED LOOPS  (cr=20994937 pr=273518 pw=0 time=42013568 us cost=21107820 size=1030052520 card=21021480)
20000000      TABLE ACCESS FULL ORDER12 (cr=273504 pr=273497 pw=0 time=4920192 us cost=76777 size=483494040 card=21021480)
20000000      TABLE ACCESS BY INDEX ROWID PRODUCT (cr=20721433 pr=21 pw=0 time=0 us cost=1 size=26 card=1)
20000000       INDEX UNIQUE SCAN PRODUCT_PK (cr=721433 pr=21 pw=0 time=0 us cost=0 size=0 card=1)
20000000     INDEX UNIQUE SCAN MENU_PK (cr=4 pr=1 pw=0 time=0 us cost=0 size=0 card=1)
20000000    TABLE ACCESS BY INDEX ROWID MENU (cr=20000000 pr=0 pw=0 time=0 us cost=1 size=2015 card=1)

********************************************************************************


해결방안

1) NL조인은 드라이빙 테이블의 조인횟수가 SQL성능을 좌지우지 하는데 
   2천만건의 주문 테이블이 드라이빙이 되면서 성능이 저하된다.

   위에서 주문테이블은 단지 조건체크로 쓰이기 때문에 NL세미조인을 이용한다.
  


튜닝후SQL:

create index order_idx02 on order12(prod_id, orderdate)nologging parallel 4;

select /*+ leading(c) use_nl(b) */ b.code, c.name
from menu c, product b
where c.code=b.code
and exists(select /*+ index(a order_idx02) nl_sj */'x'
           from order12 a
           where a.orderdate > '20090101'
           and a.prod_id = b.prod_id)
group by b.code, c.name;

Call     Count CPU Time Elapsed Time       Disk      Query    Current       Rows
------- ------ -------- ------------ ---------- ---------- ---------- ----------
Parse        1    0.063        0.053          0        132          0          0
Execute      1    0.000        0.000          0          0          0          0
Fetch        6    0.453        0.477          0      40541          0        500
------- ------ -------- ------------ ---------- ---------- ---------- ----------
Total        8    0.516        0.529          0      40673          0        500

Misses in library cache during parse   : 1
Optimizer Goal : ALL_ROWS
Parsing user : SCOTT (ID=84)


Rows     Row Source Operation
-------  -----------------------------------------------------------------------
    500  HASH GROUP BY (cr=40541 pr=0 pw=0 time=166 us cost=33042 size=20640000 card=10000)
  10000   NESTED LOOPS SEMI (cr=40541 pr=0 pw=0 time=487039 us cost=33041 size=20640000 card=10000)
  10000    NESTED LOOPS  (cr=10504 pr=0 pw=0 time=417679 us cost=3030 size=20410000 card=10000)
    500     TABLE ACCESS FULL MENU (cr=4 pr=0 pw=0 time=748 us cost=3 size=1007500 card=500)
  10000     TABLE ACCESS FULL PRODUCT (cr=10500 pr=0 pw=0 time=352225 us cost=6 size=520 card=20)
  10000    INDEX RANGE SCAN ORDER_IDX02 (cr=30037 pr=0 pw=0 time=0 us cost=3 size=483494040 card=21021480)

********************************************************************************

EMP, DEPT Table script

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;