SELECT 시에는 꼭 필요한 column 만 불러오기

많은 필드를 불러 올수록 DB는 더 많은 로드를 부담하게 되기 때문에 꼭 필요한 열만 물러오도록 한다.

  -- WORST
  SELECT * FROM TABLE;
  -- BETTER
  SELECT COLUMN1, COLUMN2, COLUMN4, COLUMN8 FROM TABLE;

WHERE절에서 연산을 걸지 않는다.

연산이 들어가게 되면 TABLE FULL SCAN을 하면서 모든 값을 탐색, 계산 한 뒤 조건 충족 여부를 판단하기 때문에 좋지 않다.

  -- WORST
  SELECT COLUMN1, COLUMN4
  FROM TABLE
  WHERE FLOOR(COLUMN4) = 2;
  -- BETTER
  SELECT COLUMN1, COLUMN4
  FROM TABLE
  WHERE COLUMN4 BETWEEN 4 AND 5;

LIKE 조회시 와일드카드 % 는 가급적 뒤에만 붙이자

COLUMN6 LIKE %DF 는 TABLE FULL SCAN을 유발한다.
COLUMN5 IN ('ASDF', 'ERDF'), COLUMN5 = 'ASDF' OR COLUMN5 = 'ERDF' 같은 형태가 낫다.

SELECT DISTINCT, UNION ALL 과 같이 중복을 제거하는 연산은 자제한다

중복을 제거하는 연산은 시간이 많이 걸린다. 불가피하게 사용해야 할 경우 EXISTS, GROUP BY를 활용하는게 낫다.
DISTINCT는 원하는 컬럼에 대해서 중복을 제거하는 것이 아니라 SELECT 해온 모든 ROW에서 중복을 제거하므로 속도가 느려진다.

같은 내용의 조건이라면 GROUP BY 연산의 HAVING 보다는 WHERE 절을 사용하는 것이 좋다

쿼리 실행 순서에서 WHERE 절이 HAVING 절 보다 먼저 실행된다. 따라서 WHERE 절로 미리 데이터를 작게 만들면 GROUP BY 절에서 다뤄야 하는 데이터 크기가 작기 때문에 효율적인 연산이 가능하다.

VIEW VS MVIEW (MATERIALIZED VIEW)

뷰는 질의 할때마다 해당 쿼리를 재 실행하는 것과 같음. 속도가 느림. 다만 데이터는 LIVE 함.
MVIEW는 세팅을 어떻게 하느냐에 따라 질의 할 쿼리를 재사용하여 가져옴. 스냅샷 처럼 이전에 만들어 놓은 엠뷰 테이블에서 데이터를 가져옴.
비용이 많이 들어가고 데이터가 고정적인 경우 엠뷰를 만들어서 사용하는게 좋음. 인덱스도 생성 가능.
리프레시 타임이 많이 드는 경우 데이터가 LIVE하지 않은 문제가 있음.

CHAR VS VARCHAR

4byte 이하는 CHAR, 그 이상은 VARCHAR가 나음.

• CHAR : 고정 길이 문자열
• VARCHAR : 가변 길이 문자열

BLOB VS TEXT

많은 양의 데이터 저장, 디폴트 값 지정 안됨, 문자열 뒷부분 공백 제거 안되는 공통점이 있음.
차이점으로 BLOB은 대소문자를 구분, TEXT는 구분 안함.

PRIMARY KEY, UNIQUE KEY

PK와 Unique Key는 자동으로 인덱스가 생성됨.

쿼리 실행 순서

SUB-QUERY -> MAIN-QUERY

INNER JOIN시 테이블 배치 순서

3개 이상의 테이블을 INNER JOIN을 할 때는 크기가 가장 큰 테이블을 FROM 절에 배치하고 INNER JOIN 절에 남은 테이블을 작은 순서대로 배치하는 것이 좋다.
테이블 한두개차이는 상관 없지만 많은 테이블을 JOIN 할 경우 JOIN의 경우의 수가 생기고 OPTIMIZER가 PLANNING을 하면서 비용이 증가된다.

실행 계획 type이 index라면 한 번 더 살펴보기

EXPLAIN으로 실행 계획을 확인 했을 때 type이 index인 부분이 있다. where 절 조건문에서 사용하는 테이블의 컬럼에 인덱스가 제대로 걸려있지 않기 때문에 TABLE FULL SCAN을 한다.
해당 테이블에 index를 걸고 다시 실행 계획을 INDEX RANGE SCAN으로 변경되었고 실제 실행 속도가 1/4로 줄었다. 아래는 type의 종류로 성능이 낮은 것 부터 높은 순으로 정렬했다.

