직장인 일기장

옵티마이저(Optimizer)란?

• SQL 개발자가 SQL을 작성하여 실행할 때, 옵티마이저는 SQL을 어떻게 실행할 것인지를 계획하게 된다.
• SQL 실행 계획(Execution Plan)을 수립하고 SQL을 실행한다.
• 옵티마이저는 SQL의 실행 계획을 수립하고 SQL을 실행하는 데이터베이스 관리 시스템의 소프트웨어이다.
• 동일한 결과가 나오는 SQL도 어떻게 실행하느냐에 따라서 성능이 달라지기에, SQL 성능에 옵티마이저는 아주 중요한 역할을 한다.

옵티마이저 특징

• 옵티마이저는 데이터 딕셔너리(Data Dictionary)에 있는 오브젝트 통계, 시스템 통계 등의 정보를 사용해서 예상되는 비용을 산정한다.
• 옵티마이저는 여러 개의 실행 계획 중에서 최저 비용을 가지고 있는 계획을 선택해서 SQL을 실행한다.

옵티마이저의 필요성

• SQL 개발자가 작성한 SQL문을 어떻게 실행하느냐에 따라 성능이 달라진다.

옵티마이저의 실행 방법

• 개발자가 SQL을 실행하면 파싱(Parsing)을 실행해서 SQL의 문법 검사 및 구문 분석을 수행한다.

• 구문 분석이 완료되면, 옵티마이저가 규칙 기반 혹은 비용 기반으로 실행 계획을 수립한다.

• 실행 수립이 완료되면, 최종적으로 SQL을 실행하고, 실행이 완료되면 데이터를 인출(Fetch)한다.

#Query Hint(쿼리 힌트)

SQL을 튜닝할때 쓰이는 지시구문
SQL이 실행될때 Optimizer가 분석하고 최적으로 선택(인덱스 등)을 하여 실행되는데
이때 사용자 임의대로 설정하여 Optimizer의 실행계획을 원하는대로 바꿀 수 있다

#작성법

1.오라클의 경우 /*+ */ or --+ 안에 작성 한다

2.오라클의 쿼리힌트는 SELECT, UPDATE, DELETE, INSERT 다음 구문에 작성한다

EX) SELECT /*+ INDEX(테이블명 인덱스명)*/

EX) SELECT --+ INDEX(테이블명 인덱스명)

3.힌트가 여러개일 경우 공백으로 구분 한다

EX) SELECT /*+ INDEX(테이블명 인덱스명) INDEX(테이블명 인덱스명)*/

4.힌트내 인자구분은 공백 or ,로 구분한다

EX) SELECT /*+ INDEX(테이블명 인덱스명)*/

EX) SELECT --+ INDEX(테이블명, 인덱스명)

#인덱스 힌트

테이블에 사용할 인덱스를 사용자가 수동으로 정의하는 것

사용하려는 테이블과 인덱스가 존재해야한다

SELECT /*+ INDEX(MST_GOODS MST_GOODS_PK)*/ --MST_GOODS테이블에 MST_GOODS_PK인덱스를 사용함
* 
FROM MST_Goods;

또한 인덱스에 설정되어 있는 오름차순/내림차순을 강제로 바꾸어 검색할 수도 있다

(실행시에만 적용되고 인덱스의 오름차순/내림차순 설정이 바뀌지는 않는다)

INDEX다음에 _ASC or _DESC을 붙이면 된다

SELECT /*+ INDEX_ASC(MST_GOODS MST_GOODS_PK)*/ --인덱스 기준키를 오름차순으로 검색
* 
FROM MST_Goods;

SELECT /*+ INDEX_DESC(MST_GOODS MST_GOODS_PK)*/ --인덱스 기준키를 내림차순으로 검색
* 
FROM MST_Goods;

#그 외 종류

/*+ ALL_ROWS */

전체 데이터를 가져올 때 가장 비용이 적은 방법의 실행계획을 만들어 실행

