SQLP 19회차 실습 1번 문제 

 

  문제를 참고한 블로그 주소 

   - 꿈을 펼처라 님의 블로그    


  simverse.tistory.com/85 




1) 내가 생각하는 답 


--- query 


select /*+ leading(b a) use_nl(a) index(a 고객_pk)  index(b 계약_x02) */ a.고객번호, a.고객이름, a.주민번호, b.계약일, b.계약번호, b.서비스시작일, b.서비스완료일 

  from 고객 a , 계약 b 

  where a.고객번호 = b.고객번호 

   and b.계약일 between :C and :D 

   and not exists ( select /*+ index(a 고객_x01) */ 'x' 

                     from 고객 c

                    where (a.고객이름, a.주민번호) in (:A, :B)

                   )

  union all 

select /*+ ordered use_nl(b) index(b 계약_x01) */ a.고객번호, a.고객이름, a.주민번호, b.계약일, b.계약번호, b.서비스시작일 b.서비스완료일 

  from 고객 a , 계약 b 

  where a.고객번호 = b.고객번호 

   and b.계약일 between :C and :D 

   and exists ( select /*+ index(a 고객_x01) */ 'x' 

                  from 고객 c 

                 where (a.고객이름, a.주민번호) in (:A, :B)

                   and a.rowid = c.rowid

              )

;


-- index 


고객_pk = 고객번호 

고객_x01 = 고객이름 + 주민번호 --- 가능하면 이 컬럼들에 not null 제약이 있으면 더 좋음 


계약_pk = 고객번호 + 계약번호 

계약_x01 = 고객번호 + 계약일 

계약_x02 = 계약일  



2) 이에 대한 나의 해설 


 - 이 문제를 풀어보면서 가장 고민 했던 것은 where 절에 exists 서브쿼리 작성이었다. 

   위의 쿼리를 보면 exists 일때는 where 절에 a.rowid = b.rowid라고 조건을 명시한다. 

   그러나 not exists 일때는 a.rowid = b.rowid를 넣어주지 않았다. 

   여기서 exists 일때 해당 조건을 넣어 준것은 서브쿼리 캐싱 기능을 사용하기 위해서이다. 

   조건이 exists 인 경우 해당 조건을 인덱스를 통해서 부분 범위 처리를 하게 되면 

   random access 양을 크게 줄일 수 있다. 

   그러나 이때 a.rowid = b.rowid 조건을 사용하지 않으면 서브쿼리 캐싱 기능을 사용할 수 없어서 

   인덱스가 조인 후 fiter 조건으로만 사용 될 것이다. 

   그런 비효율을 줄이기 위해서 a.rowid = b.rowid 조건을 넣어준다. 


   그런데 이때 not exists 에서는 a.rowid = b.rowid 를 넣어준 것은 비효율에 문제와 함께 잘못 된 값을 출력하기 때문이다. 

   not exists 시에는 고객이름과 주민번호가 모두 테이블에 존재할 시  'no rows selected' 가 되어야 하는데 

   a.rowid = b.rowid 조건을 추가하게 되면 조인 시 결과 값이 출력 될 것이다. 

   ( 이 부분은 설명이 끝나고 간단한 예시로 증명해볼 수 있다. )  

   그리하여 not exists 부분에는 a.rowid = b.rowid 조건을 빼버렸다. 

   그리고 추가로 고객 테이블의 where 절에 조건을 저런식을 넣어준 것은 인덱스를 사용할 수 있게 함이다.


   그리고 union all 을 기준으로 exists 부분 쿼리와 not exists 부분 쿼리에 힌트가 달라졌는데 이는 driving table 순서가 바뀜을 의미한다. 

   exists 의 경우 이름과 주민번호의 조건으로 소수의 값만 출력 될 것으로 생각 된다. 

   그렇게 되면 nl 조인 시 계약 테이블에 조인 되는 양을 줄일 수 있다. 

   그러나 해당 조건이 없다면 고객 테이블에서는 부분 범위 처리를 할 수 있는 값이 없다. 

   그래서 해당 순서와 같이 한다면 100만 건을 조인 시도 할 것이다. 

   그래서 not exists 부분에서는 조인 순서를 바꿔주었다. 

   7일 이기 때문에 최대 7000 건이다. 100만 건 보다는 훨씬 줄일 수 있을 것으로 생각하였다. 


   또한 index 부분을 보면 '고객번호 + 계약일' 인덱스가 있음에도 불구하고 따로 '계약일' 인덱스를 따로 생성하여 사용하였다. 

   이 쿼리에서는 계약일을 between 범위 처리를 해야 한다. 

   해당 쿼리에 내용상 in 스타일로 변경할 수 없다. 

   그렇다고 skip scan을 써도 제대로 효과가 없을 것으로 생각했는데 그 이유는 고객번호의 변별력이 좋기 때문에디 고객번호는 pk 이기 때문이다. 

   고객번호는 변별력이 매우 큰 컬럼임이다. 

   이처럼 선두 컬럼이 변별력이 클때는 skip scan은 크 효과를 거둘 수 없다. 

   그래서 계약일 인덱스를 생성하였다. 고객이름과 주민번호가 입력되지 않을 시에는 범위 처리가 가능하게 하는 컬럼은 계약일 밖에 없기 때문이다. 

   그래서 인덱스가 2개 생성됨에도 불구하고 자주 사용되는 쿼리라면 '계약일' 인덱스가 필요하다고 생각하였다. 



3) not exists 에 대한 내용에 대한 참고 쿼리 

    - 해당 emp 테이블은 오라클 샘플 emp table 이다.   



select * 

 from emp a 

 where not exists ( select 'x' 

                      from emp b

                     where ename = 'KING'

                      and a.rowid = b.rowid 

                   )

 ;


--- 14 rows 를 출력 


select * 

 from emp a 

 where not exists ( select 'x' 

                      from emp b

                     where ename = 'KING' 

                   )

 ;

  

--- no rows selected 

   

 * 하지만 이부분에서 a.rowid = b.rowid 조건을 넣었을 때 14 rows 가 출력되는 이유는 잘 모르겠다. 

   ename = 'KING'  값이 있는 ' no rows selected '가 되어야 한다고 생각하는데 말이다.   




  





'ORACLE > SQLP' 카테고리의 다른 글

SQLP 시험 문제 7일차  (0) 2016.11.24
SQLP 시험문제 6일차  (0) 2016.11.24
sqlp19회 실습문제 2번 테이블명 컬럼명 영어로 바꿈  (0) 2016.11.04
sqlp 19회 실습문제 2번 테스트 테이블 작성  (0) 2016.11.04
SQLP 19회차 실습문제 2번  (0) 2016.11.03

3) IOT (index organizaion table) 테이블 구성
   - table random 엑세스가 발생하지 않도록 테이블을 아예 인덱스 구조로 생성한 object 

    ⑴ IOT 의 장점 
       - 인위적으로 클러스터링 팩터를 좋게 만드는 방법중에 하나여서 Logical read를 줄일 수 있다. 
       - 기존에는 인덱스 + 테이블이였다면, lOT는 인덱스만 만들면 되므로, 저장 공간이 절약된다. 

    ⑵ IOT의 단점 
        - 데이터 입력할 때, 성능이 느리다. 
   
    ⑶ IOT 생성 및 데스트 
        ① create table emp_iot
           (empno, ename, job, mgr, hiredate, sal, comm, deptno, constraint pk_emp_iot primary key(empno) )
           organization index
            as
                 select * from emp  ;

              * IOT 생성시 pirmary key 가 없으면 테이블이 생성되지 않는다. 
              * 기존 테이블에 primary key가 걸려 있어도 의미가 없다. 새로 생기는 table에 primary key를 만들어 주어야 한다.
                  (ctas 사용시 not null 제약을 제외한 나머지 제약은 따라오지 않는다.) 

        ② select * from emp_iot ;
             -- consistent gets 5 
             -- index fast full scan
 
     ⑷ IOT 저장 영역 
         ① PK 속성 영역 : 자주 조회 조건에서 검색되는 컬럼들이 저장된 저장 영역

         ② 오버 플로우 영역 : 값은 필요해서 저장해두지만 출력이나 조회조건으로 거의 사용되지 않는 컬럼들을 저정하는 저장 영역
         
         * 영역을 나누어서 사용하는 이유는 검색 속도를 좀 더 높이기 위해서 
         * IOT는 table이 아니라 index로 저장 된다. 검색시에는 object_type 이나 segment_type을 index로 하여야 찾을 수 있다. 
         * 그러나 drop 이나 truncate 명령어를 사용할 때에는 table 옵션을 사용한다. 
 

  4) 클러스터 테이블 

     ⑴ 클러스터 테이블이란 
        - 클러스터 키 값이 같은 레코드가 한 블럭에 모이도록 저장하는 구조의 테이블을 말한다. 
        - 클러스터 테이블을 이용하면, 여러테이블의 레코드를 심지어 하나의 물리적 공간에 같이 저장할 수 있다 
        - 여러 테이블들을 서로 조인된 상태로 저장해 두는 것이다. 
       
         * 단점은 DML 속도가 상당히 느려진다. 

     ⑵ 클러스터 테이블의 종류 2가지 
         ① 인덱스 클러스터 테이블 
               - 단일 테이블 인덱스 클러스터 
               - 다중 테이블 인덱스 클러스터

         ② 해쉬 클러스터 테이블 

     ⑶ 단일 클러스터 
         - 클러스터 인덱스는 컬럼 값을 하나만 저장하기 때문에 인덱스의 크기가 크지 않아 검색속도가 높아진다.
         - 클러스터 테이블은 클러스터 키값에 따라서 최대한 같은 블럭안에 같은 키값들을 저장히기 때문에 검색속도가 높아진다. 

       
     ⑷ 단일 클러스터 테이블 생성 테스트 
     
            ① 클러스터를 생성한다. 
     
                 CREATE CLUSTER objs_cluster# (object_type VARCHAR2(20) ) INDEX ;
                                                  ㅣ                           ㅣ
                                           클러스터 이름             클러스터 key 값
                                             
            ② 클러스터 인덱스를 생성한다.
               CREATE INDEX objs_cluster_idx ON CLUSTER objs_cluster# ;
                                               ㅣ                                   ㅣ 
                                       클러스터 인덱스 이름            클러스터 이름

            ③ 클러스터 테이블을 생성한다.
                CREATE TABLE objs_cluster
                  CLUSTER objs_cluster# (object_type)
                            AS SELECT *
                                  FROM all_objects
                                ORDER BY DBMS_RANDOM.VALUE;

               * dbms_random.value를 사용해서 정렬해서 데이터를 저장하면 
                   실제로 논리적으로는 object_type 별로 저장되어있지 않고 데이터가 무분별하게 흩어져서 저장되어져있는 것처럼 보이겠지만
                   실제로 물리적으로는 object_type 별로 같은 블럭안에 데이터가 저장된다.                    

                * 생성시에는 cluster, table 모두 create 하지만 truncate 시에는 cluste만 truncate 하면 자동으로 table의 내용이 삭제 된다. 
                   drop시에는 table, cluster 모두 삭제 해야 한다.

                * 클러스터 테이블을 생성하기 위해서는 클러스터 생성시 클러스터 키값은 기존 테이블의 컬럼의 데이터 타입과 길이의 범위가 같아야 한다. 
                   그렇지 않을 시 클러스터 테이블 생성시 error가 발생한다. 
 
            ④ 일반 테이블과 클러스터 테이블의 성능을 비교한다. 
 
               CREATE TABLE objs_regular
                      AS SELECT *
                             FROM objs_cluster
                          ORDER BY DBMS_RANDOM.VALUE;

               CREATE INDEX objs_regular_idx ON objs_regular(object_type) ;

               ALTER TABLE objs_regular MODIFY object_name NULL;
               ALTER TABLE objs_cluster MODIFY object_name NULL;
  
       결과 )  -- consistent gets , physical read  순 

                   SELECT *
                     FROM objs_regular
                   WHERE object_type='TABLE';
                     -- 429
                     -- 235
                     -- full table access

                    SELECT /*+ index(objs_regular objs_regular_idx) */*
                     FROM objs_regular
                   WHERE object_type='TABLE';
                      -- 549
                      -- 235 
                      -- index range scan 

                  SELECT *
                    FROM objs_cluster
                 WHERE object_type ='TABLE';
                     -- 226
                     -- 199
                     -- index unique scan 
                     -- table access cluster 

                 
    5) 다중 클러스터 테이블 
         - 여러 테이블의 레코드를 물리적으로 같은 블럭안에 저장되게 하는 테이블을 말한다. 
         - 즉, 여러테이블들을 서로 조인된 상태로 저장해두는 것을 말한다.      
        

      ⑴ 다중 클러스터 테이블 구현 
          ① 클러스터를 생성한다. 
              create cluster c_deptno# on ( deptno number(20) ) index  ;
           
          ② 클러스터 인덱스를 생성한다. 
             create index i_deptno# on cluster c_deptno# ;

          ③ 클러스터 테이블을 생성한다. 
              create table emp_cluster
                cluster c_deptno#(deptno)   -----------------------> 같은 클러스터 사용 
                 as select * 
                       from emp ;

           
             create table dept_cluster
                cluster c_deptno#(deptno)   -----------------------> 같은 클러스터 사용 
                 as select * 
                       from dept ;

              * 두개의 테이블이 같은 블럭안에 존재한다는 것을 확인해보시오 ! 

                  break on deptno skip 1 ;

                  select d.deptno, e.empno, e.ename,
                            dbms_rowid.rowid_block_number(d.rowid) deptno_block_no,
                            dbms_rowid.rowid_block_number(e.rowid) emp_block_no     
                   from dept_cluster d , emp_cluster e
                   where e.deptno = d.deptno
                   order by d.deptno ;
    

          ④ 일반 테이블과 클러스터 테이블과 성능을 비교한다. 

             select e.ename, d.loc, e.sal, e.hiredate, d.dname, e.deptno, e.mgr
                 from emp e, dept d
                where e.deptno = d.deptno and d.loc ='DALLAS';
               
                  * 순서대로 1회 실행과 2회 실행 시 

                  -- 11  -> 7
                  -- 4   -> 0
                  -- table acess full
                  -- hash join

            select e.ename, d.loc, e.sal, e.hiredate, d.dname, e.deptno, e.mgr
                 from emp_cluster e, dept_cluster d
                where e.deptno = d.deptno and d.loc ='DALLAS';

                -- 26 -> 10
                -- 6  -> 0
                -- table access cluster
                -- table acess full
                -- nested loop

문제 34. 사원 번호가 7788 번인 사원의 모든 컬럼의 데이터를 가져오는 쿼리의 성능을 emp 와 emp_iot 로 비교하시오 

 SELECT /*+ full(emp) */ *
 from emp
 WHERE empno=7788 ;
 -- consistent gets 4 
 -- full table scan 


 select *
 from emp_iot
 WHERE empno=7788 ;
 -- consistent gets 1 
 -- index unique scan 

문제 35. rn 이라는 컬럼이 있는 sales500 테이블을 rn 컬럼을 primary key 로 해서 iot를 생성하는데  이름을 sales500_iot로 구현하시오 ! 

1) rn 컬럼 추가 

CREATE TABLE SALES500
  AS SELECT ROWNUM rn, b.*
       FROM SALES b ;

2) IOT 구성 

CREATE TABLE sale500_iot
  (RN ,PROD_ID, CUST_ID, TIME_ID, CHANNEL_ID, PROMO_ID, QUANTITY_SOLD, AMOUNT_SOLD,
   CONSTRAINT sale500_iot_pk PRIMARY KEY(rn) )
   organization index
            as
             select * from sales500  ;

문제 36. 아래의 쿼리의 검색 속도를 비교하시오! 

 select *
 from sales500
where rn = 384756 ;
-- consistent gets 5076
-- full table scan 

select *
 from sale500_iot
where rn = 384756 ;
-- consistent gets 3
-- index unique scan 

* rn 컬럼에 primary key가 걸려 있기 때문에 자동으로 unique index가 생성되었다. 

문제 37. sales500 table + sales500_rn index 와 sales500_iot table 의 크기 차이를 비교하시오 ! 

SELECT segment_name, segment_type, bytes/1024/1024 MB
 FROM user_segments
 WHERE segment_name LIKE 'SALE%' ;

- sales500 (table) + sales500_rn_pk(index) = 41 + 16 = 57
- sales500_iot_pk = 39 

문제 38. SALES500 테이블과 SALES500_IOT 테이블에 insert 할 때의 속도 차이를 비교해보시오! 
- 행의 갯수 918843

첫 실험 
- sales500  : 7 초
- sales500_IOT :31 초

* 사람들 마다 차이가 있어서 여러번 실험해보아야 한다. 
* 여러번 실험할 수록 차이는 줄어 든다. 그 이유는 insert 시에 high water mark를 높여 놨기 때문이다.
* 제대로 실험해보기 위해서는 해당 table을 drop 한 후 다시 생성하고 buffer cache를 지우고 실험해보아야 한다.  