• ALL : 테이블을 처음부터 끝까지 탐색하여 데이터를 찾음 (TABLE FULL SCAN)
• INDEX : 인덱스를 처음부터 끝까지 탐색하여 데이터를 찾는 방식 (INDEX FULL SCAN)
• RANGE : 특정 범위 내에서 인덱스를 사용하여 원하는 데이터를 추출, 데이터가 방대하지 않다면 준수함. (INDEX RANGE SCAN)
• REF : 조인 할 때 Primary Key 혹은 Unique Key 가 아닌 Key로 매칭 한 경우
• EQ_REF : 조인 할 때 Primary Key로 매칭

-- 락 걸린 테이블 확인
SELECT
    DO.OBJECT_NAME
  , DO.OWNER
  , DO.OBJECT_TYPE
  , DO.OWNER
  , VO.XIDUSN
  , VO.SESSION_ID
  , VO.LOCKED_MODE
FROM
    V$LOCKED_OBJECT VO
  , DBA_OBJECTS DO
WHERE VO.OBJECT_ID = DO.OBJECT_ID
;


-- 해당 테이블에 락이 걸렸는지 확인
SELECT
  A.SID
  , A.SERIAL#
  , B.TYPE
  , C.OBJECT_NAME
FROM
    V$SESSION A
  , V$LOCK B
  , DBA_OBJECTS C
WHERE A.SID = B.SID
  AND B.ID1 = C.OBJECT_ID
  AND B.TYPE='TM'
  AND C.OBJECT_NAME IN (${위 쿼리에서 가져온 OBJECT_NAME});


-- 락 발생 사용자, SQL, OBJECT 조회
SELECT
  DISTINCT X.SESSION_ID
  , A.SERIAL#
  , D.OBJECT_NAME
  , A.MACHINE
  , A.TERMINAL
  , A.PROGRAM
  , B.ADDRESS
  , B.PIECE
  , B.SQL_TEXT
FROM
    V$LOCKED_OBJECT X
  , V$SESSION A
  , V$SQLTEXT B
  , DBA_OBJECTS D
WHERE X.SESSION_ID = A.SID
  AND X.OBJECT_ID = D.OBJECT_ID
  AND A.SQL_ADDRESS = B.ADDRESS 
ORDER BY B.ADDRESS, B.PIECE;


-- 현재 접속자의 SQL 확인 
SELECT
    DISTINCT A.SID
  , A.SERIAL#
  , A.MACHINE
  , A.TERMINAL
  , A.PROGRAM
  , B.ADDRESS
  , B.PIECE
  , B.SQL_TEXT
FROM
    V$SESSION A
  , V$SQLTEXT B
WHERE A.SQL_ADDRESS = B.ADDRESS
ORDER BY A.SID, A.SERIAL#, B.ADDRESS, B.PIECE;


-- 락 세션 죽이기위한 sid 와 serial
SELECT
    A.SID
  , A.SERIAL#
  , A.SID || ', ' || A.SERIAL#
FROM
    V$SESSION A
  , V$LOCK B
  , DBA_OBJECTS C
WHERE A.SID = B.SID
  AND B.ID1 = C.OBJECT_ID
  AND B.TYPE = 'TM'
-- AND C.OBJECT_NAME IN ('${락 걸린 테이블 명}');


-- 해당 세선 kill
SELECT
    DISTINCT X.SESSION_ID
  , A.SERIAL#
  , D.OBJECT_NAME
  , A.MACHINE
  , A.TERMINAL
  , A.PROGRAM
  , A.LOGON_TIME
  , 'ALTER SYSTEM KILL SESSION '''||A.SID||', '||A.SERIAL#||''';'
FROM
    GV$LOCKED_OBJECT X
  , GV$SESSION A
  , DBA_OBJECTS D
WHERE X.SESSION_ID = A.SID
  AND X.OBJECT_ID = D.OBJECT_ID
ORDER BY LOGON_TIME
;


-- 최근 실행된 쿼리 찾기
SELECT 
    SQL_FULLTEXT
  , SQL_ID
  , PARSING_SCHEMA_NAME
  , SERVICE
  , MODULE
  , FIRST_LOAD_TIME
  , LAST_LOAD_TIME
FROM V$SQL
WHERE 1 = 1
  AND PARSING_SCHEMA_NAME = '${스키마 명}'
  AND SERVICE = '${서비스 명}'
ORDER BY LAST_LOAD_TIME DESC
;


-- 최근 실행된 쿼리에 바인팅 변수 찾기
SELECT
    NAME
  , POSITION
  , DATATYPE_STRING
  , VALUE_STRING 
FROM V$SQL_BIND_CAPTURE
WHERE 1=1
  AND SQL_ID = '${위 쿼리에서 가져온 SQL_ID}'
;

SELECT * FROM V$SQL;

SELECT 
  last_active_time
  ,parsing_schema_name
  ,sql_text 
