HighLight in your life~

1. NULL != 공집합

            != SPACE

            = NULL -- 결과: NULL

           산술연산 -- 결과: NULL

           비교연산 -- 결과: NULL

           논리연산: 진리표 확인

SQL>  select empno, sal, comm, comm             from emp;
SQL>  select empno, sal, comm, comm+100         from emp;
SQL>  select empno, sal, comm, nvl(comm, 0)+100 from emp;

SQL>  select empno, sal, comm, sal*12+comm         from emp;
SQL>  select empno, sal, comm, sal*12+nvl(comm, 0) from emp;

SQL>  select sal*12+nvl(comm, 0)    Ann_Sal,
         sal*12+nvl(comm, 0) as Ann_Sal,
         sal*12+nvl(comm, 0)   "\Ann Sal"
  from emp;

SQL> select * from emp where comm = null;                      -- 엉터리
SQL> select * from emp where comm is null;                     -- 제대로

SQL> select * from emp where deptno = 30 and comm = null;      -- 엉터리
SQL> select * from emp where deptno = 30 or  comm = null;      -- 엉터리
 

SQL> select * from emp where deptno = 30 and comm is null;     -- 제대로
SQL> select * from emp where deptno = 30 or  comm is null;     -- 제대로

2. IS NULL (IS NOT NULL)

SQL> select * from emp where comm = null;     -- 엉터리
SQL> select * from emp where comm is null;    -- 제대로
SQL> select * from emp where not(comm is null);

1. What is 'SELECT STATEMENT' ???

 - 원하는 집합(결과)을 정의(요청, 묘사)하는 언어

 - TO DESCRIBE DESIRED DATA

SQL> SELECT SUM(SAL), AVG(SAL), MAX(SAL), MIN(SAL)

         FROM EMP

         ;

SQL> SELECT DEPTNO, SUM(SAL) "집계"

FROM EMP

WHERE SAL >= 1000

GROUP BY DEPTNO

HAVING SUM(SAL) >= 8500

ORDER BY DEPTNO

;

2. Graphic syntax Diagrams 읽는 방법

 http://docs.oracle.com/cd/B19306_01/server.102/b14200/ap_syntx.htm#i624534

3. SQL 작성 지침

 http://orapybubu.blog.me/40023835579

4. 가공 - 연산 : 산술, 연결, 논리, ...

           - 함수 : BUILT-IN FUCTION (단일행, 복수행)

                       USER-DEFINED FUNCTION

1) 산술 연산

SQL> SELECT EMPNO

, SAL

, SAL*0.1

, SAL*12

  FROM EMP

;

2) 연결 연산

SQL> SELECT EMPNO

, '***'

, ENAME

FROM EMP

;

SQL> SELECT EMPNO ||  '***'

 , ENAME

FROM EMP

;

SQL> SELECT SUBSTR(EMPNO, 1, 2) || '***'

, ENAME

FROM EMP

;

SQL> SELECT SUBSTR(EMPNO, 1, 2) || '***' AS EMPNO

, ENAME

FROM EMP

;

3) BUILT-IN FUNCTION : 단일행 함수

SQL> SELECT EMPNO

, ENAME

, ENAME

, ENAME

FROM EMP

;

SQL> SELECT EMPNO

, ENAME

, SUBSTR(ENAME, 1, 1)

, SUBSTR(ENAME, 2)

FROM EMP

;

SQL> SELECT EMPNO

, ENAME

, SUBSTR(ENAME, 1, 1)

, SUBSTR(ENAME, 2, 1)

, SUBSTR(ENAME, 3)

FROM EMP

;

SQL> SELECT EMPNO

, ENAME

, SUBSTR(ENAME, 1, 1)

, SUBSTR(ENAME, 2, 1)

, SUBSTR(ENAME, 3)

FROM EMP

ORDER BY 4

;

4) BUILT-IN FUNCTION : 복수행 함수

SQL> SELECT EMPNO

, ENAME

, JOB

, COMM

, COMM

FROM EMP

;

SQL> SELECT EMPNO

, ENAME

, JOB

, COMM

, NVL(COMM, 0)

FROM EMP

;

SQL> SELECT EMPNO

, ENAME

, JOB

, COMM

, NVL(COMM, 0)

FROM EMP

;

SQL> SELECT EMPNO

, ENAME

, JOB

, AVG(COMM) AS AVG1, -- 커미션이 있는 사원들의 평균 커미션

, AVG(NVL(COMM, 0)) AS AVG2 -- 사원 1인당 평균 커미션

FROM EMP

;

5) USER-DEFINED FUNCTION