문제 39. sales500  테이블을 클러스터 테이블로 구성하는데 클러스터 키컬럼을 amount_sold로 해서 생성하시오 ! 

 CREATE CLUSTER sales500_cluster# (amount_sold VARCHAR2(22) ) INDEX ;

 CREATE INDEX sales500_cluster_idx ON CLUSTER sales500_cluster# ;

  CREATE TABLE sales500_cluster
     CLUSTER sales500_cluster# (amount_sold)
           AS SELECT *
                FROM sales500
              ORDER BY DBMS_RANDOM.VALUE;

  CREATE TABLE sales500_regular
          AS SELECT *
        FROM sales500_cluster
       ORDER BY DBMS_RANDOM.VALUE;

  CREATE INDEX sales500_regular_idx ON sales500_regular(amount_sold) ;

문제 40. sales500 테이블과 sales500_cluster 테이블과 성능을 비교하시오 

* 각각 실험 마다 flush buffer_cache 실행 
* 1차, 2차 실행 

select /*+ full(sales500_regular) */*
 from sales500_regular
where amount_sold=136.64 ;
-- 5079 -> 5079
-- 5070 -> 5069


select /*+ index(sales500 sales500_regular_idx) */ *
 from sales500_regular
where amount_sold=136.64 ;
-- 94 -> 90
-- 88 -> 0

select *
 from sales500_cluster
 where amount_sold=136.64 ;
-- 9   -> 9
-- 3   -> 0

문제 41. 아래의 조인문장의 성능이 좋아지도록 SALES 테이블과 PRODUCTS 테이블을 다중 클러스터 테이블과 구성하시오 ! 

create table sales                                    create table products
 as                                                             as 
     select * from sh.sales ;                           select * from sh.products ;

select  /*+ leading(p s) use_nl(p) */ p.prod_name, sum(s.amount_sold) 
   from sales s, products p 
 where s.prod_id = p.prod_id 
    and p.prod_id = 40
  group by p.prod_name ;

-- 4597  ->  4441
-- 4576   -> 4433
-- table acess full 
-- table acess full 
-- hash join 

 SELECT p.prod_name, sum(s.amount_sold)
   from sales_cluster s, product_cluster p
 where s.prod_id = p.prod_id
    and p.prod_id = 40
  group by p.prod_name ;
  -- 1238 -> 244
  -- 619  -> 0
  -- TABLE ACCESS CLUSTER -> INDEX UNIQUE SCAN
  -- TABLE ACCESS CLUSTER -> INDEX UNIQUE SCAN
  -- NESTED LOOPS

* 점심시간 문제 :

  create table emp_iot2
  (empno, ename, job, mgr, hiredate, sal, comm, deptno,
   CONSTRAINT emp_iot2_pk PRIMARY KEY (empno,ename) )
    organization INDEX
    INCLUDING sal
    OVERFLOW tablespace ts01
     as
    select * from emp  ;

-- 조회를 * 로 함 
-- where 조건 including 밖

   SELECT *
     FROM emp_iot
     WHERE deptno = 10;
   -- 5

   SELECT *
    FROM emp_iot2
     WHERE deptno = 10 ;
   -- 7 

-- 조회를 * 로 함 
-- where 조건 including 안

    SELECT *
     FROM emp_iot
     WHERE job = 'SALESMAN';
   -- 5

   SELECT *
    FROM emp_iot2
     WHERE job = 'SALESMAN';
    -- 7

-- 조회를 * 로 함 
-- where 조건 PK 컬럼
 
    SELECT *
     FROM emp_iot
     WHERE empno = 7788;
     -- 1

   SELECT *
     FROM emp_iot2
     WHERE empno = 7788;
     -- 3

* 아래부터는 할 때마다 alter system flush buffer_cache ; (buffer cache 내용 지우기) 를 실행함 
* 그래서 consistent get, physical read 둘다 기록 

-- where 조건 pk 컬럼
-- including 전 컬럼까지 조회

    SELECT empno, ename, job
     FROM emp_iot
     WHERE empno = 7788;
     -- 1
     -- 8

    SELECT empno, ename, job
     FROM emp_iot2
     WHERE empno = 7788 ;
    -- 7
    -- 6

-- where 조건 pk 컬럼 x
-- including 전 컬럼까지 조회

   SELECT empno, ename, job
     FROM emp_iot
     WHERE deptno = 10;
   -- 10
   -- 6

   SELECT empno, ename, job
    FROM emp_iot2
     WHERE deptno = 10 ;
   -- 13
   -- 14

-- where 조건 pk 컬럼
-- including 이외의 컬럼까지 조회

   SELECT empno, ename, deptno
     FROM emp_iot
     WHERE empno = 7788;
   -- 1
   -- 8 

   SELECT empno, ename, deptno
    FROM emp_iot2
     WHERE empno = 7788 ;
   -- 3
   -- 16   


-- where 조건 pk 컬럼 x
-- including 이외의 컬럼까지 조회

   SELECT empno, ename, deptno
     FROM emp_iot
     WHERE deptno = 10;
   -- 10
   -- 6

   SELECT empno, ename, deptno
    FROM emp_iot2
     WHERE deptno = 10 ;
   -- 13
   -- 14

* 오늘의 마지막 문제 

SALES500_IOT 와 SALES_CLUSTER 와 SALES 테이블 3개의 DML 속도를 비교하시오 !

* 모두 truncate 로 아무 것도 없던 상태에서 작업 
* db도 테스트하기 전에 startup force 실행 
* 각 테스트가 끝나면 flush buffer_cache 후 다시 테스트 진행함 

INSERT INTO SALEs500
  SELECT *
  FROM sales500_backup ;
-- 5초 13

INSERT INTO sales500_iot
  SELECT *
  FROM sales500_backup ;
-- 26초 71

INSERT INTO sales500_cluster
  SELECT *
  FROM sales500_backup ;
-- 45초 59 
* sales500 에도 sales500_iot와 동일한 조건을 주기 위해서 rn 컬럼에 pk 제약을 건 후 다시 테스트 
* 이번에는 좀 더 정확한 결과를 위해서 각 테스트 마다 startup 을 새로함 

INSERT INTO SALEs500
  SELECT *
  FROM sales500_backup ;
-- 5초 07

INSERT INTO sales500_iot
  SELECT *
  FROM sales500_backup ;
-- 22초 71

INSERT INTO sales500_cluster
  SELECT *
  FROM sales500_backup ;
-- 40초 49 

- 조금 씩 위의 결과보다 차이는 나지만 큰 차이는 발생하지 않았다. 


'ORACLE > SQL 튜닝' 카테고리의 다른 글

SQL튜닝(3) - index(index merge scan, index bitmap merge scan, index join, index uniqe scan), table random access 부하  (0) 2016.04.06
SQL튜닝(2)(1.10) - index ( index range scan, index full scan, index fast full scan, index skip scan )  (0) 2016.04.01
SQL튜닝(1) - index (index 구조, index 사용 시 주의 사항, index 스캔 방식의 종류 )  (0) 2016.03.15

  7) index merge scan 
        - 두개의 인덱스를 동시에 사용해서 하나의 인덱스만 사용했을 때보다 더 큰 시너지 효과를 보겠금 하는 스캔 방법 
           (시너지 효과 : table 엑세스 횟수를 줄이는 효과가 있다.)
            (10g 부터는 사용되지 않고 있는 기능이다. )  
        -  힌트 : and_equal 

     ⑴ index merge test
        ① @ctas (초기화)
        ② create index emp_deptno on emp(depno) ; 
        ③ create index emp_job on emp(job) ;

     ⑵ index merge 실행계획 

     SELECT /*+ and_equal(emp emp_deptno emp_job) */ ename, job, deptno
      FROM EMP
     WHERE deptno = 30
       AND job = 'SALESMAN';    
 
------------------------------------------------------------------------------------------
| Id  | Operation                   | Name       | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |            |     1 |    14 |     3   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS BY INDEX ROWID| EMP        |     1 |    14 |     3   (0)| 00:00:01 |
|   2 |   AND-EQUAL                 |            |       |       |            |          |
|*  3 |    INDEX RANGE SCAN         | EMP_JOB    |     3 |       |     1   (0)| 00:00:01 |
|*  4 |    INDEX RANGE SCAN         | EMP_DEPTNO |     5 |       |     1   (0)| 00:00:01 |
------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("JOB"='SALESMAN' AND "DEPTNO"=30)
   3 - access("JOB"='SALESMAN')
   4 - access("DEPTNO"=30)
 
* 실행계획을 읽을 때 동일한 위치에 있는 것들은 위에서 부터 읽으면 된다. 

 8) index bitmap merge scan  ( 10g 버젼부터 가능한 스캔 방법 _ 책에는 9c 부터)
     - index merge scan 과 스캔방법은 똑같은데 인덱스의 크기를 줄이기 위해서 인덱스를 bitmap 으로 변환하는 작업이 추가되었다. 
     - hint : index_combine  

    SELECT /*+ index_combine(emp) */ ename, job, deptno
    FROM EMP
    WHERE deptno = 30
     AND job = 'SALESMAN';
      -- 4 
   
-----------------------------------------------------------------------------------------------
| Id  | Operation                        | Name       | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                 |            |     1 |    14 |     3   (0)| 00:00:01 |        -----------------------------------------> 실행순서 8
|   1 |  TABLE ACCESS BY INDEX ROWID     | EMP        |     1 |    14 |     3   (0)| 00:00:01 |  ------------------------------------------> 실행순서 7
|   2 |   BITMAP CONVERSION TO ROWIDS    |            |       |       |            |          |     ------------------------------------------> 실행순서 6
|   3 |    BITMAP AND                    |            |       |       |            |          |             ------------------------------------------> 실행순서 5
|   4 |     BITMAP CONVERSION FROM ROWIDS|            |       |       |            |          |   ------------------------------------------> 실행순서 2
|*  5 |      INDEX RANGE SCAN            | EMP_JOB    |       |       |     1   (0)| 00:00:01 |     ------------------------------------------> 실행순서 1
|   6 |     BITMAP CONVERSION FROM ROWIDS|            |       |       |            |          |   ------------------------------------------> 실행순서 4
|*  7 |      INDEX RANGE SCAN            | EMP_DEPTNO |       |       |     1   (0)| 00:00:01 |   ------------------------------------------> 실행순서 3
-----------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   5 - access("JOB"='SALESMAN')
   7 - access("DEPTNO"=30)

※ 선택도 확인 방법 
      ① ANALYZE TABLE EMP COMPUTE STATISTICS ;

      ② select c.column_name, t.num_rows, c.num_nulls, c.num_distinct
                    , 1/c.num_distinct selectivity
                    , num_rows/c.num_distinct cardinality
           from   user_tables t, user_tab_columns c
         where  t.table_name = 'EMP'
            and   c.table_name = t.table_name
            and   c.column_name in ('DEPTNO','JOB') ;   


- num_distinct 가 클 수록 선택도가 좋아진다. (num_distinct : 해당 컬럼을 구분하는 구분기준의 갯수)
  (selectivity, cardinality 의 값이 작을 수록 선택도가 좋아진다.) 
- 선택도가 좋아진다는 것은 optimize 가 선택할 가능성이 크다는 것을 의미한다. 

 9) index join
   - 인덱스끼리 조인해서 바로 결과를 보고 테이블 엑세스는 따로 하지 않는 스캔 방식 
   - 힌트 : index_join

   ⑴ index join 테스트
      ① create index emp_deptno on emp(deptno) ;
         create index emp_job on emp(job) ;

      ② select deptno, job
          from emp
          where deptno = 30 and job='SALESMAN'; 
        -- consistent gets 4 
        -- full table scan  

      ③ SELECT /*+ index_join(emp emp_deptno emp_job) */ deptno, job
           FROM EMP
          where deptno = 30 and job='SALESMAN';
         -- consistent gets 3
         -- 2번의 index range scan 
         -- hash join 

 10) index unique scan 
       - primary key 나 unique 제약을 걸면 unique 인덱스가 자동으로 생성이 되는데 바로 이 unique 인덱스를 이용해서 데이터를 스캔하는 방법 
         ( 생성할 수만 있다면 non unique 인덱스보다 성능이 좋다.) 
       - 힌트 : index ( 해당 컬럼에 unique 제약이 있으면 자동으로 적용된다.) 

        ⑴ index unique scan 생성 방법  
             ①  primary key 생성시 unique 인덱스 자동 생성)
               alter table emp 
               add constraint emp_empno_pk primary key(empno) ;

               select index_name, uniqueness
                 from user_indexes
               where table_name ='EMP'
               
           
             ②  unique index 직접 생성
               - create unique index emp_comm on emp(comm);
             
         ⑵ unique index 의 특징               
             - non unique index는 unique 하다고 선언되어 있지 않아서 필요한 자료를 찾은 후 또 자료 있는지 그 행 다음열 부터 다시 찾는 작업을 한 번더 실행한다.)
             - 즉, unique index는 필요한 data를 찾는 1번의 검색만 하는 반면 non unique index는 총 2번의 검색 작업이 이루어진다.)


(4) 테이블 random 엑세스 부하 
     1) 테이블 랜덤 엑세스 줄이는 방법 
         ① 결합 컬럼 인덱스를 활용한다. 
         ② index의 클러스터링 팩터를 좋게한다. (p. 68)
         ③ index의 컬럼추가 (p. 79)               
         ④ 테이블의 구조를 IOT (index organizaion table) 로 구성한다. (p. 98)
         ⑤ 테이블의 구조를 cluster table로 구성한다. (p.118)
 
     2) index 컬럼추가 
         - 기존에 사용되던 index에서 필요한 컬럼을 추가하여 table access를 줄인다

          ①   create table ts
                 as select *
                  from all_objects
                  order by object_type ;

          ② create index t2_owner
              on t2(owner) ;

          ③ alter system flush buffer_cache ; ----------------> buffer cache 내용 비우는 작업 

    ※ 차세대 시스템 구축시 주의 사항 !! 
 
         <예시>
            SQL> insert /*+ parallel(e1,4) */ into emp e1
            SQL> select /*+ parallel(e2,4) */ *
                     from emp e2; 

      - 데이터 이행시 위와 같이 병렬로 작업해서 이행하면 데이터가 무작위로 흩어져서 저장되기 때문에 클러스터링 팩터가 더 안좋아져서 성능이 느려질 수 있다. 

문제 29. 부서번호가 30번이고 직업이 SALESMAN인 사원들의 이름과 직업과 부서번호를 출력하시오! 
          (실행계획을 보고 인덱스 둘중에 어느 인덱스를 사용했는지 확인하시오!) 

SELECT ename, job, deptno
 FROM EMP
 WHERE deptno = 30
  AND job = 'SALESMAN';       

- emp(job) 에 걸려 있는 index를 사용 
- emp(job) 으로는 4건 , emp(deptno) = 6 건 
- 더 적게 table acess 하는 방법으로 optimize 가 선택하였다.

* 튜닝을 할 때는 where 조건에 걸려 있는 각각의 건수들이 몇건인지 확인해보는 것이 기본이다. 
* 옵티마이져가 인덱스의 선택도를 조사해서 선택도가 좋은 인덱스를 선택했다. 
     ( emp_job > emp_deptno) 

문제 30. 위의 SQL 의 실행계획이 부서번호의 인덱스를 엑세스 하겠금 힌트를 주시오 ! 

SELECT /*+ index(emp emp_deptno) */ ename, job, deptno
 FROM EMP
 WHERE deptno = 30
  AND job = 'SALESMAN'; 

문제 31. 아래의 SQL 의 컬럼에 각각 인덱스를 걸고 두개의 실행계획을 비교하는데 하나는 index merge scan 이 되게하고 , 
          다른 하나는 index bitmap merge scan 이 되게해서 consistent gets를 비교하시오 ! 

튜닝 전 :

select * 
 from sales500
where channel_id = 3 
  and amount_sold= 1232.16 ;

 --  consistent gets 4442
 -- (full hint 사용 시) 

튜닝 후:

1) 일반적인 인덱스 사용 

select /*+ index(sales500 sales500_amount) */ *
 from sales500
where channel_id = 3
  and amount_sold= 1232.16 ;

-- consistent gets 18
-- index range scan 

2) index merge scan 사용

SELECT /*+ and_equal(sales500 sales500_channel sales500_amount) */ *
 from sales500
where channel_id = 3
  and amount_sold= 1232.16 ;

-- consistent gets 31
-- index range scan 2번

3) index bitmap merge scan 

SELECT /*+ index_combine(sales500) */ *
 from sales500
where channel_id = 3
  and amount_sold= 1232.16 ;     

-- consistent gets 412
-- index range scan
-- bitmap conversion 

문제 32. 아래의 SQL 을 튜닝하시오 ! 

튜닝 전:

select * 
 from t2
 where owner='SCOTT' and object_name='EMP';

-- consistent gets 232

튜닝 후:

