1. 자료의 조회

2. SELECT 에서의 산술 연산#

note
  • + 더하기
  • - 빼기
  • * 곱하기
  • / 나누기
  • () 괄호
SELECT EMP_NAME,
SALARY / 18,
SALERY * 2 / 18
FROM TEMP;

3. NULL 의 사용#

tip

DML 을 이용하여 자료를 다룰 때는 항상 NULL 을 염두에 두고 다뤄야 한다.

  • NUMBER 형 자료를 NULL 과 연산(+, -, , /)* 하면 결과는 항상 NULL 이다.
NULL 이 포함될 우려가 있는 컬럼을 이용할 때는 항상 NVL 함수를 이용하여 값을 치환 시켜 사용하는 습관을 들여야 한다.
  • 숫자형 컬럼 → 적절한 숫자로 치환
  • 문자형 컬럼 → ''(스페이스) 나 다른 특정 문자값으로 치환
NULL 의 비교
NULL 의 비교일반 값의 비교
같은지 비교WHERE A IS NULLWHERE A = '1'
다른지 비교WHERE A IS NOT NULLWHERE A <> '1

A = NULL 또는 A <> NULL

  • 에러가 발생하지는 않지만 의도한 바와 전혀 다른 결과가 나올 수 있기 때문에 절대로 사용하면 안된다
  • ?? result??