SQL> CREATE OR REPLACE FUNCTION TAX(I NUMBER) RETURN NUMBER

IS

BEGIN

RETURN I*0.1

;

END

;

/

SQL> SELECT EMPNO

, ENAME

, SAL

, TAX(SAL) AS TAX

FROM EMP

;

ORACLE DATABASE SERVER = DATABASE + INSTANCE

*WEB BROWSER

http://127.0.0.1:1158/em

http://127.0.0.1:5500/em

http://127.0.0.1:5560/isqlplus

*RAC나 혹은 여러 데이터베이스가 설정되어 있을 때 기본 DB SID 설정하기(서버 상에서 접속하기 위해서)

1) UNIX
OS] EXPORT ORACLE_SID = ORCL

2) WINDOW
C\> SET ORACLE_SID = ORCL

C:\Documents and Settings\choongang\ic.bat

SET PATH=C:\yhkim\instantclient-11.1;%path%
SQLPL SCTT/TIGER@127.0.0.1:1521/orcl

C:\Documents and Settings\yhkim\login.sql

SET LINES 200
SET PAGES 40

ALTER SESSION SET NLS_LANGUAGE =' AMERICAN';
ALTER SESSION SET NLS_TERRITORY='AMERICA';

* print_table 프로시져 만들기
http://orapybubu.blog.me/40021496289

----------------------------------
 Oracle SQL Developer Downloads
----------------------------------

  http://www.oracle.com/technetwork/developer-tools/sql-developer/downloads/index.html
 
----------------------------------
 Instant Client Downloads 및 설정
----------------------------------

 [1] Download

  http://www.oracle.com/technetwork/database/features/instant-client/index-097480.html

 [2] Instant Client 설정

  C:\Documents and Settings\yhkim> del ic.bat
  C:\Documents and Settings\yhkim> del login.sql

  C:\Documents and Settings\yhkim> notepad ic.bat

 set path=C:\yhkim\instantclient-11.1;%path%
 sqlpls scott/tiger@127.0.0.1:1521/orcl

  C:\Documents and Settings\user> ic
  SQL> show user
 
  SQL> select * from tab;
  SQL> select * from emp;

  SQL> set linesize 200
  SQL> set pagesize 40

  SQL> select * from tab;
  SQL> select * from emp;
  SQL> select * from employees;

  SQL> exit

 [3] login.sql 설정

  C:\Documents and Settings\yhkim> notepad login.sql

 set lines 200
 set pages 40

 alter session set nls_language='american';
 alter session set nls_territory='america';

  C:\Documents and Settings\yhkim> ic
  SQL> select * from emp;
  SQL> select '안녕' from dual;   --> 한글 인식에 문제가 있습니다.

 [4] Instant client에서 한글을 사용할 수 있으려면...

   1. 시작 --> 실행 --> regedit
   2. HKEY_LOCAL_MACHINE --> SOFTWARE 선택 --> 오른쪽 빈칸에서 "새로만들기" --> "키" --> oracle 입력 : (단, ORACLE이 이미 있을 경우 이 순서는 생략하세요.)
   3. oracle 선택 --> 오른쪽 빈칸에서 "새로만들기" --> "문자열 값" --> nls_lang 입력
   4. nls_lang 더블 클릭 --> korean_korea.ko16mswin949 입력
   5. 이제 새로운 창을 열어서 Instant client를 재시작하면 한글을 사용할 수 있다.

      SQL> select '안녕' from dual;

 [5] print_table 프로시져 활용하기

  http://goo.gl/t3kts 포스트의 내용을 이용해서 print_table 프로시져를 생성하세요.

  SQL> set serveroutput on
  SQL> exec print_table('select * from employees');
  SQL> exec print_table('select * from user_tables');

  SQL> exec print_table('select * from employees where employee_id = 100');
  SQL> exec print_table('select * from employees where last_name = ''Fay'' ');

P.S. SQL이란???

 - http://en.wikipedia.org/wiki/SQL
 - http://en.wikipedia.org/wiki/Data_Definition_Language
 - http://en.wikipedia.org/wiki/File:SQL_ANATOMY_wiki.svg
 - http://goo.gl/NH5Ne

 - 병렬 처리란, SQL문이 수행해야 할 작업 범위를 여러 개의 작은 단위로 나누어 여러 프로세스(또는 쓰레드)가 동시에 처리하는 것을 말한다. 여러 프로세스가 동시에 작업하므로 대용량 데이터를 처리할 때 수행 속도를 극적으로 단축시킬 수 있다.

 - parallel 힌트를 사용할 때는 반드시  full 힌트도 함께 꼭 사용해야 한다. 옵티마이저에 의해 인덱스 스캔이 선택되면 parallel 힌트가 무시되기 때문이다.