1) index column 1개만 사용 

 select /*+ index(t2 t2_owner)*/ *
 from t2
 where owner='SCOTT' and object_name='EMP';

  -- consistent gets 30
  -- index range scan

 2) index column 을 2개 사용하여 복합 컬럼 인덱스 구성  

 select /*+ index(t2 t2_owner_name)*/ *
 from t2
 where owner='SCOTT' and object_name='EMP';
  -- consistent gets 4
  -- index range scan 

문제 33. 짝궁의 SCOTT 의 SALES500 테이블의 SALES500_CUST_ID 인덱스의 클러스터링 팩터를 비교하시오! 

SQL> select a.index_name, a.clustering_factor, b.index_name, b.clustering_factor
          from user_indexes a, user_indexes@shs_link2 b
      WHERE a.table_name = 'SALES500';

* 점심시간 문제 : 아래의 SQL 을 튜닝하시오 ! 
                    (deptno , sal 2개의 컬럼으로 구성 된 index가 있을 시 )   

create index emp_deptno_sal on emp(deptno,sal);


튜닝 전 :

select deptno, max(sal)
  from emp
where deptno = 20
 group by deptno ; 

-- 46
-- full (full 힌트를 주어서 작동 시켰을 때 값)  


튜닝 후 : 
1) 일반적인 복합 컬럼 인덱스를 사용하여 튜닝

SELECT /*+ index_desc(emp emp_deptno_sal) */ deptno, sal
  FROM EMP
 WHERE deptno = 20
  AND ROWNUM = 1;

-- consistent gets 1
-- index range scan 

2) 인라인 뷰 안에서 먼저 복합 컬럼 인덱스를 사용하여 튜닝 후 첫번째 로우만 뽑아 내기 
 
 SELECT *
 FROM (SELECT /*+ index_desc(emp emp_deptno_sal) */ deptno, sal
         FROM EMP
        WHERE deptno = 20 )
 where rownum = 1 ;

-- consistent gets 1
-- index range scan 

3) 인라인 뷰 안에서 스킵 스캔을 사용하여 튜닝 후 첫번째 로우만 뽑아 내기 

SELECT *
 FROM (SELECT /*+ index_ss_desc(emp emp_deptno_sal) */ deptno, sal
         FROM EMP
        WHERE deptno = 20 )
 where rownum = 1 ;

-- consistent gets 1
-- index skip scan 


'ORACLE > SQL 튜닝' 카테고리의 다른 글

SQL튜닝(4) - IOT (index organizaion table) 구성, 클러스터 테이블 구성, 다중 클러스터 테이블 구성  (0) 2016.04.11
SQL튜닝(2)(1.10) - index ( index range scan, index full scan, index fast full scan, index skip scan )  (0) 2016.04.01
SQL튜닝(1) - index (index 구조, index 사용 시 주의 사항, index 스캔 방식의 종류 )  (0) 2016.03.15

- 함수를 왜 배워야하는가? 

ex)
우리반 테이블에서 전공, 전공별 인원수를 출력하시오!

우리반 테이블에세 가장 나이가 어린 학생의 이름을 출력하시오!

메일 도메인, 메일 도메인별 건수를 출력하시오! 

- 함수란 무엇인가?



- 함수의 종류 2가지? 


1. 단일행 함수
   - 문자
   - 숫자
   - 날짜
   - 변환
   - 일반 

1) 문자 함수 
   (1) 대소문자 변환 함수 
       - lower : 소문자로 출력하는 함수
       - upper : 대문자로 출력하는 함수
       - initcap: 첫번째 철자는 대문자 나머지는 소문자로 출력하는 함수 

   (2) 문자 조작 함수 
       - concat : 두개의 컬럼의 값을 연결하는 함수
       - substr : 특정 철자를 잘라내는 함수
       - length : 철자의 길이를 출력하는 함수 
       - instr : 특정 철자의 위치를 출력하는 함수
       - lpad : 왼쪽에 특정값을 채워넣는 함수 
       - rpad : 오른쪽에 특정값을 채워넣는 함수 
       - trim : 특정 철자나 값을 잘라내는 함수 
       - replace : 특정 철자로 변경하는 함수 

2) 숫자 함수 
      - round : 반올림하는 함수
      - trunc : 잘라내는 함수
      - mod : 나눈 나머지값을 출력  

3) 날짜 함수 
     - between month : 해당 기간 동안의 몇달이 있었는지 출력 
     - add-months: 기준 날짜에서 입력한 달 수 만큼 지난 날짜를 출력
     - next-day: 기준 날짜에서 해당 요일이 오늘 날짜를 출력 
     - last-day: 기준 날짜에서 해당 달의 끝까지 몇일이 남았는지 출력

4)변환 함수 / data type 을 변환
    
   (1) data type 
         - 숫자
         - 문자
         - 날짜 
   (2) 형변환 2가지 (하나의 데이터가 아니라 형식을 바꾼다고 생각해라)
        1. 암시적 형변환 : 오라클이 알아서 형변환 수행 
           예시) select ename, sal 
                     from emp
                     where sal = '3000'
                               숫자   문자 
                     - 이는 원래 실행 되어서는 안된지만 오라클 내부적으로 data type을 자동 변환하여 실행된다.
                     - 오라클은 숫자를 문자보다 더 높은 우선 순위에 두기 때문에 문자를 숫자로 변환한다.  (숫자>문자)
                     - BUT. 이런 식의 코드는 실행은 되지만 효율이 떨어져 수정해야 한다. 

        2. 명시적 형변환:
           - to_char :문자로 형변환하는 함수 
                            날짜 ---> 문자 
                            숫자 ---> 문자 

           - to_number : 숫자로 형변환하는 함수 
           - to_date :날짜로 형변환하는 함수 
  
  5) 일반 함수 
     - NVL 함수: null 값이 숫자를 변화하여 출력하라 
     - NVL2
     - decode 함수
     - case 함수 

2. 복수행 함수
   - max
   - min
   - count
   - avg
   - sum

문제 92. 나이대가 20대인 학생들의 이름과 나이와 주소를 출력하는데, 나이가 높은 학생부터 출력하시오!

SQL> SELECT ename, age, address
           FROM EMP2
           WHERE age BETWEEN 20 AND 29
           ORDER BY age DESC;

문제 93. 서울에서 살지 않는 학생들의 이름과 학원까지의 시간을 출력하는데 시간이 가장 오래 걸리는 학생부터 출력하시오! 

SQL> SELECT ename, distance
           FROM EMP2
           WHERE address NOT LIKE '%서울%'
           ORDER BY distance DESC;

문제 94. 이름에 EN 또는 IN이 포함되어져 있는 사원의 이름과 월급을 출력하시오!(EMP 테이블) 
   
  SQL> SELECT ename, sal
             FROM EMP
             WHERE ename LIKE '%EN%' OR
                        ename LIKE '%IN%';

*Regexp_substr 활용하여 더 간편하게 검색 가능

SQL> select ename, sal 
         from emp
         where regexp_like(ename,'(EN|IN)');
  - 이 쿼리에서 '|'는 또는을 의미하며 계속 추가할 수 있다. 
  - regexp_like 는 '%'(와일드 카드)를 쓸 필요가 없다.  

* MS_SQL에서는 regexp를 사용할 수 없다.


문제 95. 이름, 월급을 출력하는데 월급을 암호화해서 출력하시오! 월급의 숫자 234를 각각 *로 출력하시오 

SQL> SELECT ename,regexp_REPLACE(sal,'[2-4]','*') 
           FROM EMP;

* regexp/정규 표현식 
  
* 이 경우 이런 코드도 가능하다. 
  SELECT ename,regexp_REPLACE(sal,'(2|3|4)','*') 
  FROM EMP;

만약 연속 된 숫자가 아니였다면, 이런 식으로 입력가능 할듯 하다.

* MS_SQL 에서는 replace에 정규표현식을 사용할 수 없어서 위와 같은 결과를 내려면 아래와 같이 중첩으로 사용해야 한다. 

select ename,replace(replace(replace(sal,'2','*'),'3','*'),'4','*')
 from emp ;

문제 96. 이름, 월급, 보너스(sal,*02) 를 출력하시오! 
             컬럼별칭을 사용해서 월급*0.2가 보너스로 컬럼명이 출력되게하시오! 
             (보너스가 일의 자리에서 반올림되게 하시오!)

SELECT ename, sal, ROUND (sal*0.2,-1) AS 보너스
  FROM EMP;

*ROUND / 반올림 하는 keyword
  TRUNC / 버림하는 ketword

예시) select round(756.57,1), trunc (756.57,1)
         여기서 '.'은 기준으로 '0'이 된다. 
         뒤로 가면서 1,2, ... 
         앞으로 가면서 -1, -2, -3, 으로 된다. 

Tip. dual 이란?
  - dumy table 인데 select절의 결과 값을 보기 위한 가상의 테이블 
   (실제 테이블을 일력하면 같은 숫자가 대체 되어 나온다.)
 

주의! 별칭을 정해주기 전에 ROUND로 묶어준다. 

* MS_SQL도 반올림과 버림을 할 수 있지만 Oracle과 차이가 있다. 
  - round는 소수점 몇 번째까지 지정해 줄 수 있지만 버림음 floor 함수를 사용해서 정수만 나타낸다.  
   예시 ) select round(756.57,1) ;
            - 이 문장은 756.5 으로 결과가 나타남 

     
          select FLOOR(756.57 ,1) ;
            - 이 문장은 뒤에 1 때문에 문자 오류가 발생하고, 1을 없애면 756 만 출력된다. 


문제 97. 우리반 테이블에서 이름과 나이를 출력하는데 이름과 나이컬럼 옆에 나이가 짝수인지 홀수인지를 출력되게 하시오!

SQL> SELECT ename, age, MOD(age,2) AS 짝수홀수여부
           FROM EMP2;

*MOD/ 해당 숫자로 나누어서 나머지를 구하는 keyword

예시) select mod(10,3)
          from dual;
     - 10을 3으로 나눈 나머지 값 
      (실무에서는 해당 숫자가 짝수인지 홀수인지 판별할때 주로 사용한다.)

* MS_SQL 은 Oracle의 Mod 함수를 지원하지 않는다. 
   - 그래서 위의 Oracle 쿼리처럼 구현하기 위해서는 %를 사용해야 한다. 
    예시 ) SQL> SELECT ename, age, age%2 AS 짝수홀수여부
                     FROM EMP2;


문제 98.  오늘날짜를 출력하시오! 

SQL> select sysdate
           from daul;

*날짜 - 숫자 =   날짜 (date)
*날짜 + 숫자 =  날짜 (date)
*날짜  - 날짜 =   숫자 (for)


* MS_SQL에서 오늘의 날짜 출력 법 
  SQL> select SYSDATETIME() ;


문제 99 우리반 테이블에서 이름, 내가 태어날 날부터 오늘까지 총 몇일 살았는지 출력하시오!

SQL> SELECT ename, ROUND((sysdate - birth),0)
            FROM EMP2;

* '0'을 쓰지 않아도 default 값이 0으로 자동 출력된다. 

* MS_SQL에서는 이런식의 날짜 계산은 사용할 수 없다. 
  - 날짜 변환 시 문자 형식으로 변환되는데 문자끼리는 연산이 되질 않는다. 
  - 그래서 dateadd, datediff 같은 날짜 계산 함수를 써서만 날짜 계산이 가능하다. 


문제 100. 우리반 테이블에서 이름, 내가 태어난 날부터 오늘까지 총 몇주를 살았는지 출력하시오! 

SQL> SELECT ename, ROUND((sysdate - birth)/7,0)
           FROM EMP2;


문제 101. 우리반 테이블에서 이름, 내가 태어난 날부터 오늘까지 총 몇달을 살았는지 출력하시오!

SQL> SELECT ename, MONTHS_BETWEEN(SYSDATE, birth)
            FROM EMP2;

* 날짜 함수 
      1. months_between / 주의 사항은 앞에 변수 값에 최신 날짜를 입력해야 한다. 

* MS_SQL에서 날짜 계산 함수 형식
  - datediff( 시간단위구분자, 시작시간, 종료시간 )

select DATEDIFF ( mi , getdate() , getdate()+1 ) ==> 분
select DATEDIFF ( s , getdate() , getdate()+1 ) ==> 초 
select DATEDIFF ( hour , getdate() , getdate()+1 ) ==> 시간 (1일)
select DATEDIFF ( day , getdate() , getdate()+1 ) ==>  일 (1일)
select DATEDIFF ( month , getdate() , getdate()+31 ) ==> 개월 (31일)
select DATEDIFF ( year , getdate() , getdate()+730 ) ==>  년 (730일)

예시) (위와 같이 출력하고자 할때) 
     SQL> select datediff(month, hirth, getdate())
               from emp2 ;


문제 102. 사원 테이블에서 이름,  입사후 부터 그동안 받았던 총 급여를 출력하시오! (sal이 월급이라고 할 때)


SQL> SELECT ename, sal*ROUND(MONTHS_BETWEEN(SYSDATE,hiredate))
           FROM EMP;

* MS_SQL에서 출력 시 

  SQL> select ename, sal*datediff(month, HIREDATE, getdate())
           from emp ;


문제 103. 오늘부터 100달 뒤에 돌아오는 날짜를 출력하시오! 
 
SQL> select add_months(sysdate, 100)
           from dual;

* add_months/ 앞으로 해당 달 수 이후의 날짜 

* MS_SQL에서 앞으로 해당 달 수 이후의 날짜 구하는 법
  - MS_SQL에서는 Oracle 의 add_month 와 같은 dateadd 함수가 있다. 
    예시 )  select ename,dateadd(month,100,hiredate)
               from emp ;


문제 104. 오늘부터 앞으로 돌아올 금요일에 날짜를 출력하시오! 

SQL> select next_day(sysdate,'friday')
           from dual;

주의! 지정 요일에는 ' ' 표시를 꼭 해야 한다. 

* MS_SQL에서는 next_day와 같은 기능이 없다. 
   - 그래서 특정 날짜의 요일을 찾고 추론해야 한다. 
     예시 )   select datename(dw,getdate());
              
               OR 

               select datename(weekday,getdate());
             
             위의 쿼리를 돌리면 해당 요일이 나온다 거기서 원하는 요일까지 추론해야 한다. 



문제 105. 오늘부터 200달 뒤에 돌아오는 목요일의 날짜를 출력하시오 

SQL>  select next_day(add_months(sysdate, 200),'thursday')
           from dual;

* MS_SQL 에서 몇 달 후 돌아오는 날짜 출력하기 
   - 이번에도 위와 같이 특정 날짜를 찾고 추론해야 한다. 

SQL > select datename(dw,dateadd(month,200,getdate()))

문제 106. 오늘부터 요번달 말일까지 총 몇일 남았는지 출력하시오! 

SQL> SELECT LAST_DAY(SYSDATE)-SYSDATE
            FROM dual;


* MS_SQL은 LAST_DAY를 지원하지 않는다. 
   - 그래서 날짜 비교함수를 이용해서 해당 말일까지 몇일이 남았는지 출력해야 한다.
     
     예시 ) select datediff(day, getdate(), dateadd(month,1,getdate())-day(getdate()));
                   ( dateadd(month,1,getdate())-day(getdate()) 는 해당달의 마지막 날을 표시해준다.) 

문제 107. 이름과 월급을 출력하는데 월급을 출력할때에 천단위를 표시하시오! 

 SQL> select ename, to_char(sal), to_char(sal,'999,999')
   from emp;

* to_char(sal) / sal 의 type 이 문자로 바뀜
    따라서.
   select ename, to_char(sal) AS aaa
     from emp 
     ORDER BY aaa ASC;
    이 코드는 숫자의 크기로 정렬되지 않는다. 

* to_char(sal, '999,999') 
   - 이는 천단위로 끊어주게 해준다. 
   - 여기서 '9'는 0~9까지 무슨 수가 와도 관계 없다는 의미 

* MS_SQL은 천단위 표시를 하기 위해서는 2번의 형변환이 필요하다 
   - 첫번째 money로 형변화, 두번째 문자로 형변환이다. 
     예시 ) select ename, replace (convert(varchar,cast(sal as money),1) , '.00','')
              from emp ;
      
           위의 예시를 설명하자면 
              1) 먼저 숫자를 money 형으로 바꾸어 주어야 한다. 
              2) 두번째는 money형을 문자로 바꾸어 주어야 한다. 
                 (이때 convert에 3번째 인자 값은 money 형의 형식을 구분한다. '1' 의 형식은 천의자리에 콤마이다. )
              3) 필요 없는 소수점을 없애주기 위해서 replace를 사용한다. 
 
* money의 문자로 변환하기 위한 style 값의 차이 비교 

값

출력

0(기본값)

소수점 왼쪽의 세 자릿수마다 쉼표를 사용하지 않으며, 소수점 오른쪽에 두자리가 나타난다
(예 : 1234.56)

1

소수점 왼쪽의 세 자릿수마다 쉼표를 사용하며, 소수점 오른쪽에 두자리가 나타납니다.
(예:1,234.56)

2

