SQL 작성 시 고려 사항
- 데이터와 비즈니스 프로세스 파악
SQL 을 작성하기 전에 비즈니스 개체 관계에 같은 데이터 모델을 전체적으로 이해해야 함
데이터베이스 내의 데이터 크기와 분포를 알아야 함
여러 테이블에서 정보를 검색하는데 있어서 보다 좋은 Query 를 작성할 수 있게 함 - 실제 데이터를 가지고 QUERY 검사
SQL 문을 테스트 할 때, 테스트 데이터베이스가 가지고 있는 데이터는 실제 운영될 데이터베이스를 반영해야 함
즉, 비실제적인 데이터를 가지고 테스트된 SQL 문은 운영 DB 안에서 다르게 작동할 수 있음
테스트 환경에서의 데이터 분포는 운영 환경에서의 데이터 분포와 밀접하게 닮아야 보다 정확한 SQL 문 검사 가능 - SQL 문 코딩 규칙 통일
동일한 SQL 문의 이점은 Parsing (: SQL 문의 문법적 오류 및 의미상 오류를 확인하는 과정)이 불필요하기에
SQL 문을 공유함으로써 데이터베이스 서버 안에서 메모리 사용을 감소시키고 수행을 빠르게 하게 됨
대소문자, 스페이스 공간, 주석이 다르면 동일한 SQL 문으로 취급하지 않음
SQL 문 공유를 위해 코딩 규칙을 통일할 것을 권장
- 예약어는 대문자로 씀
- 줄 바꿈을 일정하게 함
- 콤마 후에는 반드시 공백을 씀
- 계속되는 콤마는 줄 앞에 위치시킴
- 나열되는 필드는 1개씩으로 통일
- 괄호 사용시에는 괄호 안에 공백을 주지 않음
- 연산자를 기준으로 양쪽에 공백을 줌
- where 절의 and는 줄 앞에 위치 시킴
- SQL 문 안에는 comment 사용하지 않음
SQL 공유 및 재사용
① 사용자가 SQL 쿼리문을 입력하면
② SQL 파싱과정을 통해 SQL 문장에 문법적 오류가 없는지, 의미상 오류가 없는 지 확인
오류가 없다면 해당 SQL 이 라이브러리 캐시*에 존재하는 지 확인
이 때, SQL 쿼리문은 대소문자, 공백, 주석이 완전히 일치해야 함
해당 SQL 문을 캐시에서 찾지 못하면 캐쉬에 등록 후,
④ 최적화 (optimizer) 과정을 거침
그 후, 최적화를 통해 얻은 실행계획을 내부적으로 처리하는
⑤ 로우소스생성 단계를 거친 후, 최종적으로
⑥ SQL을 처리함
③ 캐시에 존재 여부파악에서 파싱과정이 소프트 파싱과 하드 파싱으로 나뉨
(*라이브러리 캐쉬 (Library Cache) : SQL 파싱, 최적화, 로우 소스 생성 과정을 거쳐 생성한 내부 프로시저를 반복 재사용 할 수 있도록 캐싱해 두는 메모리 공간)
소프트 파싱 VS 하드 파싱
사용자가 SQL 문을 전달하면 DBMS 는 SQL 을 파싱한 후 해당 SQL 이 라이브러리 캐시에 존재하는 지 부터 확인
해당 SQL 을 캐시에서 찾으면 곧바로 실행 단계로 넘어가지만, 찾지 못하면 최적화 단계를 거침
해당 SQL 과 동일한 SQL 문을 캐시에서 찾아 바로 실행단계로 넘어가는 것을 '소프트 파싱 (Soft Parsing)'이라 하고,
동일한 SQL 문 조회에 실패해 최적화 및 로우 소스 생성 단계까지 모두 거치는 것을 '하드 파싱 (Hard Parsing)' 이라고 함
- 소프트 파싱 (Soft Parsing) : ① → ② → ③ → ⑥
- 하드 파싱 (Hard Parsing) : ① → ② → ③ → ④ → ⑤ → ⑥
SQL 최적화 과정은 네비게이션 경로 찾는 것과 유사하게 생각하면 쉬움
하나의 쿼리를 수행하는 데 있어 후보군이 될만한 무수히 많은 실행경로를 도출하고,
짧은 순간에 딕셔너리와 통계정보를 읽어 각각에 대한 효율성을 판단해야 함
그렇기에 최적화 과정을 거치는 하드 파싱은 오라클 CPU 를 많이 소비하는 작업임
최초로 수행되는 SQL 은 하드파싱을 피할 순 없음
그러나 두번째 수행부터는 소프트파싱을 하는 게 보다 빠르고 적은 리소스를 사용하니 효율적임
그러므로 하드파싱 작업을 거쳐 생성한 내부 프로시저를 한 번만 사용하고 버리면 이만저만한 비효율이 아님
바인드 변수를 사용한 동일한 SQL 문은 라이브러리 캐쉬에서 빠르게 재사용할 수 있는 장점이 있음
옵티마이저 역할로 보는 바인드 변수의 중요성
SQL >
SELECT * FROM 거래 WHERE 종목 = 'T1';
SELECT * FROM 거래 WHERE 종목 = 'GEN';
SELECT * FROM 거래 WHERE 종목 = 'KT';
SELECT * FROM 거래 WHERE 종목 = 'FOX';
SELECT * FROM 거래 WHERE 종목 = 'DRX';
SELECT * FROM 거래 WHERE 종목 = 'DK';
종목별 거래 데이터를 조회하려고 위와 같은 식으로 SQL 을 작성하면,
아래처럼 프로시저 (= 커서) 가 종목마다 하나씩 만들어지게 됨
이들 프로시저를 만들어주는 역할을 옵티마이저와 로우 소스 생성자 (Row-Source Generator)가 담당함
procedure T1 거래 () { ... }
procedure GEN 거래 () { ... }
procedure KT 거래 () { ... }
procedure FOX 거래 () { ... }
procedure DRX 거래 () { ... }
procedure DK 거래 () { ... }
종목별 거래 데이터의 분포가 편중되지 않았다면 위 프로시저의 내부 처리 루틴은 모두 같을 것이며, 가장 큰 문제가 있음
모든 프로시저의 처리 루틴이 같다면 여러 개 생성하기 보다 아래처럼 종목을 파라미터로 받아 하나의 프로시저로 처리하도록 하는 것이 마땅함
procedure 거래 (종목 in varchar 2) {...}
파라미터 Driven 방식으로 SQL 을 작성하는 방법이 제공되는데, 그것이 곧 바인드 변수를 사용하는 것
하나의 프로시저를 공유하면서 반복 재사용할 수 있게 됨
SELECT * FROM 거래 WHERE 종목 = :종목;
바인드 변수를 사용하면 커서를 많이 생성하지 않고 하나를 반복 재사용하므로 메모리 사용량과 파싱 소요시간을 줄여줌
궁극적으로 시스템 전반의 메모리와 CPU 사용률을 낮춰 데이터베이스 성능과 확장성을 높이는데 기여하고,
특히 동시 사용자 접속이 많을 때는 그 영향력이 절대적임
바인드 변수의 부작용과 해법
바인드 변수를 사용하면 최초 수행할 때 최적화를 거친 실행계획을 캐시에 적재하고,
실행시점에는 그것을 그대로 가져와 값을 다르게 바인딩하면서 반복 재사용하게 됨
변수를 바인딩하는 시점이 (최적화 시점보다 나중인) 실행시점이라는 사실을 아는 것이 중요함
즉, SQL 을 최적화하는 시점에 조건절 컬럼의 데이터 분포도를 활용하지 못하는 문제점을 가짐
따라서 바인드 변수를 사용할 때 옵티마지어 평균 분포를 가정한 실행계획을 생성함
컬럼 분포가 균일할 때는 문제될 것이 없지만 그렇지 않을 때는 실행 시점에 바인딩되는 값에 따라 최적이 아닌 실행계획일 수 있는 문제가 있음
이처럼 오라클의 바인드 변수 부작용 극복을 위해 '필요시 입력 값에 따라 SQL 분리' 할 것을 권고함
인덱스 액세스 경로 (Access Path)로서 중요하고 조건절 컬럼의 데이터 분포가 균일하지 않은 상황에서 바인드 변수 사용에 따른 부작용을 피하려면 바인딩 되는 값에 따라 실행계획을 분리하는 방안을 고려해야 함
여기서도 주의할 사항이 한가지 있는데, OLTP 시스템에서 union all 을 이용해 SQL 을 지나치게 길게 작성하면 오히려 라이브러리 캐시 효율을 떨어뜨리게 된다는 사실임
예를 들어, union all 을 사용해 10개의 SQL 을 결합했다고 가정하자.
그러면 하드피싱 시점에 옵티마이저는 10개 SQL 을 최적화해야 함 그리고 그만큼 메모리에서 많은 공간을 차지하게 됨
10개가 항상 골고루 사용된다면 모르지만 그 중 한 두개만 주로 사용된다면 나머지는 불필요하게 공간만 낭비하는 결과를 초래함
더욱이 매번 수행할 때마다 긴 텍스트를 파싱하면서 Syntax 를 체크하고 파싱트리를 만들어 Semantic 체크하는 과정을 반복한다면 Parse 단계에서 CPU 를 과도 소비할 것임
또 다른 바인드 변수 부작용의 해법으로 '예외적으로 Literal 상수값을 사용하는 방법'이 있음
특정 컬럼의 값에 고정된 값이 들어오는 경우에는 바인드 변수보다 오히려 Literal 상수를 사용하는 게 나은 선택일 수 있음
왜냐하면 입력 값 종류가 몇 개에 불과하다면 하드피싱 부하가 미미할 테고, Literal 상수를 사용함으로써 옵티마이저가 더 나은 선택을 할 기능성이 커지기 때문임
옵티마이저 원리
통계정보를 활용하는 옵티마이저
오라클은 비용기반 원리로 옵티마이저가 최적의 실행계획을 계산함
옵티마이징 팩터가 동일한 상황에서 CBO 행동에 결정적 영향을 미치는 요소는 통계정보임
통계정보가 없다면 정확한 실행 계획을 얻어내지 못함
선택도
- 선택도는 전체 대상 레코드 중에서 특정 조건에 의해 선택될 것으로 예상되는 레코드 비율을 말함
선택도를 가지고 카디널리티를 구하고, 다시 비용을 구함으로써 사용 여부, 조인 순서와 방법 등을 결정하므로 선택도는 최적의 실행계획을 수립하는데 있어 가장 중요한 요인이라고 할 수 있음 - 선택도 → 카디널리티 → 비용 → 액세스 방식, 조인 순서, 조인 방법 등 결정
- 히스토그램* 이 있으면 그것으로 선택도를 산정하며, 단일 컬럼에 대해서는 정확도도 비교적 높음
히스토그램이 없거나, 있더라도 조건절에 바인드 변수를 사용하면 옵티마이저는 데이터 분포가 균일하다고 가정한 상태에서 선택도를 구함
* 히스토그램 : 테이블의 데이터 분포 정보를 가지고 있는 컬럼 통계정보 중 하나 - 히스토그램 없이 등치(=) 조건에 대한 선택도를 구하는 공식
- 히스토그램 없이 부등호, between 같은 범위검색 조건에 대한 선택도를 구하는 기본 공식
카디널리티
- 카디널리티 (Cardinality) 는 특정 액세스 단계를 거치고 나서 출력될 것으로 예상되는 결과 건수를 말하며,
총 로우 수에 선택도를 곱해서 구함 - 카디널리티 공식
카디널리티 = 총 로우 수 X 선택도 - 컬럼 히스토그램이 없을 때 = 조건에 대한 카디널리티
통계정보가 없을 때 카디널리티의 오류
옵티마이저가 통계수집을 하지 않고 이용할 경우 어떠한 문제가 발생하는지 카디널리티를 확인하며 테스트해보자.
1,000 개의 ROW 를 가진 테이블 T_EMP 이 아래와 같이 있다고 하자.
JOB 칼럼의 'CLERK' 과 'SALESMAN'이 컬럼 히스토그램을 생성하지 않은 상태에서 옵티마이저가 이 두 값의 카디널리티를 어떻게 추정하는지 살펴보자.
'CLERK' 과 'SALESMAN' 모두 카디널리티 (=Rows) 를 200 으로 추정하였다.
히스토그램이 없으므로 평균적으로 컬럼 분포를 가정해 정해진 계산식에 따라 선택도와 카디널리티를 구한 것임
하지만 실제 데이터 분포가 있는 통계정보를 활용한 실행계획을 확인해보자.
각각 100 과 400 으로 예측하였고 앞에서 count 쿼리로 확인한 값과 일치함
공식에 의존하지 않고 미리 구해놓은 히스토그램을 이용한 결과임
이처럼 통계정보를 활용한 옵티마이저의 실행계획을 통해 카디널리티를 정확히 파악하고 어떤 칼럼을 인덱스 칼럼으로 잡아야하는 지 활용할 수 있음
NULL 값을 포함 할 때
조건절 컬럼이 NULL 값을 포함할 때 카디널리티를 구해보자.
테스트를 위해 3.4 의 테이블 T_EMP 테이블을 활용하여 50 개 레코드의 job 을 null 로 치환함
SQL > update t_emp set job = NULL where no <= 50;
이제 아래 조건식에 의한 결과 건수는 50개일 것임
SQL > select * from t_emp where job = null;
위 조건식은 공집합임
따라서 아래와 같이 바인드 변수를 이용한 조건식일 때 어떤 값이 입력되든 (null 값이 입력되더라도) job 이 null 인 레코드는 결과집합에서 제외됨
SQL > select * from t_emp where job = :job;
'=' 조건에 대한 선택도를 구할 때 이런 특성을 반영하려면 기존 공식에 null 값이 아닌 로우 비중을 곱하고, 분모인 Distinct Value 개수에서 null 값을 제외시키면 됨 하지면 이 경우 null 값은 원래부터 값의 종류로 간주하지 않았기에 Distnict Value 개수는 그대로 5임
카디널리티 활용
- 카디널리티와 중복도
카디널리티는 전체 행에 대한 특정 컬럼의 중복 수치를 나타내는 지표
- 중복도가 낮으면 카디널리티가 높다고 표현
- 중복도가 높으면 카디널리티가 낮다고 표현
- 카디널리티와 인덱스
우리는 인덱스를 생성할 때, 원하는 데이터를 선택하는 과정에서 최대한 많은 데이터를 걸러져야 성능을 향상시킬 수 있음 그러므로 인덱스를 생성할 때는 중복도가 낮은 컬럼을 선택해야 함
즉, 실행계획을 통해 카디널리티가 높은 인덱스 컬럼으로 선택해야 함
옵티마이저의 한계
옵티마이저도 결국 사람이 만든 소프트웨어 엔진에 불과하며, 모든 프로그램이 업그레이드를 통해 조금씩 개선되듯 옵티마이저도 여러 가지 제약과 한계점들을 극복하며 발전해 나가는 과정 속에 있음
부족한 옵티마이징 팩터
옵티마이저는 주어진 환경에서 최선을 다할 뿐 적절한 옵티마이징 팩터를 제공하는 것은 결국 사람의 몫
적절한 인덱스도 제공하지 않은 채 옵티마이저가 고성능 실행계획을 수립해 주기를 기대해선 안됨
이미 존재하는 길을 찾아줄 뿐 옵티마이저가 없는 길까지 만들어 낼 수는 없기 때문
부정확한 통계
많은 정보를 수집 / 보관한다면 그만큼 좋은 결과를 낼 수 있겠지만 현실적으로 100% 정확한 통계를 유지하기 어려움
이런 현실적인 제약 때문에 샘플링 방식으로 통계를 수집하다 보니 실제 데이터와 불일치가 발생하기 마련
또한, 통계 수집 주기도 매우 중요함
특히, 어느 날 갑자기 데이터가 아주 많이 변경되거나 새로 입력됐을 때 곧바로 통계를 재수집해주지 않는다면 옵티마이저가 잘못된 선택을 할 수 있기 때문
바인드 변수 사용 시 균등분포 가정
바인드 변수를 사용한 SQL 은 옵티마이저가 균등분포를 가정하고 비용을 계산하기 때문에 정확한 실행계획을 얻을 수 없음
규칙에 의존하는 CBO
CBO 가 사용하는 규칙과 관련해 꼭 기억해야 할 사항이 있는데, 두 대안 인덱스의 예상 비용이 같을 때 알파벳 순에선 앞선 것을 인덱스로 선택한다는 사실
a, b 두 컬럼을 가진 테이블 T 은 100% 같은 값을 가지고 있다. 그리고 각 컬럼에 단일 컬럼 인덱스를 생성하였다.
SQL > create index t_x01 on t(a);
SQL > create index t_x02 on t(b);
위와 같은 조건절을 만났을 때 t_x01, t_x02 둘 중 어느 것을 선택하든 쿼리 수행 비용은 같다.
이 때 옵티마이저가 t_x01 인덱스를 선택한 판단 근거는, 허무하게도 인덱스명의 알파벳 순이다.
t_x01 인덱스명을 t_x03 으로 바꾸고 나서 실행 계획을 다시 확인하면, t_x02 인덱스가 사용되는 것을 볼 수 있다.
SQL > alter index t_x01 rename to t_x03;
선택도 산정의 어려움
- 범위기반의 조건절
등치(=) 조건이 아닌 부등호나 Between 같은 범위 기반 검색조건일 때는 고정된 규칙을 사용하므로 더 부정확한 예측에 기반한 실행계획이 만들어짐
좀 더 구체적으로 말해, 아래 1~4 번은 선택도를 5% 로 계산하고, 5~8번까지는 0.25%로 계산함
| 1 | 번호 > :NO | 5 | 번호 between :NO1 and :NO2 |
| 2 | 번호 < :NO | 6 | 번호 > :NO1 and 번호 <= :NO2 |
| 3 | 번호 >= :NO | 7 | 번호 >= :NO1 and 번호 < :NO2 |
| 4 | 번호 <= :NO | 8 | 번호 > :NO1 and 번호 < :NO2 |
따라서 테이블(t)에 1~1000 까지의 1,000 개 로우가 있을 때 옵티마이저는 1~4 번과 같은 조건절에 대해서는 1000*0.5=50 개 로우가 출력될 것으로 예상하고., 5~8 번과 같은 조건절에 대해서는 1000*0.0025=3 개 로우가 출력될 것으로 예상한다.
SQL > explain plan for select * from t where no <= :no;
SQL > explain plan for select * from where no between :no1 and :no2;
반면, 상수 조건식을 사용할 때는 거의 정확한 카디널리티를 계산해 냄
SQL > explain plan for select * from t where no <= :100;
SQL > explain plan for select * from where no between 500 and 600;
그러므로 옵티마이저로부터 보다 정확한 실행계획을 얻기 위해서는 업무에 맞는 최소 / 최대값을 모두 지정한 바인드 범위 조건절을 작성하거나, 적절한 최소 / 최대 상수값으로 범위롤 고정시켜야 함
- 조건절 컬럼이 서로 상관관계인 경우
조건절 컬럼이 서로 상관관계에 있으면 정확한 데이터 분포와 카디널리티를 산정하기 어려움
카디널리티가 잘못 계산되면 다른 집합과 여러 번 조인을 거치는 동안 카디널리티는 점점 더 부정확해지고 궁극적으로 옵티마이저가 잘못된 실행계획을 수립하는 결과를 낳음
칼럼이 서로 상관관계를 가지고 있고, 이에 대해 알고 있을 경우 SQL 문 작성 시 변수 컬럼과 값을 적절히 선정하여 옵티마이저의 성능을 높여줘야 함
인덱스 사용불가의 경우
B*Tree 인덱스를 정상적으로 사용하려면 범위 스캔 시작지점을 찾기 위해 루트 블록부터 리프 블록까지의 수직적 탐색 과정을 거쳐야 함
만약 인덱스 선두 컬럼이 조건절에 사용되지 않으면 범위 스캔을 위한 시작점을 찾을 수 없어 옵티마이저는 인덱스 전체를 스캔하거나 테이블 전체를 스캔하는 방식을 선택함
또한 인덱스 선두 컬럼이 조건절에 사용되더라도 인덱스를 사용 못하거나 범위 스캔이 불가능한 경우가 있음
인덱스 사용이 불가능하거나 범위 스캔이 불가능한 경우
인덱스 컬럼을 조건절에서 가공하면 정상적으로 인덱스를 사용할 수 없음
즉, 정상적인 인덱스 범위 스캔이 불가능 함
그렇다고 인덱스 사용자체가 불가능한 것이 아니라 Index Full Scan 은 가능함
또한 결합 인덱스일 대는 인덱스 구성 컬럼 중 하나라도 값이 null 이 아닌 레코드가 인덱스에 포함되어 있어야 함
묵시적 형변환으로 인한 인덱스 사용 불가
오라클의 묵시적 형변환으로 인해 인덱스 컬럼이 가공되면 인덱스 사용이 불가능하여 성능 측면에서 좋지 않음
하지만 쿼리 수행 도중 에러카 발생하거나 결과가 틀릴 수 있다는 측면이 더 치명적일 수 있음
참고로 묵시적 형변환 순서는 DATA 형 → CHAR 형 → NUMBER 형 순임
예를 들어 아래와 같이 숫자형 컬럼 (n_col)과 문자형 컬럼(v_col)을 비교하면 문자형 컬럼이 숫자형으로 변환되는데,
만약 문자형 컬럼에 숫자가 변환할 수 없는 문자열이 들어 있으면 쿼리 수행 도중 에러가 발생함
문자형과 숫자형이 만나면 숫자형으로 문자형과 날짜형이 만나면 날짜형으로 변하는 등 데이터 타입 간 우선순위 규칙이 존재하지만 이를 굳이 외울 필요도 없음
쿼리 성능뿐만 아니라 올바른 결과집합을 얻기 위해서라도 명시적으로 변환함수를 사용하는 게 바람직하기 때문임
성능을 위해서라면 인덱스 칼럼과 비교되는 반대쪽을 인덱스 컬럼 데이터 타입에 맞추면 됨
SQL 작성 가이드
FULL TABLE SCAN 유도를 최소화
크기가 작은 테이블의 경우 index scan 의 장점이 없다하여 인덱스를 생성하지 않거나 /* + full */ 힌트 등으로 full table scan 을 유도하는 경우가 있으나 다음과 같은 이유 등으로 full table scan 유도를 최소화하여야 함
- 초기에는 테이블의 크기 (rows 수, block 수)가 작아 full table scan 이 큰 문제가 없으나 향후 데이터량이 증가하는 경우 성능상의 큰 이슈가 되는 경우가 많음
- 업무의 변경 등으로 초기 예측시에는 데이터 크기가 증가하지 않을 것으로 예상했으나 향후헤 데이터량이 크게 증가하는 경우가 있음
- 특히 /* + full */ 힌트를 사용한 경우에는 향후에 SQL 이 포함된 소스를 수정해야되는 경우 발생
- 명백하게 SQL 조건에서 전체범위 (전체기간, 전체기관, 전사원, 전고객)를 처리하는 경우가 아니면 /* + full */ 힌트는 사용하지 않음
결합 인덱스에서 LEADING 컬럼 선택에 신중을 기함
- 결합인덱스의 첫번째 컬럼을 조건에서 사용하지 않으면 그 인덱스는 사용되지 않음
또한, 첫번째 컬럼은 처리범위를 결정하는데에도 큰 영향을 미침
첫번째 컬럼을 결정하는데 있어서 가장 중요한 기준은 그 조건에 항상 사용되는 컬럼들 중에서 선정되어야 함 - 선행 컬럼이 '=' 조건으로 비교되지 않는다면 뒤에 있는 컬럼이 '=' 조건으로 사용하여 처리범위를 줄일 수 있도록 하는 것이 중요함
- 결합 인덱스를 생성 시 컬럼순서 선정 기준
- = 조건으로 조회되는 컬럼
- in 등으로 조건으로 조회되는 컬럼
- like, <, > 조건으로 조회되는 컬럼
- SORTING 이 빈번하게 발생하는 테이블의 경우 SORT 되는 순서를 감안해주는 것이 좋음
인덱스는 결합된 컬럼의 순서로 Ascending SORT 되어 저장되므로 인덱스의 컬럼순서와 수행하고자 하는 SORT 순서가 같다면 굳이 SORT 를 시키지 않고도 결과를 추출할 수 있을 뿐만 아니라 '부분범위처리' 방식으로 유도함으로써 속도 향상을 얻을 수 있음
WHERE 절에서 인덱스를 사용하지 못하는 경우
- 부정으로 비교하는 경우 : <>, !=, NOT IN, NOT LIKE
ex) SELECT * FROM EMP WHERE EMPCD IS NOT NULL
→ 이런 경우 테이블 설계시에 default 값을 지정하도록 함 - 와일드카드로 시작하는 STRING 을 LIKE 로 비교하는 경우
ex) SELECT * FROM EMP WHERE ENAME LIKE '%AN' → 인덱스 사용 불가
ex) SELECT * FROM EMP WHERE ENAME LIKE '%AN%' → 인덱스 사용 불가
ex) SELECT * FROM EMP WHERE ENAME LIKE 'AN%' → 인덱스 사용 불가
★ like 의 경우 %가 앞쪽에 오지 않도록 해야 함
인덱스를 사용하지 못하는 사례
- 부정 조건문을 가진 경우
- NULL 값과 비교하는 겨우
- 데이터 타입이 다른 경우
- 쿼리의 인덱스된 컬럼에 함수 사용하는 경우
- like 구문 사용 '%AA' : 와일드카드로 시작하는 STRING을 LIKE 로 비교하는 경우
ORDER BY 절의 자주 사용되는 컬럼에 인덱스 생성
- 오라클의 optimizer 는 만약 ORDER BY 절의 컬럼이 인덱스로 생성되어 있다면 index scan 을 사용할 것임
아래의 Query 는 이러한 점을 보여 주는 것인데 비록 그 컬럼이 where 절에 명시되어 있지 않다고 해도 EMPID 컬럼에 있는 가용한 인덱스를 사용할 것임 이 Query 는 인덱스로부터 각각의 ROWID 를 검색하고, 그 ROWID 를 사용하는 테이블에 접근함
SELECT SALARY,
FROM EMP,
ORDER BY EMPID;
* 로 모든 컬럼을 지정하지 말 것
- SELECT * FROM [NAME] 작성금지
SELECT * FROM 형태로 사용하면
- 불필요한 컬럼을 ACCESS 하여 성능 저하
- 테이블 컬럼 변경 시 오류 발생
반드시 필요한 컬럼을 명시하여 사용해야 함
중복이 허용되는 경우에는 UNION 대신 UNION ALL 사용
UNION 보다 UNION ALL 을 사용하여 sort 발생 방지
중복 row 제거를 위하여 sorting 이 필요한 경우에만 UNION 사용
- UNION
- 두 Data Set 의 모든 컬럼으로 'GROUP BY' 하여 중복된 Row를 제거
- 정렬된 순서로 결과를 반환 (Parallel Query의 경우 순서 보장 안함)
- Data Set 의 크기가 커질 수록 Sort 작업 부하 커짐
- UNION ALL
- 두 Data Set 을 중복 제거 없이 모두 반환
- 정렬 없이 두 Data Set 을 순차적으로 반환
- Sort 작업 부하 없음
SCALAR QUERY 사용자제
일반적으로 select 문 속의 select 문이 있는 경우를 Scalar Query 라 함
대량의 데이터 처리 시 성능저하를 일으킬 수 있음
- Scalar query 는 메인절 1건 처리시 마다 1건씩 처리하므로 처리량이 많아질수록 성능 저하
- Scalar query 는 가능하면 Main 절의 Join 으로 처리
- 불가피하게 Scalar query 를 사용하게 되는 경우에는 반드시 Scalar query 부분은 선택도가 매우 좋은 index 를 사용해야하며 조회되는 테이블의 크기가 작아야 함
- 만약 Scalar query 부분이 full scan 하게 되면 메인절 1건 마다 Scalar query 에서 full scan 이 발생하여 성능 상에 심각한 문제를 발생시킴
Optimizer Hint
Optimizer hint 는 실행계획을 변경하기 위해 SQL 문장에 사용됨
HINT 명시하기
- *statement block 은 1개의 hint 만 가질 수 있음
- 간단한 SELECT, UPDATE, DELETE 문장
- Complex statement 중 parant 문이나 subquery
- Compound query 의 각 부분
- 기본 작성형태 : /* + hint [text] [hint[text]] ... */
- +(plus sign : 오라클이 문장을 HINT로 해석되도록 유도
→ 구분기호 옆에 바로 작성해야하며 space 공간도 작성하면 안됨 - hint : 사용할 hint 를 작성함
- text : hint 에 사용될 다른 주석 text 를 작성함
- +(plus sign : 오라클이 문장을 HINT로 해석되도록 유도
HINT 종류
ORACLE 의 다양한 HINT 들 중 이 문서는 실제 업무에서 주로 사용하는 hint 만을 설명한 것
INDEX HINT
- 작성 형태 : /* + INDEX(TABLE_name, INDEX_name) */
- table 에는 스캔될 index 와 관련된 테이블의 이름이나 alias 작성
- index 에는 index scan 이 수행될 index 명을 작성
- 분류 : 접근경로 변경
- 용도 : 명시된 테이블을 index scan 함
- 특징
- HINT 에 Single avaliable index 를 명시하면 옵티마이저는 해당 index 의 index scan 을 수행
- HINT 에 list of avaliable index 를 명시하면 옵티마이저는 리스트의 각 index 에 대해서만 index scan cost 를 고려하여 가장 낮은 것 선택
USE_NL HINT
- 작성 형태 : /* + USE_NL(TABLE1 TABLE2) */
- table1 에는 nested loops join으 inner table로 사용될 테이블의 이름이나 alias 작성
- table1 에는 nested loops join으 outer table로 사용될 테이블의 이름이나 alias 작성
- 분류 : join 방법 변경
- 용도 : table1에 명시된 테이블이 inner table로 nested loops join 하게 함
- 특징
- 첫번째 행을 빠르게 반환
- best throughput 이 아닌 best reponse time 이나 minimal elapsed time 에 최적화
LEADING HINT
- 작성 형태 : /* + LEADING (TABLE1 [TABLE2] ... ) */
- table 에는 JOIN 할 테이블의 순서대로 이름이나 alias 작성
- 분류 : join 방법 변경
- 용도 : FROM 절에 기술한 테이블의 순서와 상관없이 HINT 에 명시된 테이블의 순서대로 JOIN
- 특징
- ORDERED hint 와는 달리 FROM 절을 변경할 필요없이 사용할 수 있음