/*+ FIRST_ROWS(n개) */

첫번째 행을 가져올 때 가장 비용이 적은 방법의 실행계획을 만들어 실행

/*+ FULL (table) */

파라미터에 명시된 테이블을 FULL TABLE SCAN 방식을 사용하여 접근한다.

/*+ INDEX (table index) */

파라미터에 명시된 테이블의 인덱스를 사용해서 INDEX SCAN 방식을 사용한다.

/*+ NO_INDEX (table index) */

파라미터에 명시된 테이블의 인덱스를 사용하지 않는다.

#TRUNC(대상, 서식)

시간이나 소숫점을 초기화 하는 함수

#시간 관련

SELECT
    TRUNC(TO_DATE('2020/11/30 12:30:30')),       -- 2020/11/30 00:00:00
    TRUNC(TO_DATE('2020/11/30 12:30:30'), 'YY'), -- 2020/11/30 12:30:30 년 아래로 초기화
    TRUNC(TO_DATE('2020/11/30 12:30:30'), 'MM'), -- 2020/11/30 12:30:30 월 아래로 초기화
    TRUNC(TO_DATE('2020/11/30 12:30:30'), 'DD'), -- 2020/11/30 00:00:00 일 아래로 초기화
    TRUNC(TO_DATE('2020/11/30 12:30:30'), 'HH'), -- 2020/11/30 12:00:00 시간 아래로 초기화
    TRUNC(TO_DATE('2020/11/30 12:30:30'), 'MI'), -- 2020/11/30 12:30:00 분 아래로 초기화
FROM
    DUAL

#숫자 관련

SELECT
    TRUNC(1234.567),     -- 1234     소숫점 초기화
    TRUNC(1234.567, 1),  -- 1234.5   소숫점 첫째자리 이후 초기화
    TRUNC(1234.567, 2),  -- 1234.56  소숫점 둘째자리 이후 초기화
    TRUNC(1234.567, -1), -- 1230     첫째자리 초기화
    TRUNC(1234.567, -2)  -- 1200     둘째자리까지 초기화
FROM
    DUAL

#현업에서 기존의 테이블의 컬럼이나 조건 수정이 많을때

테이블을 새로 만들고 데이터를 이동시킬 때 사용하는 방법이다

1.의 작업을 하기전에 기존 테이블이 존재 하지않으면 에러가 나기때문에

3.을 먼저 실행 한다(이미 존재하는 테이블은 스킵하고 없는 테이블만 생성)

1. 기존 테이블과 같은 임시테이블을 생성

CREATE TABLE 테이블1_임시 AS
SELECT TABLE 테이블1

2. 기존 테이블을 삭제

DROP TABLE 테이블1

3. 새 테이블 생성

CREATE TABLE 테이블1(
컬럼1,
컬럼2
컬럼3
...
CONSTRAINT PK_테이블1 PRIMARY KEY(
컬럼1
   )
)

4. 임시 테이블에서 새 테이블로 이동(SQLPLUS의 경우 실행후 COMMIT 필수)

INSERT INTO 테이블1(
컬럼1,
컬럼2,
컬럼3,
컬럼4,
...
) SELECT
컬럼1,
컬럼2,
컬럼3,
'100' --새로 추가된 컬럼을 임의의 값을 지정해준다, 컬럼 갯수가 맞지않으면 에러가 발생함
FROM 테이블1_임시

5. 4.의 작업이 정상적으로 작동했는지 확인후 임시 테이블 삭제

DROP TABLE 테이블1_임시

#VARCHAR2

영문 데이터형과 테이블에 설정된 기본 언어 타입을 저장할때 사용

#NVARCHAR2

유니코드를 지원, 다국어를 사용해야하는 DB에 적합,

다만 용량을 VARCHAR2대비 2배 많이 사용한다

이외에도 (TEXT, NTEXT), (CHAR, NCHAR) 등이 있다

선두에 N이 붙으면 유니코드 지원 데이터 타입이라고 보면 된다