소수점 왼쪽의 세 자릿수마다 쉼표를 사용하지 않으며, 소수점 오른쪽에 네 자리가 나타납니다.
(예:1234,5678)


문제 108. 이름과 총급여 (SAL*25000000) 을 출력하는데, 총급여를 출력할때에 천단위와 백만단위를 출력되게하시오!

SQL> SELECT ename, TO_CHAR((sal*25000000),'999,999,999,999')
           FROM EMP; 

주의! 숫자를 끊어줄 때 범위를 실제 값보다 적게 잡아주면 모든 숫자가 '#'로 표시 된다.

* 숫자에 to_char 함수 사용 

문제 109. 이름, 입사일, 입사한 요일을 출력하시오! 

  SQL> SELECT ename, hiredate, TO_CHAR(hiredate,'day')
              FROM EMP;

* 지난 10년 동안 살인 사간이 가장 많이 일어난 요일이 무엇인가?
* 날짜 형식 
  - YYYY , RRRR, YY, RR : 숫자 년도 
  - YEAR : 영문으로 년도 출력
  - MM : 달
  - MONTH : 달을 영문으로 출력
  - DD : 일
  - DAY, DY : 요일 (DY는 요일이 요약해서 출력 됨)

문제 110. 현재 세션의 날짜 형식을 확인하시오! 

SQL>SELECT *
           FROM nls_session_parameters;

문제 111. 81년 12월 11일에 입사한 사원의 이름과 입사일을 출력하시오! 

SQL> SELECT ename, hiredate
            FROM EMP
             WHERE hiredate = '11/dec/81';

*현재 세션의 날짜 형식을 RR/MM/DD 로 변경
   ALTER SESSION SET nls_date_format='RR/MM/DD';

문제 112. 현재 세션의 날짜 형식을 아래와 같이 변경하고, 81년 12월 11일에 입사한 사원들의 이름과 입사일을 출력하시오

SQL> SELECT ename, hiredate
            FROM EMP
            WHERE hiredate = '81/12/11';


 * RR  과 YY 의 차이
                 RR                                   YY
                 81                                    81
         1981       2081                 1981          2081
                2015                               2015
        현재 연도에서 가장           현재 세기의 연도
        가까운 연도 선택

*주로 RR 을 사용한다. 
문제 113. 현재 세션의 날짜 형식이 어떻게 되는 상관없이 81년 12월 11일에 입사한 사원들의 이름과 입사일을 출력하시오 !


1. to_char
         
        select ename, hiredate
            from emp
            where to_char(hiredate, 'RR/MM/DD')='81/12/11';

* 그러나 이럴 경우 모든 데이타를 변화해야 하기 때문에 성능이 떨어지게 된다. 

2. to_date
         
         select ename, hiredate
            from emp
            where hiredate = to_date('81/12/11','RR/MM/DD');

* 이럴 경우 성능에 영향을 주지 않으면서 정확하게 검색할 수 있다.

문제 114. 87년 5월 6일 부터 93년 3월 4일 사이에 태어난 학생들의 이름과 생일을 출력하시오!

SQL> SELECT ename, birth
           FROM EMP2
           WHERE birth BETWEEN TO_DATE('87/05/06','RR/MM/DD')
                                      AND TO_DATE('93/03/04','RR/MM/DD');


* MS_SQL은 to_data 함수를 사용할 수 없다. 
  - 그래서 실제 날짜 데이터 형식과 다른 형식으로 검색해서 결과를 보고 싶다면 convert 함수를 이용해야 한다. 

    예시 )  select ename, hirth 
               from emp2 
            where hiredate between convert(varchar,'1987/05/06',111) and convert(varchar,'1993/03/04',111) ;

  
* convert 시 날짜 형식 변화 시 style 식

세기 포함 안함(yy)

세기 포함(yyyy)

표준

입력/출력**

-

0 또는 100 (*)

기본값

mon dd yyyy hh:miAM(또는 PM)

1

101

USA

mm/dd/yy

2

102

ANSI

yy.mm.dd

3

103

영국/프랑스

dd/mm/yy

4

104

독일

dd.mm.yy

5

105

이탈리아

dd-mm-yy

6

106

-

dd mon yy

7

107

-

Mon dd, yy

8

108

-

hh:mm:ss

-

9 또는 109 (*)

기본값 + 밀리초

mon dd yyyy hh:mi:ss:mmmAM(또는 PM)

10

110

USA

mm-dd-yy

11

111

일본

yy/mm/dd

12

112

ISO

yymmdd

-

13 또는 113 (*)

유럽 기본값 + 밀리초

dd mon yyyy hh:mm:ss:mmm(24h)

14

114

-

hh:mi:ss:mmm(24h)

-

20 또는 120 (*)

ODBC 표준

yyyy-mm-dd hh:mi:ss(24h)

-

21 또는 121 (*)

ODBC 표준(밀리초)

yyyy-mm-dd hh:mi:ss.mmm(24h)

-

126(***)

ISO8601

yyyy-mm-dd Thh:mm:ss.mmm(스페이스 없음)

-

130*

회교식****

dd mon yyyy hh:mi:ss:mmmAM

-

131*

회교식****

dd/mm/yy hh:mi:ss:mmmAM
             

문제 115. 오늘 부터 100달 뒤에 돌아오는 날짜가 어떻게 되는가?

SQL> SELECT ADD_MONTHS(SYSDATE,100)
            FROM dual;


문제 116. 오늘 부터 100달뒤에 돌아오는 날짜의 요일을 출력하시오! 

SQL> SELECT to_char(ADD_MONTHS(SYSDATE,100),'day')
            FROM dual;


문제 117. 이름과 커미션을 출력하는데 커미션이 NULL 인 사원들은 0으로 출력되게 하시오! 

SQL> SELECT ename,comm, NVL(comm,'0')
           FROM EMP;


문제 118. 이름, 커미션을 출력하는데 커미션이 null인 사원들은 no comm이란 글씨로 출력되게 하시오! 

SQL>  select ename, comm, nvl(TO_CHAR(comm), 'no comm')
             from emp;

주의 ! select ename, nvl(comm, 'no comm') 
           from emp;
         - 이 경우  comm은 number이고  치환하는 값이 string 이기 때문에 오류가 난다. 

Tip.
   nvl 은 data가 없는 상태, data를 문자 type을 바꾸어도 null 값은 data가 없다.
   그래서 변경, 치환된다.
   즉, 자신이 변환하고 싶은 모양으로 미리 데이터를 변환 시켜놓아야 한다. 

* MS_SQL은 Oracle 의 nvl 함수에 대응하는 isnull 이 있다. 
   - 그래서 Oracle의 nvl 함수와 동일한 효과를 MS_SQL에서 얻고 싶다면 isnull 함수를 사용해야 한다. 

   예시 ) SQL> select ename, isnull(cast(comm as varchar), 'no comm') 
                   from emp ;



문제 119. 이름, mgr을 출력하는데 mgr이 null인 사원들은 no manager란 글씨로 출력되게하시오!

 SQL> SELECT ename, NVL(TO_CHAR(mgr),'no manager')
             FROM EMP;  


문제 120. 이름, 월급, 커미션, 월급+커미션을 출력하시오! 

SQL> SELECT ename, sal, comm, sal+NVL(comm,0)
            FROM EMP;

SQL> SELECT ename, sal, comm, sal+NVL(comm,0),NVL2(comm,sal+comm,sal)
          FROM EMP;
 
* nvl2 (comm, sal+comm,sal) 
   1. 만약 comm 이 null 이 아니면 sal+comm 값을 출력하고 
   2. 만약 comm 이 null 이면 sal 값을 출력해라 
   (자바스크립트의 if/else 와 비슷한듯 하다.) 


주의!
 - nvl 에서 '0'이 숫자 이기 때문에 ' '이 필요 없다. 


문제 121. 이름, 직업, 보너스를 출력하는데 보너스가
             직업이 SALESMAN이면 3000으로 출력되게하고
             직업이 SALESMAN이 아니면 0으로 출력되게하시오 !

SQL> select ename, job, decode(job,'SALESMAN',3000,0) bonus
           from emp;


*decode/ null 값이 아닌 data가 있는 값 중 특정 data를 선택해야 할 때 

* MS_SQL은 decode를 지원하지 않는다. 
  - 그래서 Oracle의 decode 식을 MS_SQL에서 구현하기 위해서는 case 문을 사용해야 한다. (But. Oracle에서도 case 문을 지원한다.) 

   예시) select ename, job, (case job when 'SALESMAN' then 3000
                                                  else 0 end ) as bonus
      from emp ;


문제 122. 이름 직업, 보너스를 출력하는데 보너스가 직업이 SALESMAN이면 3000이 출력되게하고
               직업이 ANALYST면 2500이 출력되게하고
               나머지 직업은 0으로 출력되게하시오!

  SQL>  select ename, job, decode(job,'SALESMAN',3000,
                                               'ANALYST',2500,0) bonus
               from emp; 


문제 123. 이름, 나이, 등급을 출력하는데 
              나이가 30살 이상이면 A 등급
              나이가 27살 이상이면서 30미만이면서 B등급 
              나이가 24살 이상이면서 27살 미만이면 C등급
              나머지 나이는 D 등급으로 출력하시오! 

SQL> SELECT ename, age, case when age >=30 then 'A'
                                      when age >=27 then 'B'
                                      when age >=24 then 'C'
                                             else 'D' end as 등급 
           from emp2;

* decode는 등호비교만 가능하다. 부등호 비교를 하려면 case 를 사용해야 한다.
* case는 등호 비교, 부등호 비교 모두 가능하다.
* 부등호 사용시 자동으로 그 이전까지의 범위로 제한되는 듯 하다. 
* 범위는 between and를 가지고도 설정 가능하다. 


문제 124. 이름, 월급, 부서번호, 보너스를 출력하는데
               부서번호 보너스가 부서번호가 10번이면 자신의 월급의 40%를 보너스로 출력하고
               부서번호가 20번이면 자신의 월급의 20%를 보너스로 출력하고
               나머지 부서번호는 그냥 0으로 출력하시오!

 SQL> select ename, sal, empno, case when empno = 10 then sal*0.4
                                             when empno = 20 then sal*0.2
                                                         else 0 end as bonus
   from emp; 
         
주의! case는 한 번만 쓰자! 

* 점심시간 문제: ( 라인 검사) 

내가 태어난 생일의 요일을 출력하시오! 

SQL> SELECT ename,TO_CHAR(birth,'Day')
           FROM EMP2
           WHERE ename = '송수신';

* to_char 앞 부분에는 해당 날짜만 지정해주면 자동으로 요일을 찾아서 변환해준다. 



* 마지막 문제 :
 
   점심시간 면담순서 (연장자 부터) 일주일 목, 금
   
   월, 화,  수 

   이름, 나이, 분류 (A,B,C,D)
   A는 성별이 남자이고 서울에 거주하는 학생 
   B는 성별이 여자이고 서울에 거주 하는 학생 
   C는 성별이 남자이고 서울이 아닌 학생 
   D는 성별이 여자이고 서울이 아닌 학생 

    나이가 높은 순서대로 출력하시오! 

SQL> SELECT ename, age, CASE WHEN (gender='남자' AND address LIKE '%서울%') THEN 'A'
                                        WHEN (gender='여자' AND address LIKE '%서울%') THEN 'B'
                                        WHEN (gender='남자' AND address NOT LIKE '%서울%') THEN 'C'
                                        WHEN (gender='여자' AND address NOT LIKE '%서울%') Then 'D'
                                          END AS 분류
           FROM EMP2
           ORDER BY age DESC;

SQL> SELECT ename, age, CASE WHEN (gender='남자' AND address LIKE '%서울%') THEN 'A'
                                        WHEN (gender='여자' AND address LIKE '%서울%') THEN 'B'
                                        WHEN (gender='남자' AND address NOT LIKE '%서울%') THEN 'C'
                                          else 'D' END AS 분류
           FROM EMP2
           ORDER BY age DESC;

* 괄호는 안 넣어주어도 괜찮다. 




























'ORACLE > SQL' 카테고리의 다른 글

SQL(3)- order by 복습, 단일 함수 中 문자 함수  (0) 2016.03.29
SQL(2)- as, SQL 작성 규칙, " ", distinct, 대체 인용 연산자(q'[ ]'), 날짜 출력, 날짜 세션 변경, order by, 오름차순(asc), 내림차순 (decsend), 연산자 우선순위 규칙, 테이블 생성, 데이터 입력  (0) 2016.03.23
SQL(1)- select,from, where 연산자(산술, 비교, 논리, 기타논리 (between, in, is null, like(%,_,escape)))  (0) 2016.03.15
  3) full table scan 
       - High water mark 까지 스캔하는 방법 
          (HWM - 포멧된 디스크와 포멧되지 안은 디스크의 경계선)
       - full table scan 방법은 인덱스 스캔은 아니지만 인덱스가 없는 경우 발생하는 기본 스캔임으로 알아둘 필요가 있다.  

     ⑴ Full table scan을 할 수 밖에 없는 경우 
       ① 인덱스가 없을 때 
       ② full 힌트를 사용하면 
       ③ 인덱스를 생성할 때 
       ④ 테이블의 통계정보를 수집할 때 

     ⑵ Full table scan을 할 수밖에 없다면 full table scan 속도를 높이는 방법 
        ① full table scan 에 관련한 오라클 파라미터를 조정 
            - 파라미터 : db_file_multiblock_read_count  
              ( 이 파라미터는 full table scan 을 할 때 한번에 읽어들이는 블럭의 갯수를 결정하는 파라미터 ) 

            alter session set db_file_multiblock_read_count = 128;

       ② 병렬로 작업한다.
            select /*+ full(emp) parallel(emp,4) */ ename, sal, job
             from emp 
           where job = 'SALESMAN';

    ⑶ full table scan 속도 향상 테스트

     ① set timing on
     ② create table sales800
           as 
               select * from sh.sales ;

     ③ alter session set db_file_multiblock_read_count=4 ;
        alter session set workarea_size_policy=manual ;
        alter session set sort_area_size=10 ;      

     ④ create index sales_index
           on sales800(amount_sold, prod_id) ;
          -- 시간 확인 
     ⑤ drop table sales800 purge ;
     ⑥ 다시 scott 으로 접속해서 
     ⑦ 2번부터 다시 수행하는데 아래와 같이 128로 설정하고 테스트 
          alter session set db_file_multiblock_read_count=128 ;
          alter session set workarea_size_policy=manual ;
          alter session set sort_area_size=10000000 ;  

 4)  index range scan 
     - 인덱스의 일부분만 범위 스캔해서 DATA를 엑세스 스캔 방법 
        힌트 :  /*+ index (emp emp_sal) */ 
              (기본적으로 사용하는 hint 는 index range scan을 이용하라는 의미이다.) 

    ⑴ 실습 환경 구성 
       
      drop table sales500 purge
       
      create table sales500
       as 
         select rownum rn , s.* 
            from sh.sales s;

       create index sales500_rn on sales500(rn) ;
       create index sales500_promo_id on sales500(promo_id);

      튜닝 전 : select /*+ full(sales500) */ * 
                      from sales500
                      where rn=299;

       튜닝 후 : select /*+ index(sales500 sales500_rn) */ *
                      from sales500
                      where rn=299; 

    * 실행 통계 및 자동 추적 사용 시 (SQL gate ) 

     db block gets       ┐
     consistent gets     ┘  ------------> 메모리 영역 
     physical reads    ------------------> 디스크 영역


  5) index full scan 

     ⑴ index full scan 의 종류 
       - index full scan  : 인덱스 full 로 스캔 
           (힌트 : index_fs )                   

       - index fast full scan  : 인데스 full 로 스캔하는데  index full scan 보다 더 속도가 빠르다.  
           (힌트 : index_ffs )   (multiblock i/o를 하기 때문에)
           

     ⑵ index full scan 과  index fast full scan 의 차이 
         index full scan                    vs          index fast full scan 
     -  인덱스 구조에 따라 스캔                   세그먼트 전체를 스캔 
     -  순서가 보장 (정렬 됨)                     순서가 보장이 안된다. (정렬이 보장이 안된다는 것이지 아예 안된다는 것은 아니다.)
     -  single block i/o                            multi block i/o
     -  병렬 스캔이 블가능하다.                   병렬스캔이 가능하다. 


      * SELECT /*+ index_fs(sales500 sales500_rn) parallel_index(sales500,sales500_rn,4) */ count(rn)
             from sales500;
           ( 위의 SQL 은 index full scan으로 hint 를 사용하여 병렬처리는 실행계획에 포함되지 않는다.) 

  6) index skip scan  스캔 방법 
     - 인덱스를 full  또는 fast full 로 전체를 스캔하는 것이 아니라 중간 중간 skip 을 해서 사용
        (힌트 : index_ss)  

      ⑴ index skip scan 이란 
         - 인덱스의 첫번째 컬럼이 where 조건이 없어도 인덱스를 사용할 수 있게 한다.
            ( 이전에는 결합 컬럼 인덱스의 첫번째 컬럼이 where 조건에 있어야만 결합 컬럼 인덱스를 엑세스 할 수 있다. ) 
          
             예시 ) index의 컬럼 순서에 따라서 index를 사용하지 못하는 경우
     
                       create index emp_deptno_sal 
                           on emp(deptno, sal) ;

                       select ename, sal 
                         from emp 
                       where sal = 3000;     ----------------> 위의 index 사용 못 함
 
                       select ename, sal 
                         from emp 
                        where deptno = 10; -----------------> 위의 index를 사용 함 
                       
                        ( 실무에서는 인덱스를 임의로 추가하기 힘들다.)

                      SELECT /*+ index_ss(emp emp_deptno_sal) */ ename, sal
                          from emp
                        where sal = 3000;


      ⑵ index skip scan 스캔 방법 설명 

        SELECT /*+ index_ss(emp emp_deptno_sal) */ ename, sal
            from emp
          where sal = 2975;

          DEPTNO    SAL    ROWID

                 10    1300    AAAg7WAAZAAAAfrAAQ
                 10    2975    AAAg7WAAZAAAAfrAAF
                 20     800    AAAg7WAAZAAAAfrAAN
                 20    1100    AAAg7WAAZAAAAfrAAP
                 20    2975    AAAg7WAAZAAAAfrAAG
                 20    3000    AAAg7WAAZAAAAfrAAM
                 20    3000    AAAg7WAAZAAAAfrAAO
                 30     950    AAAg7WAAZAAAAfrAAK
                 30    1250    AAAg7WAAZAAAAfrAAH
                 30    2975    AAAg7WAAZAAAAfrAAL
                 30    3500    AAAg7WAAZAAAAfrAAJ
                 30    3600    AAAg7WAAZAAAAfrAAI
                 30    4050    AAAg7WAAZAAAAfrAAE

            - deptno 번호별 해당 sal 의 data 가 있으면 값을 찾고 다름 deptno 그룹으로 넘어간다. 
            - 해당 경우에서는 deptno의 종류가 적을 수록 효과적이다. 
               ( 앞의 컬럼이 나누어지는 경우의 수가 적을 수록 더 빠르게 값을 찾을 수 있다.) 
    
        * 결합 컬럼 인덱스의 첫번째 컬럼이 where 절에서 선분조건 (betwwen .. and like) 으로 사용 된다면 
             인덱스를 넓게 읽을 수 밖에 없기 때문에 성능이 저하된다. 그럴때는 두가지 방법으로 튜닝한다. 
                 1 index skip scan 사용 
                 2 between .. and 를 in으로 변경 

            (그림은 index 사용 시에 대한 실행 내용)           
           


