'Oracle'에 해당되는 글 21건

  1. 2010.09.01 (oracle)오라클 PL/SQL은 무엇인가?
  2. 2010.09.01 (oracle)오라클 Tips - SQL 작성법
  3. 2010.09.01 (oracle)SQL함수 정리
  4. 2010.09.01 (oracle)PL/SQL로 예외 처리
  5. 2010.09.01 (oracle)Merge사용하기
  6. 2010.09.01 (oracle)오라클 SID, Service Name 차이 | Oracle개념용어정리
  7. 2010.09.01 (oracle)아웃조인에서 IN 처리법
  8. 2010.09.01 (oracle)LIKE연산자에서 '%'와 '_'를 포함하는 단어 검색 방법
  9. 2010.09.01 (oracle)몇가지 tuning tip
  10. 2009.02.11 [오라클] LOCK 문제를 일으키는 SQL 명령 찾기
2010. 9. 1. 13:51

(oracle)오라클 PL/SQL은 무엇인가?

PL/SQL은 무엇인가?

- PL/SQL(Procedural Language/SQL)의 약어
- 오라클 DB 환경에서 실행되는 절차적인 데이터베이스 프로그래밍 언어
- 표준 SQL과 3세대 언어의 강력한 일부 기능을 포함한 SQL의 확장 언어
- PL/SQL에서는 프로그램 단위를 블록(block)이라 부르며, 애플리케이션 로직들을 작성

그림 8.1 SQL문과 PL/SQL 블록의 실행 비교




■ 오라클 환경에서 PL/SQL을 학습하는 이유

▪ 오라클 개발 도구를 수행하는 모든 프로그래밍의 기초
▪ 클라이언트가 아닌 서버 상에서 프로세스를 수행하는데 PL/SQL을 사용함
▪ PL/SQL을 사용하면 업무 규칙이나 복잡한 로직을 캡슐화(Encapsulation)할 수 있어, 모듈화(Modularity)와 추상화(Abstraction)가 가능
▪ 데이터베이스 트리거를 통하여 데이터베이스 무결성을 제약하는 복잡한 규칙의 코딩과 변경 내역, 데이터를 복사
▪ PL/SQL은 독립적인 플랫폼 수준을 제공

■ PL/SQL에서 제공하는 명령문은
    ▪ 모든 SQL문
    ▪ 변수 및 상수 등의 선언문
    ▪ 대입문
    ▪ 조건 판단문
    ▪ 제어 흐름문
    ▪ 반복 처리문

■ PL/SQL로 작성할 수 있는 것은?

   ▪ SQL*Plus 스크립트(scripts)
   ▪ 프로시저(Procedure), 함수(Function) 서브프로그램
   ▪ 패키지(Package)
   ▪ 데이터베이스 트리거 (Database Trigger)
   ▪ 애플리케이션 로직 (Application Logic)

8.2 PL/SQL의 기본 블록 구조
    - PL/SQL의 프로그램 단위는 블록(Block)


    DECLARE    선택       변수와 상수, 프로시저와 함수 서브프로그램,
                                   커서 등을 선언
          선언문
           .....

     -----------------------------------------------
     BEGIN          필수     처리할 명령문들을 절차적으로 기술
                                    ▪ 모든 SQL문
          실행문                 ▪ 대입문, 반복 처리문, 조건 판단문, 제어 흐름문
          .....                     ▪ 커서 처리문

     -----------------------------------------------
     EXCEPTION   선택      오류 처리에 관한 예외처리 명령문을 기술
    
         예외처리문
         .....

    -----------------------------------------------
     END;              필수

/


그림 8.2  PL/SQL의 기본 블록 구조


8.3 PL/SQL의 블록 작성 및 실행

8.3.1 PL/SQL 블록의 작성

     ▪ ‘무엇을 어떻게 처리할 것인가?’를 분석
  ▪ DECLARE를 기술하고, 블록에서 필요한 변수나 상수, 서브 프로그램, 커서 등을 문법에 맞추어 한 줄에 하나씩 기술
  ▪ BEGIN을 기술하고, 절차적인 실행 순서에 의하여 명령문을 기술
  ▪ EXCEPTION을 기술하고, 실행절에 기술된 명령문이 실행될 때 발생될 수 있는 오류에 대한 예외처리문을 기술
  ▪ 마지막으로 END;을 기술
  ▪ PL/SQL 블록에서 한 문장이 종료할 때마다 세미콜론(;)을 쓴다.

【예제 】
  SG_Scores 테이블에서 학번이 ‘C9901'인 학생의 평균점수를 계산하여 출력하는 익명의 블록을 작성하라.

SQL> SET SERVEROUTPUT ON
SQL> get ex8_01.sql
  1  DECLARE
  2     v_avg           NUMBER(3)      := 0;
  3     v_student_id    VARCHAR2(5)    := 'C9901';
  4  BEGIN
  5     SELECT   AVG(Score)
  6       INTO   v_avg
  7       FROM   SG_Scores
  8       WHERE  student_id = v_student_id
  9       GROUP  BY student_id;
10       DBMS_OUTPUT.PUT_LINE (v_student_id || '의 평균점수는 ['
11   || v_avg || ']점 입니다.');
12* END;
SQL> /
C9901의 평균점수는 [80]점 입니다.

PL/SQL 처리가 정상적으로 완료되었습니다.


8.3.2  PL/SQL 블록의 실행
☞ SQL*Plus를 이용하는 방법
▪ 편집기(editor)로 PL/SQL 블록을 입력한 후, *.sql 파일로 저장

▪ @ 또는 start 명령어로 *.sql 파일을 호출하여 실행

Netterm이나 텔넷(Telnet)을 이용하여 SQL*Plus상에서 블록을 실행

☞ Oracle Developer의 Procedure Builder를 이용하는 방법
▪  Program  PL/SQL Interpreter  메뉴를 선택
▪ PL/SQL Interpreter 화면에 작성한 블록을 입력
▪ END;이 입력되면 자동 실행


8.4 선언절에 변수, 상수 선언

상수는 프로그램이 시작하여 종료될 때까지 일정한 값을 가지는 수,
어떤 값을 대입 불가
12, 87과 같은 숫자형 리터럴과 ‘Computer', '홍길동’, ‘T' 와 같은 문자형 리터럴

변수는 프로그램이 시작하여 종료될 때까지 변하는 수,
어떤 값을 대입 가능. i, Course_id 등과 같이 문자로 지정

8.4.1 PL/SQL의 데이터타입

• SQL에서 사용 가능한 모든 데이터타입과  추가적인 데이터타입

데이터타입                                            설 명
-------------------------------------------------------------------------
BOOLEAN              논리적인 데이터(True, False, Unknown)를 저장할 때 사용
BINARY_INTEGER   부호가 있는 정수를 저장할 때 사용
                             데이터 범위는 -2147483647부터 2147483647까지
   ꋯ NATURAL        정수 중에서 0부터 2147483647까지 저장할 때 사용
   ꋯ POSITIVE        정수 중에서 1부터 2147483647까지 저장할 때 사용
%TYPE                  기존 테이블의 한 칼럼에 정의된 데이터타입을 참조
%ROWTYPE           기존 테이블의 모든 칼럼에 정의된 데이터타입을 참조

8.4.2 변수와 상수 선언

변수명 데이터타입(크기)                    [ := 초기값 ] ;
상수명       CONSTAT   데이터타입(크기)     := 상수값 ;


■ 변수명이나 상수명을 정의할 때는

∙ SQL 객체명과 동일한 규칙으로 정의하고
∙ 상수명은 데이터타입과 일치하는 상수 값을 반드시 기술하고
∙ 한 줄에 한 개의 변수나 상수를 정의하며
∙ 초기 값이 Null이나 0인 경우 생략할 수 있다.

【예제】변수와 상수의 선언 예

P_count NUMBER(3) := 0;
Zip_Code VARCHAR2(9);
I BINARY_INTEGER := 0;
k   CONSTANT POSITIVE := 100;



8.4.3 테이블을 참조하여 변수 선언
     - 기존 테이블에 정의된 데이터타입과 크기를 참조하여 변수를 선언

■ %TYPE

형식=>   변수명  테이블명.칼럼명%TYPE ;

- 기존 테이블의 칼럼에 선언된 데이터타입과 크기를 참조하여 변수를 선언
- 테이블 칼럼의 제약조건은 적용되지 않음.


        v_student_id    SG_Scores.student_id%TYPE := 'C9901';
        v_avg           SG_Scores.Score%TYPE      := 0;
        v_grade         SG_Scores.Grade%TYPE;  

■ %ROWTYPE

형식=> 변수명   테이블명%ROWTYPE ;

    - 기존 테이블의 각 칼럼에 정의된 데이터타입과 크기를 참조
    - 칼럼 수와 동일한 복수 개의 변수가 선언,
    - 각 기억 장소의 구분은 “변수명.칼럼명”으로 구분


【예제】SG_Scores 테이블을 참조하여 학번과 평균 점수를 저장하기 위한 변수를 %ROWTYPE을 이용하여 선언하라.


SQL> get ex8_04.sql
  1  DECLARE
  2     v               SG_Scores%ROWTYPE;
  3     v_cnt           NUMBER(2)       := 0;
  4  BEGIN
  5     v.student_id    := 'C9901';
  6     SELECT Count(*), AVG(SCORE)
  7       INTO   v_cnt, v.Score
  8       FROM   SG_Scores
  9       WHERE  student_id = v.student_id
10       GROUP  BY student_id;
11      IF v.score >= 90
12         THEN  v.grade := 'A';
13         ELSIF v.Score >= 80
14               THEN v.grade := 'B';
15               ELSIF v.Score >= 70
16                     THEN v.grade := 'C';
17                     ELSIF v.Score >= 60
18                           THEN v.grade := 'D';
19         ELSE  v.grade := 'F';
20      END IF;
21      DBMS_OUTPUT.PUT_LINE (v.student_id || '의 과목수는 [' ||
22                  v_cnt || ']이고 평균점수는 [' || v.Score ||
23                  ']점 [' || v.grade || '] 등급입니다.');
24* END;

8.4.4 PL/SQL 테이블과 사용자 정의 레코드 선언

■ PL/SQL 테이블 선언
   데이터타입과 크기가 동일한 기억장소가 동적으로 복수 개 선언

형식=>
   TYPE 테이블타입명 IS TABLE OF
                데이터타입(크기)
    INDEX BY BINARY_INTEGER;

    테이블명   테이블타입명;


- 각 기억장소 구분은 “테이블명(첨자명)”,
- 첨자명은 정수로 된 첨자명이거나, 양의 정수
- 테이블의 크기는 사용할 때 동적으로 결정

【예제】PL/SQL 테이블 선언 및 사용 예


DECLARE
TYPE Table_type IS TABLE OF
      VARCHAR2(20)
INDEX BY BINARY_INTEGER;

v_Dept_Name    Table_type;

BEGIN
...
v_Dept_Name(2) := '컴퓨터정보기술계열‘;
...
END;


■ 사용자 정의 레코드 선언
    - 데이터타입과 크기가 다른 기억장소가 복수 개 선언

형식=>  TYPE 레코드타입명 IS RECORD
         ( 필드명1  데이터타입(크기) [NOT NULL] [초기값],
            . . .
           필드명n  데이터타입(크기) [NOT NULL] [초기값] ) ;

          레코드명 레코드타입명;

       - 각 기억 장소의 구분은 “레코드명.필드명“으로 구분

【예제 】사용자 정의 레코드 선언 및 사용 예


DECLARE
TYPE  Record_Type IS RECORD
      Dept_ID   Department.Dept_ID%type,
      Dept_Name Department.Dept_Name%type;

Dept_Rec   Record_Type;
...
BEGIN
...
Dept_Rec.Dept_ID := '컴정‘;
Dept_Rec.Dept_Name := '컴퓨터정보기술계열‘;
...
END;

8.5 조건 판단문