FROM v$sqlarea
WHERE 
  parsing_schema_name <> 'SYS'
  AND parsing_schema_name <> 'SYSMAN'
  AND parsing_schema_name <> 'DBSNMP'
  AND parsing_schema_name <> 'MDSYS'
  AND parsing_schema_name <> 'EXFSYS'
ORDER BY 
  last_active_time DESC;

안타깝게도 어떤 쿼리가 락을 걸었는지는 알기 쉽지 않다.

특히나 Oracle DB S/W update를 주기적으로 하지 않는다면 유지보수가 쉽지 않다.

인덱스 리빌드

DB의 쿼리 수행 속도 저하의 문제 중 하나는 쿼리 튜닝이 문제일 수도 있지만, 인덱스의 밸런스가 깨졌을 경우의 가능성도 있다.
인덱스 밸런스가 깨졌다는게 무슨 의미냐 하면,
DB는 ArrayList와 트리를 합쳐 놓은 구조와 비슷한 B+트리 구조인데, 트리 아래의 데이터들이 무작위로 수정되거나 삭제 되었을 경우 트리 구조가 불균형을 이루게 된다.

이런 경우 인덱스를 재구성 해주면 쿼리 수행 속도를 향상 시킬 수 있다.
(물론 튜닝이 잘 된 쿼리를 짜는게 중요하다.)

오라클의 인덱스 재설정 쿼리는 ALTER INDEX 인덱스명 REBUILD;이고,
인덱스 명을 조회하는 쿼리는 SELECT INDEX_NAME FROM USER_INDEXED;이다.
그리고 인덱스의 데이터 타입이 LOB이라면 인덱스 리빌드는 불가능 하다.

따라서 이 두 쿼리를 조합하고, 조건을 추가하면 리빌드 하는 쿼리는 다음과 같다.

SELECT 'ALTER INDEX ' || INDEX_NAME || ' REBUILD ;'
FROM USER_INDEXES
WHERE INDEX_TYPE != 'LOB'
;

경험상 CUBRID나 TIBERO 도 동일했던 것 같다.

참고 : https://docs.oracle.com/database/121/SPATL/alter-index-rebuild.htm#SPATL1017

FUNCTION

DB에서 제공하는 미리 정의 된 기능.

• 데이터 값을 계산하거나 조작. (단일 행 함수)
• 열의 데이터 타입을 변환.
• 행의 그룹에 대해 계산하거나 요약. (그룹 함수)

DUAL 테이블

쿼리문의 수행 결과가 하나의 ROW로 출력되기 위한 DUMMY 컬럼만 갖고 있음.
함수를 위한 임시 테이블 같은 개념.

단일 행 함수 : 데이터 값을 계산 혹은 조작

• 각 행에 대해 수행.
• 데이터 타입에 맞는 함수를 사용 해야 함.
• 행렬로 하나의 결과를 반환.
• SELECT, WHERE, ORDER BY 절 등에서 사용 가능.
• 함수 속의 함수로 중첩해서 사용 가능.

단일 행 함수의 종류.

문자 처리 함수

숫자 처리 함수

날짜 처리 함수

날짜 연산

SELECT
  TO_CHAR( SYSDATE, 'YY/MM/DD/HH24:MI' ),
  SYSDATE + 1,
  SYSDATE - 1,
  (SYSDATE + 1) - (SYSDATE - 7),
  SYSDATE + 13 / 24
FROM
  DUAL;

날짜 처리 함수

MONTH_BETWEEN 함수 외에는 모두 날짜 타입을 반환함.

MONTHS_BETWEEN

두 날짜 사이의 개월 수 계산

  MONTHS_BETWEEN( 날짜, 날짜 )

SELECT
  SYSDATE, hire_date, MONTHS_BETWEEN( SYSDATE, hire_date )
FROM
  employees
WHERE
  department_id = 100;

ADD_MONTHS

월에 날짜 더하기

  ADD_MONTHS( 날짜, 숫자 )

SELECT
  hire_date,
  ADD_MONTHS( hire_date, 3),
  ADD_MONTHS( hire_date, -3)
FROM
  employees
WHERE
  department_id BETWEEN 100 AND 106; 

NEXT_DAY

돌아오는 요일의 날짜 개산하기

  NEXT_DAY( 날자, '요일' or 숫자 )

SELECT
  hire_date,
  NEXT_DAY( hire_date, 'Friday'),
  NEXT_DAY( hire_date, 6)
FROM
  employees
WHERE
  department_id BETWEEN 100 AND 106; 

LAST_DAY

돌아오는 월의 마지막 날짜 계산하기

  LAST_DAY( 날짜 )

SELECT
  hire_date,
  LAST_DAY( hire_date )