문제 17. 아래 SQL 의 성능을 높일 수 있도록 인덱스를 생성하시오 ! 

튜닝 전 :
             select * 
               from sales500 
            where cust_id=35834 ;

튜닝 후 :
           CREATE INDEX sale500_cust ON SALES500(cust_id);

              select /*+ index (sales500 sales500_cust) */ * 
               from sales500
             where cust_id=35834 ;

문제18. EMP테이블에 사원 수가 몇명인지 조회하시오.

튜닝 전 :
          select count(*) 
            from emp ;

튜닝 후 : 
          select /*+ index (emp emp_sal) */ sal 
            from emp ;

문제 19. 아래의 SQL 의 성능을 높일 수 있도록 인덱스를 생성하고 힌트를 주시오 ! 

튜닝전  :
          select count(ename) 
            from emp;

튜닝 후 :
          (인덱스 생성 후 create index emp_ename on emp(ename) ; )

          SELECT /*+ index(emp emp_ename) */ COUNT (ename)
            FROM EMP ;

or 

  더 성능이 좋게 하려면
          SELECT /*+ index(emp emp_ename) */ COUNT (ename)
            FROM EMP
          WHERE ename > ' ' ; 

 - 더 성능이 좋을 것이라고 판단하는 것은 위의 SQL 은 index full scan이 실행계획에 포함되지만 
 - 아래 SQL은  index range scan 이 실행계획에 포함된다. 

* ename 컬럼에 not null 제약이 걸려있어야 더 확실히 옵티마니져가 index full scan 이나 index fast full scan 을 할 수 있다.  

문제 20. 직업에 인덱스를 걸고 아래의 SQL 의 성능을 높이시오 
튜닝 전 :
          select count(job) from emp ;

튜닝 후 :
          CREATE INDEX EMP_JOB ON EMP(job);

          SELECT /*+ index_ffs(emp emp_job)*/COUNT(job)
            FROM EMP;

   - 이 경우는 어차피 테이블의 모든 row를 보아야 하고 테이블도 작기 때문에 fast full scan을 하는 것이 더 나을 수 있다. 

   * 오라클 버전에 따라서 not null 조약에 따라서 index full scan이 되지 않을 수도 있다. 

문제 21. 아래의 SQL의 성능을 높이시오 ! 

튜닝 전 : 
          select count(*)  
            from sales500;

튜닝 후 :
          create index sales500_rn on sales500(rn) ;

          select /*+ index_ffs (sales500 sales500_rn) count(rn) 
            from sales500;

문제 22. demo를 다시 돌리고 월급에 인덱스를 생성한 후에 월급이 0 이상인 사원들의 월급을 출력하는데 index fast full scan 이 되도록 하시오 ! 

 SELECT /*+ index_ffs(emp emp_sal) */ sal
  FROM EMP
  WHERE sal > 0 ;

* 이 경우  index full scan hint를 주더라도 실행계획은 rang scan으로 짠다.
   ( 더 효율적인 방식으로 인식하여) 

 SELECT /*+ index_fs(emp emp_sal) */ sal
  FROM EMP
  WHERE sal > 0 ;

문제 23. 이름에 EN 또는 IN을 포함하고 있는 사원들의 이름과 월급과 직업과 부서번호를 출력하시오! 

  SQL> select ename, sal, job, deptno 
            from emp 
           where ename like '%EN%' 
                or ename like '%IN%'
    
  * regexp_like 를 사용하여도 된다. 
  
   SQL> select ename, sal 
         from emp
         where regexp_like(ename,'(EN|IN)'); 

문제 24. 아래의 환경을 구성하고 아래의 SQL을 튜닝하시오.

create table customers 
 as select * from sh.customers ;

create index customers_indx1
 on customers (cust_street_address) ;

SELECT  *
 FROM customers
 WHERE cust_street_address LIKE '%Ceneva%'
  or cust_street_address LIKE '%Mcintosh%';
   
  consistent gets -- 1528 

* 각 튜닝 방법 적용 후에 consistent gets의 값 
 
1) where 절에 like 대신 instr 함수를 사용한 경우 

SELECT /*+ index(customers customers_indx1) */ *
 FROM customers
 WHERE INSTR(cust_street_address, 'Ceneva') >0
  or INSTR(cust_street_address, 'Mcintosh') >0 ;
 
 consistent gets -- 389   

2) where절에 like를 사용한 경우

SELECT /*+ index(customers customers_indx1) */ *
 FROM customers
 WHERE cust_street_address LIKE '%Ceneva%'
  or cust_street_address LIKE '%Mcintosh%';

   consistent gets -- 586

3) where 절에 like를 사용하고 index full scan을 시도한 경우 

SELECT /*+ index_fs(customers customers_indx1) */ *
 FROM customers
 WHERE cust_street_address LIKE '%Ceneva%'
  or cust_street_address LIKE '%Mcintosh%';

   consistent gets -- 1462 ( 실제 실행계획이 full table scan 함 )

4) where 절에 like를 사용하고 index fast full scan을 시도한 경우

SELECT /*+ index_ffs(customers customers_indx1)  */ *
 FROM customers
 WHERE cust_street_address LIKE '%Ceneva%'
  or cust_street_address LIKE '%Mcintosh%';

   consistent gets -- 1462 (실제 실행계획이 full table scan 함) 

5) Rowid를 사용해서 테이블 스캔이 일어나도록 하는 방식 

 SELECT /*+ no_query_transformation rowid(customers) */ a.*
  FROM customers a, (SELECT /*+ index(customers customers_indx1) */ ROWID
                       FROM customers
                       WHERE cust_street_address LIKE '%Ceneva%'
                          or cust_street_address LIKE '%Mcintosh%') b
  WHERE A.ROWID = B.ROWID ;

 consistent gets -- 1725

* where 절에 like 사용 보다  instr 사용이 더 빨라진다. 

문제 25. 부서번호의 종류가 몇가지 인지 출력하시오! 

SQL> SELECT COUNT (*)
        FROM (SELECT DISTINCT (deptno) 
                   FROM EMP
                  WHERE deptno IS NOT null) ;

or 

SQL> SELECT COUNT( DISTINCT deptno)
          FROM EMP;

문제 26. 아래의 SQL을 튜닝하시오! 

create index sales500_channel_custid
    on sales (channel_id, cust_id) 

튜닝 전 :
          select /*+ full(sales500) */ * 
            from sales500
          where cust_id = 2869 ;

  consistent gets    -- 4452

튜닝 후 : 
          select /*+ index_ss(sales500 sales500_channel_custid) */ *
            from sales500
          where cust_id = 2869 ;

  consistent gets  -- 200

* 해당 테이블에 alias를 사용하였다면 index 사용시 table 명에도 사용하는 alias를 사용해야 한다. 
     (alias를 사용하지 않으면  hint 가 제대로 사용되지 않는다. ) 

select /*+ index_ss(s sales500_channel_custid) */ *
    from sales500 s
  where cust_id = 2869 ;

문제 27. 아래의 SQL을 튜닝하시오 ! 

( 환경 설정  : 
                     CREATE TABLE mcustsum
                             AS
                       SELECT rownum custno
                                   , '2008' || lpad(CEIL(rownum/100000), 2, '0') salemm
                                   , decode(MOD(rownum, 12), 1, 'A', 'B') salegb
                                   , round(dbms_random.VALUE(1000,100000), -2) saleamt
                          FROM   dual
                     CONNECT BY level <= 1200000 ;

CREATE INDEX mcustsum_IDX2 ON mcustsum(salemm, salegb);

EXEC dbms_stats.gather_schema_stats('SCOTT');



튜닝 전 :

select count(*) 
 from mcustsum t
where salegb = 'A'
and salemm between '200801' and '200812';

-- 3367
-- index fast full scan : 실행계획
-- index range scan : 실제 실행계획

튜닝 후 :

SELECT /*+ index_ss(t mcustsum_IDX2) */ count(*)
 from mcustsum t
where salegb = 'A'
and salemm between '200801' and '200812';
-- 300
-- index skip scan 

* between .. and는 index range scan  과 index fast full scan 이 가능하다. 
* 2가지를 고려해서 테스트 한 후 더 성능이 좋은 것을 사용하면 된다. 

문제 28. 위의  SQL을 힌트쓰지 말고 재작성하는데 Between .. and 가 아니라 IN으로 변경해서 실행하시오 

SELECT /*+ index(t mcustsum_IDX2) */ count(*)
 from mcustsum t
where salegb = 'A'
and salemm IN   ( '200801', '200802','200803','200804','200805','200806','200807','200808','200809','200810','200811', '200812' );
-- 304

* in을 사용해서 실행계획에서 index range scan이 사용 된다. 
* in 사용 시 index_ss(skip scan) 을 사용해도 skip scan 으로 실행계획이 세워지지 않는다. 
* 과거 skip scan 기능이 나오기 전에 사용한 튜닝 방법이다. 

* 점심시간 문제 : 아래의 SQL 을 튜닝하시오!
                            (직업과 월급에 각각 인덱스가 있다.) 

튜닝 전 : 
             select ename,job, sal, deptno
                from emp 
                where job||sal = 'SALESMAN3000' ;
-- 41
-- full table scan 

튜닝 후 :
              select ename, job, sal ,deptno 
              from emp 
               where job = 'SALESMAN'
                    and sal = 3000;

-- 1
-- index range scan 

* 비교 방법
 
db block gets       ┐
consistent gets     ┘  ------------> 메모리 영역 
physical reads    ------------------> 디스크 영역
            
* 인덱스를 각각 생성하지 않아도 가능하다. 
CREATE INDEX emp_job_sal ON EMP(job, sal);

* 오늘의 마지막 문제: 

 아래 SQL 을 튜닝하시오 ! 
 (그룹 함수 쓰지 말고 sal 인덱스만 이용해서 결과를 출력하시오!) 

튜닝 전 :
select max(sal) 
  from emp  ;

튜닝 후 : 

SELECT /*+ index_desc(emp emp_sal) */ sal
 FROM EMP
 WHERE sal > 0
 AND ROWNUM = 1 ;
  ( - 따로 sal 에 index를 걸어주었을 때) 

or 

SELECT sal
 FROM  (SELECT /*+ index_ss_desc(emp emp_deptno_sal) */ sal
          FROM EMP
          WHERE sal > 0)
 WHERE ROWNUM = 1; 
( - 기존에 걸어둔  emp_deptno_sal (deptno, sal) index를 사용하려면 ) 

* 실행 통계를 보기 전에 analizy를 하는 것이 더 정확하다 

* 간혹 SQL gate 버전에 따라서 실행 통계 값이 부정확 할 수 있다. 


'ORACLE > SQL 튜닝' 카테고리의 다른 글

SQL튜닝(4) - IOT (index organizaion table) 구성, 클러스터 테이블 구성, 다중 클러스터 테이블 구성  (0) 2016.04.11
SQL튜닝(3) - index(index merge scan, index bitmap merge scan, index join, index uniqe scan), table random access 부하  (0) 2016.04.06
SQL튜닝(1) - index (index 구조, index 사용 시 주의 사항, index 스캔 방식의 종류 )  (0) 2016.03.15

문제 59. 직업이 SALESMAN이 아닌 사원들의 이름과 월급과 직업과 입사일을 출력하는데,
              먼저 입사한 사원순으로 출력하시오  

SQL> 3 SELECT ename, sal, JOB, hiredate
           1 FROM EMP
            2 WHERE JOB != 'SALESMAN'
            4 ORDER BY hiredate ASC;

*keyword 앞에 번호들은 실행 순서 

문제 60. 20번 부서번호에서 근무하는 사원들의 이름과 월급과 연봉(sal*12)을 출력하는데
              연봉이 높은 사원부터 출력하시오 

SQL> SELECT ename, sal, sal*12 as "연봉"
           FROM EMP
           WHERE deptno = 20
           ORDER BY "연봉" DESC;


문제 61. 우리반 테이블에서 이름과 나이를 출력하는데 나이가 높은 사람부터 출력하시오!

SQL >SELECT ename, age
            FROM emp2
            ORDER BY age DESC;


문제 62. 우리반 테이블에서 여학생의 이름과 주소와 나이를 출력하는데
                 나이가 높은 순서대로 출력하시오! 

SQL>SELECT ename, address, age
          FROM emp2
          WHERE gender = '여자'
          ORDER BY age DESC;


문제 63. 우리반 테이블에서 성씨가 김씨인 학생의 이름과 나이를 출력하시오! 

SQL>SELECT ename, age
          FROM emp2
          WHERE ename LIKE '김%';


문제 64. 나이가 30대인 학생들의 이름과 성별과 나이를 출력하시오!

SQL>SELECT ename, gender, age
           FROM emp2
           WHERE age BETWEEN 30 AND 39;


문제 65. 서울에 사는 학생들에 이름과 주소를 출력하시오!   

SQL>SELECT ename, address
           FROM emp2
           WHERE address LIKE '%서울%';


문제 66. 전공이null인 학생들의 이름과 전공을 출력하시오!

SQL> SELECT ename, major
            FROM EMP2
            WHERE major is NULL;

주의! null 같은 '='가 아닌 is 를 사용


문제 67. 전공을 출력하는데 중복제거해서 출력하시오!

SQL> SELECT DISTINCT major
            FROM EMP2;


문제 68. 전공이 컴퓨터 공학이 아닌 학생들의 이름과 전공을 출력하시오!

SQL > SELECT ename, major
            FROM EMP2
            WHERE major <> '컴퓨터공학'; 

* null 이 아닌 이상  'is'를 쓰지 않는다. 


문제 69. 주소가 서울이 아닌 학생들의 이름과 주소를 출력하시오! 

SQL > SELECT ename, address
            FROM EMP2
            WHERE address NOT LIKE '%서울%';

* 'not like' 도 사용가능


문제 70. 학원까지 오는 시간이 가장 적게 걸리는 학생부터 이름과 주소와 시간을 출력하시오! 

SQL > SELECT ename, address, distance
            FROM EMP2
            ORDER BY distance ASC;


문제 71.  전공이 컴퓨터 공학이고 성별이 여학생인 학생들의 이름과 전공과 주소를 출력하는데,
               집과의 걸리가 먼 학생순으로 출력하시오 

SELECT ename, major, address
  FROM EMP2
  WHERE major ='컴퓨터공학' AND gender ='여자'
  ORDER BY distance DESC;


문제 72. 같은 라인에 있는 학생들의 결과가 아래와 같이 출력되게하시오! 

            곽민영 학생의 나이는 34이고 전공이 컴퓨터 공학이며 집에서 학원으로 오는 시간은 3분 입니다. 

SELECT ename||' 학생의 나이는'||age||'이고 전공이 '||major||'이며 집에서 학원으로 오는 시간은 '||distance||'분 입니다.'
  FROM EMP2
  WHERE empno BETWEEN 16 AND 19;

* MS_SQL 구현시 

  SELECT concat( ename,' 학생의 나이는',age,'이고 전공이 ',major,'이며 집에서 학원으로 오는 시간은 ',distance,'분 입니다.')
  FROM EMP2
  WHERE empno BETWEEN 16 AND 19;


3장 함수 


- 함수를 왜 배워야하는가? 
  아래와 같은 정보을 알기 위하여 배워야 한다. 

ex)
우리반 테이블에서 전공, 전공별 인원수를 출력하시오!