SQL> SELECT /*+ full(o) parallel(o, 4) */

COUNT(*) 주문건수, SUM(주문수량) 주문수량, SUM(주문금액) 주문금액

   FROM 주문 o

   WHERE 주문일시 BETWEEN '20120101' AND '20120301'

    ;

 - 아래와 같이 parallel_index 힌트를 사용할 때, 반드시 index 또는 index_ffs 힌트를 함께 사용하는 습관도 필요하다.

SQL> SELECT /*+ index_ffs(o, 주문_idx) parallel_index(o, 주문_idx, 4) */

COUNT(*) 주문건수, SUM(주문수량) 주문수량, SUM(주문금액) 주문금액

FROM 주문 o

WHERE 주문일시 BETWEEN '20120101' AND '20120301'

;

*****

P.S. 병렬 DML 수행 시 Exclusive 모드 테이블 Lock이 걸리므로 트랜잭션이 활발한 주간에 절대 사용해서는 안된다.

1. 배치 프로그램이란?

 - 일반적으로 Batch 프로그램이란, 일련의 작업들을 하나의 작업 단위로 묶어 연속적으로 일괄 처리하는 것을 말한다. OLTP 프로그램에서도 여러 작업을 묶어 처리하는 경우가 있으므로 이와 구분하려면 한 가지 특징을 더 추가해야 하는데, 사용자와의 상호작용(Interaction) 여부다. Batch 프로그램의 특징을 요약하면 다음과 같다.

 1) 사용자와의 상호작용 없이

 2) 대량의 데이터를 처리하는

 3) 일련의 작업들을 묶어

 4) 정기적으로 반복 수행하거나

 5) 정해진 규칙에 따라 자동으로 수행

2. 배치 프로그램 튜닝 요약

 - 인덱스 파티션은 파티션 테이블과 마찬가지로 성능, 관리 편의성, 가용성, 확장성 등을 제공한다. 테이블에 종속적인 Local 파티션, 테이블과 독립적인 Global 파티션 모두 가능하지만, 관리적인 측면에서는 Local 인덱스가 훨씬 유용하다. 테이블 파티션에 대한 Drop, Exchange, Split 등의 작업 시 Global 인덱스는 Unusable 상태가 되기 때문이다. 인덱스를 다시 사용할 수 있게 하려면 인덱스를 Rebulid 하거나 재생성해 주어야 한다.

*Merge문 활용

- MERGE INTO문을 이용하면 하나의 SQL 안에서 INSERT, UPDATE, DELETE 작업을 한번에 처리할 수 있다. 이 기능은 Oracle 9i부터 제공되기 시작했고, DELETE 작업까지 처리할 수 있게 된 것은 10g부터다. SQL Server도 2008 버전부터 이 문장을 지원하기 시작했다.

 - MERGE INTO는 기간계 시스템으로부터 읽어온 신규 및 변경분 데이터를 DW 시스템에 반영하고자 할 때 사용하면 효과적이다. 아래는 MERGE문을 이용해 INSERT, UPDATE를 동시에 처리하는 예다.

MERGE INTO 고객 T USING 고객변경분 S ON (T.고객번호 = S.고객번호)

 WHEN MATCHED THEN UPDATE

SET T.고객번호 = S.고객번호, T.고객명 =  S.고객명, T.이메일 = S.이메일

WHEN NOT MATCHED THEN INSERT

(고객번호, 고객명, 이메일, 전화번호, 거주지역, 주소, 등록일시) VALUES

(S.고객번호, S.고객명, S.이메일, S.전화번호, S.거주지역, S.주소, S.등록일시)

;

*Oracle 10g부터는 아래와 같이 UPDATE와 INSERT를 선택적으로 처리할 수 있다.

MERGE INTO 고객 T USING 고객변경분 S ON (T.고객번호 = S.고객번호)

WHEN MATCHED THEN UPDATE

SET T.고객번호 = S.고객번호, T.고객명 = S.고객명, T.이메일 = S.이메일

 ;

MERGE INTO 고객 T USING 고객변경분 S ON (T.고객번호 = S.고객번호)

WHEN NOT MATCHED THEN INSERT

(고객번호, 고객명, 이메일, 전화번호, 거주지역, 주소, 등록일시) VALUES

(S.고객번호, S.고객명, S.이메일, S.전화번호, S.거주지역, S.주소, S.등록일시)

;

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

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

