Post
Index 기본3
인덱스가 장비번호 + 변경일자 + 변경순번 일 때
SELECT MIN(변경순번)
FROM 상태변경이력
WHERE 장비번호 = ‘C’
AND 변경일자 = ‘20220909’
옵티마이저는 정렬 연산을 따로 수행하지 않음
수직적 탐색을 통해 조건을 만족하는 가장 왼쪽 지점으로 내려가서 첫 번째로 읽는 레코드가 바로 최소값이기 때문
SELECT MAX(변경순번)
FROM 상태변경이력
WHERE 장비번호 = ‘C’
AND 변경일자 = ‘20220909’
NVL(MAX(TO_NUMBER(변경순번)), 0) >> 이건 정렬 안된다. 인덱스는 문자열 기준 정렬 이거를 숫자값으로 바꾼 값 기준으로 요구했기 때문
수직적 탐색을 통해 조건을 만족하는 가장 오른쪽 지점으로 내려가서 첫 번째로 읽는 레코드가 바로 최대값
오라클에서 실행계획 보는 법
예시
EXPLAIN PLAN -- EXPLANIN PLAN 선언부 SET STATEMENT_ID = ‘PLAN1’ INTO PLAN_TABLE -- SQL에 PLAN1이라는 ID 부여 FOR SELECT SABUN,RET_YMD,EMP_YMD FROM THRM100 WHERE RET_YMD BETWEEN ‘20200101’ AND ‘20220101’;
--------------------------------------------------------------------------------------------- SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY -- PLAN_TABLE에 저장된 실행계획 조회 (‘PLAN_TABLE’,’PLAN1’,’ALL’));
PLAN_TABLE에 저장한 후 조회해 보면 아래처럼 나오게 된다.
Plan hash value: 1921208606
----------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ----------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 56 | 1344 | 15 (0)| 00:00:01 | |* 1 | TABLE ACCESS FULL| THRM100 | 56 | 1344 | 15 (0)| 00:00:01 | -----------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id): -------------------------------------------------------------
1 - SEL$1 / THRM100@SEL$1
Predicate Information (identified by operation id): ---------------------------------------------------
1 - filter(“RET_YMD”>=‘20200101’ AND “RET_YMD”<=‘20220101’)
Column Projection Information (identified by operation id): -----------------------------------------------------------
1 - “SABUN”[VARCHAR2,13], “EMP_YMD”[VARCHAR2,8], “RET_YMD”[VARCHAR2,8]
2번째
SET AUTOTRACE ON;
하게되면 sql 실행 시 실행계획을 매번 확인하게 된다.
sql developer 같은 툴에서는
이 부분에서 확인하여 볼 수 있다.
자동 형변환
타입이 다른 두개를 비교하였을 때 EX) 숫자,문자 -> 컴파일 시점에서 에러를 내는 DBMS가 있고 자동 형변환 처리해주는 DBMS가 있다. Oracle은 자동 형변환 한다.
Oracle 에서는 숫자형과 문자형이 만나면 숫자형이 이긴다. 숫자형 컬럼 기준으로 문자형 컬럼을 변환한다.
날짜형과 문자형이 만나면 날짜형이 이긴다.
그렇다고 해서 ,
SELECT * FROM 고객 WHERE 가입일자 = ‘01-JAN-2022’; 처럼 하면 NLS_DATE_FORMAT 파라미터가 다르게 설정된 환경에서는 결과가 원하는 대로 안나올 수 있다.
TO_DATE() 로 포맷 설정해 주는 습관을 들여야 한다.
숫자형 컬럼을 LIKE 조건 검색하면 자동형변환이 발생한다. 따라서, 구성된 인덱스 Range Scan을 못하는 경우가 발생하기도 함.
또한, 숫자형 과 문자형 비교할 때 문자형 컬럼에 영문등이 들어가면 에러 발생한다.
DECODE에서도 오라클에서 해당 함수를 내부적으로 처리할때 자동형변환을 사용한다.
DECODE(A,B,C,D)처리 시 A=B이면 C반환 아니면 D반환한다. 라는 내부 처리 계획에서 반환값의 데이터 타입은 세번째 인자인 C에 의해서 결정된다.
D가 숫자인 경우 C를 NULL로 설정하거나 하면, 반환 타입을 문자형으로 정해서 원하는 결과값이 안나올 수도 있다
EX) MAX(DECODE(JOB,’PRESIDENT’,NULL,SAL)) AS MAX_SAL
>> MAX(DECODE(JOB,’PRESIDENT’,TO_NUMBER(NULL),SAL)) AS MAX_SAL 혹은 NULL 말고 0 기입
따라서 자동 형변환 기능에 의존하지 말고 인덱스 컬럼 기준으로 반대편 컬럼 또는 값을 정확히 형변환 해주어야 한다.
TO_CHAR, TO_DATE, TO_NUMBER 등의 함수를 생략할 때 연산횟수가 더 줄어 성능이 더 좋지 않을까 생각하기도 하는데
SQL의 성능은 저런 부분에서 결정되지 않고, 블록 I/O를 줄일 수 있느냐 없느냐에서 결정된다. 형변환 함수를 생략한다고 해서 연산횟수가 자동으로 줄지도 않는다. 옵티마이저가 자동 생성하기 때문이다.
댓글