우리반 테이블에세 가장 나이가 어린 학생의 이름을 출력하시오!

메일 도메인, 메일 도메인별 건수를 출력하시오! 



- 함수의 종류 2가지? 


1. 단일행 함수
   - 문자
   - 숫자
   - 날짜
   - 변환
   - 일반 

1) 문자 함수 
1. 대소문자 변환 함수 
     - lower : 소문자로 출력하는 함수
     - upper : 대문자로 출력하는 함수
     - initcap: 첫번째 철자는 대문자 나머지는 소문자로 출력하는 함수 

2) 문자 조작 함수 
      - concat : 두개의 컬럼의 값을 연결하는 함수
      - substr : 특정 철자를 잘라내는 함수
      - length : 철자의 길이를 출력하는 함수 
      - instr : 특정 철자의 위치를 출력하는 함수
      - lpad : 왼쪽에 특정값을 채워넣는 함수 
      - rpad : 오른쪽에 특정값을 채워넣는 함수 
      - trim : 특정 철자나 값을 잘라내는 함수 
      - replace : 특정 철자로 변경하는 함수 

2. 복수행 함수
   - max
   - min
   - count
   - avg
   - sum


문제 73.  아래와 같이 결과를 출력하시오! 

SQL> select upper(ename), lower(ename), initcap(ename)
           from emp;

* MS_SQL 로 작성시 
  - MS_SQL은 inticap 함수가 없어서 Oracle의  inticap 함수 처럼 나타내기 위해서는 아래와 같이 사용해야 한다. 

    SQL> select upper(ename), lower(ename), concat(upper(left(ename,1)),lower(substring(ename,2,10)))
           from emp;


문제 74. 이름이 smith인 사원의 이름과 월급을 출력하시오! 
              아래 ? 부분을 채워 넣으시오!

     where ? = 'smith'

SQL> SELECT ename, sal
           FROM EMP
           WHERE LOWER(ename) = 'smith';

* 자료가 대문자 인지 소문자 인지 확실히 모를 때 유용하게 사용할 수 있다.


문제 75. 이름과 월급을 붙여서 출력하시오! 
 
 SQL> select concat(ename, sal)
            from emp;
 
* concat은 2개의 컬럼만 붙일 수 있다. 

* MS_SQL 에서 concat 함수 
  - MS_SQL에서는 concat 안에 여러개의 컬럼은 붙일 수 있다. 


문제 76. 우리반 테이블에서 이름과 나이를 붙여서 출력하시오!

 SQL> SELECT CONCAT(ename, age)
            FROM EMP2;


문제 77. 이름을 출력하는데 이름의 첫번째 철자만 출력하시오! 

SQL> select ename, substr(ename,1,1)
           from emp;

* substr(컬럼명, 숫자1, 숫자2) 
- 숫자1 : 시작점을 의미한다. 
- 숫자 2: 시작점 부터 몇개를 출력할 것인지 입력한다. 

* MS_SQL 에서 해당 철자만 사용하기 
  - Oracle substr 함수에 해당 하는 MS_SQL 함수는 substring 이다. 

  SQL> select ename, substring(ename,1,1)
           from emp;


문제 78. 이름의 세번째 철자만 출력하는데 소문자로 출력하시오! 

SQL>SELECT LOWER(substr(ename,3,1))
           FROM EMP;

* MS_SQL 에서 출력 방법 
SQL> SELECT LOWER(substring(ename,3,1))
          FROM EMP;


문제 79. 아래의 쿼리의 결과를 initcap 사용하지 말고,
              upper, lower, substr || 를 사용해서 출력하시오!

예시) select initcap(ename)
           from emp;

SQL> SELECT UPPER(substr(ename,1,1))||LOWER(substr(ename,2,8))
            FROM EMP;


문제 80. 우리반 테이블에서 성씨가 김씨, 이씨, 박씨인 학생들의 이름과 주소를 출력하시오!
              (like 사용하지 말고)

SQL> SELECT ename, address
           FROM EMP2
           WHERE substr(ename,1,1) IN ('김','이','박');

* MS_SQL에서 출력 방법 

SQL> SELECT ename, address
           FROM EMP2
           WHERE substring(ename,1,1) IN ('김','이','박');


문제 81. 이름과 이름의 철자의 갯수를 출력하시오 !

 SQL> select ename, length(ename)
            from emp;

* MS_SQL 에서 철자의 갯수 출력 방법 
- Oracle length에 해당하는 MS_SQL 의 함수는 len 이다 

SQL> select ename, len(ename)
         from emp;


문제 82. 이름, 이메일, 이메일의 철자의 갯수를 출력하는데, 
             이메일의 철자의 갯수가 가장 많은 학생부터 출력하시오! 

SQL>SELECT ename,email, length(email)
          FROM EMP2
          ORDER BY LENGTH(email) DESC;

Tip.
  select 절에서 length에 alias 를 주고 order by에 alias를 활용하여도 됨

* MS_SQL에서 위와 같은 사용 법
SQL>SELECT ename,email, len(email)
          FROM EMP2
          ORDER BY LEN(email) DESC;


문제 83. 이름 이메일의 도메인만 출력하시오!

SQL> SELECT substr(email,instr(email,'@')+1,(instr(email,'.')-instr(email,'@')-1))
           FROM EMP2;

* 다른 방법들도 있다. length를 사용해서 뒤에서 부터 빼주는 방법도 있다. 
   
   SQL> SELECT substr(email,instr(email,'@')+1, length(email)-instr(email,'@')-4 )
              FROM EMP2;

* MS_SQL 로 위의 쿼리 변환 시 
 - 함수 변화가 필요하다.  
 SQL> SELECT substring(email,patindex('%@%',email)+1,(patindex('%.%',email)-patindex('%@%',email)-1))
          FROM EMP2 ;


문제 84. 이름을 출력하고 그 옆에 이름에 철자 A가 몇번째 있는지 출력하시오! 

 SQL> select ename, instr(ename, 'A')
             from emp;

*instr/ 해당 문자가 몇 번째에 위치하는지 알수 있게 해주는 keyword

* MS_SQL에서 instr 대체 사용법 
   - MS_SQL에서 instr을 대체하는 함수는 patindex 이다 
 SQL> select ename, patindex('%A%',ename)
             from emp;


문제 85. 우리반 테이블에서 이메일을 출력하고
              그 옆에 이메일의 @ 몇번째 자리에 있는지 출력하시오!

SQR> SELECT email, instr(email,'@')
            FROM EMP2; 


문제 86. 이메일을 출력하는데 @ 다음의 철자부터 출력하시오! 

SQR> SELECT substr(email,instr(email,'@')+1)
            FROM EMP2;

*substr에서 두번째 자리에 아무것도 쓰지 않으면 끝까지 출력 된다.

* MS_SQL에서 substring 사용법 
   - MS_SQL에서 substring은 무조건 3개의 인자가 있어야지 쿼리가 실행된다. 
   - 아래의 쿼리는 필요 인자 부족으로 error가 발생한다. 

SQL> SELECT substring(email,patindex('%@%',email)+1)
         FROM EMP;


문제 87. 이름과 월급을 출력하는데 월급을 10자리로 출력하고, 
             월급이 출력되는 나머지 자리에*를 출력하시오! 

  select ename, lpad(sal, 10, '*')
     from emp;

*lpad/rpad
  - 자리 개수를 선정하고 나머지는 다른 것으로 채우겠다. 
  - ex) lpad(컬럼명, 자리개수, 채울 내용)

* MS_SQL 에서 lpad 대체법 
 - MS_SQL은 lpad가 사용되지 않는다. 그래서 replicate를 사용해서 쿼리를 작성해야 한다. 
   또한, 숫자와 같은 경우 형변환이 일어나서 기존에 숫자 값도 형변환을 해줘야 한다. 

SQL> select replicate('*',10-len(sal))+cast(sal as varchar) 
         from emp ;


문제 88. 아래의 data를 입력하고 이름이 JACK인 사원의 이름과 월급을 출력하시오! 

SQL> insert into emp(empno, ename, sal)
           values(1235,'  JACK  ', 3600);

         select ename, sal
           from emp
           where trim(ename) = 'JACK';

*trim 
 - 앞, 뒤의 공백을 지워준다. (단, 중간에 공백은 지워주지 못한다.)

* MS_SQL 의 Trim 함수 대체 방법 
 - MS_SQL은 trim 함수를 사용할 수 없어서 ltrim 과 rtrim을 중첩 사용하여 Oracle의 trim 함수와 같은 효과를 낼 수 있다. 

SQL> select ename, sal
         from emp
       where rtrim(ltrim(ename)) = 'JACK';


문제 89. 이름과 전공을 출력하는데 전공을 출력할때에 과 가 안나오게 하시오! 

SQL> SELECT ename, TRIM('과' FROM major)
           FROM EMP2;

* MS_SQL 에서 Trim 함수 대체 방법 2 
  - MS_SQL 에서는 Trim 함수를 사용할 수 없어서 replace를 활용하여서 쿼리를 작성해야 한다. 

SQL> SELECT ename, replace(substring(major,1,1),'과','') + substring(major,2,(len(major)-2)) + replace(substring(mjor,len(ename),1),'과','')
          FROM EMP2 ;


문제 90. 이름과 월급을 출력하는데 월급을 출력할때에 0을 *로 출력하시오! 

SQL> select ename, replace(sal, 0, '*')
          from emp;

* replace
   - 대체하는 keyword
   - replace(컬럼명, 대체하고 싶은 문자 or 숫자, 대체할 숫자 or 문자)
   - regexp_replace(sal, '[0-2]', '*')를 사용해서 좀 더 확장할 수 있다. 


문제 91. 우리반 테이블에서 20대인 학생들 중에 naver 메일을 사용하는 학생들의 이름과 주소와 나이와 메일주소를 출력하는데, 나이가 높은순서대로 출력을 하고 그리고 컬럼명이 아래와 같이 출력되게 하시오! 

      학생이름, 사는 곳, 메일주소, 나이 

SQL> SELECT ename AS "학생이름", address AS "사는곳", email AS "메일주소", age AS "나이"
            FROM EMP2
            WHERE age BETWEEN 20 AND 29
               AND email LIKE '%naver%'
           ORDER BY age DESC;

 



'ORACLE > SQL' 카테고리의 다른 글

SQL(4) - 단일함수 [숫자(round, trunc, mod), 날짜(between months, add-month, 변화, 일반]  (0) 2016.04.05
SQL(2)- as, SQL 작성 규칙, " ", distinct, 대체 인용 연산자(q'[ ]'), 날짜 출력, 날짜 세션 변경, order by, 오름차순(asc), 내림차순 (decsend), 연산자 우선순위 규칙, 테이블 생성, 데이터 입력  (0) 2016.03.23
SQL(1)- select,from, where 연산자(산술, 비교, 논리, 기타논리 (between, in, is null, like(%,_,escape)))  (0) 2016.03.15


  5) password file
    - 특별한 권한을 가진 유저를 인정해주기 위한 파일 (ocm)
      (db를 올리고 내릴 수 있는 권한)

   * 패스워드 파일의 위치 확인 방법
    $cd $ORACLE_HOME/dbs
    $ls -l orapw*

  * 패스워드 파일에 등록된 유저를 확인하는 방법?

   SQL> select * 
            from v$pwfile_users;

    * 패스워드에 등록하기 유저 
 
    SQL> grant sysdba to scott;
        ( But. scott으로만 접속하면 db를 내릴 수 없다. ) 
   (SQL> connect scott/oracle_4U as sysdba 이렇게 접속해야 사용할 수 있다.)
                               (sysdba의 비밀번호)               

   * 패스워드 파일을 이용하여 remote로 연결시 sysdba 권한으로 연결하는 방법 
    sqlplus sys/oracle_4U@orcl as sysdba 
    ( @orcl을 붙여준 이유는 리스너를 타고 접속하겠다는 뜻, 즉 다른 말로 말하면 원격에서 내 db로 sys 유저로 접속할 수 있게 하겠다.)  


  6) parameter file
   - 인스턴스의 구조정보를 담고 있는 파일 

   * 파라미터 파일의 위치 확인 
  
    SQL> show parameter spfile

   * 파라미터 파일의 내용을 확인하는 방법 

    SQL> create pfile='/home/oracle/initorcl.ora'
              from sqfile='+DATA/orcl/spfileorcl.ora';

    $cd /home/oracle
    $vi initorcl.ora


   7) database configuration
       가. non clustered system
       나. clustered system (=RAC)
            real Application Cluster

    * 노드 (컴퓨터 묶음의 단위) / 기본적으로는 4노드로 주로 사용/ 이론적으로는 100대까지 묶을 수 있음
    * 24시간 중단없는 서비스를 지원받을 수 있다는 것이 장점

   8) connection 과 session의 차이

     * connection 이란 
        - user process가 instance와 통신할 수 있는 상태가 되었다. 
           client -----------------> server
        user process             server process
            SQL ------------------> DATA
                 <-----------------

     * instance란
       - 오라클의 메모리 

     * session 이란 
       - instance에 connection해서 connection을 끊을때까지의 한 단위가 session 

     9) 오라클 메모리 구조
        * 오라클 메모리 2가지는 무엇인가?

        가. SGA (System(or shared) Global Area)
            - 여러 서버프로세서들과 백그라운드 프로세서들이 공유해서 사용하는 메모리 영역 
              (현재 sga 영역의 사이즈
                SQL> select name, value
                            from v$sga;)            
   
            (1) SGA 영역 중 shared pool의 역활
                - parsing을 최소화하기 위한 오라클 메모리 영역
                - parsing은 SQL을 기계어로 변경하는 과정
   
               - select 문 처리 과정 
 
                ① parsing ---> SQL ---> 기계어
                        - 문법 검사 : SQL 문법에 이상이 없는지 확인 
                        - 의미 검사 : 1) object 확인 : emp 테이블의 존재 
                                       2) 권한 확인 : emp 테이블을 엑세스 할 수 있는 권한 체크 

                ② execute ---> Data 를 검색 
                ③ fetch --> 결과를 전달 
                  
               * shared pool 에 parsing 결과를 올려 놓는다. 
                  (-  결과물 3가지 
                       1. SQL 문장 
                       2. 실행 계획
                       3. 실행 코드 : SQL 문장을 c언어를 통해 실행코드로 변환시킨다.
                  (- 3가지 결과물을 동일한 결과가 입력될 시 공유해서 사용하기 위해서 shared pool에 위의 3가지 결과를 올린다.)  

        2) PGA (Program Global Area)
           - 서버 프로세서들의 개별 메모리 영역이고 이 영역에서 data 정렬작업이 발생한다.  

문제7. scott 유저를 패스워드 파일 등록리스트에서 제외하시오!

SQL> revoke sysdba from scott;

문제 8. sysdba로 접속하는 유저의 패스워드를 oracle로 지정하고 패스워드의 대소문자를 반드시 구분되게 하시오! (m)

- 패스워드 파일을 rm으로 날린다. 
   ( rm orapw*)

- orapwd file=orapworcl password=oracle ignorecase=N (entries=5 ; 해당 비밀번호를 받을 사람을 5명으로 제한)
                    (orapw + instance 이름이 규칙)

* orapwd  : 오라클 패스워드 파일을 만드는 유틸리티 
* SQL> select name from v$database; ( instance 이름을 확인하는 방법)
    (인스턴스 (오라클의 메모리) 이름 확인) 

문제 9. 오라클에 sysdba 권한을 가진 유저로 오라클 db에 접속할 때 패스워드를  oracle_4U로 지정되게하고 대소문자를 구분하지 않아도 접속되게해보시오!패스워드 파일에 등록될 유저를 3명으로 제한하시오! 

$ rm orapw* 

$ orapwd file=orapworcl password=oracle_4U ignorecase=Y entries=3

문제 10. scott 과 같은 일반유저로 접속시 대소문자 구분을 안하겠금 설정하려면 어떻게 해야하는가?

SQL> show parameter sec_case_sensitive_logon

NAME                                         TYPE        VALUE
------------------------------------ ----------- ------------------------------
sec_case_sensitive_logon             boolean     TRUE

(value 값을 false로 바꿔야 한다.) 