FROM
  employees
WHERE
  department_id BETWEEN 100 AND 106; 

ROUND, TRUNC

날짜를 반올림 혹은 절삭

  ROUND or TRUNC ( 날짜, 지정 값[MONTH, YEAR등] )

SELECT
  hire_date,
  ROUND( hire_date, 'MONTH' ),
  ROUND( hire_date, 'YEAR' ),
  TRUNC( hire_date, 'MONTH' ),
  TRUNC( hire_date, 'YEAR' )
FROM
  employees
WHERE
  department_id BETWEEN 100 AND 106; 

형 변환 함수

TO_DATE

날짜를 나타내는 문자열을 명시도니 날짜 형식으로 변환하는 함수

  TO_DATE( 문자열, '지정형식' )

SELECT
  TO_DATE( '20111217', 'YYYYMMDD')
FROM
  DUAL;

TO_NUMBER

숫자 타입의 문자열을 숫자 데이터 타입으로 변환하는 함수. 데이터 타입만 바뀜.

  TO_NUMBER( '숫자' )

SELECT
  TO_NUMBER('1234')
FROM
  DUAL;

TO_CHAR

숫자 값을 지정한 데이터 형식의 문자열로 변환

  TO_CHAR( 숫자 데이터 타입, '지정 형식' )

TO_CHAR

날자 값을 지정한 형식의 문자열 타입으로 면환

  TO_CHAR( 날짜 데이터 타입, '지정 형식' )

일반 함수

NVL

  NVL( 열이름, '치환 값' )

NULL 값을 치환 값으로 처리함.

SELECT
  salary * NVL( commission_pct, 1 )
FROM
  employees
ORDER BY
  commission_pct;

NVL2

  NVL2( COLUMN1, COLUMN2, COLUMN3 )

COLUMN1의 값이 NULL이면 COLUMN3를 출력 NULL이라면 COLUMN2를 출력.

DECODE

조건 논리 처리.
3항 연산과 같음.

  DECODE( 열 이름, 조건 값, 치환 값, 기본 값 )

SELECT
  first_name,
  last_name,
  department_id,
  salary as 급여,
  DECODE( department_id, 60, salary * 1.1, salary )   as 조정급여,
  DECODE( department_id, 60, '10%인상', '미인상' )    as 인상여부
FROM
  employees;
ORDER BY
  commission_pct;

CASE

복잡한 조건 논리처리 하기

CASE
  WHEN  조건 1  THEN  출력 값 1
  WHEN  조건 3  THEN  출력 값 2
  ...
  ELSE  출력 값 3
END

SELECT
  employee_id, first_name, last_name, salary,
  CASE
    WHEN salary >= 9000 THEN 'TOP'
    WHEN salary BETWEEN 6000 AND 8999 THEN 'MID'
    ELSE 'LOW'
  END AS salary_grade
FROM
  employees
WHERE
  job_id = 'IT_PROG';

RANK, DENSE_RANK, ROW_NUMBER

데이터 값에 순위 매기기

  RANK () OVER ( [PARTITION BY 열 이름] ORDER BY 열 이름)

SELECT
  employee_id, salary,
  RANK()       OVER( ORDER BY salary DESC ) AS RANK_SALARY,
  DENSE_RANK() OVER( ORDER BY salary DESC ) AS DENSE_RANK_SALARY,
  ROW_NUMBER() OVER( ORDER BY salary DESC ) AS ROW_NUMBER_SALARY
FROM
  employees;

SELECT
  A.employee_id, A.department_id, B.department_name, A.salary,
  RANK()       OVER( PARTITION BY A.department_id ORDER BY salary DESC ) AS RANK_SALARY,
  DENSE_RANK() OVER( PARTITION BY A.department_id ORDER BY salary DESC ) AS DENSE_RANK_SALARY,
  ROW_NUMBER() OVER( PARTITION BY A.department_id ORDER BY salary DESC ) AS ROW_NUMBER_SALARY
FROM
  employees A, departments B
WHERE
  A.department_id = B.department_id
ORDER BY B.department_id, A.salary DESC;

그룹 함수

집계 함수라고도 함.
단일 행 함수와 달리 여러 행에 대해 함수가 적용되어 하나의 결과를 나타내는 함수.
기준 열에 대해 같은 데이터 값 기리 그룹으로 묶고,
묶은 행의 집합에 대해 그룹 함수 연산이 필요하다면 GROUP BY 절을 이용하여 처리할 수 있음.
묶은 그룹에 대해 조건이 필요하다면 HAVING 절을 이용함.

그룹 함수의 사용법

  SELECT  그룹함수( 열 이름 )
  FROM    테이블 이름
  [WHERE 조건식]
  [ORDER BY 열 이름]

주요 그룹 함수

예제 생략