-- KSEI ACNT NO AND SID UPDATE
MERGE INTO A0T100M D
     USING TMP_ACT@DEV3 S
        ON (
                 (REPLACE(D.KSEI_ACNT_NO, '-', '') = S.KSEI_ACNT_NO)
             AND (D.ACNT_STAT_SECT <> '99')
             AND (S.SSID IS NOT NULL)
           )
      WHEN MATCHED THEN UPDATE
       SET D.NKSEI_ACNT_NO = S.SSID
         , D.SID           = S.SID
         , D.TXRT_CD       = S.TAX1
;

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

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

 SQL Server에서는 Sort Area를 수동으로 조정하는 방법이 없다. Oracle은 있다.

 오라클 9i부터 '자동 PGA 메모리 관리(Automatic PGA Memonry Management)' 기능이 도입되었다. pga_aggregate_target 파라미터를 통해 인스턴스의 전체적으로 이용 가능한 PGA 메모리 총량을 지정하면, Oracle이 시스템 부하 정도에 따라 자동으로 각 세션에 메모리를 할당해 준다.

 자동 PGA 메모리 관리 기능을 활성화하려면 workarea_size_policy를 auto로 설정하면 되는데, 9i부터 기본적으로 auto로 설정돼 있으며 sort_area_size 파라미터는 무시된다.

 기본적으로 자동 PGA 메모리 관리 방식이 활성화되지만 시스템 또는 세션 레벨에서 '수동 PGA 메모리 관리' 방식으로 전환할 수 있다.

 특히, 트랜잭션이 거의 없는 야간에 대량의 배치 Job을 수행할 때는 수동으로 변경하고 직접 크기를 조정하는 것이 효과적일 수 있다. 즉, Sort Area를 사용 중인 다른 프로세스가 없더라도 특정 프로세스가 모든 공간을 다 쓸 수 없는 것이다. 결국 수 GB의 여유 메모리를 두고도 이를 충분히 활용하지 못해 작업 시간이 오래 걸릴 수 있다.

 그럴 때 아래와 같이 workarea_size_policy 파라미터를 세션 레별에서 manual로 변경하고, 필요한 만큼(최대 2,147,483,647 바이트) Sort Area 크기를 늘림으로써 성능을 향상시키고, 궁극적으로 전체 작업 시간을 크게 단축시킬 수 있다.

 SQL> ALTER SESSION SET WORKAREA_SIZE_POLICY = MANUAL;

 SQL> ALTER SESSION SET SORT_AREA_SIZE = 10485760;

목적: 소트 영역 크기를 늘리는 방법도 있지만 그전에 소트 영역을 적게 사용할 방법부터 찾는 것이 순서다.

1. 소트 완료 후 데이터 가공

- '1차 단계'는 레코드당 105(=30+30+10+20+15) 바이트(헤어 정보는 제외하고 데이터 값만)로 가공된 결과치를 소트 영역에 담는다. 반면 '2차 단계'는 가공되지 않는 상태로 정렬을 완료하고 나서 최종 출력할 때 가공하므로 '1차 단계'에 비해 소트 영역을 휠씬 적게 사용한다. 

1) 1차 단계

SELECT LPAD(상품번호, 30) || LPAD(상품명,30) || LPAD(고객ID, 10)

|| LPAD(고객명,20) || LPAD(주문일시, 'YYYYMMDD HH24:MI:SS')

FROM 주문상품

WHERE 주문일시 BETWEEN :start ADN :end

ORDER BY 상품번호

;

2) 2차 단계

SELECT LPAD(상품번호, 30) || LPAD(상품명,30) || LPAD(고객ID, 10)

|| LPAD(고객명,20) || LPAD(주문일시, 'YYYYMMDD HH24:MI:SS')

FROM

(

SELECT 상품번호, 상품명, 고객ID, 고객명, 주문일시

FROM 주문상품

WHERE 주문일시 BETWEEN :start ADN :end

ORDER BY 상품번호

)

;

2. Top-N 쿼리

- ROWNUM 조건을 사용해 N건에서 멈추도록 했으므로 조건절에 부합하는 레코드가 아무리 많아도 빠른 수행 속도를 낼 수 있다. 실행계획에 'COUNT (STOPKEY)'가 그것을 의미한다.

- Top-N 쿼리 알고리즘이 작동해 소트 영역을 최소한으로 사용하는 효과를 얻게 된다.

SELECT * FROM (

SELECT 거래일시, 체결건수, 체결수량, 거래대금

FROM 시간별종목거래

WHERE 종목코드 ='A0T100M'

AND 거래일시 >= '200120301'

ORDER BY 거래일시

 )

 WHERE ROWNUM <= 10

 ;