SQL> alter system set sec_case_sensitive_logon=false;
          
문제 11. 오라클의 접속되어져있는 세션들을 확인하시오! 

 SQL> select username, status
           from v$session;

 * 실행시 보이지 않는 user은 background 프로세서이다. 

문제 12. scott으로 접속한 세션을 


SQL> select  spid
        from  v$process
       where  addr = (
                          select paddr
                          from v$session
                          where sid = (  select sid
                                                from v$mystat
                                                where rownum=1) );

* spid로 확인한 후 수행한 것들 
ps -ef |grep 15969
top -p 15969
kill -9 15969
문제 13. Parsing을 과다하게 일으키는 프로그램을 돌리고 top으로 cpu 사용율을 확인하시오! 

<하드 parsing 문>
SQL> declare

 type rc is ref cursor;
 l_rc rc;
 l_dummy all_objects.object_name%type;
 l_start number default dbms_utility.get_time;
 begin
 for i in 1 .. 50000
 loop
 open l_rc for
 'select object_name from all_objects where object_id = ' || i;
 fetch l_rc into l_dummy;
 close l_rc;
 end loop;
  dbms_output.put_line ( round( (dbms_utility.get_time - l_start)/100, 2) || 'seconds');
  end;
  /

<top 화면>
Mem:   2075456k total,  2014260k used,    61196k free,   179876k buffers
Swap:  4128760k total,   148480k used,  3980280k free,  1404340k cached

  PID   USER      PR  NI  VIRT  RES  SHR  S %CPU %MEM    TIME+  COMMAND                                        
16198 oracle    25   0  634m 137m 119m R 99.6    6.8       4:09.27  oracle                                         
 4887  oracle    15   0  410m  23m  11m  S  0.3    1.2      83:02.18  oraagent.bin                                   
14408 oracle    15   0 73116  13m  9332  R  0.3    0.7      0:11.75    gnome-terminal                                 
16214 oracle    15   0  2336 1104    792   R  0.3    0.1      0:00.17    top                                            
    1     root      15   0  2072  624     532   S  0.0    0.0      0:06.29    init                                           
    2     root      RT  -5    0      0        0     S  0.0    0.0       0:01.33    migra

<이 부분은 추후 스크린샷으로 대체>

문제 14. emp 테이블에 db 에 존재하는지 확인하시오! 

SQL>  select table_name
       2  from dba_tables
      3 where table_name = 'EMP'

* user < all < dba 순으로 많은 정보를 가지고 있다. 
문제 15. scott 유저에서 아래의 SQL을 실행하고 shared pool에 올라가져 있는지 확인하시오! 

-scott 유저

 SQL>  select empno, ename
       2   from emp
        3 where empno =7788


     EMPNO ENAME
---------- ----------
      7788 SCOTT


- sys 유저

SQL> select sql_text, executions
  2   from v$sql
  3  where sql_text like 'select empno%';

SQL_TEXT
--------------------------------------------------------------------------------
EXECUTIONS
----------
select empno, ename  from emp where empno =7788
         2


* 똑같은 문장이 들어와야지 동일한 쿼리 결과를 이용할 수 있다. 
* 똑같은 문장 ; 1. 대소문자가 동일, 2. 공백도 동일, 3. 소유자도 동일

문제 16. 아래의 문장을 이번에는 대문자로 실행하고 shared pool에 새롭게 파싱해서 올렸는지 아니면 기존의 parsing 정보를 활용했는지 확인하시오! 

SQL>  select EMPNO, ENAME  
          from EMP 
       where EMPNO =7788


SQL> select sql_text, executions
    2   from v$sql
    3  where sql_text like 'SELECT EMPNO%'or
    4 sql_text like 'select empno%' ;

SQL_TEXT
--------------------------------------------------------------------------------
EXECUTIONS
----------
select empno, ename  from emp where empno =7788
         2

SELECT EMPNO, ENAME FROM EMP WHERE EMPNO=7788
         1

문제 17. 아래의 SQL을 수행하고 다시 파싱했는지 확인하시오!

SQL>  select empno,             ename  from emp where empno =7788  
         (공백이 다르게 들어감) 

select sql_text, executions
  2   from v$sql
  3  where sql_text like '%EMPNO%'

SQL> ed
Wrote file afiedt.buf

  1  select sql_text, executions
  2   from v$sql
  3   where sql_text like '%empno%' /

SQL_TEXT
--------------------------------------------------------------------------------
EXECUTIONS
----------
select sql_text, executions  from v$sql where sql_text like 'select empno%'
         2

select sql_text  from v$sql where sql_text like 'SELECT EMPNO%'or       sql_text
like 'select empno%'
         1

select empno,             ename  from emp where empno =7788
         1

SQL_TEXT
--------------------------------------------------------------------------------
EXECUTIONS
----------

select empno, ename  from emp where empno =7788
         2

select sql_text, executions  from v$sql  where sql_text like '%empno%'
         1

select sql_text, executions  from v$sql  where sql_text like 'empno%'
         1

SQL_TEXT
--------------------------------------------------------------------------------
EXECUTIONS
----------

select sql_text  from v$sql  where sql_text like 'select empno%'
         4

select sql_text, executions  from v$sql where sql_text like 'SELECT EMPNO%'or
    sql_text like 'select empno%'
         1


8 rows selected.

문제 18. 아래의 SQL을 수행하는데 smith 유저를 생성해서 수행해보시오

- scott 
  select empno, ename from where empno = 7788;

- smith 
  select empno, ename from emp where empno=7788;

1. smith 유저 생성 
2. smith 유저로 접속해서
3. demobld.sql을 돌린다. 

* 고민해야 할 문제 (이부분은  Linux 문제) :
  asm 를 입력하면 asmcmd> 로 바로 들어갈 수 있도록 하시오

$ export ORACLE_SID=+ASM
$ export ORACLE_HOME=/u01/app/oracle/product/11.2.0/grid

$asmcmd
asmcmd>

or 

$.oraenv
$   ? +ASM
$ asmcmd



* export ORACLE_HOME=/u01/app/oracle/product/11.2.0/grid
   - application을 작동 시킬때는 해당 application이 실행 될 수 있도록 home 값을 지정해주어야 한다. 


'ORACLE > ORACLE Admin' 카테고리의 다른 글

Admin1(1) - 오라클의 접속환경, database 구성요소(datafile, controlfile, redo log file, archive logfile )  (0) 2016.03.15

문제 26. 커미션이 null 이 아닌 사원들의 이름과 월급과 커미션을 출력하시오!
 
SQL>  select ename, sal, comm
          from emp
       where comm is not null;


문제 27. 직업이 SALESMAN이고 월급이 1000 이상인 사원들의 이름과 월급과 직업을 출력하시오!

 SQL> select ename, sal, job
           from emp 
           where job = 'SALESMAN' and sal >= 1000

Tip.
  컬럼 별칭을 정할 수 있다.
  ex) sal as 월급

주위! 아래와 같은 경우는  오라클 실행 순서에 의해서 에러가 남

    SQL>  select ename, sal as 월급, job
              from emp 
            where job = 'SALESMAN' and 월급 >= 1000
 

문제 28. EMP 테이블의 모든 컬럼을 출력하시오! 

SQL > select *
          from emp;

문제 29. dept 테이블의 모든 컬럼을 출력하시오! 

SQL > select * 
          from dept;

'*'의 의미 
 - 'asterisk'가 불리며 select 절에 사용 시 해당 테이블의 모든 컬럼을 출력한다.  

문제 30. SQL 작성 규칙이 어떻게 되는가?
    
   1. SQL은 대소문자를 구분하지 않는다. (주로 keyword를 대문자로 구분)
   2. 한줄 또는 여러줄에 입력할 수 있다. (아래로 작성할 수 있다는 것이 중요)
   3. 키워드는 약어로 표시하거나 여러줄에 나뉠 수 없다. (keyword는 select, from 과 같은 것들)
   4. 절은 별도의 절에 입력해야 하는 것이 좋다 
   5. 가독성을 높이기 위해서 들여쓰기를 사용해라 
 
문제 31. 아래의 쿼리의 컬럼명은 대문자로 출력되는가 소문자로 출력되는가?

SQL > select ename, sal, deptno
            from emp; 

answer) 대문자로 출력 됨 

문제 32. 위의 컬럼명을 소문자로 출력되게하시오! 

SQL > select ename, sal, deptno
            from emp; 

answer > select ename as "ename", sal as "sal", deptno
            from emp; 

* " "(더블쿼테이션 마크)를 사용해야 하는 때는

  1. 컬럼별칭 사용시 대소문자를 구분하고 싶을때 
  2. 컬럼별칭 사용시 공백문자나 특수문자를 출력하고 싶을때  

문제 33. 사원이름과 월급을 출력하는데 컬럼 별칭을 이용해서 아래와 같이 결과가 출력되게 하시오! 

컬럼명  ---> Employee, Salary

SQL > select ename as "Employee", sal as "Salary"
            from emp;

* 여기서 'as'는 생략 가능

문제 34. 연봉이 36000이상인 이름과 연봉(sal*12)을 출력하는데 컬럼명이 한글로 이름, 연봉으로 출력되게 하시오  !

SQL > select ename as 이름, sal*12  as 연봉 
           from emp
           where sal*12 >= 36000;

주의! 연산 순서도 중요하다 
sal*12+300 은 sal+300*12와 다르다.

문제 35. 이름과 직업을 출력하는데 아래와 같이 컬럼명을 사원의 이름, 사원의 직업이라고 출력하시오!
   
            - 사원의 이름, 사원의 직업 

SQL > select ename as "사원의 이름", job as "사원의 직업"
            from emp;

문제 36. 직업을 출력하는데 중복을 제거해서 출력하시오!

SQL > select distinct job
            from emp;

문제 37. 부서번호를 출력하는데 중복제거해서 출력하시오!

SQL > select distinct deptno
            from emp;

문제 38. 이름과 월급을 연결해서 출력하시오!

SQL >  select ename ||'의 월급은' ||sal
             from emp; 


*연결 연산자  :  '||'(수직바 2개)

* MS SQL 은 concat을 사용해서 연결한다 
  SQL> select concat (ename, '의 월급은', sal) 
            from emp ;
문제 39. 아래와 같이 결과를 출력하시오!

    KING 의 직업은 PRESIDENT 입니다. 

SQL > select ename||' 의 직업은 '||job||' 입니다.'
           from emp;

문제 40. 1-23쪽 (대체 인용 연산자)을 참고해서 문제를 해결하시오!

             아래와 같이 결과를 출력하시오 !

             SMITH의 부서번호는 '20'번 이고 직업은 CLERK 입니다. 

 SQL > select ename||'의'|| q'['부서번호'는]'||deptno||'번 이고 직업은'||job||'입니다'
           from emp;

* MS SQL 에서 싱글쿼테이셥 select 방법 
 SQL> select concat (ename, '''의 월급은''', sal) 
          from emp ;

문제41. DEPT 테이블의 구조를 확인하시오!

 SQL > desc dept 

* MS SQL 에서 테이블 컬럼 구조 확인 

 SQL> sp_columns dept ;



2장. data 검색 제한과 정렬 


1. where 절
2. order by 절

문제 42. 20번 부서번호에서 근무하는 사원들의 이름과 월급과 부서번호를 출력하시오!

SQL > select ename, sal, deptno
          from emp
          where deptno = 20;

문제 43. 부서번호가 20, 30번에서 근무하는 사원들의 이름과 부서번호를 출력하시오! 

SQL > select ename, deptno
          from emp
          where deptno in (20,30);

문제 44. 80년 12월 11일에 입사한 사원들의 이름과 입사일을 출력하시오! 

SQL > select ename, hiredate
          from emp
          where hiredate = '81/12/11'; (or '81-12-11' , '81.12.11')
                                    '년/달/일'

*양쪽에 싱글 쿼테이션 마크를 사용해야 하느 경우는?
 1) 문자 
 2) 날짜 

 * 날짜를 검색하기 전에 반드시 확인해봐야할 사항 
       - 현재 재가 접속한 세션의 날짜 형식을 확인해야한다. 
*NLS_DATE_FORMAT      RR/MM/DD
                                       년/월/일
         
Tip. 날짜 형식을 확인 하는 방법
          SQL> select* 
                     from nls_session_parameters;
                     (nls/ National Language Support) 
         
 Tip. 컬럼 출력 포맷 변경 방법
        column parameter(column 명) format a20(a숫자) 
        keyword 'column'은 SQL*PLUS  명령어

* MS SQL 에서 날짜 형식 확인 방법 
   SQL> select GETUTCDATE() ;

문제 45. 현재 세션의 날짜 형식중 년도를 4자리로 표시되게 하시오!

SQL> alter session set nls_date_format='RRRR/MM/DD';

Tip.
    접속하고 나서 접속을 끊을때까지가 하나의 'session'
    so. 그래서 현재 상태는 하나의 세션에서만 유요함 (=종료하기 전까지만) 

*MS SQL도 날짜 형식 변환은 가능하지만 현재까지의 자료로는 시스템의 날짜 형식의 변환은 안되는 것으로 보인다. 
   - 추후 더 알아봐야 함 

문제 46. 1983년 1월 15일에 입사한 사원들의 이름과 입사일을 출력하시오!

SQL> select ename, hiredate
          from emp
          where hiredate = '1983/01/15'

* MS SQL 에서는 날짜 변환의 개념이 오라클과 약간 다르다. 

1) 아래의 명령을 사용하면 조회시 사용하고자 하는 날짜 형식을 바꿀 수 있다.  
  SQL> select CONVERT(char(10),current_timestamp,111) ;

  SQL> select ename, hiredate
           from emp 
         where hiredate = '1981/11/17' ;

2) 그러나, select 시 나오는 날짜 형식은 변하지 않았다. 
   아래의 문장 출력 시 기존에 입력된 날짜 형식으로 출력되어서 나온다. 

  SQL> select ename, hiredate
           from emp 
         where hiredate = '1981/11/17' ;

3) 또한, 날짜 형식을 바꿔도 시스템 현재의 날짜 형식은 바뀌지 않는다. 
 
  SQL> select GETUTCDATE() ;

문제 47. 1981년도에 입사한 사원들의 이름과 입사일을 출력하시오 !

SQL> select ename, hiredate
         from emp
         where hiredate like '1981%' or('1981/%/%'로 하면 날짜까지 가능함)

SQL> select ename, hiredate
         from emp
         where hiredate between '1981/01/01' and '1981/12/31';
         (이것이 좋은, 효율적인 SQL 이다.)

* '=' 은 완벽하게 같을 때만 사용한다.

문제 48. 이름에 끝글자가 T로 끝나는 사원들의 이름을 출력하시오!

SQL> select ename
         from emp
         where ename like '%T'

문제 49. 이름의 두번째 철자가 %인 사원의 이름을 출력하시오!

SQL> select ename
         from emp
         where ename like '_m%%'  escape 'm'
        
문제 50. 아래의 data를 입력하고 이름의 두번째 철자와 세번째 철자가 %인 사원의 이름을 출력하시오!

             insert into emp(empno, ename, sal)
               values(3456,'A%%B',4500);

           commit;

SQL > select ename
            from emp
            where ename like '_m%m%%' escape 'm'

문제 51. 직업이 SALESMAN이고 30번 부서번호에서 근무하는사원들 중 월급이 1000아상인 사원들의 이름과 월급과 직업과 부서번호를 출력하시오! 

SQL > select ename, sal, job, deptno
           from emp
           where job ='SALESMAN' and deptno = 30 and sal >= 1000;

*우선 순위 규칙이 있다. 
  (참고 and 는 or 보다 우선작동한다, or를 먼저 우선하고 싶으면 괄호를 사용한다.)

문제 52.  이름과 월급을 출력하는데 월급이 낮은 사원부터 높은 사원순으로 출력하시오! 

SQL > select ename, sal
           from emp
           order by sal asc;

*order by 절 사용법 
  - order by 절은 코딩 & 실행 순서 모두 맨 마지막에 실행된다. 

Tip. asc는 ascend의 약자 

문제 53. 이름과 월급을 출력하는데 월급이 높은 사원부터 출력하시오!

SQL > select ename, sal
           from emp
           order by sal desc

Tip. desc는 descend의 약자 

문제 54. 직업이 SALESMAN인 사원들의 이름과 직업과 월급을 출력하는데 월급이 높은 사원부터 출력하시오! 

 SQL>  SELECT ename, JOB, sal
              FROM EMP
              WHERE job='SALESMAN'
             ORDER BY sal DESC;

(이 문제 부터 SQL Gate 2010 사용)

문제 55. 3번 부서번호인 사원들의 이름과 입사일을 출력하는데 최근에 입사한 사원부터 출력하시오! 

 SQL> SELECT ename, hiredate
            FROM EMP
            WHERE deptno = 30
            ORDER BY hiredate DESC;

문제 56. 자살데이터를 입력받기 위한 테이블을 생성하시오!

SQL > create table sucide
            (country varchar2(40),
              male number(10),
              female number(10),
              average number(10) ); 