SELECT EMP_NAME,
NVL(HOBBY, '없음')
FROM TEMP;
EMP_NAME | NVL(HOBBY, '없음)'
---------|-------------------
...
SELECT EMP_NAME
FROM TEMP
WHERE NVL(HOBBY, '등산') = '등산';

4. 컬럼 Alias 와 테이블 Alias#

Alias 를 사용하는 이유

  1. 편리해서.. how??
  2. 반드시 사용해야 하는 경우

사용법

SELECT EMP_ID SABUN, -- 1.
EMP_NAME AS NAME -- 2.
FROM TEMP
| SABUN | NAME |
|--------|-------|
|....
SELECT A.EMP_ID,
A.DEPT_CODE,
B.DEPT_NAME
FROM TEMP A,
TDEPT B
WEHRE A.DEPT_CODE = B.DEPT_CODE;
Alias 를 반드시 사용해야 할 때
  • 셀프 조인
  • ROWNUM 사용
  • TREE 구조의 전개
  • LEVEL 값 사용

5. Concatenation#

두 개 이상의 문자열을 연결하여 하나의 문자열을 만들어 내는 방법

  1. Concat 함수
  2. || 합성 연산자
    SELECT EMP_NAME || '(' || LEV || ')' 성명
    FROM TEMP;
    | 성명 |
    |----------|
    |김길동(부장) |
  • 작은 따옴표로 묶어주는 경우
    SELECT EMP_NAME || '''' || LEV || '''' 성명
    FROM TEMP;
    | 성명 |
    |------------|
    | 김길동'부장' |
    1. 문자열임을 의미하는 작은 따옴표가 문자열을 감싸야 하는 규칙 (2개)
    2. 작은 따옴표를 문자열로 표현하기 위해 '' (2개) 를 사용해야 하는 규칙
    • 4개 필요

많은 OBJECTS 를 한번에 DROP(ALTER) 시키는 명령문#

USER_OBJECTS 에는 현재 LOG IN 한 USER 가 소유하고 있는 모든 OBJECT 가 나타난다.

Column Name Null ? | Type
------------------------------------
OBJECT_NAME | VARCHAR2(128)
OBJECT_ID | NUMBER
OBJECT_TYPE | VARCHAR2(13)
CREATED | DATE
LAST_DDL_TIME | DATE
TIMESTAMP | VARCHAR2(75)
STATUS

OBJECT 의 명칭과, OBJECT 의 타입 (TABLE, PROCEDURE, FUNCTION, VIEW, TRIGGER) 에 대한 정보이다.

SELECT OBJECT_NAME, OBJECT_TYPE
FROM USER_OBJECTS

이 정보를 이용하여, 현재 유저에 존재하는 모든 OBJECTS 를 DROP 시키는 명령문을 만들 것이다.

SELECT 'DROP' || OBJECT_TYPE || ' ' || OBJECT_NAME || ';'
FROM USER_OBJECT;

QUERY 의 결과를 출력하여 EXCEL 파일 만들기#

탭을 컬럼 중간마다 합성연산자로 묶어주면 탭을 분리자로 가지는 파일을 만들 수 있다

  • 이것을 그대로 엑셀에서 열거나
  • 결과 COPY 후 EXCEL 에 붙여넣기 하면 된다.
SELECT EMP_ID || '' || -- 작은 따옴표 사이는 탭이다.
EMP_NAME || ' ' || -- 작은 따옴표 사이는 탭이다.
LEV
FROM TEMP;

문자와 숫자의 자동변환#

  • 숫자형 → 문자형 NUMBER 형과 VARCHAR2 형을 합성 연산자로 묶으면

    • 결과는 마치 문자열을 묶은 것처럼 나온다.
    • NUMBER 형이 문자형으로 변형이 일어난다.
      SELECT EMP_ID || EMP_NAME
      FROM TEMP

  • 숫자형 → 문자형 변환

    1. TO_CHAR 함수
    2. SUBSTR 함수
      ```sql
      SELECT EMP_ID, EMP_NAME
      FROM TEMP
      WHERE SUBSTR(EMP_ID, 1, 4) + 1 = 1998;
      ```
      - EMP_ID(숫자형이) 문자형 함수에서 문자열로 변형된다.
      - '+ 1998' 을 만나 다시 숫자형으로 변형이 일어난다.
      tip

      튜닝을 공부하다 보면 중요하게 다루어지는 부분이지만 이런 현상이 있다는 정도만 알고 넘어가겠다.

6. 질의결과의 제한#

오라클의 RULE BASE OPTIMIZER MODE

오라클의 RULE BASE OPTIMIZER MODE 에서 인덱스를 이용하고자 할 때,
WHERE 절을 이용하여 INDEX 의 사용을 유도하거나 제한할 수 있다.

아래 (1) (2) 의 결과값은 같지만 RECORD 들의 순서가 다르다.

(1) 입력 순서대로 출력

SELECT EMP_ID, EMP_NAME
FROM TEMP;

(2) 사번 순으로 정렬되어 출력

SELECT EMP_ID, EMP_NAME
FROM TEMP
WHERE EMP_ID > 0; -- 전체 조건을 다 만족시키는 조건
note

조건 절에 기술된 컬럼이 INDEX 가 존재하면
해당 INDEX 가 실행계획에 포한된다.

예외 사항

  • 컬럼을 함수로 가공하는 경우
  • NOT 연산자가 쓰이는 경우

OPTIMIZER#

  • DML (SELECT, DELETE, UPDATE, INSERT) 을 수행할 때 OPTIMIZER 가 관여한다.
  • DML 을 가장 효율적으로 처리할 수 있는 최적화 경로를 찾아준다.

OPTIMIZER 와 실행계획#

OPTIMIZER 는 많은 요인을 고려하여 실행계획을 작성한다.

실행 계획에 의해 DML 이 수행되고, 수행 경로를 PLAN 을 이용해 볼 수 있다.

실행 계획

  • 어떤 테이블을 먼저 읽을 것인가?
  • 테이블을 읽을 때 인덱스를 이용할 것인가?
  • 어떤 인덱스를 사용할 것인가?
  • 어떤 방식으로 조인할 것인가?

PLAN#

DML 이 어떤 경로를 통해 데이터베이스를 ACCESS 했는지를 보여주는 일종의 순서도

  1. SQL 의 PLAN 작성

    EXPLAIN PLAN SET STATEMENT_ID = '임의지정' FOR
    SELECT EMP_ID, EMP_NAME
    FROM TEMP
    WHERE EMP_ID > 0;

  2. 실행계획 검사 image

INDEX 를 조회할 수 있는 DATA DICTIONARY#

DICTIONARY VIEW

오라클의 SYS USER 가 소유하고 있는 데이터베이스와 관련된 정보를 제공해주는 READ-ONLY VIEW 이다.

실행계획을 살펴보면 INDEX 이름이 나온다.

인덱스의 정보를 탐색할 수 있다.

  • 어떤 테이블에 대해 생성 되었는지
  • 어떤 컬럼으로 구성되어 있는지
  • USER_INDEXES
    • 테이블이 소유하고 있는 INDEX 가 어떤 것이 있는지 알아볼 때 편하다.
      SELECT INDEX_NAME
      FROM USER_IDNEXES
      WHERE TABLE_NAME = 'TEMP';
  • USER_IND_COLUMNS
    • 인덱스가 어떤 컬럼들로 구성 되어 있는가를 확인할 때 주로 사용한다.
SELECT INDEX_NAME, COLUMN_NAME
FROM USER_IND_COLUMNS
WHERE TABLE_NAME = 'TEMP';

6-2 ORDER BY#

ORDER BY 의 옵션

  • ASCENDING(default) / DESCENDING

ORDER BY 컬럼명 대신 컬럼 순서 기술하기

SELECT LEV, EMP_ID, EMP_NAME
FROM TEMP
ORDER BY 1, 2, DESC;

RECORD 정렬순서를 결정하는 것#

  • ORDER BY
  • QUERY 시 내부적으로 SORT 가 일어나는 경우
  • INDEX 가 사용되는 경우

자료가 DB 의 BLOCK 상에 쌓이는 원리#

TABLE CREATE 시 지정하는 PCTFREE 와 PCTUSED 에 의해 결정된다.

  • RECORD 가 테이블에 저장될 때는 순서가 정해져 있지 않다.
  • 입력한 순서대로 저장되는 경우
    • 자료의 양이 적어서 DB 상의 동일 BLOCK 상에 쌓임
    • 삭제/변경이 없어 운좋게 입력순서대로 쌓임

DB BLOCK 의 구조#

오라클 데이터베이스의

  • 물리적인 저장 단위
  • 입출력시의 최소 단위

HEADER#

블록의 주소와 같은 BLOCK 에 대한 일반적인 정보

TABLE DIRECTORY#

블록안에 존재하는 ROW 를 소유하는 테이블에 대한 정보

ROW DIRECTORY#

각 ROW 의 주소 등, 블록안에 존재하는 ROW 들에 대한 실제 정보

FREE SPACE#

필요로 하게 되는 추가적인 공간을 위해 확보되는 영역

  • NULL 컬럼 값이 NOT NULL 로 UPDATE 될 때
  • ROW 의 변경
  • 새로운 ROW 의 삽입

ROW DATA#

테이블이나 인덱스의 데이터를 물리적으로 저장하는 영역

PCTUSED 와 PCTFREE#

자료가 DB 의 BLOCK 상에 쌓이는 원리는,
TABLE CREATE 시 지정하는 PCTFREE 와 PCTUSED 에 의해 결정된다.

PCTFREE#

이미 블록에 쓰여진 ROW 의 UPDATE 나 INSERT 를 위하여 예약되는 공간.

PCTFREE 20

  • 블록 공간 중 20% 를 PCTFREE 로 남게 하겠다.

PCTUSED#

PCTFFREE 에서 지정한 영역만큼만 FREE SPACE 가 남게 되면,
오라클은 더이상 해당 블록에 새로운 ROW 를 삽입하지 않는다.

사용공간이 ROW 의 삭제 등으로 인하여 PCTUSED 에서 지정한 값 아래로 떨어지게 되면,
그 때 다시 BLOCK 에 새로운 ROW 가 삽입될 수 있다.

7. 연산자#

WHERE 조건절에서 자료의 검색 시 조건을 준다.

'=' 같은지를 묻는다.
WHERE EMP_ID = 19970824
'<>' 다른지를 묻는다
WHERE EMP_ID <> 19970824
'!=' 다른지를 묻는다
WHERE EMP_ID != 19970824
'NOT' IN, BETWEEN, LIKE, ANY, ALL, EXISTS 등과 쓰여 연산의 결과를 부정한다.
WEHRE EMP_ID NOT IN (19970824, 19970825)
'LIKE' 값의 일부를 주어주고 검색할 때 사용한다
-- 2번째 자리에 A 가 들어가는 ROW 검색
SELECT EMP_ID, EMP_NAME, DEPT_CODE
FROM TEMP
WHERE DEPT_CODE LIKE '_A____';
-- BETWEEN 연산자를 이용하여 성명 (EMP_NAME) 이 'ㄱ' 으로 시작되는 사람 조회
SELECT FROM EMP_ID, EMP_NAME
FROM TEMP
WHERE EMP_NAME BETWEEN '가' AND '나'
'IN' OR 조건으로 연결된 조건을 한번에 기술해 줄 수 있는 기능 제공
SELECT EMP_ID, EMP_NAME
FROM TEMP
WHERE EMP_NAME IN ('홍길동', '김길동');

OPTIMZER 에 의해 변환되는 연산자#

LIKE <br/> (%기호 미포함시)
-- before
EMP_NAME LIKE '홍길동'
-- afer
EMP_NAME = '홍길동'
ANY
-- before
EMP_NAME = ANY('홍길동, '김길동')
-- after
EMP_NAME = '홍길동'
OR EMP_NAME = '김길동'
ANY(SUBQUERY)
-- before
A.SALARY > ANY(SELECT SALARY FROM TEMP WHERE LEV = '사원')
-- after
EXISTS (
SELECT SALARY
FROM TEMP
WEHERE LEV = '사원'
AND A.SALARY > SALARY
)
ALL
-- before
EMP_NAME > ALL('김길동', '배뱅이')
-- after
EMP_NAME > '김길동'
AND EMP_NAME > '배뱅이'
ALL(SUBQUERY)
-- before
A.SALARY > ALL(
SELECT SALARY FROM TEMP WHERE LEV = '사원'
)
-- after
NOT EXISTS (
SELECT SALARY FROM TEMP WHERE LEV = '사원'
AND A.SALARY <= SALARY
)
BETWEEN
-- before
EMP_ID BETWEEN 19970001 AND 19979999
-- after
EMP_ID >= 19970001 AND EMP_ID <= 19979999
NOT
-- before
NOT SALARY < 5000000
-- after
SALARY >= 5000000
IN
-- before
EMP_NAME IN('홍길동', '김길동')
-- after
EMP_NAME = '홍길동' OR EMP_NAME = '김길동'

8. GROUP BY 와 HAVING#

GROUP BY#

GROUP BY

특정 컬럼이나 값을 기준으로 ROW 를 묶어서 자료를 다룰 때

  • 그룹함수 (MAX, MIN, AVG, SUM, COUNT) 을 적용할 때
직급 별로 최고 연봉 액
SELECT LEV, MAX(SALARY) MAX_SAL
FROM TEMP
GROUP BY LEV; -- ORDER BY 를 할 때, QUERY 내부적으로 SORT 가 일어난다

LEV 값이 가나다 순으로 정렬되어 있을 것이다.

GROUP BY 수행 과정

검색 대상이 되는 모든 자료를 GROUP BY 에 기술된 컬럼 순서로 SORT 영역에서 SORT 를 먼저 한다.

GROUP BY 절 없이 컬럼에 함수 값 적용
SELECT MAX(SALARY)
FROM TEMP;

GROUP BY 문장이 기술되지 않아도 GROUP BY 함수가 적용된다.

  • 전체 ROW 를 하나의 단위로 보아 GROUP BY 절을 기술하지 않음
DISTINCT 해당 컬럼의 UNIQUE 한 값 선택
SELECT DISTINCT LEV
FROM TEMP;
-- same as
SELECT LEV
FROM TEMP
GROUP BY LEV;
부서장 중 사번이 가장 빠른 사람이 근무하는 부서
-- 합성 연산 후, MIN 함수 사용
SELECT MIN(BOSS_ID || DEPT_CODE)
FROM TDEPT
-- MIN(BOSS_ID || DEPT_CODE)
-- -------------------------
-- 19930301BA0001

HAVING#

HAVING

GROUP BY 된 결과를 제한 하고자 할 때 사용한다.

직급별로, 평균 연봉이 5천만원 이상인 경우의 직급과, 평균연봉
SELECT LEV, AVG(SALARY)
FROM TEMP
GROUP BY LEV
HAVING AVG(SALARY) > 5000000;
직급별로, 사번이 제일 늦은 사람을 구하고, 그 결과 내에서 사번이 1997 로 시작하는 결과
SELECT LEV, MAX(EMP_ID)
FROM TEMP
GROUP BY LEV
HAVING MAX(EMP_ID) LIKE '1997%'