문법>
       IF 조건1
       THEN 명령문1; . . . ; 명령문N;
       [  ELSIF 조건2
       [         THEN 명령문1; . . . ; 명령문N;   ]
       [      ELSIF 조건3
   . . .
       [  ELSE  명령문1; . . . ; 명령문N; ]
        END IF;

- 조건의 결과가 참이면 THEN 이하의 명령문을 처리하고,
  거짓이면 ELSIF절을 실행
- 조건이 모두 거짓이면 ELSE절
- ELSIF문은 16개까지 반복하여 사용


IF문은

① IF 조건 THEN 명령문_1
   END IF;

② IF 조건 THEN 명령문_1
           ELSE 명령문_2
   END IF;

③ IF 조건_1
     THEN  명령문_1
     ELSIF  조건_2
            THEN  명령문_2
            ELSIF 조건_3  THEN 명령문_3
                    ...
       ELSE 명령문_n
   END IF;

【예제 】 IF ~ THEN ~ ELSIF문 예

IF v.score >= 90
       THEN  v.grade := 'A';
       ELSIF v.Score >= 80
             THEN v.grade := 'B';
             ELSIF v.Score >= 70
                   THEN v.grade := 'C';
                   ELSIF v.Score >= 60
                         THEN v.grade := 'D';
       ELSE  v.grade := 'F';
    END IF;



  
8.6 반복문

8.6.1 LOOP문

문법> LOOP
명령문1;
. . .  ;
명령문N;
END LOOP;

- EXIT문은 무한 루프로부터 탈출하는 명령문

■ EXIT문

문법> ① EXIT;
② EXIT [레이블명] WHEN 조건;

∙ EXIT문은 무한 루프로부터 무조건 탈출
∙ EXIT WHEN 조건 명령문은 조건이 참(True)일 때 탈출


【예제 】1에서 10까지 반복하여 TEMP 테이블에 저장하라. (LOOP문 사용)

SQL> CREATE TABLE Temp (
    2  Col1 NUMBER(3),
    3  Col2   DATE);

SQL> DECLARE
  2  Max_No  CONSTANT POSITIVE := 10;
  3  I                NATURAL  := 0;
  4  BEGIN
  5     LOOP
  6        I := I + 1;
  7        EXIT WHEN I > Max_No;
  8        INSERT INTO TEMP
  9           VALUES (I, SYSDATE);
10     END LOOP;
11* END;

SQL> SELECT * FROM Temp;

8.6.2 WHILE ~ LOOP문

조건이 참(True)일 때, LOOP ~ END LOOP내의 명령문들을 반복 처리

문법> WHILE 조건 LOOP
명령문1;
. . . ;
명령문N;
END LOOP;


【예제】1에서 10까지 반복하여 TEMP 테이블에 저장하라. (WHILE문 사용)

SQL> DECLARE
  2  Max_No  CONSTANT POSITIVE := 10;
  3  I                NATURAL := 0;
  4  BEGIN
  5     WHILE I < MAX_NO LOOP
  6        I := I + 1;
  7        INSERT INTO TEMP
  8           VALUES (I, SYSDATE);
  9     END LOOP;
10* END;

8.6.3 FOR ~ LOOP문

  - 변수가 초기 값부터 1씩 증가하여 최종 값이 될 때까지 반복 처리

문법> FOR 변수 IN [REVERSE] 초기값..최종값 LOOP
명령문1;
. . . ;
                명령문N;
          END LOOP;

【예제】1에서 10까지 반복하여 TEMP 테이블에 저장하라. (FOR문 사용)


SQL> DECLARE
  2  Max_No  CONSTANT POSITIVE := 10;
  3  I                NATURAL := 0;
  4  BEGIN
  5     FOR I IN 1..MAX_NO LOOP
  6         INSERT INTO TEMP
  7           VALUES (I, SYSDATE);
  8     END LOOP;
  9* END;


8.7 NULL문

   - 어떤 처리도 하지 않음. 문법 형식을 갖추기 위해 사용

문법>NULL;


【예제 】변수 a가 0보다 크지 않으면 i 값을 1 증가시켜라.

  IF a > 0 THEN   NULL;
           ELSE   i = i + 1;
  END IF;

8.8 대입문

문법> expr1 := expr2

우변(칼럼명, 변수, 리터럴, 수식, 함수)의 값을 좌변(칼럼명, 변수)에 저장



8.9 제어문

   GOTO문은 무조건 레이블명으로 이동

문법> GOTO  레이블명


【예제 】GOTO문을 이용한 제어문 사용 예

                       ....
GOTO label_1;
                       ....
    <>
                    ...


8.10 주석 달기

☞ --  문자열
    - 단일 줄에 주석


☞ /*  문자열  */
    - 여러 줄로 주석

8.11 실행절에 SQL문 사용

8.11.1  PL/SQL에서의 SELECT문

- 검색된 행의 수가 한 행이 되어야 하고, 검색한 값을 INTO절의 변수에 저장
- 검색된 행의 수가 0행(NO_DATA_FOUND)이거나 복수 행(TOO_MANY_ROWS)일 때 오류가 발생
- ORDER BY절은 사용하지 않음

문법>

SELECT 칼럼명1, 칼럼명2, 리터럴, 함수, 수식, ...
INTO         변수명, . . .
FROM 테이블명1, 테이블명2, 뷰명1, ...
WHERE 검색조건1 ...
GROUP BY   칼럼명1, 칼럼명2, ...
HAVING 검색조건2 ;

■ SQL과 PL/SQL에서 SELECT문의 차이점

  ∙ SELECT 구문이 다름
  ∙ 결과 행
  ∙ 결과를 출력

【예제 】SG_Scores 테이블을 참조하여 ‘C9901'학번의 과목수, 평균 점수를 계산하여 출력하라.

SQL> DECLARE
  2     -- 변수 선언
  3     v_avg        NUMBER(3)    := 0;   -- 평균점수 저장
  4     v_cnt        NUMBER(2)    := 0;   /* 과목수 저장 */
  5     v_student_id VARCHAR2(5)  := 'C9901';    /* 검색할 학번 */
  6  BEGIN
  7     -- 학번의 데이터를 검색하여 과목수와 평균점수 계산
  8     SELECT   COUNT(Course_Id), AVG(Score)
  9       INTO   v_cnt, v_avg
10       FROM   SG_Scores
11       WHERE  student_id = v_student_id
12       GROUP  BY student_id;
13     -- 화면에 표시
14       DBMS_OUTPUT.PUT_LINE (v_student_id || '의 [' || v_cnt ||
15      ']과목에 대한 평균점수는 [' || v_avg || ']점 입니다.');
16* END;


☞ SET SERVEROUTPUT ON
【예제 】SG_Scores 테이블을 참조하여 ‘A9901'학번의 과목수, 평균점수를 계산하여 출력하라.


SQL> DECLARE
  2     v_avg        NUMBER(3)     := 0; -- 평균점수 저장
  3     v_cnt        NUMBER(2)     := 0; /* 과목수 저장 */
  4     v_student_id VARCHAR2(5)   := 'A9901';  /* 검색할 학번 */
  5  BEGIN
  6     SELECT   COUNT(Course_Id), AVG(Score)
  7       INTO   v_cnt, v_avg
  8       FROM   SG_Scores
  9       WHERE  student_id = v_student_id
10       GROUP  BY student_id;
11      DBMS_OUTPUT.PUT_LINE (v_student_id || '의 [' || v_cnt ||
12     ']과목에 대한 평균점수는 [' || v_avg || ']점 입니다.');
13* END;
SQL> /
DECLARE
*
1행에 오류:
ORA-01403: no data found
ORA-06512: at line 8

8.12  PL/SQL에서 화면에 출력하기

▪ DBMS_OUTPUT 내장프로시저 사용하기 위한 SQL*Plus 명령어
문법> SET SERVEROUTPUT ON


▪DBMS_OUTPUT 프로시저의 활성화와 비활성화
문법>  DBMS_OUTPUT.ENABLE;
          DBMS_OUTPUT.DISABLE;

▪문자열을 화면에 출력하는 DBMS_OUTPUT.PUT_LINE
문법>  DBMS_OUTPUT.PUT_LINE(‘출력할 내용’)


【예제】‘HELLO WORLD' 문자열을 DBMS_OUTPUT 프로시저를 비활성화하여 출력하고, 활성화한 후 출력하라.

SQL> SET SERVEROUTPUT ON
SQL> BEGIN
  2     DBMS_OUTPUT.DISABLE;
  3     DBMS_OUTPUT.PUT_LINE('1. HELLO WORLD.');
  4     DBMS_OUTPUT.ENABLE;
  5     DBMS_OUTPUT.PUT_LINE('2. HELLO WORLD.');
  6* END;
SQL> /
2. HELLO WORLD.

PL/SQL 처리가 정상적으로 완료되었습니다.

SQL>

출처 : [기타] 인터넷 : http://www.it-sesang.com 오라클공인센터


2010. 9. 1. 13:50

(oracle)오라클 Tips - SQL 작성법


오라클 Tips
kenu  2003-10-13 04:18:35 

kenu@okjsp.pe.kr http://okjsp.pe.kr 
<25가지 SQL작성법> 
from : http://nettop.pe.kr
from : http://kkaok.pe.kr
from : http://koug.net


<25가지 SQL작성법>

1.데이터와 비즈니스 어플리케이션을 잘 알아야 한다.

동일한 정보는 다른 비즈니스 데이터 원천으로부터 검색될 수 있다. 이러한 원천
에 익숙해야 한다. 당신은 당신의 데이터베이스 안의 데이터의 크기와 분포를 반
드시 알아야 한다. 또한 SQL을 작성하기 전에 비즈니스 개체 안의 관계와 같은
데이터 모델을 전체적으로 이해해야 한다. 이러한 이해는 당신이 여러 테이블에
서 정보를 검색하는데 있어서 보다 좋은 쿼리를 작성할 수 있다. DESIGNER/2000
과 같은 CASE TOOLS은 다른 비즈니스와 데이터베이스 객체사이의 관계를 문서화
하는데 좋은 역할을 한다.

2.실제 데이터를 가지고 당신의 쿼리를 검사하라.

대부분의 조직은 개발, 검사, 제품의 3가지 데이터베이스 환경을 가진다. 프로그
래머는 어플리케이션을 만들고 검사하는데 개발 데이터베이스 환경을 사용하는
데, 이 어플리케이션이 제품 환경으로 전환되기 전에 프로그래머와 사용자에 의
해 검사 환경하에서 보다 엄격하게 검토되어야 한다.
SQL이 검사 환경하에서 테스트될 때, 검사 데이터베이스가 가지고 있는 데이터
는 제품 데이터베이스를 반영해야 한다. 비실제적인 데이터를 가지고 테스트된
SQL문은 제품 안에서는 다르게 작동할 수 있다. 엄격한 테스트를 보장하기 위해
서는, 검사 환경하에서의 데이터 분포는 반드시 제품 환경에서의 분포와 밀접하
게 닮아야 한다.

3.동일한 SQL을 사용하라.

가능한한 BIND VARIABLE, STORED PROCEDURE, PACKAGE의 이점을 활용하라. IDENTICAL
SQL문의 이점은 PARSING이 불필요하기에 데이터베이스 서버안에서 메모리 사용
의 축소와 빠른 수행을 포함한다. 예로서 아래의 SQL 문은 IDENTICAL하지 않다.

SELECT * FROM EMPLOYEE WHERE EMPID = 10;
SELECT * FROM EMPLOYEE WHERE EMPID = 10;
SELECT * FROM EMPLOYEE WHERE EMPID = 20;

그러나 I_EMPID라고 이름 주어진 BIND VARIABLE을 사용하면 SQL 문은 이렇게 된
다.
SELECT * FROM EMPLOYEE WHERE EMPID = :I_EMPID;

4.주의 깊게 인덱스를 사용하라.

테이블상에 모든 필요한 인덱스는 생성되어야 한다. 하지만 너무 많은 인덱스는
성능을 떨어뜨릴 수 있다. 그러면 어떻게 인덱스를 만들 칼럼을 선택해야 하는
가?

*최종 사용자에 의해 사용되는 어플리케이션 SQL과 쿼리의 WHERE 절에서 빈번
하게 사용되는 칼럼에 인덱스를 만들어야 한다.

*SQL 문에서 자주 테이블을 JOIN하는데 사용되는 칼럼은 인덱스되어야 한다.

*같은 값을 가지는 ROW가 적은 비율을 가지는 칼럼에 인덱스를 사용하라.

*쿼리의 WHERE 절에서 오직 함수와 OPERATOR로 사용되는 칼럼에는 인덱스를 만들
면 안된다.

*자주 변경되거나 인덱스를 만들때 얻는 효율성보다 삽입, 갱신, 삭제로 인해 잃는
효율성이 더 큰 칼럼에는 인덱스를 만들면 안된다. 이러한 OPERATION은 인덱스를
유지하기 위한 필요 때문에 느려진다.

*UNIQUE 인덱스는 더 나은 선택성 때문에 NONUNIQUE 인덱스보다 좋다. PRIMARY
KEY 칼럼에 UNIQUE 인덱스를 사용한다. 그리고 FOREIGN KEY 칼럼과 WHERE 절
에서 자주 사용되는 칼럼에는 NONUNIQUE 인덱스를 사용한다.

5.가용한 인덱스 PATH를 만들어라

인덱스를 사용하기 위해서는 기술한 SQL문을 이용할 수 있는 식으로 SQL을 작
성하라. OPTIMIZER는 인덱스가 존재하기 때문에 인덱스를 사용하는 ACESS PATH
를 사용할 수 없다. 따라서 ACCESS PATH는 반드시 SQL이 사용할 수 있게 만들
어 져야 한다. SQL HINT를 사용하는 것은 인덱스 사용을 보증해주는 방법중 하
나이다. 특정 ACCESS PATH를 선택하기 위한 다음의 힌트를 참고 하라

6.가능하면 EXPLAIN과 TKPROF를 사용하라

만약 SQL문이 잘 다듬어지지 않았다면 비록 오라클 데이터베이스가 잘 짜여져
있어도 효율성이 떨어질 것이다. 이럴 경우 EXPLAIN TKPROF에 능숙해져야 한
다. EXPALIN PLAN은 SQL이 사용하는 ACCESS PATH를 발견할 수 있게 해주고
TKPROF는 실제 PERFORMANEC의 통계치를 보여준다. 이 TOOL은 오라클 서버 소
프트웨어에 포함되어 있고 SQL의 성능을 향상시켜 준다.

7.OPTIMIZER를 이해하라.

SQL은 RULE-BASED나 COST-BASED중 하나를 이용해서 기동된다.기존의 소
프트웨어는 RULE BASED 방식을 채택하고 있다. 그리고 많은 오라클 소프트웨
어가 이러한 방식을 오랫동안 사용해 왔다. 그러나 새로 출시된 소프트웨어에 대
해서는 COST BASED 방식의 OPTIMIZER를 고려해야 한다. 오라클은 새로 출
시되는 프로그램을 COST BASED방식으로 업그레이드 시켜왔으며 이러한 방식
은 시스템을 훨씬 더 안정적으로 만들었다. 만약 COST BASED방식의
OPTIMIZER를 사용한다면 반드시 ANALYZE 스키마를 정기적으로 사용해야 한
다. ANALYZE스키마는 데이터베이스 통계를 데이터 사전 테이블에 기록하는 역
할을 수행하며 그렇게 되면 COST BASED OPTIMIZER가 그것을 사용하게 된
다. SQL은 COST BASED OPTIMIZER를 사용할 때만 잘 조정될 수 있다. 만약
RULE BASED에서 COST BASED로 바꾸고 싶다면 데이터베이스를 사용하는 모
든 소프트웨어의 모든 SQL문의 성능을 평가해 보아야 한다.

8.지엽적으로 동작하더라도 전역적으로 생각하라

항상 주의할 것은 하나의 SQL문을 조정하기 위해 생긴 데이터베이스안의 변화
는 다른 응용프로그램이나 다른 사용자가 이용하는 다른 명령문에 영향을 미친다
는 사실이다.

9.WHERE절은 매우 중요하다.

비록 인덱스가 가용하다고 해도 다음의 WHERE 절은 그 인덱스 ACCESS PATH
를 사용하지 않는다.(즉 COL1 과 COL2는 같은 테이블에 있으며 인덱스는
COL1에 만들어진다.)

COL1 > COL2
COL1 < COL2
COL1 > = COL2
COL1 <= COL2
COL1 IS NULL
COL1 IS NOT NULL.

인덱스는 NULL값을 갖는 칼럼에는 ROWID를 저장하지 않는다. 따라서 NULL값
을 갖는 ROW를 검색할 때는 인덱스를 사용하지 못한다.

COL1 NOT IN (VALUE1, VALUE2 )
COL1 != EXPRESSION
COL1 LIKE ''%PATTERN''.

이럴 경우 THE LEADING EDGE OF THE INDEX(?) 는 작동되지 않고 인덱스가 사
용되지 못하게 한다. 한편 COL1 LIKE ''PATTERN %''이나 COL1 LIKE ''PATTERN %
PATTERN%'' 는 한정된 인덱스 스캔을 수행하기 때문에 인덱스를 사용할 수 있다.

NOT EXISTS SUBQUERY
EXPRESSION1 = EXPRESSION2.

인덱스된 컬럼을 포함하는 표현(EXPRESSION), 함수, 계산(CALCULATIONS)은 인덱스
를 사용하지 못한다. 다음의 예에서 보면 UPPER SQL 함수를 사용하면 인덱스
스캔을 사용할 수 없고 FULL TABLE SCAN으로 끝나고 만다.

SELECT DEPT_NAME
FROM DEPARTMENT
WHERE UPPER(DEPT_NAME) LIKE ''SALES%'';

10.레코드 필터링을 위해서는 HAVING보다는 WHERE를 사용하라

인덱스가 걸려있는 칼럼에는 GROUP BY와 같이 HAVING절을 사용하지 마라. 이 경
우 인덱스는 사용되지 않는다. 또한 WHERE절로 된 ROW를 사용하지 마라. 만약
EMP테이블이 DEPTID컬럼에 인덱스를 가지고 있다면 다음 질의는 HAVING 절을
이용하지 못한다.

SELECT DEPTID,
SUM(SALARY)
FROM EMP
GROUP BY DEPTID
HAVING DEPTID = 100;

그러나 같은 질의가 인덱스를 사용하기 위해 다시 씌여질 수 있다.

SELECT DEPTID,
SUM(SALARY)
FROM EMP
WHERE DEPTID = 100
GROUP BY DEPTID;

11. WHERE 절에 선행 INDEX 칼럼을 명시하라.

복합 인덱스의 경우, 선행 인덱스가 WHERE절에 명시되어 있다면 쿼리는
그 인덱스 를 사용할 것이다. 다음의 질의는 PART_NUM과 PRODUCT_ID 칼럼
에 있는 PRIMARY KEY CONSTRAINT에 기초한 복합 인덱스를 이용할 것이다.

SELECT *
FROM PARTS
WHERE PART_NUM = 100;

반면, 다음의 쿼리는 복합인덱스를 사용하지 않는다.

SELECT *
FROM PARTS
WHERE PRODUCT_ID = 5555;

같은 요청(REQUEST)이 인덱스를 이용하기 위해 다시 씌어 질 수 있다. 다음 질의
의 경우, PART_NUM컬럼은 항상 0 보다 큰 값을 가질것이다.

SELECT *
FROM PARTS
WHERE PART_NUM > 0
AND PRODUCT_ID = 5555;

12.인덱스 SCAN과 FULL TABLE SCAN을 평가하라.

한 행(ROW)의 15% 이상을 검색하는 경우에는 FULL TABLE SCAN이 INDEX
ACESS PATH보다 빠르다. 이런 경우, SQL이 FULL TABLE SCAN을 이용할 수 있도록
여러분 스스로 SQL을 작성하라. 다음의 명령문은 비록 인덱스가 SALARY
COLUMN에 만들어져 있어도 인덱스 SCAN을 사용하지 않을 것이다. 첫 번째 SQL
에서, FULL HINT를 사용한다면 오라클은 FULL TABLE SCAN을 수행할 것이다. 인덱
스의 사용이 나쁜 점이 더 많다면 아래의 기술을 이용해서 인덱스 수행을 막을
수 있다.

SELECT * --+FULL
FROM EMP
WHERE SALARY = 50000;

SELECT *
FROM EMP
WHERE SALARY+0 = 50000;

다음의 명령문은 비록 인덱스가 SS# COLUMN에 있어도 인덱스 SCAN을 사용하
지 않을 것이다.

SELECT *
FROM EMP
WHERE SS# || '' '' = ''111-22-333'';

오라클이 불분명한 데이터 변환을 수행해야 하는 경우 인덱스가 항상 사용되지
않는 것은 아니다. 다음의 예를 보면, EMP 칼럼에 있는 SALARY는 숫자형 칼
럼이고 문자형이 숫자값으로 변환된다.

SELECT *
FROM EMP
WHERE SALARY = ''50000'';

테이블의 행이 15%이거나 그보다 작을 경우 인덱스 스캔은 보다 잘 수행 될 것
이다. 왜냐 하면 인덱스 스캔은 검색된 행(ROW)하나 하나 마다 다중의 논리적인
읽기 검색(READ)을 할 것이기 때문이다. 그러나 FULL TABLE SCAN은 하나의 논리적
인 읽기 검색 영역 안의 BLOCK에 있는 모든 행들을 읽을 수 있다. 그래서 테이
블의 많은 행들에 접근해야 하는 경우에는 FULL TABLE SCAN이 낫다. 예로 다음의
경우를 보자. 만약 EMP TABLE과 그 테이블의 모든 인덱스에 대해 ANALYZE라
는 명령어가 수행된다면, 오라클은 데이터 사전인 USER_TABLES와
USER_INDEXES에 다음과 같은 통계치를 산출해 낸다.

TABLE STATISTICS:
NUM_ROWS = 1000
BLOCKS = 100

INDEX STATISTICS:

BLEVEL = 2
AVG_LEAF_BLOCKS_PER_KEY = 1
AVG_DATA_BLOCKS_PER_KEY = 1

이러한 통계치 에 근거해서, 아래에 보이는 것이 각각의 다른 SCAN에 대한 논리
적인 읽기(READ)-즉 ACESS된 BLOCK이 될 것이다.

USE OF INDEX TO RETURN ONE ROW = 3

(BLEVEL+(AVG_LEAF_BLOCKS_PER_KEY - 1) +
AVG_DATA_PER_KEY

FULL TABLE SCAN = 100
(BLOCKS)

USE OF INDEX TO RETURN ALL ROWS = 3000
(NUM_ROWS * BLOCKS ACCESSED TO RETURN ONE ROW USING INDEX)

13. 인덱스 스캔에 ORDER BY를 사용하라

오라클의 OPTIMIZER는 , 만약 ORDER BY라는 절이 인덱스된 칼럼에 있다면 인
덱스 스캔을 사용할 것이다. 아래의 질의는 이러한 점을 보여 주는 것인데 이 질
의는 비록 그 칼럼이 WHERE 절에 명시되어 있지 않다고 해도 EMPID컬럼에 있
는 가용한 인덱스를 사용할 것이다. 이 질의는 인덱스로부터 각각의 ROWID를
검색하고 그 ROWID를 사용하는 테이블에 접근한다.

SELECT SALARY
FROM EMP
ORDER BY EMPID;

만약 이 질의가 제대로 작동하지 않는다면, 당신은 위에서 명시되었던 FULL HINT
를 사용하는 같은 질의를 다시 작성함으로써 다른 대안들을 이용해 볼 수 있다.

14. 자신의 데이터를 알아라

내가 이미 설명한 것처럼, 당신은 당신의 데이터를 상세하게 알고 있어야 한다.
예를 들어 당신이 BOXER라는 테이블을 가지고 있고 그 테이블이 유일하지 않은
인덱스를 가진 SEX라는 컬럼과 BOXER_NAME이라는 두 개의 테이블을 가지고 있
다고 가정해 보자. 만약 그 테이블에 같은 수의 남자, 여자 복서가 있다면 오라
클이 FULL TABLE SCAN을 수행하는 경우 다음의 질의가 훨씬 빠를 것이다.

SELECT BOXER_NAME
FROM BOXER
WHERE SEX = ''F'';

당신은 다음과 같이 기술함으로써 질의가 FULL TABLE SCAN을 수행하는지를 확실
하게 해 둘 수 있다.

SELECT BOXER_NAME --+ FULL
FROM BOXER
WHERE SEX = ''F'';

만약 테이블에 980 명의 남성 복서 데이터가 있다면, 질의는 인덱스 SCAN으로
끝나기 때문에 아래형식의 질의가 더 빠를 것이다.

SELECT BOXER_NAME --+ INDEX (BOXER BOXER_SEX)
FROM BOXER
WHERE SEX = ''F'';

이 예는 데이터의 분포에 대해 잘 알고 있는 것이 얼마나 중요한 가를 예시해 준
다. 데이터가 많아지고(GROW) 데이터 분포가 변화하는 것처럼 SQL 도 매우 다
양할 것이다. 오라클은 OPTIMIZER 가 테이블에 있는 데이터의 분포를 잘 인식하
고 적절한 실행 계획을 선택하도록 하기 위해 오라클 7.3 에 HISTOGRAMS라는
기능을 추가했다.

15. KNOW WHEN TO USE LARGE-TABLE SCANS.

작거나 큰 테이블에서 행들을 추출할 때, 전체 테이블의 검색은 인텍스를 사용한
검색보다 성능이 더 좋을 수도 있다. 매우 큰 테이블의 인덱스 검색은 수많은
인덱스와 테이블 블록의 검색이 필요할수도 있다. 이러한 블록들이 데이터베이
스 버퍼 캐쉬에 이동되면 가능한한 오래도록 그곳에 머무른다. 그래서 이러한
블록들이 다른 질의등에 필요하지 않을 수도 있기 때문에, 데이터베이스 버퍼 히
트 비율이 감소하며 다중 사용자 시스템의 성능도 저하되기도 한다. 그러나 전
체 테이블 검색에 의해서 읽혀진 블록들은 데이터베이스 버퍼 캐쉬에서 일찍 제
거가 되므로 데이터베이스 버퍼 캐쉬 히트 비율은 영향을 받지 않게 된다.

16. MINIMIZE TABLE PASSES.

보통, SQL질의시 참조하는 테이블의 숫자를 줄임으로 성능을 향상시킨다. 참조
되는 테이블의 숫자가 적을수록 질의는 빨라진다. 예를 들면 NAME, STATUS,
PARENT_INCOME, SELF_INCOME의 네개의 컬럼으로 이루어진 학생 테이블
에서 부모님에 의존하는 학생과 독립한 학생의 이름과 수입에 대해서 질의시, 이
학생 테이블을 두번 참조하여 질의하게 된다..
SELECT NAME, PARENT_INCOME
FROM STUDENT
WHERE STATUS = 1
UNION
SELECT NAME, SELF_INCOME
FROM STUDENT
WHERE STATUS = 0;
( NAME이 프라이머리 키이며, STATUS는 독립한 학생의 경우는 1, 부모님에
의존적인 학생은 0으로 표시한다)
위의 같은 결과를 테이블을 두번 참조하지 않고도 질의 할 수 있다.

SELECT NAME,PARENT_INCOME*STATUS + SELF_INCOME(1-STATUS)
FROM STUDENT;

17. JOIN TABLES IN THE PROPER ORDER.

다수의 테이블 조인시 테이블들의 조인되는 순서는 매우 중요하다. 전반적으로,
올바른 순서로 테이블이 조인되었다면 적은 수의 행들이 질의시 참조된다. 언제
나 다수의 조인된 테이블들을 질의시 우선 엄격하게 조사하여 행들의 숫자를 최
대한으로 줄인다. 이러한 방법으로 옵티마이저는 조인의 차후 단계에서 적은 행
들을 조사하게 된다. 뿐만 아니라, 여러 조인을 포함하는 LOOP JOIN에서는 가
장 먼저 참조되는 테이블(DRIVING TABLE)이 행들을 최소한으로 리턴하도록 해야
한다. 그리고, 마스터와 상세 테이블 조인시에는(예를 들면 ORDER & ORDER
LINE ITEM TABLES) 마스터 테이블을 먼저 연결 시켜야 한다.
규칙에 근거한 옵티마이저의 경우에는 FROM CLAUSE의 마지막 테이블이 NESTED
LOOP JOIN의 DRIVING TABLE이 된다. NESTED LOOP JOIN이 필요한 경우에는
LOOP의 안쪽의 테이블에는 인텍스를 이용하는 것을 고려할 만하다. EXPLAIN
PLAN과 TKPROF는 조인 타입, 조인 테이블 순서, 조인의 단계별 처리된 행들
의 숫자들을 나타낸다.
비용에 근거한 옵티마이저의 경우에는 WHERE CLAUSE에 보여지는 테이블의 순
서는 옵티마이저가 가장 최적의 실행 계획을 찾으려고 하는 것과 상관 없다. 조
인되는 테이블의 순서를 통제하기 위해서 ORDERED HINT를 사용하는 것이 낫다.

SELECT ORDERS.CUSTID, ORDERS.ORDERNO,
ORDER_LINE_ITEMS.PRODUCTNO --+ORDERED
FROM ORDERS, ORDER_LINE_ITEMS
WHERE ORDERS.ORDERNO = ORDER_LINE_ITEMS.ORDERNO;

18. USE INDEX-ONLY SEARCHES WHEN POSSIBLE.

가능하다면, 인덱스만을 이용하여 질의를 사용하라. 옵티마이저는 오직 인덱스만
을 찾을 것이다. 옵티마이저는 SQL을 만족시키는 모든 정보를 인덱스에서 찾을
수 있을 때, 인덱스만을 이용할 것이다. 예를들면, EMP테이블이 LANME과
FNAME의 열에 복합 인덱스를 가지고 있다면 다음의 질의는 인덱스만은 이용할
것이다.

SELECT FNAME
FROM EMP
WHERE LNAME = ''SMITH'';

반면에 다음의 질의는 인덱스와 테이블을 모두 참조한다.

SELECT FNAME , SALARY
FROM EMP
WHERE LNAME = ''SMITH'';

19. REDUNDANCY IS GOOD.

WHERE CLAUSE에 가능한한 많은 정보를 제공하라. 예를 들면 WHERE COL1 =
COL2 AND COL1 = 10이라면 옵티마이저는 COL2=10이라고 추론하지만,
WHERE COL1 = COL2 AND COL2 = COL3이면 COL1=COL3이라고 초론하지는
않는다.

20. KEEP IT SIMPLE, STUPID.

가능하면 SQL문을 간단하게 만들라. 매우 복잡한 SQL문은 옵티마이저를 무력
화시킬 수도 있다. 때로는 다수의 간단한 SQL문이 단일의 복잡한 SQL문보다
성능이 좋을 수도 있다. 오라클의 비용에 근거한 옵티마이저는 아직은 완벽하지
않다. 그래서 EXPLAIN PLAN에 주의를 기울여야 한다. 여기서 비용이란 상대적인
개념이기에 정확히 그것이 무엇을 의미하는지 알지 목한다. 하지만 분명한 것은
적은 비용이 보다 좋은 성능을 의미한다는 것이다.
종종 임시 테이블을 사용하여 많은 테이블들을 포함하는 복잡한 SQL 조인을 쪼
개는 것이 효율적일 수도 있다. 예를 들면, 조인이 대량의 데이터가 있는 8개의
테이블을 포함할 때, 복잡한 SQL을 두 세개의 SQL로 쪼개는 것이 낫을 수 있
다. 각각의 질의는 많아야 네개정도의 테이블들을 포함하며 그 중간 값을 저장
하는 것이 낫을 수 있다.

21. YOU CAN REACH THE SAME DESTINATION IN DIFFERENT WAYS.

많은 경우에, 하나 이상의 SQL문은 의도한 같은 결과를 줄 수 있다. 각각의
SQL은 다른 접근 경로를 사용하며 다르게 수행한다. 예를들면, MINUS(-) 산술
자는 WHERE NOT IN (SELECT ) OR WHERE NOT EXISTS 보다 더 빠르다.
예를들면, STATE와 AREA_CODE에 각각 다른 인덱스가 걸려 있다. 인덱스에
도 불구하고 다음의 질의는 NOT IN의 사용으로 인해 테이블 전체를 조사하게
된다.
SELECT CUSTOMER_ID
FROM CUSTOMERS
WHERE STATE IN (''VA'', ''DC'', ''MD'')
AND AREA_CODE NOT IN (804, 410);

그러나 같은 질의가 다음 처럼 쓰여진다면 인덱스를 사용하게 된다
SELECT CUSTOMER_ID
FROM CUSTOMERS
WHERE STATE IN (''VA'', ''DC'', ''MD'')
MINUS
SELECT CUSTOMER_ID
FROM CUSTOMERS
WHERE AREA_CODE IN (804, 410);

WHERE절에 OR을 포함한다면 OR대신에 UNION을 사용할 수 있다. 그래서,
SQL 질의를 수행하기 전에 먼저 실행계획을 조심스럽게 평가해야 한다. 이러한
평가는 EXPLAIN PLAN AND TKPROF를 이용하여 할 수 있다.

22. USE THE SPECIAL COLUMNS.

ROWID AND ROWNUM 열을 이용하라. ROWID를 이용하는 것이 가장 빠르다.
예를들면, ROWID를 이용한 UPDATE는 다음과 같다.

SELECT ROWID, SALARY
INTO TEMP_ROWID, TEMP_SALARY
FROM EMPLOYEE;

UPDATE EMPLOYEE
SET SALARY = TEMP_SALARY * 1.5
WHERE ROWID = TEMP_ROWID;

ROWID값은 데이터베이스에서 언제나 같지는 않다. 그래서, SQL이나 응용 프
로그램이용시 ROWID값을 절대화 시키지 말라. 리턴되는 행들의 숫자를 제한
시키기위해 ROWNUM을 이용하라. 만약에 리턴되는 행들을 정확히 모른다면
리턴되는 행들의 숫자를 제한하기위해 ROWNUM을 사용하라
다음의 질의는 100개 이상의 행들을 리턴하지는 않는다.
SELECT EMPLOYE.SS#, DEPARTMENT.DEPT_NAME
FROM EMPLOYEE, DEPENDENT
WHERE EMPLOYEE.DEPT_ID = DEPARTMENT.DEPT_ID
AND ROWNUM < 100;

23.함축적인 커서대신 명시적인 커서를 사용하라.

함축적 커서는 여분의 FETCH를 발생시킨다. 명시적 커서는 DECLARE, OPEN,
FETCH와 CLOSE CURSOR문을 사용하여 개발자에 의해서 생성된다. 함축 커서는
DELETE, UPDATE, INSERT와 SELECT문을 사용하면 오라클에 의해서 생성
된다.

24.오라클 병렬 쿼리 옵션을 찾아서 이용하라.

병렬 쿼리 옵션을 사용하면, 보다 빠른 성능으로 SQL을 병렬로 실행할 수 있다.
오라클 7에서는, 오직 FULL TABLE SCAN에 기반한 쿼리만이 병렬로 수행될 수 있다.
오라클 8에서는, 인덱스가 분할되어있다면 INDEXED RANGE SCANS에 기반한 쿼리도
병렬로 처리될 수 있다. 병렬 쿼리 옵션은 다수의 디스크 드라이버를 포함하는
SMP와 MPP SYSTEM에서만 사용될 수 있다.

오라클 서버는 많은 우수한 특성을 가지고 있지만, 이러한 특성의 존재만으로는
빠른 성능을 보장하지 않는다. 이러한 특성을 위해서 데이터베이스를 조정해야하
며 특성을 이용하기 위해 특별하게 SQL을 작성해야 한다. 예를 들면, 다음의
SQL은 병렬로 수행될 수 있다.

SELECT * --+PARALLEL(ORDERS,6)
FROM ORDERS;

25.네트웍 소통량을 줄이고 한번에 처리되는 작업량을 늘려라.

ARRAY PROCESSING과 PL/SQL BLOCK을 사용하면 보다 나은 성능을 얻을 수 있고
네트웍 소통량을 줄인다. ARRAY PROCESSING은 하나의 SQL문으로 많은 ROW를 처
리할 수 있게 한다. 예를 들면, INSERT문에서 배열을 사용하면 테이블내의
1,000 ROW를 삽입할 수 있다. 이러한 기술을 사용하면 주요한 성능 향상을 클라
이언트/서버와 배치시스템에서 얻어질 수 있다.

복합 SQL문은 과도한 네트웍 소통을 유발할 수 있다. 그러나 만일 SQL문이 단
일 PL/SQL 블록안에 있다면, 전체 블록은 오라클 서버에 보내져서 그곳에서 수
행되고, 결과는 클라이언트의 APPLICATION에게 돌아온다.

개발자와 사용자는 종종 SQL을 데이터베이스에서 데이터를 검색하고 전송하는
간단한 방법으로 사용한다. 때때로 직접적으로 SQL을 작성하지 않고 코드 발생
기를 사용하여 작성한 APPLICATION은 심각한 성능 문제를 일으킨다. 이러한 성능
감퇴는 데이터베이스가 커지면서 증가한다.

SQL은 유연하기 때문에, 다양한 SQL문으로 같은 결과를 얻을 수 있다. 그러나
어떤 문은 다른 것보다 더 효율적이다. 여기에 기술된 팁과 기법을 사용하면 빠
르게 사용자에게 정보를 제공할 수 있는 APPLICATION과 리포트를 얻을 수 있다.
원본: 오라클 Tips
2010. 9. 1. 13:47

(oracle)SQL함수 정리

-- 무조건 올림
SELECT CEIL(13.11) FROM DUAL;

 

-- 반올림
SELECT ROUND(345.123, 0), ROUND(345.123,2), ROUND(345.123, -1) FROM DUAL;

 

-- 나머지
SELECT MOD(23,5) FROM DUAL;

 

-- 승수값
SELECT POWER(3,2), POWER(3, -2) FROM DUAL;

 

-- 버림값
SELECT TRUNC(345.123, 1), TRUNC(345.123, 0), TRUNC(345.123, -1) FROM DUAL;

 

-- IF + THEN 1 ELSEIF 0 THEN 0 ELSE -1
SELECT SIGN(5.342), SIGN(0), SIGN(-2334) FROM DUAL;
SELECT DECODE( SIGN(POWER(2,10)-1000), 1, '2의 10승이 1000보다 크다',
-1, '2의 10승이 1000보다 작다', '2의 10승이 1000이다') 비교답 FROM DUAL;

 

-- ASCII <--> CHAR
SELECT CHR(65) "CHR", ASCII('A') "ASCII" FROM DUAL;

 

-- 대소문자 변환
SELECT LOWER('My name is KIMJINDOO') "LOWER", UPPER('My name is kimjindoo') "UPPER" FROM DUAL;

 

-- 자릿수 맞춰 채우기
SELECT LPAD('DALMA', 10, '*') "LPAD", RPAD('DALMA', LENGTH('DALMA')+11, '@dalcom.net') "RPAD" FROM DUAL;
SELECT LPAD('1234567890', 20, '+') || RPAD('1234567890', 20, '^') "1234567890" FROM DUAL;
SELECT LPAD('1,234,567', 30, ' ') "LPAD 사용으로 30자리 맞춤", '1,234,567' "단순문자 사용",
1234567 "단순숫자 사용" FROM DUAL;

 

-- 공백 제거
SELECT REPLACE(LTRIM('    AAA    '), ' ', '0') "LTRIM",
REPLACE(RTRIM('    AAA    '), ' ', '0') "RTRIM" FROM DUAL;

-- 문자열 치환
SELECT REPLACE('ORACLE', 'A', 'a') "REPLACE" FROM DUAL;
SELECT EMP_NAME, REPLACE(EMP_NAME, '이', '박') "이->박" FROM PERSONNEL WHERE EMP_NAME LIKE '이%';

 

-- 문자열의 일부만을 취하기
SELECT SUBSTR('ORACLE 프로젝트', 1, 3) SUBSTR1, SUBSTR('오라클 프로젝트', 4, 5) SUBSTR2,
SUBSTR('오라클 PROJECT', 10) SUBSTR3 FROM DUAL;
SELECT SUBSTRB('ORACLE 프로젝트', 2, 3) SUBSTRB1, SUBSTRB('오라클 프로젝트', 4, 5) SUBSTRB2,
SUBSTRB('오라클 PROJECT', 10) SUBSTRB3 FROM DUAL;


-- 문자열의 길이
SELECT EMPNO, LENGTH(EMPNO), LENGTHB(EMPNO), EMP_NAME, LENGTH(EMP_NAME), LENGTHB(EMP_NAME) FROM
PERSONNEL WHERE EMPNO>'98102'
SELECT LENGTH('가나다') "LENGTH", LENGTHB('가나다') "LENGTHB" FROM DUAL;

-- LANGUAGE : KOREAN_KOREA.KO16KSC5601
SELECT LENGTH('학교') FROM DUAL;   RESULT: 2
SELECT LENGTHB('학교') FROM DUAL;  RESULT: 4

-- LANGUAGE : AMERICAN_AMERICA.US7ASCII
SELECT LENGTH('학교') FROM DUAL;   RESULT: 4


-- 문자열에서 특정 문자나 문자열의 위치

INSTR[B](string1, [char||string], start_position, n_counted)
SELECT INSTR('ORACLE PROJECT', 'R', 1, 1) INSTR1, INSTR('ORACLE PROJECT', 'R', 1, 2) INSTR2,
INSTR('ORACLE PROJECT', 'R', 1, 3) INSTR3 FROM DUAL;
SELECT INSTR('CORPORATE FLOOR', 'OR', 3, 2) INSTR, INSTRB('CORPORATE FLOOR', 'OR', 3, 2) INSTRB FROM DUAL;
SELECT INSTR('하늘 아래 하늘이 또 있겠는가.', '하늘', 1, 2) 하늘1,INSTRB('하늘 아래 하늘이 또 있겠는가.', '하늘', 1, 2) 하늘2 FROM DUAL;


-- SYSTEM 시간
SELECT TO_CHAR(SYSDATE, 'YYYY-MM-DD HH24:MI:SS') "SYSDATE" FROM DUAL;

 

-- 주어진 날짜가 속하는 월의 마지막 날짜
SELECT TO_CHAR(LAST_DAY(SYSDATE), 'YYYY-MM-DD HH24:MI:SS') "LAST_DAY" FROM DUAL;

 

-- 주어진 두 날짜간의 기간을 월 단위로 계산 
-- MONTHS_BETWEEN(date1, date2)  : IF date1 > date2  THEN +
SELECT MONTHS_BETWEEN('2006/12/26', '2007/01/25') "MONTHS_BETWEEN(-)",
MONTHS_BETWEEN('2006/01/13', '2005/11/13') "MONTHS_BETWEEN(+)" FROM DUAL;
SELECT ROUND(MONTHS_BETWEEN('2006/12/28', '2006/12/01'), 1) FROM DUAL;

 

-- 월단위 계산
SELECT ADD_MONTHS(SYSDATE, 12) "ADD_MONTHS(+)", ADD_MONTHS(SYSDATE, -12) "ADD_MONTHS(-1)" FROM DUAL;

 

-- 주어진 날짜를 기준으로 주어진 요일이 처음 오는 날짜
-- 일요일:1 ~ 토요일:7
SELECT SYSDATE, NEXT_DAY(SYSDATE, '일요일') "NEXT_DAY 1", NEXT_DAY(SYSDATE, 1) "NEXT_DAY 2" FROM DUAL;


-- NUMBER와 DATE를 문자타입으로 변환
SELECT TO_CHAR(123456789) "NUMBER", TO_CHAR(SYSDATE,'YYYY/MM/DD HH24:MI:SS') "DATE" FROM DUAL;

 

-- 문자를 숫자로 변환
SELECT TO_NUMBER('123456789') "TO_NUMBER1",
TO_NUMBER('123,456.9', '999,999.9') "TO_NUMBER2",
TO_NUMBER('1,234,567', '9G999G999') "TO_NUMBER3" FROM DUAL;

 

-- 문자형 데이타를 DATE형으로 변환
SELECT TO_DATE('20020824', 'YYYYMMDD') "TO_DATE1",
TO_DATE('2002-08-24', 'YYYY-MM-DD') "TO_DATE2",
TO_DATE('200208', 'YYYYMM') FROM DUAL;

 

-- 평균값 구하기
SELECT AVG(HEIGHT), AVG(WEIGHT) FROM PERSONNEL;

 

-- 최대값 최소값
SELECT MAX(EMPNO), MAX(EMP_NAME), MIN(EMPNO), MIN(EMP_NAME) FROM PERSONNEL;

 

-- 합계 구하기
SELECT SUM(WEIGHT) FROM PERSONNEL;

 

-- 조회 범위의 조회 건수
SELECT COUNT(*), COUNT(EMPNO), COUNT(JIKCH_CODE) FROM PERSONNEL;

 

-- NULL 값 치환
SELECT EMPNO, EMP_NAME, HOBBY 취미, NVL(WELL, '없음') 특기
FROM PERSONNEL WHERE EMPNO BETWEEN '98001' AND '98005';

-- 연속 조건문
-- DECODE(a, b, c, d)  a가 b면 c고, 아니면 d
-- DECODE(a, b, c, d, e, f, g, h ....) a가 b면 c고, d면 e고, f면 g고 h면 ...
-- DECODE(a, b, c, DECODE(e, f, g, h))
SELECT EMPNO, EMP_NAME, DECODE(HT_CODE, '1', '현재원', '2', '휴직원', '퇴사') HT_CODE
FROM PERSONNEL WHERE EMPNO BETWEEN '98071' AND '98080';

 

-- 오라클 환경변수 값 구하기
SELECT USERENV('LANGUAGE') "LANGUAGE", USERENV('TERMINAL') "TERMINAL",
USERENV('SESSIONID') "SESSIONID" FROM DUAL;

 

-- 주어진 데이타중 최대값 최소값 구하기
SELECT GREATEST(132,33 ,45,99,22, 32, 77, 12) GREATEST, LEAST(132,33 ,45,99,22, 32, 77, 12) FROM DUAL;
SELECT GREATEST('가', '나', '다', '라', '마') GREATEST1, GREATEST('가', '나', '다', '라', '마', '마마') GREATEST2,
LEAST('가', '나', '다', '라', '마') FROM DUAL;

 

-- UID, USER
SELECT UID, USER FROM DUAL;


2010. 9. 1. 13:47

(oracle)PL/SQL로 예외 처리


PL/SQL 코드를 실행할 때 error 발생하는 경우가 있다. Error는 예외(Exception)를 발생시켜 PL/SQL 블록을 중지시키고 예외 처리기 부분으로 제어가 이동한다. Exception handler Exception을 검출하고 조건에 따라 조치 작업을 할 수 있다.
 
예외 처리란
예외는 PL/SQL 블록의 실행 중에 발생하여 블록의 주요 부분을 중단 시킨다. 항상 PL/SQL 예외가 발생할 때 블록은 항상 종료되지만 마지막 조치 작업을 수행하도록 예외 처리 부분을 작성할 수 있다.
 
1.        예외란 무엇인가 ?
- PL/SQL을 실행 동안에 발생하는 error 처리를 의미한다.
2.        어떻게 발생되는가 ?
- Oracle 오류가 발생할 때
- 사용자가 직접 발생시킬 수 있다.
3.        처리하는 방법은 무엇인가 ?
- 처리기를 이용한다.
- 실행 환경에 전달한다.
 
예외를 발생시키는 두 가지 방법
n         Oracle 오류가 발생하면 관련된 예외가 자동적으로 발생한다. 예를 들어, 오류 ORA-014-3는 데이터베이스에서 검색된 행이 전혀 없을 때 발생하며 PL/SQL NO_DATA_FOUND라 는 예외를 발생시킨다.
n         블록에 RAISE문을 써서 명시적으로 예외를 발생시킬 수 있다. 발생하는 예외를 사용자가 정의한 것일 수도 있고 미리 정의된 것일 수도 있다.
 
예외 처리
예외 트랩(trap)
만일 예외가 블록의 실행 가능한 섹션에서 발생한다면, 처리는 블록의 예외 섹션에서 해당 예외 처리기로 제어가 넘어 갑니다. PL/SQL 블록이 성공적으로 예외를 처리 한다면 이때 예외는 둘러싸는 블록이나 환경으로 전달 되지 않는다.
 
예외 전달
예외를 처리하는 다른 방법은 실행 환경으로 예외를 전달하도록 하는 것이다. 예외가 블록의 실행부에서 발생하여 해당 예외 처리기가 없다면, PL/SQL 블록의 나머지 부분은 수행되지 못하고 종료된다.
 
예외 검출
예외가 블록의 실행부에서 발생하면 블록의 예외부에 있는 해당 예외 처리부로 제어가 넘어간다.
 
예외의 유형
실행 중에 ERROR가 발생하면 프로그램이 중단되지 않고 예외에 대한 프로그램을 할 수 있다.
 
   
   
       
정의된 ORACLE
SERVER ERROR
PL/SQL코드에서 자주 발생하는 ERROR을 미리 정의함
선언할 수 없고 ORACLE SERVER이 암시적으로 발생
정의되지 않은
ORACLE SERVER
ERROR
기타 표준 ORACLE SERVER
ERROR
사용자가 선언하고 ORACLE SERVER이 그것을 암시적으로 발생
사용자 정의
ERROR
프로그래머가 정한 조건이 만족되지 않을 경우 발생
사용자가 선언하고 명시적으로 발생한다.
 
예외 정의
PL/SQL블록의 예외 섹션 내에서 해당 루틴을 포함하므로 모든 에러를 처리할 수 있다. 각각의 에러 처리기는 WHERE절로 구성되는데 그 곳에 에러를 명시하고 WHERE절 뒤에는 예외가 발생했을 때 처리할 문장을 기술한다.
 
n        EXCEPTION키워드로 블록의 예외 처리 섹션을 시작합니다.
n        블록에서 개별적인 작업에 대해 여러 예외 처리기를 정의합니다.
n        예외가 발생할 때 블록 종료 전에 PL/SQL은 하나의 처리기만 프로세스 합니다. 다른 모든 예외 처리 절 후에 OTHERS절을 넣습니다.
n        최대 하나의 OTHERS절을 가질 수 있습니다.
n        예외는 지정(assignment) 문장 또는 SQL문장에서 쓰일 수 없습니다.
 
EXCEPTION
                  WHEN  exception1 [OR exception2, . . . .] THEN
                                   statement1;
                                   statement2;
                                   . . . . . .
                  [WHEN  exception2 [OR exception3, . . . .] THEN
                                   statement3;
                                   statement4;
                                   . . . . . .]
                  [WHEN  OTHERS THEN
                                   statement5;
                                   statement6;
                                   . . . . . .]
END;
 
n         Exception : 선언섹션에서 선언된 미리 정의된 예외의 표준 이름 이거나 사용자 정의예외의 이름입니다.
n         Statement : 하나 이상의 PL/SQL또는 SQL문장입니다.
n         OTHERS : 명시적으로 선언되지 않은 모든 예외를 트랩하는 예외 처리 절입니다.
 
WHEN OTHERS 예외 처리기
예외 처리 섹션은 지정된 예외만 트랩(trap)합니다. OTHERS예외 처리기를 사용하지 않으면 다른 예외들은 트랩(trap) 되지 않습니다. 이것은 아직 처리되지 않은 모든 예외를 트랩합니다. 그러므로 OTHERS는 마지막에 정의되는 예외 처리기입니다. 일부 ORACLE 툴들은 어플리케이션에서 이벤트를 발생시키기 위해 일으키는 개별적인 미리 정의된 예외들을 가지고 있습니다. OTHERS는 또한 이 예외들도 트랩 합니다.
 
미리 정의된 ORACLE SERVER 에러
해당 예외 처리 루틴에서 표준 이름을 참조함으로써 미리 정의된 ORACLE SERVER 에러를 트랩(trap)합니다.
예외 이름
에러 번호
    
ACCESS_INTO_NULL
ORA-06530
초기화 되지않은 객체의 속성에 대해 값을 지정하는 것을 시도합니다.
COLLECTION_IS_NULL
ORA-06531
초기화되지 않은 중첩 테이블 대해 EXISTS를 제외한 메쏘드 모음의 적용을 시도합니다.
CURSOR_ALREADY_OPEN
ORA-06511
이미 열린 CURSOR의 열기를 시도합니다.
DUP_VAL_ON_INDEX
ORA-00001
중복 값의 삽입을 시도합니다.
INVALID_CURSOR
ORA-01001
잘못된 CURSOR연산이 발생합니다.
INVALID_NUMBER
ORA-01722
수의 문자열 전환은 실패입니다.
LOGIN_DENIED
ORA-01017
잘못된 사용자명과 비밀 번호로 ORACLE에 로그온합니다.
NO_DATA_FOUND
ORA-01403
데이터를 RETURN하지 않는 SELECT문장
NOT_LOGGED ON
ORA-01012
PL/SQL프로그램은 ORACLE에 연결하지 않고 데이터베이스 호출을 발생합니다.
PROGRAM_ERROR
ORA-06501
PL/SQL은 내부 문제를 가지고 있습니다.
ROWTYPE_MISMATCH
ORA-06504
지정문에 포함된 호스트CURSOR변수와 PL/SQL    CURSOR변수는 RETURN 유형이 다릅니다.
STORAGE_ERROR
ORA-06500
PL/SQL이 메모리를 다 써버리거나 또는 메모리가 훼손되었습니다.
SUBSCRIPT_BEYOND_COUNT
ORA-06533
모음의 요소 개수보다 더 큰 인덱스 개수를 사용하는 중첩 테이블 참조합니다.
SUBSCRIPT_OUTSIDE_LIMIT
ORA-06532
범위 밖의 인덱스 번호를 사용하여 중첩 테이블 참조 합니다.
TIMEOUT_ON_RESOURCE
ORA-00051
ORACLE이 리소스를 대기하는 동안 시간 초과가 발생합니다.
TOO_MANY_ROWS
ORA-01422
단일 행 SELECT는 하나 이상의 행을 RETURN합니다.
VALUE_ERROR
ORA-06502
계산,변환,절단,또는 크기 제약 오류가 발생합니다.
ZERO_DIVIDE
ORA-01476
0으로 배분을 시도합니다.
 
 
미리 정의되지 않은 ORACLE SERVER 에러
우선 에러를 선언하고 나서 OTHERS에서 미리 정의되지 않은 ORACLE SERVER 에러를 처리(에러 번호 확인)합니다. 선언된 예외는 암시적으로 발생합니다. PL/SQL에서 PARAGMA EXCEPTION_INT ORACLE 에러 번호와 예외 이름을 관련시키기 위해 컴파일러에게 알려줍니다. PARAGMA PL/SQL블록이 실행될 때 처리되지 않는 컴파일러 명령문임을 의미하는 키워드입니다. 블록 내에서 예외 이름이 발생되면 그것을 관련된 ORACLE SERVER 에러번호로 해독하기 위해 PL/SQL컴파일러에게 지시합니다.
 
선언 부분에서 예외 이름을 선언
exception_name    EXCEPTION;
 
PRAGMA EXCEPTION_INIT문장을 사용하여 표준 에러 번호와 선언된 예외를 연결한다.
PRAGMA          EXCEPTION_INIT(exception_name,  error_number);
 
ACCEPT  p_ename PROMPT '삭제하고자 하는 사원의 이름을 입력하시오 : '
DECLARE
                  v_ename                   emp.ename%TYPE := '&p_ename';
                  v_empno                   emp.empno%TYPE;
                  emp_constraint       EXCEPTION;
                  PRAGMA  EXCEPTION_INIT (emp_constraint, -2292);
BEGIN
                  SELECT empno
                                   INTO v_empno
                                   FROM emp
                                    WHERE ename = UPPER(v_ename);
                  DELETE emp
                                   WHERE empno = v_empno;
EXCEPTION
                  WHEN NO_DATA_FOUND THEN
                      DBMS_OUTPUT.PUT_LINE('&p_ename' || ' 자료가 없습니다.');
                  WHEN TOO_MANY_ROWS THEN
                      DBMS_OUTPUT.PUT_LINE('&p_ename' || ' 자료가 여러개 있습니다.');
                  WHEN emp_constraint THEN
                      DBMS_OUTPUT.PUT_LINE('&p_ename' || ' 삭제할 없습니다.');
                  WHEN OTHERS THEN
                      DBMS_OUTPUT.PUT_LINE('기타 에러입니다.');
END;
 
사용자 정의 예외
PL/SQL에서는 개별적으로 예외를 정의할 수 있습니다. 사용자 정의 PL/SQL 예외는PL/SQL 블록의 선언 섹션에서 선언 하고RAISE 문장으로 명시적으로 발생시킨다.
선언 섹션에서 사용자가 선언한다.
Exception_name    EXCEPTION;
 
실행 섹션에서 명시적으로 예외를 발생하기 위해 RAISE문장을 사용한다.
RAISE   exception_name;
 
ACCEPT  p_deptno  PROMPT '조회하고자 하는 부서번호를 입력하시오 : '
DECLARE
                  v_deptno                  emp.deptno%TYPE := &p_deptno;
                  CURSOR emp_cursor IS
                                   SELECT empno,ename,job,sal
                                                     FROM emp
                                                     WHERE deptno = v_deptno;
                  emp_deptno_ck      EXCEPTION;
BEGIN
                  IF v_deptno NOT IN (10,20,30) THEN
                                   RAISE emp_deptno_ck;
                  ELSE
                                   DBMS_OUTPUT.PUT_LINE('사번        담당업무        ');
                                   DBMS_OUTPUT.PUT_LINE('---- ---------- --------- ------------');
                                   FOR emp_record IN emp_cursor LOOP
                                       DBMS_OUTPUT.PUT_LINE(RPAD(emp_record.empno,4) || ' ' ||
                                          RPAD(emp_record.ename,11) || RPAD(emp_record.job,10) ||
                                              RPAD(TO_CHAR(emp_record.sal,'$999,990.00'),12));
                                   END LOOP;
                  END IF;
EXCEPTION
                  WHEN emp_deptno_ck THEN
                                   DBMS_OUTPUT.PUT_LINE('&p_deptno' || ' 자료가 없습니다.');
                  WHEN OTHERS THEN
                                   DBMS_OUTPUT.PUT_LINE('기타 에러입니다.');
END;
 
예외 트래핑 함수
에러가 발생 했을 때 두 함수를 사용하여 관련된 에러 코드 또는 메시지를 확인할 수 있습니다. 코드 또는 메시지에 따라 에러에 대해 취할 작업을 정할 수 있습니다.
 
    
SQLCODE
에러 코드에 대한 숫자를 RETURN한다.
SQLERRM
에러 번호에 해당하는 MESSAGE RETURN한다.
 
SQL CODE
SQL CODE
    
0
예외가 없습니다.(NO ERROR)
1
사용자 정의 ERROR NUMBER
+100
NO_DATA_FOUND 예외
양의 정수
표준 에러 번호
 
ACCEPT  p_ename PROMPT '삭제하고자 하는 사원의 이름을 입력하시오 : '
DECLARE
                  v_ename                   emp.ename%TYPE := '&p_ename';
                  v_empno                   emp.empno%TYPE;
                  v_err_code               NUMBER;
                  v_err_msg                VARCHAR2(255);
BEGIN
                  SELECT empno
                                   INTO v_empno
                                   FROM emp
                                   WHERE ename = UPPER(v_ename);
                  DELETE emp
                                   WHERE empno = v_empno;
EXCEPTION
                  WHEN OTHERS THEN
                                   ROLLBACK;
                                   v_err_code := SQLCODE;
                                   v_err_msg := SQLERRM;
                                   DBMS_OUTPUT.PUT_LINE('에러 번호 : ' || TO_CHAR(v_err_code));
                                   DBMS_OUTPUT.PUT_LINE('에러 내용 : ' || v_err_msg);
END;
 
RAISE_APPLICATION_ERROR
표준화 되지 않은 에러 코드와 에러 MESSAGE RETURN하는 RAISE_APPLICATION_ERROR 프로시저를 사용합니다. RAISE_APPLICATION_ERROR로 어플리케이션에 대한 에러를 제어할 수 있고 처리되지 않은 에러가 RETURN되지 않도록 합니다.
 
raise_application_error  (error_number, message[,{TRUE|FALSE}]);
 
n         error_number : -20000 20999사이의 예외에 대해 지정된 번호
n         message : 예외에 대한 사용자 지정 MESSAGE
n         TRUE|FALSE : 선택적 BOOLEAN 매개변수로 TRUE면 에러는 이전의 에러 스택에 의치하고 FALSE(DEFAULT)면 에러는 모든 이전의 에러를 대치합니다.
 
ACCEPT  p_ename PROMPT '삭제하고자 하는 사원의 이름을 입력하시오 : '
DECLARE
                  v_ename                   emp.ename%TYPE := '&p_ename';
                  v_err_code               NUMBER;
                  v_err_msg                VARCHAR2(255);
BEGIN
                  DELETE emp
                                   WHERE ename = v_ename;
                  IF SQL%NOTFOUND THEN
                                   RAISE_APPLICATION_ERROR(-20100,'no data found');
                  END IF;
EXCEPTION
                  WHEN OTHERS THEN
                                   ROLLBACK;
                                   v_err_code := SQLCODE;
                                   v_err_msg := SQLERRM;
                                   DBMS_OUTPUT.PUT_LINE('에러 번호 : ' || TO_CHAR(v_err_code));
                                   DBMS_OUTPUT.PUT_LINE('에러 내용 : ' || v_err_msg);
END;
2010. 9. 1. 13:45

(oracle)Merge사용하기


- 문법
MERGE INTO table_name alias
          USING (table|view|subquery) alias
          ON (join condition)
          WHEN MATCHED THEN
                       UPDATE SET col1 = val1[, col2 = val2…]
          WHEN NOT MATCHED THEN
                       INSERT (컬럼리스트) VALUES (값들....);


MERGE는 UPDATE와 INSERT를 결합한 문장으로 각각의 쓰임새는 다음과 같다.
INTO clause : data가 update 되거나 insert될 table 이름
USING clause : 대상 table의 data와 비교한 후  update 또는 insert할 때 사용할 data의 source.  
ON clause : update나 insert를 하게 될 condition으로, 해당 condition을 만족하는 row가 있으면 WHEN MATCHED 이하를 실행하게 되고, 없으면 WHEN NOT MATCHED 이하를 실행하게 된다.
WHEN MATCHED : ON clause의 조건이 TRUE인 row에 수행할 내용
WHEN NOT MATCHED : ON clause의 조건에 맞는 row가 없을 때 수행할 내용

--------------------------------------------- 예
SQL> create table emp_history (
  2  empno number(4) not null primary key,
  3  mydate date,
  4  salary number(7,2));

테이블이 생성되었습니다.

SQL> MERGE INTO emp_history eh
  2        USING emp e
  3        ON (e.empno = eh.empno)
  4  WHEN MATCHED THEN
  5        UPDATE SET eh.salary = e.sal
  6  WHEN NOT MATCHED THEN
  7        INSERT VALUES (e.empno, sysdate, sal);

14 행이 병합되었습니다.

SQL> commit;

커밋이 완료되었습니다.

SQL> select * from emp_history;

     EMPNO MYDATE       SALARY
---------- -------- ----------
      7844 04/01/06       1500
      7839 04/01/06
      7782 04/01/06       2450
      7521 04/01/06       1250
      7654 04/01/06       1250
      7788 04/01/06       3000
      7698 04/01/06       2850
      7566 04/01/06       2975
      7499 04/01/06       1600
      7934 04/01/06       1300
      7902 04/01/06       3000
      7369 04/01/06        800
      7876 04/01/06       1100
      7900 04/01/06        950

14 개의 행이 선택되었습니다.  

 

===============================================================================

-. 추가하는 데이터가 테이블에 존재하지 않을 때는 INSERT가 수행되고, 이미 데이터가
   존재할 경우에는 UPDATE가 수행된다.
-. MERGE문장 작성 순서
   1. 새로운 행이 추가되거나 갱신이 될 테이블을 지정한다.
   2. 다른 테이블을 참조하여 데이터를 비교한다면 대상 테이블 명을 별칭과 함께 정의한다.
   3. 새로운 행을 추가하려고 하는 테이블과 대상 테이블 간에 조인이 필요하면 ON절을
       활용 한다.
   4. 조인 조건에 의해 만족하는 데이터가 있을 경우엔 UPDATE가 수행되고 만족하는
       데이터가 존재하지 않으면 INSERT문이 수행된다.

===============================================================================

 

또다른 예문

merge 간단한 예제

 

1. 테이블을 생성한다.
create table table_dest (
   id  number primary key,
   txt varchar2(20)
);

insert into table_dest values (1,'one');
insert into table_dest values (3,'three');
insert into table_dest values (5,'five');

commit;

 

create table table_source (
   id  number primary key,
   txt varchar2(20)
);

insert into table_source values (2,'TWO');
insert into table_source values (3,'THREE');

commit;

 

2. merge를 사용하여 처리한다.

merge into
  table_dest d 
  [설명] 처리되는 테이블를 명시한다.
using
  table_source s
  [설명] 비교할 대상이 여기에 해당되는데.. table, view, sub-query
on
  (s.id = d.id)
  [설명] 조건을 나열한 만약 여러개일 경우에는 and로 나열하면 됨
when matched then
  update set d.txt = s.txt
when not matched then
  insert (id, txt) values (s.id, s.txt);

 

3. 확인함.
select * from table_dest;

 

[요약정리]
MERGE <hint> INTO <table_name>
USING <table_view_or_query>
ON (<condition>)
WHEN MATCHED THEN <update_clause>
WHEN NOT MATCHED THEN <insert_clause>;

 

[또 다른 예제]
MERGE INTO SALES_FACT D
     USING SALES_JUL01 S
     ON (D.TIME_ID = S.TIME_ID
        AND D.STORE_ID = S.STORE_ID
        AND D.REGION_ID = S.REGION_ID)
     WHEN MATCHED THEN
    UPDATE
     SET d_parts = d_parts + s_parts,
          d_sales_amt = d_sales_amt + s_sales_amt,
          d_tax_amt = d_tax_amt + s_tax_amt,
          d_discount = d_discount + s_discount
      WHEN NOT MATCHED THEN
     INSERT (D.TIME_ID ,D.STORE_ID ,D.REGION_ID,
        D.PARTS ,D.SALES_AMT ,D.TAX_AMT ,D.DISCOUNT)
     VALUES (
        S.TIME_ID ,S.STORE_ID ,S.REGION_ID,
        S.PARTS ,S.SALES_AMT ,S.TAX_AMT ,S.DISCOUNT);

=================================================================================

애플리케이션 로직에서  데이터 유뮤를 검사하고 있으면 ,  데이터를 UPDATE 하고

없으면 INSERT 하는 흔한 패턴을 아주 간단하게 쿼리 한문장으로 처리할 수 있게 하는 쿼리가

MERGE Statement 입니다.

 

예문)

MERGE INTO bonuses D
   USING (SELECT employee_id, salary, department_id FROM employees
   WHERE department_id = 80) S
   ON (D.employee_id = S.employee_id)
   WHEN MATCHED THEN UPDATE SET D.bonus = D.bonus + S.salary*.01
   WHEN NOT MATCHED THEN INSERT (D.employee_id, D.bonus)
   VALUES (S.employee_id, S.salary*0.1);

 

오라클 도큐먼트에 있는 예문입니다.

 

=============================================================================

짱가's 사족 : 한시간여 헤멘끝에 발견한...

         key관련절은 merge update불가능

( 키워드 오류가 남 )

MERGE INTO  TB_BUSI_MENU x
     USING ( SELECT '2005' BUSI_YEAR, '21S000' BUSI_CD, '21S001' DETAIL_BUSI_CD,
    '125010' DETAIL_MENU_CD,  '2' ORDER_RANK ,
    'testUserId' REG_ID, SYSDATE REG_DT, '127.0.0.1' REG_IP, 'Y' DEL_YN
 FROM DUAL )
        ON (  x.BUSI_YEAR = y.BUSI_YEAR AND BUSI_CD = '21S000' AND DETAIL_BUSI_CD = '21S001' AND DETAIL_MENU_CD = '125010' )
   WHEN MATCHED THEN
      UPDATE
         SET BUSI_YEAR= '2005',BUSI_CD= '21S000', DETAIL_BUSI_CD='21S001',
    DETAIL_MENU_CD='125010', ORDER_RANK='2',
    UPD_ID='testUserId', UPD_DT=SYSDATE, DEL_YN='Y'
         WHEN NOT MATCHED THEN
      INSERT (BUSI_YEAR, BUSI_CD, DETAIL_BUSI_CD,
   DETAIL_MENU_CD, ORDER_RANK ,
   REG_ID, REG_DT, REG_IP, DEL_YN)
      VALUES ('2005', '21S000', '21S001',
    '125010', '2',
    'testUserId', SYSDATE, '127.0.0.1', 'Y' ) 

 

( 밑의 문장은 몇개의 update문장을 제외하고 실행성공한 문장 )

그외에 몇가지 다른 이유가 있는 듯함 제외하고 실행하니 update는 되나

insert는 되지 않는 오류가 발생

그래서 dual에서 읽어오는 것 처럼 작성하여 join문을 작성.

 

MERGE INTO tb_busi_menu x
     USING (SELECT '2005' busi_year, '21S000' busi_cd, '21S001' detail_busi_cd,
                   '125010' detail_menu_cd, '2' order_rank, 'testUserId' reg_id,
                   SYSDATE reg_dt, '127.0.0.1' reg_ip, 'testUserId' upd_id,
                   SYSDATE upd_dt, 'Y' del_yn
              FROM DUAL) y
        ON (    x.busi_year = y.busi_year
            AND x.busi_cd = y.busi_cd
            AND x.detail_busi_cd = y.detail_busi_cd
            AND x.detail_menu_cd = y.detail_menu_cd)
        WHEN MATCHED THEN
                UPDATE
                   SET order_rank = y.order_rank, upd_id = y.upd_id,
                       upd_dt = y.upd_dt, del_yn = y.del_yn
        WHEN NOT MATCHED THEN
                INSERT (busi_year, busi_cd, detail_busi_cd, detail_menu_cd,
                        order_rank, reg_id, reg_dt, reg_ip, del_yn)
                VALUES (y.busi_year, y.busi_cd, y.detail_busi_cd,
                        y.detail_menu_cd, y.order_rank, y.reg_id, y.reg_dt,
                        y.reg_ip, y.del_yn)

2010. 9. 1. 13:43

(oracle)오라클 SID, Service Name 차이 | Oracle개념용어정리


 

오라클 SID, Service Name 차이 | Oracle개념용어정리

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

DBMS 서버를 기동하기 위해서는 DB서버가 기동하는 서버의 IP 그리고

DB서버가 접속을 받아들이기 위한 프로토콜에 대한 정의가 필요합니다.

오라클의 경우 인스턴스가 서버 역할을 하는 DBMS프로세스인데,

인스턴스가 기동할때 SID를 필요로 합니다.

즉 SID는 인스턴스의 이름인 셈이지요.

SID가 필요한 이유는 한 서버(H/W)에 여러개의 인스턴스가 기동될 수 있으므로

구별하는 태그가 필요하겠지요.  따라서 SID는 DB서버에서 필요한 정보입니다.

SID정보는 환경변수와, LISTENER.ORA라는 파일에서 정의 됩니다.

DB에 접속하는 클라이언트 프로그램의 경우 접속하고자 하는 오라클 인스턴스 정보를

필요로 합니다. 클라이언트 프로그램이 접속하는데 필요한 정보는 서버IP, 오라클SID, 접속프로토콜
같은 정보가 필요하지요. 이러한 정보를 묶어서 서비스명으로 대표하고,
이 서비스명으로 클라이언트 프로그램이 서버에 접속하는데 사용합니다.

이 정보는 클라이언트쪽의 TNSNAMES.ORA라는 파일에 정의 되어있습니다.

 

출처 : 네이버 지식 검색 : 정확히는 모름(?)


instance, instantiate ; 인스턴스, 인스턴스화

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

인스턴스는 추상화 개념 또는 클래스 객체, 컴퓨터 프로세스 등과 같은 템플릿이 실제 구현된 것이다.
인스턴스화는 클래스 내의 객체에 대해 특정한 변형을 정의하고, 이름을 붙인 다음, 그것을 물리적인
어떤 장소에 위치시키는 등의 작업을 통해, 인스턴스를 만드는 것을 의미한다.

 

1. 몇몇 필자들은, 객체지향 프로그래밍에서 클래스를 인스턴스화 한다는 것이, 클래스의 구체적인 인스턴스,
즉 객체를 만드는 것이라고 말한다. 그 객체는 컴퓨터 내에서 실행시킬 수 있는 실행 파일이다.

 

2. 객체지향 프로그램 언어인 자바에서는, 클래스로부터 인스턴스화된 객체를, 객체라는 말 대신에
역시 클래스라고 부름으로써 많은 사용자들을 혼란스럽게 한다. 즉 자바에서는, 특정한 클래스를
만들기 위해 클래스를 인스턴스화하며, 그것 역시 컴퓨터 내에서 동작하는 실행 파일이다.

 

3. 객체지향 프로그래밍 개념이 나오기 이전의 데이터 모델링이나 프로그래밍에서는, 인스턴스화라는 것이
관계형 데이터베이스 테이블 내에 새로운 엔트리를 만듦으로써 추상화된 객체로부터 실재(데이터가 들어있는)
객체를 만드는 것도, 한 가지 용례였다. 

 

출처 : 텀즈

2010. 9. 1. 13:42

(oracle)아웃조인에서 IN 처리법


===========================
--아웃터조인에서 IN처리법
===========================
SELECT a.woori_COD, a.EMPLO_NUM, a.EMPLO_NAM
 ,b.brnch_cod, b.brnch_nam
FROM TCMEMPL a
 ,TCMBRNC b
WHERE a.WOORI_cod = '20'
AND a.woori_cod = b.woori_cod(+)
AND a.RALPST_BRCNUM = b.brnch_cod(+)
AND b.brnch_cod(+) IN ('20808','20015')
--에러발생함.


--해결1
---------------------------
AND  (CASE WHEN b.brnch_cod(+) IN ('20808','20015') THEN '1' ELSE '2' END) = '1'

--해결2
---------------------------
AND  DECODE(b.brnch_cod(+), '20808','1','20015','1','2') = '1'

2010. 9. 1. 13:41

(oracle)LIKE연산자에서 '%'와 '_'를 포함하는 단어 검색 방법

LIKE연산으로 '%'나 '_'가 들어간 문자를 검색하기 위해서는 ESCAPE를 사용해야 합니다.
'_'나 '%'앞에
ESCAPE로 특수 문자를 지정하면 검색할 수 있습니다.


1. '_' 가 있는 문자 검색

 SQL>SELECT   loc
         FROM   dept
         WHERE   loc like '%#_%' ESCAPE '#';
        
LOC
-------------
NEW_YORK      
  
 
위 문장에서 #대신 다른 문자가 와도 됩니다.
(아래같이 @같은 특수 문자나, A같은 아파벳이나, 9같은 숫자가 와도 똑같은 결과가 나오네요)

아래의 결과들을 한번 비교해 보시기 바랍니다.

SQL> SELECT   loc
         FROM   dept
         WHERE   loc like '%N%@_%' ESCAPE '@'
        
LOC
-------------
NEW_YORK   
     
 

 
SQL>SELECT loc
        FROM dept
        WHERE loc like '%_%';
        
LOC
-----------
NEW_YORK
DALLAS
CHICAGO
BOSTON




2, '%' 가 있는 문자 검색

'%'가 있는 문자도 '_'가 있는 문자 검색과 동일 합니다.

SQL>SELECT loc
        FROM dept
        WHERE loc like '%@%%' ESCAPE '@';
 
LOC
-------------
BOS%TON

 
 
SQL>SELECT loc
       FROM dept
       WHERE loc like '%%%';

LOC
-------------
NEW_YORK
DALLAS
CHICAGO
BOS%TON
2010. 9. 1. 13:37

(oracle)몇가지 tuning tip


출처 : 지식in

 

이 문제는 오라클만의 문제가 아니고 거의 모든 RDBMS 제품들에 대해 공통적인 문제입니다.

 

일단 SQL 튜닝에서는 모든 상황에 항상 맞는 것은 없습니다. 

즉, SQL 튜닝엔 왕도가 없다는 말입니다. 수학공식 외우듯이 외워서 튜닝을 하는 것은 아니며 그때 그때 데이터의 분포, 서버의 상태, 인덱스의 유무 및 SQL trace나 tkprof결과 등의 각종 참조가능한 수치들을 분석하여 튜닝방향을 정합니다.

상황에 따라 다른 모든 경우엔 가장 안 좋던 방법이 특정 경우엔 최적의 솔루션이 될 수 있습니다.

참고하세요...;

 

질문의 3가지 + @ 방법의 두드러진 특징만 구별할 수 있어도 판단에 많은 도움이 되겠지요.

A 집합에서 B집합의 데이터를 제외한 나머지만 구하는 방법은 질문의 3가지를 포함하여 상황에 따라 보통 크게 5가지정도를 주로 쓰게 됩니다. 하나씩 특징만 간단히 적겠습니다...자세한 내용은 직접 공부하세요...;

 

1. not in ...

SELECT * FROM A WHERE a.key not in (SELECT b.key FROM B)

형태의 구문이며, B쪽을 먼저 access하여 b.key로 a.key에 공급자역할을 하는 서브쿼리로 쓰고 싶을 때 주로 사용합니다.

 

2. not exists ...

SELECT * FROM A WHERE not exists (SELECT * FROM B WHERE b.key = a.key)

형태의 구문이며, A쪽을 먼저 access하고 나서 a의 각 row들을 not exists로 조사하여 filtering하는 처리를 할 때 주로 사용합니다. 즉, B를 access하기 전에 A쪽의 전체범위가 먼저 access됩니다.

이 때의 서브쿼리는 공급자가 아닌 확인자역할만 해 줄 수 있습니다.

 

3. minus ...

SELECT key, col1, col2 FROM A

MINUS

SELECT key, col1, col2 FROM B

형태의 구문이며, 테스트 해 보면 아시겠지만 MINUS는 특성 상 sort와 중복제거 수행을 동반합니다.

그러므로 가장 이해하기는 간단하나 대용량에서는 사용 시 주의해야 합니다.

A나 B집합의 access대상이 대량인 경우 대량의 sort와 중복제거가 발생하므로 이들 처리에 많은 시간이 소요될 수 있는 쿼리입니다.

 

4. Outer + Null Check ...

SELECT * FROM A, B WHERE A.key = B.Key(+) AND B.Key IS NULL

형태의 구문이며, 위의 not in이나 not exists가 주로 Nested Loop Join 또는 Nested Loop Anti Join 방법을 수행하는데 비해 대용량의 경우 Hash Join이나 Merge Join을 유도하여 성능을 보장받을 수 있는 방법입니다.

단, 각 DBMS 마다 A LEFT OUTER JOIN B ON ~ , (*)등으로 아우터조인에 대한 표현은 약간 씩 다릅니다.

 

5. UNION ALL + Group count 또는 count() over() 분석함수 이용등 ...

SELECT *

FROM(

    SELECT a.*

        , COUNT(DISTINCT gbn) OVER(PARTITION BY key) AS cnt

        , COUNT(DISTINCT DECODE(gbn, 'A', 1)) OVER(PARTITION BY key) AS a_cnt

    FROM(

        SELECT 'A' AS gbn, key, col1, col2 FROM A UNION ALL

        SELECT 'B' AS gbn, key, col1, col2 FROM B

        ) a

    )

WHERE cnt < 2 AND a_cnt = 1

형태의 구문이며, UNION ALL은 MINUS와 달리 sort나 중복제거를 하지 않고 별다른 조인도 없기 때문에 양쪽집합에 scan할 마땅한 인덱스가 없거나 하는 상황에서 위력을 발휘할 수 있는 솔루션입니다.

GROUP BY와 COUNT 함수로도 위의 의미를 그대로 만들 수 있습니다...분석함수나 통계함수를 지원하지 않는 DBMS들은 COUNT() OVER() 대신 GROUP BY / COUNT로 변경해야겠지요...;

 

이외에도 구현할 수 있는 방법들이야 더 있겠지만, 대부분의 상황들에서 위의 예시들이 주로 많이 쓰인다는 것을 거듭 밝힙니다.

2009. 2. 11. 16:16

[오라클] LOCK 문제를 일으키는 SQL 명령 찾기

LOCK 문제를 일으키는 SQL 명령 찾기
----------------------------------
* 다음 Query는 Lock과 관련된 트랜잭션을 출력해준다.

column username format a10
column sid format 999
column lock_type format a15
column MODE_HELD format a11
column MODE_REQUESTED format a10
column LOCK_ID1 format a8
column LOCK_ID2 format a8
select a.sid,
decode(a.type,
'MR', 'Media Recovery',
'RT', 'Redo Thread',
'UN', 'User Name',
'TX', 'Transaction',
'TM', 'DML',
'UL', 'PL/SQL User Lock',
'DX', 'Distributed Xaction',
'CF', 'Control File',
'IS', 'Instance State',
'FS', 'File Set',
'IR', 'Instance Recovery',
'ST', 'Disk Space Transaction',
'IR', 'Instance Recovery',
'ST', 'Disk Space Transaction',
'TS', 'Temp Segment',
'IV', 'Library Cache Invalidation',
'LS', 'Log Start or Switch',
'RW', 'Row Wait',
'SQ', 'Sequence Number',
'TE', 'Extend Table',
'TT', 'Temp Table',
a.type) lock_type,
decode(a.lmode,
0, 'None', /* Mon Lock equivalent */
1, 'Null', /* N */
2, 'Row-S (SS)', /* L */
3, 'Row-X (SX)', /* R */
3, 'Row-X (SX)', /* R */
4, 'Share', /* S */
5, 'S/Row-X (SSX)', /* C */
6, 'Exclusive', /* X */
to_char(a.lmode)) mode_held,
decode(a.request,
0, 'None', /* Mon Lock equivalent */
1, 'Null', /* N */
2, 'Row-S (SS)', /* L */
3, 'Row-X (SX)', /* R */
4, 'Share', /* S */
5, 'S/Row-X (SSX)', /* C */
6, 'Exclusive', /* X */
to_char(a.request)) mode_requested,
to_char(a.id1) lock_id1, to_char(a.id2) lock_id2
from v$lock a
where (id1,id2) in
(select b.id1, b.id2 from v$lock b where b.id1=a.id1 and
b.id2=a.id2 and b.request>0)
/


( 위의 Query를 실행시키면 다음과 같은 내용이 출력된다. )

SID LOCK_TYPE MODE_HELD MODE_REQUE LOCK_ID1 LOCK_ID2
--- --------------- ---------- ---------- -------- --------
5 Transaction Exclusive None 262172 90
6 Transaction None Exclusive 262172 90
9 Transaction None Exclusive 262172 90

SID 6과 9는 SID 5가 걸고 있는 Lock이 풀리기를 기다리고 있음을 알 수 있다.

* 다음 Query는 Lock과 관련된 테이블을 출력해 준다.

column username format a10
column lockwait format a10
column sql_text format a80
column object_owner format a14
column object format a15
select b.username username, c.sid sid, c.owner object_owner,
c.object object, b.lockwait, a.sql_text SQL
from v$sqltext a, v$session b, v$access c
where a.address=b.sql_address and
a.hash_value=b.sql_hash_value and
b.sid = c.sid and c.owner != 'SYS';
/

( 위의 Query를 실행하면 다음과 같은 결과가 출력된다.

USERNAME SID OBJECT_OWNER OBJECT LOCKWAIT
--------------- --- ------------- -------------- ----------
SQL
---------------------------------------------------------------
LTO2 6 LTO EMP C3D320F4
update lto.emp set empno =25 where empno=7788
LTO3 9 LTO EMP C3D320C8
delete from lto.emp where empno=7788
LTO 5 LTO DEPT
insert into lto.dept values (60,'PROGRAMMER','LOS ANGELOS')

여기서는 USERNAME에 나와있는 유저가 OBJECT에 나와있는 테이블을 수정하려고
함을 나타낸다. LT02,LT03는 LT0가 Commit,Rollback 하기를 기다리고 있음을 알
수 있다. 하지만 여기에는 가장 최근의 DML 명령 하나만 나와있기 때문에 여기
나온 명령이 반드시 Lock을 걸고 있는 명령이라고 단정지을 수는 없다.

관련된 프로세스
---------------
* 다음 Query를 실행해 보면 프로세스와 관련된 정보를 얻을 수 있다.

column "ORACLE USER" format a11
column SERIAL# format 9999999
column "OS USER" format a8
select substr(s.username,1,11) "ORACLE USER", p.pid "PROCESS ID",
s.sid "SESSION ID", s.serial#, osuser "OS USER",
p.spid "PROC SPID",s.process "SESS SPID", s.lockwait "LOCK WAIT"
from v$process p, v$session s, v$access a
where a.sid=s.sid and
p.addr=s.paddr and
s.username != 'SYS'
/

* 위의 Query를 실행하면 다음과 같은 결과가 출력된다.

ORACLE PROCESS SESSION SERIAL# OS USER PROC SESS LOCKWT 
USER ID ID SPID SPID 
------ ------- ------- ------- ------- ---- ---- ------
LTO 19 5 31 usupport 17312 17309
LTO2 25 6 43 usupport 17313 17310 C3D320F4
LTO3 26 9 1 usupport 17314 17311 C3D320D8

이 때는 다음과 같이 조치한다.

1. LTO에게 Commit,Rollback 할 것을 요구한다.
2. SQLDBA>ALTER SYSTEM KILL SESSION '5,31';
3. %kill -9 17309 (유닉스상의 Shadown Process)
stop/id= (PROC SPID=SESS SPID on vms running single task)

여기서 SYS 유저는 제외시켰는데 필요하다면 Query의 Where 조건에서
s.username != 'SYS' 부분을 삭제하면 된다.

CATBLOCK.SQL & UTLLOCKT.SQL
---------------------------
$ORACLE_HOME/rdbms/admin 디렉토리에 있는 스크립트 가운데 catblock.sql과
utlockt.sql을 사용하여서 Lock 상황을 쉽게 파악할 수 있다. 이들은 다음과
같이 실행한다.

%cd $ORACLE_HOME/rdbms/admin
%sqldba lmode=y
SQLDBA>connect internal
SQLDBA>@catblock
SQLDBA>@catproc

결과는 다음 Query 문으로 알아 본다.

column waiting_session format a8
select lpad(' ',3*(level-1)) || waiting_session,
lock_type,
mode_requested,
mode_held,
lock_id1,
lock_id1,
lock_id2
from lock_holders
connect by prior waiting_session = holding_session
start with holding_session is null;

위의 Query에 의한 출력은 다음과 같다.

WAITING_ LOCK_TYPE MODE_REQUE MODE_HELD LOCK_ID1 LOCK_ID2
-------- ----------------- ---------- ---------- ---------- ----------
5 None
6 Transaction Exclusive Exclusive 262172 90
9 Transaction Exclusive Exclusive 262172 90

여기서 Session 6, Session 9가 Session 5를 기다리고 있음을 알 수 있다.

Lock & Hanging 문제를 추정하는 방법
-----------------------------------
프로그램 상에서 어느 부분이 Lock, Hanging 문제를 일으키는지 알아내기가
여의치 않을때 다음과 같은 방법을 사용해 보기 바란다.

1. init.ora의 sql_trace=ture로 세팅하면 연관된 SQL 명령이 출력될
것이다.
2. OS 상에서도 Process 상태를 점검하여 본다.
3. OS 상의 Debugging 기능을 사용하거나 만약 가능하다면 oradbx를 사용한다.
Platform 에 따라서 없을 수도 있다.
4. 여러가지 Monitoring 방법과 Locking/Blocking 스크립트를 이용한다.

PROCESS STACKS 확인
-------------------
때로는 Hanging Process나 리소스를 점유하고 있는 Process의 Process Stack을
점검해 보는 것이 문제 해결에 도움이 되는 경우가 있다.

1. OS Debugging 프로그램을 이용하여 Hangup 되기 전의 마지막 Call을 확인
한다.

ex)%truss -p 

2. oradbx(오라클 debugging 프로그램)은 Support,Development 시에만 사용
된다.

select substr(s.username,1,11) "ORACLE USER" ,
p.pid "PROCESS ID", s.sid "SESSION ID", s.serial#,
osuser "OS USER", p.spid "PROC SPID"
from v$session s, v$access a
where a.sid=s.sid and
p.addr=s.paddr and
s.username != 'SYS'
/

위의 Query를 실행하면 다음과 같은 결과가 출력된다.

ORACLE PROCESS SESSION SERIAL# OS USER PROC SESS LOCKWT 
USER ID ID SPID SPID 
------ ------- ------- ------- ------- ---- ---- ------ 
LTO 19 5 31 usupport 17312 17309 
LTO2 25 6 43 usupport 17313 17310 C3D320F4 
LTO3 26 9 1 usupport 17314 17311 C3D320D8

만약 oradbx가 없다면 다음과 같이 해서 만들어 준다.

%cd $ORACLE_HOME/rdbms/lib
%make -f oracle.mk oradbx

LTO Process가 무엇을 하고 있는지 알고 싶으면 Process Stack을 보면 알수
있다.

ps -ef | grep 17312
usupport 17312 17309 0 Sep 15 ? 0:00 oracleV713(DESCRIPTION=(LOCAL=YE
type 
debug 17312 (이 유저의 oracle shadow process)
dump stack
dump procstat

위에서 생성된 트레이스 화일(user_dump_dest 에 생성됨)을 이용하면 Hanging
문제를 해결하는데 큰 도움이 된다.

자주 발생하는 LOCK 문제들
-------------------------
1. Parent-Child 관계로 묶인 테이블에서 Child 테이블에 Index가 없는 상황
에서 Child 테이블을 수정하게 되면 Parent테이블에 TABLE LEVEL SHARE
LOCK이 걸리게 되어서 Parent 테이블에 대한 모든 Update가 금지된다.
2. 블럭의 PCTFREE가 매우 작다면 한 블럭에 여러개의 레코드가 들어 있기
때문에 한 블럭에 과도한 트랜잭션이 들어와서 블럭의 Transaction Layer가
Release 되기를 기다리게 되는 경우도 있다.

Ex)
create table test (a number) initrans 1 maxtrans 1;

SYSTEM: insert into test values (5); /* completed */
SCOTT: insert into SYSTEM.test values (10); /* Scott waits */

SID OWNER LOCK_TYPE MODE_HELD MODE_REQUE LOCK_ID1 LOCK_ID2
---- ----- ------------- ----------- ---------- -------- --------
7 System Transaction Exclusive None 196639 54
10 Scott Transaction None Share 196639 54



[출처] http://www.ihelpers.co.kr/programming/tipntech.php?CMD=view&TYPE=6&KEY=&SC=S&&CC=&PAGE=1&IDX=408