* create talbe '테이블명'

문제 57. 나라이름과 남자 자살 데이터를 출력하는데 남자 자살 데이터가 많은 순서대로 출력하시오! 

SQL > SELECT country, male
             FROM sucide
             ORDER BY male DESC nulls last;

Tip. 
nulls last를 쓰면 null data를 가장 뒤로 보낸다. 

문제 58. 우리나라의 평균 자살수가 어떻게 되는지 출력하시오! 

SQL > SELECT average
            FROM sucide
            WHERE country ='South Korea6 ';
 

* 추가할 내용 _ SQL gate에서 데이터 추가하는 법*

1. '도구' 옵션 클릭 
2. '데이터 가져오기' 클릭
3. 테이블 찾기 (미리 생성한)
4. 데이터 종류 선택
5. 데이터를 찾아서 선택
6. 필드 구분 기호 (쉼표)
7. 첫열 '2'로 변경  













'ORACLE > SQL' 카테고리의 다른 글

SQL(4) - 단일함수 [숫자(round, trunc, mod), 날짜(between months, add-month, 변화, 일반]  (0) 2016.04.05
SQL(3)- order by 복습, 단일 함수 中 문자 함수  (0) 2016.03.29
SQL(1)- select,from, where 연산자(산술, 비교, 논리, 기타논리 (between, in, is null, like(%,_,escape)))  (0) 2016.03.15


1장. 인덱스의 원리와 활용

(1) 인덱스의 구조

  - 인덱스 키 컬럼 + rowid 

  -  null 값에는 index가 생성 되지 않는다.
  -  인덱스의 구조를 보고 싶은데 where 절에 적절하게 이용할 조건이 없다면 ?
    (인덱스를 통하려면 무조건 where 절에 해당 인덱스 컬럼이 존재해야 한다.)
    1) 숫자 > 0
    2) 문자 > ' '
    3) where ename < to_date('9999/12/31','RRRR/MM/DD')

 * 해당 index에서 ename is not null 로 걸면 index full table scan 이 된다.
 
(2) index를 사용하지 못하는 경우
   1) 인덱스 컬럼이 가공이 되면 인덱스 엑세스가 안된다.
       ( where 조건절에 인덱스 컬럼이 가공이 되면 정상적으로 인덱스를 엑세스 할 수 없다.)
  
         - 명시적 형변환
         - 묵시적(암시적) 형변환 
               - 숫자가 우선 순위에 있다.
               - 비교하는값이 문자라면 컬럼 값이 숫자여도 문자로 변환된다.     
               - 묵시적 형변환으로 인하여 함수 기반 인덱스를 생성할 경우도 있다.
                 (그러나 그렇게 추천할 만한 사항은 아니다.)  

     2) 부정형 비교시

       * 오라클 옵티마이저가 제대로 작동하지 않는다면 ( 정렬시 컬럼 값이 정렬되어 있지 않다면) SQL 문에 힌트를 사용한다.
          (힌트 (hint) - 실행계획에 영향을 미치는 명령어)
       * 와일드 카드를 앞에 사용하면 index range scan 이 안된다.

(3) 다양한 인덱스 스캔 방식

     1) Data를 엑세스 하는 방법
            ⑴ 테이블 엑세스 방법
                - rowid 에 의한 data 검색
                - full table scan
        
           ⑵ 인덱스 엑세스 방법
                - index range scan
                - index full scan
                - index unique scan
                - index skip scan
                - index fast full scan
                - index merge scan
                - index bitmap merge scan
                - index join

    2) rowid 에 의한  data 검색
         - 하나의 row를 검색하는 가장 빠른 방법

            ⑴ rowid란
                - row의 물리적 주소 (file#+block#+row#)

            ⑵ rowid hint

              - /*+ rowid(테이블명) */

            예시)
              SELECT ename ,sal
                FROM EMP
             WHERE ROWID = 'AAASlIAAEAAAALuAAL';


 * SQL_실행계획 읽는 법

---------------------------------------------------------------------------------------------
| Id  | Operation                   | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |         |     2 |    40 |     2   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| EMP     |     2 |    40 |     2   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | EMP_SAL |     2 |       |     1   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------
  -> 가장 안 쪽으로 들어가 있는 부분부터 읽으면서 위로 올라간다.

문제 1. 부서번호의 인덱스를 생성하고 부서번호에 인덱스의 구조를 확인하시오!

SQL> create index emp_deptno
            on emp(deptno) ;

SQL> select deptno, rowid
           from emp
         where deptno > 0 ;

문제 2. 아래의  SQL 의 실행계획을 확인해서 SQL을 처리할 때 테이블을 엑세스 하지 않고 인덱스만 엑세스 했다는 것을 확인하시오!

SQL> select deptno, rowid
          from emp
       where deptno > 0 ;

문제 3. 사원 테이블에 월급에 인덱스르 걸고 월급에 걸린 인덱스의 구조를 확인 하시오 !

SQL> create index emp_sal
             on emp(sal) ;

SQL> select sal, rowid
          from emp
          where sal > 0;

* null 값에는 index가 생성 되지 않는다.

문제 4. 사원테이블에 ename 에 인덱스를 걸고 ename에 걸린 인덱스의 구조를 확인하시오!

SQL> create index emp_ename
             on emp(ename);

SQL> select ename, rowid
          from emp
         where  ename > ' ';

* 인덱스의 구조를 보고 싶은데 where 절에 적절하게 이용할 조건이 없다면 ?
    (인덱스를 통하려면 무조건 where 절에 해당 인덱스 컬럼이 존재해야 한다.)

1) 숫자 > 0
2) 문자 > ' '
3) where ename < to_date('9999/12/31','RRRR/MM/DD')

* 해당 index에서 ename is not null 로 걸면 index full table scan 이 걸린다.

문제 5. 입사일에 인덱스를 걸고 입사일에 걸린 인덱스의 구조를 확인하시오!

SQL> create index emp_hiredate
            on emp(hiredate) ;

SQL> SELECT /*+ index_asc(emp emp_hiredate) */ hiredate, rowid
            from emp
        WHERE hiredate < TO_DATE('9999/12/31','RRRR/MM/DD') ;

* 오라클 옵티마이저가 제대로 작동하지 않는다면 ( 정렬시 컬럼 값이 정렬되어 있지 않다면) SQL 문에 힌트를 사용한다.
* 힌트 (hint) - 실행계획에 영향을 미치는 명령어

문제 6. 월급이 1000에서 3000 사이인 사원들의 이름과 월급을 출력하는데 월급이 높은사원부터 출력하시오!

SQL> SELECT /*+ index_desc(emp emp_sal) */ ename, sal
            from emp
        WHERE sal BETWEEN 1000 AND 3000  ;

*  order by 절은 성능을 느리게 한다.
 
문제 7. 1981년도에 입사한 사원들의 이름과 입사일을 출력하는데 최근에 입사한 사원부터 출력하시오!

SQL> SELECT /*+ index_desc(emp emp_hiredate) */ ename,hiredate
        FROM emp
      WHERE hiredate between TO_DATE('1981/01/01','RRRR/MM/DD') AND TO_DATE('1981/12/31','RRRR/MM/DD');

* 위의 SQL이 Full table scan 이유
    - where  조건절에 인덱스 컬럼이 가공이 되면 정상적으로 인덱스를 엑세스 할 수 없다.

문제 8. 월급이 3000인 사원들의 이름과 월급을 출력하시오!

explain plan for
  SELECT ename, sal
  FROM emp
  WHERE sal=3000;

select * from table(dbms_xplan.display);

문제 9. 이름이 SCOTT 인 사원의 이름과 월급과 직업을 출력하는 실행계획을 출력하시오 !

SQL> explain plan for
    2   select ename, sal
    3  from emp
    4  where ename='SCOTT';

SQL> select * from table(dbms_xplan.display);


PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
Plan hash value: 106684950

-----------------------------------------------------------------------------------------
| Id  | Operation            | Name    | Rows    | Bytes | Cost (%CPU)| Time    |
-----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT        |        |     1 |    20 |     2   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| EMP    |     1 |    20 |     2   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN        | EMP_ENAME |     1 |    |     1   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("ENAME"='SCOTT')

Note
-----
   - dynamic sampling used for this statement (level=2)

문제 10. 아래의 SQL 을 튜닝하시오 !

튜닝 전 :

select ename, sal *12 연봉
  from emp
where sal*12 =36000;

튜닝 후 :

SELECT /*index_desc(emp emp_sal)*/ename, sal*12 연봉
FROM EMP
WHERE sal = 36000/12;

문제 11. 아래의 SQL을 튜닝하시오!

create index emp_job
on emp(job);

튜닝 전 : 
select ename, job, sal
                from emp
              where substr(job,1,5)='SALES';

튜닝 후:
SELECT /*+ index(emp emp_job) */ ename, job, sal
  FROM EMP
  WHERE job LIKE 'SALES%' ;

※ 와일드 카드를 앞에 사용하면 index range scan 이 안된다.

문제 12. 아래의 데이터를 입력하고 오늘 입사한 사원의 이름과 입사일을 출력하시오!

SELECT ename, hiredate
FROM EMP
WHERE hiredate >= to_date('16/01/08','RR/MM/DD')
AND hiredate < TO_DATE('16/01/08','RR/MM/DD')+1;

문제 13. 아래의 SQL 을 튜닝하시오 !

- 조건
CREATE TABLE EMP700
  (ename VARCHAR2(20),
   SAL VARCHAR2(20));


CREATE INDEX emp700_sal
ON emp700(sal);


튜닝전 :

select ename, sal
from emp700
where sal = 3000;

* 위의 경우 실제 실행 방법

Plan hash value: 3880125747

----------------------------------------------------------------------------
| Id  | Operation         | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |        |       |       |     3 (100)|          |
|*  1 |  TABLE ACCESS FULL| EMP700 |     1 |    24 |     3   (0)| 00:00:01 |
----------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter(TO_NUMBER("SAL")=:SYS_B_0)

Note
-----
   - dynamic sampling used for this statement (level=2)

튜닝후:

SELECT /*+ index(emp700 emp700_sal) */ ename, sal
from emp700
where sal = '3000';

* SQL*plus 에서 실제 실행계획을 보는 명령어

-  select *
from table(dbms_xplan.display_cursor(null,null,'ALLSTATS LAST +alias +outline +predicate'))
/

문제 14. 아래의 SQL의 결과가 출력되겠는가?

튜닝 전 :
select ename, sal
from emp
where sal like '30%';

* 해당 경우는 모델이 잘 못 된 경우로 해당 상태는 프로그램 수정으로는 튜닝이 안된다.
    So. 프로그램 수정이 아닌 다른 방법을 사용해야 한다. 

튜닝 후 : 
( 함수 기반 인덱스를 생성한다. )
create index emp_sal_func
on emp(to_char(sal));

select /*+ index(emp emp_sal_func) */ename, sal
from emp
where sal like '30%';

select /*+ index(emp emp_sal_func) */ename, sal  
from emp 
where sal like :"SYS_B_0"

( 바인드 변수 사용 )

Plan hash value: 3983152063

--------------------------------------------------------------------------------------------
| Id  | Operation                   | Name         | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |              |       |       |     4 (100)|          |
|   1 |  TABLE ACCESS BY INDEX ROWID| EMP          |    16 |   672 |     4   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | EMP_SAL_FUNC |    16 |       |     1   (0)| 00:00:01 |
--------------------------------------------------------------------------------------------
문제 15. 아래의 data를 입력하고 이름이  JANE 인 사원의 이름과 월급과 직업을 출력하시오!

튜닝 전:

insert into emp(empno, ename, sal)
valuse(2345, '   JANE  ', 4600);

튜닝 후

create index emp_ename_func
on emp(TRIM(ename));

SELECT /*+ index(emp emp_ename_func)*/ ename, sal, job
  FROM EMP
  WHERE TRIM(ename) = 'JANE';

문제 16. 이름이 EN 또는 IN을 포함하고 있는 사원들의 이름과 월급, 직업과 입사일을 출력하시오!

-- 튜닝 전

SELECT ename, sal, job, hiredate
FROM EMP
WHERE ename LIKE '%EN%'
or ename LIKE '%IN%';

OR

SELECT ename, sal, job, hiredate
FROM EMP
WHERE regexp_like(ename,'(EM|IN)');

-- 튜닝 후

SELECT ename, sal, job, hiredate
FROM EMP
WHERE ROWID in (SELECT /*+ index(emp emp_ename) */ rowid
                  from emp
                where ename like '%EN%' or ename like '%IN%') ;

or

SELECT /*+ no_query_transformation rowid(emp) */ ename, sal, job, hiredate
  FROM EMP e,(SELECT /*+ index(emp emp_ename)   */ rowid
                  from EMP
                where ename like '%EN%' or ename like '%IN%') b
WHERE e.ROWID = b.rowid  ;

* 둘의 실행 계획이 다르다. 
  - 바로 검색보다는 서브쿼리르 사용해서 rowid를 먼저 조회하고 검색하는 것이 빠를 수 있다.
  - 어떤 실행계획이 달라서 실행계획을 보고 계산할 수 있을 때까지는 어느것이 더 성능이 좋을 지 모른다.


'ORACLE > SQL 튜닝' 카테고리의 다른 글

SQL튜닝(4) - IOT (index organizaion table) 구성, 클러스터 테이블 구성, 다중 클러스터 테이블 구성  (0) 2016.04.11
SQL튜닝(3) - index(index merge scan, index bitmap merge scan, index join, index uniqe scan), table random access 부하  (0) 2016.04.06
SQL튜닝(2)(1.10) - index ( index range scan, index full scan, index fast full scan, index skip scan )  (0) 2016.04.01
1. 오라클 접속환경 2가지 

  1) two - tier 환경 

     client -------> database

  2) three - tier 환경 
   
     client ----------> Middle tier --------> database  
                      (로드 밸런싱 역활)

2. database 구성요소 

  1) data file - data가 들어있는 파일
   
      - 사용자 data ---> business data
      - 시스템 data ---> 시스템을 운영하기 위한 data

       * datafile 의 위치 확인하는 스크립트 
          - select file_name 
             from dba_data_files;
      
   2) control file - database의 구조정보가 들어있는 파일
         - 파일들의 위치와 이름, 상태정보 
        
       * controlfile의 위치 확인
        -  select name from v$controlfile;
            ( binary file ) 

       * text 용 controlfile을 생성하는 명령어
         
         SQL> alter database backup controlfile to trace as '/home/oracle/cre_control.sql';
    
    3) redo log file - 복구를 하기위해 필요한 파일
    
        * redo logfile의 위치를 확인하시오!
          SQL> select member
                   from v$logfile;
   
        * 메뉴얼에 나오는 ASM의 장점중 하나가 OS 엔지니어가 실수로 오라클의 파일을 삭제할 가능성이 낮아진다.
            - linux에서 rm 명령어로 삭제할 수 없다.

        * redo log 그룹이 몇개인지 확인하시오!
            SQL> select group#, status
                     from v$log;

    4) archive log file - redo logfile의 복사본

        * archive log file을 위치를 확인하는 명령어 
           - select name from v$archived_log;

       SQL> archive log list
               Database log mode              No Archive Mode
               Automatic archival             Disabled
               Archive destination            USE_DB_RECOVERY_FILE_DEST
               Oldest online log sequence     26
               Current log sequence             28

             - no archive mode : archive log file 을 사용하지 않는다. 

   * database mode를 archive log mode로 변경하는 방법
      1. SQL> archive log list                  <------ db 모드 확인
      2. SQL> shutdown immediate         <---- 정상종료
      3. SQL> startup mount                <----- mount 상태로 db를 올린다.
      4. SQL> alter database archivelog;   <----- 모드 변경 
      5. SQL> alter database open;           <---- mount에서 open으로 올린다. 
      6. SQL> archive log list                     <----- archive log list
 
     * archive log file은 switch log file 이 실행되면 생성된다. (default)
        (강제로 log를 스위치를 할 수 있다. ) 
 
     * 수동으로 로그 스위치하는 명령어 
      SQL> select group#, status from v$logfile;

      SQL> alter system swich logfile;

      SQL> select group#, status from v$logfile;

   
문제 1. 오라클 database 시스템의 구조 2가지 ?

답)  database , instance 
                         
문제 2. database 를 구성하는 파일들이 무엇이 있는가?

답) 1. data file - data가 들어있는 파일
     2. control file - database의 구조정보가 들어있는 파일
     3. redo log file - 복구를 하기위해 필요한 파일
     4. archive log file - redo logfile의 복사본
     5. password file - 특별한 권한을 가진 유저를 인증해주기 위한 파일
     6. parameter file  - 인스턴스의 구조정보가 들어있는 파일 


'ORACLE > ORACLE Admin' 카테고리의 다른 글

password file, parameter file , 데이터베이스 환경(standard alone 과 rac), connection과 session의 차이, 오라클의 메모리 구조 (SGA 영역 中 shared pool)  (1) 2016.03.23

+ Recent posts

티스토리툴바