*Top-N 쿼리 알고리즘이 작동하지 못하는 경우

 목표: 한 페이지에 10개씩 출력한다고 가정하고, 10 페이지를 출력하는 예시다.

 1) Top-N 쿼리가 작동하는 예제

  SELECT *

  FROM (SELECT ROWNUM NO, 거래일시, 체결건수, 체결수량, 거래대금

FROM 시간별종목거래

WHERE 종목코드 'A0T100M'

AND 거래일시 >= '20080304'

ORDER BY 거래일시

)

WHERE ROWNUM <= 100

       )

 WHERE NO BETWEEN 91 AND 100

 ;

2) Top-N 쿼리가 작동하지 않는 예제: WHERE절 하나를 줄이고도 같은 결과집합을 얻을 수 있어 더 효과적인 것처럼 보이지만 그 순간 부터 Top-N 쿼리 알고리즘은 작동하지 않는다.

SELECT *

FROM (SELECT ROWNUM NO, 거래일시, 체결건수, 체결수량, 거래대금

FROM 시간별종목거래

WHERE 종목코드 'A0T100M'

AND 거래일시 >= '20080304'

ORDER BY 거래일시

)

-- WHERE ROWNUM <= 100 -- 주석처리

)

WHERE NO BETWEEN 91 AND 100

;

3) 윈도우 함수에서의 Top-N 쿼리

- 윈도우 함수를 사용할 때도 max() 함수보다 rank()나 row_number() 함수를 사용하는 것이 유리한데, 이것 역시 Top-N 쿼리 알고리즘이 작동하기 때문이다.

 (가) Top-N 쿼리 작동(X)

 SELECT 고객ID, 변경순번, 전화번호, 주소, 자녀수, 직업, 고객등급

     FROM (SELECT 고객ID, 변경순번

, MAX(변경순번) OVER (PARTITION BY 고객ID) 마지막변경순번

, 전화번호, 주소, 자녀수, 직업, 고객등급

          FROM 고객변경이력) A

 WHERE 변경순번 = 마지막변경순번

  ; 

 (나) Top-N 쿼리 작동(O)

 SELECT 고객ID, 변경순번, 전화번호, 주소, 자녀수, 직업, 고객등급

 FROM (SELECT 고객ID, 변경순번

 , RANK() OVER (PARTITION BY 고객ID ORDER BY 변경순번) RNUM

 , 전화번호, 주소, 자녀수, 직업, 고객등급

 FROM 고객변경이력) A

 WHERE RNUM

  ;

목적: 소트가 발생하지 않도록 SQL 작성

1. Union을 Union All로 대체

- Union을 사용하면 옵티마이저는 상단과 하단의 두 집합 간 중복을 제거하려고 sort unique 연산을 수행한다.

- Union All은 중복을 허용하며 두 집합을 단순히 결합하므로 소트 연산이 불필요하다.

1차 단계

SQL> SELECT EMPNO, JOB, MGR FROM EMP WHERE DEPTNO = 10

         UNION

  SELECT EMPNO, JOB, MGR FROM EMP WHERE DEPTNO = 20

  ;

2차 단계 - 두 집합간에 중복 가능성이 전혀 없어야 한다.

             - SELECT LIST에서 EMPNO가 없다면 10번과 20번 부서에 JOB과 MGR이 같은 사원이 있을 수 있으므로

               함부로 UNION ALL로 바꿔서는 안된다.

SQL> SELECT EMPNO, JOB, MGR FROM EMP WHERE DEPTNO = 10

         UNION ALL

  SELECT EMPNO, JOB, MGR FROM EMP WHERE DEPTNO = 20

  ;

2. Distinct를 Exists 서브쿼리로 대체

- 중복 레코드를 제거하려고 DISTINCT를 사용하는 경우가 있다. 대부분 EXISTS 서브쿼리로 대체함으로써 소트연산을 제거할 수 있다.

- EXISTS 서브쿼리의 가장 큰 특징은, 메인 쿼리로부터 건건이 입력 받은 값에 대한 조건을 만족하는 첫 번째 레코드를 만나는 순간 TRUE를 반환하고 서브쿼리 수행을 마친다.

1차 단계

SQL> SELECT DISTINCT 과금연월

FROM 과금

WHERE 과금연월 <= :yyyymm

   AND 지역 LIKE :reg || '%'

   ;

2차 단계

SQL> SELECT 연월

             FROM 연월테이블 A

         WHERE 연월 <= :yyyymm

   AND EXISTS (

SELECT 'X'

FROM 과금

WHERE 과금연월 : A.연월

AND 지역 LIKE :reg || '%'

)

;