콘텐츠로 이동
≡

Administrator

Developer

Tools

External Tools

6. SQL 힌트#

이 장은 사용자가 직접 SQL문의 실행 계획을 변경할 수 있는 SQL 힌트에 대해 설명한다.

힌트의 개요#

힌트는 SQL문 실행 계획을 사용자가 명시적으로 변경하고자 할 때 사용하는 기능이다.

사용자가 생성 가능한 SQL문은 그 수를 헤아릴 수가 없으며, 동일한 질의라 하더라도 데이터의 구성에 따라 서로 다른 실행 계획이 생성될 수 있다. 옵티마이저는 보편적으로 효율적인 실행 계획을 생성하기는 하나 모든 질의에 있어 가장 좋은 실행 계획을 생성할 수 있는 것은 아니다.

이러한 단점을 보완하기 위하여 사용자는 힌트를 사용하여 실행 계획을 명시적으로 변경하여 보다 나은 성능을 얻을 수 있다. 그러나 모든 질의에 대하여 힌트를 사용하여 실행 계획을 변경하는 것은 매우 불합리하다. 이는 인덱스의 생성, 데이터 구성의 변경 등에 대하여 모든 질의에 대하여 다시 힌트를 작성해야 하는 부하가 따르게 되기 때문이다. 따라서 시스템의 성능에 영향을 미치는 일부 치명적인 질의에 한하여 힌트를 사용하는 것이 바람직하다.

힌트 처리 정책#

사용자가 정의한 힌트는 다음과 같은 정책에 의하여 처리된다.

사용자가 정의한 힌트가 문법적으로 오류가 없고 실행이 가능한 경우 무조건 힌트를 따른다. 즉, 문법적으로 오류가 있는 경우나 실행이 불가능한 힌트는 적용되지 않는다.

힌트 사용법#

힌트는 아래의 구문에 명시할 수 있다

  • 단순 SELECT, UPDATE, DELETE, INSERT 구문

  • 복잡한 구문에서 메인 쿼리 또는 subquery

  • 복합 구문(집합 연산자로 묶인)에서 첫 번째 쿼리

SELECT, UPDATE, DELETE, INSERT 키워드 다음에 아래와 같이 힌트를 명시할 수 있다.

/*+ hint */

플러스 기호(+)는 그 주석이 힌트임을 Altibase에 알려주는 역할을 한다. 플러스 기호(+)는 주석 구분자 바로 뒤에 공백 없이 위치해야 한다.


힌트의 종류#

최적화 적용 기준#

최적화를 규칙 기반으로 수행할 것인지, 비용 기반으로 수행할 것인지를 설정하는 힌트이다.

RULE#

비용을 배제한 실행 계획 생성

COST#

비용을 고려한 실행 계획 생성

FIRST_ROWS(n)#

처음 n개의 행을 가장 효율적으로 반환할 수 있는 실행 계획 생성

질의에 따라 항상 같은 실행 계획이 생성되길 원한다면 RULE 힌트를 지정하며, 데이터의 변경되는 양이 커 조인 순서 등에 많은 영향을 미친다면 COST를 지정해 주는 것이 좋다. 만약 클라이언트가 n 개의 행만 가장 짧은 응답 시간으로 받기를 원한다면 FIRST_ROWS 힌트를 지정한다. FIRST_ROWS(n) 힌트는 비용 기반으로 동작하며 조인 방법에 영향을 준다. 아무런 힌트도 명시하지 않을 경우 기본적으로 비용 기반 최적화가 수행된다.

정규화 형태#

정규화 방법을 SQL문 단위로 설정할 수 있게 하는 힌트이다.

CNF#

Conjunctive Normal Form으로 정규화

Conjunctive Normal Form에서는 AND 연산자가 최상위 레벨의 논리 연산자이고 OR 연산자는 낮은 레벨의 연산자이다. CNF 힌트 사용시 Altibase는 실행 계획을 생성할 때 SELECT 문을 Conjunctive Normal Form 으로 변환한다. 그러나 가끔은 Conjunctive Normal Form으로 변환할 경우 아주 복잡한 조건절로 변환되고 이는 과도한 시스템 자원의 소모를 초래하게 된다. 이런 경우에는 힌트가 지정되었더라도 Altibase는 CNF 를 사용하지 않는다.

DNF#

Disjunctive Normal Form으로 정규화

Disjunctive Normal Form에서는 OR 연산자가 최상위 레벨의 논리 연산자이고 AND 연산자는 낮은 레벨의 연산자이다. DNF 힌트를 사용하면 SELECT 문을 Disjunctive Normal Form 으로 변환하는 실행 계획이 생성된다. 따라서 각 조건절은 각자의 인덱스를 사용하여 따로 처리된다.

단, SQL구문에 어떤 OR 절도 포함되어 있지 않다면, 이 힌트는 무시된다. 또한, 조건절의 속성에 따라서 DNF로 변환 시 너무 많은 수의 조건절이 생성될 수도 있고 이는 과도한 시스템 자원의 소모를 수반한다. 이런 경우에는 힌트가 지정되었더라도 Altibase는 DNF를 사용하지 않는다.

NO_EXPAND#

CNF와 같다.

USE_CONCAT#

DNF와 같다.

해당 힌트가 사용되지 않을 경우 두 정규화의 비용 비교를 통하여 하나의 정규화 방법이 선택된다.

조인 순서#

조인 순서를 결정하는 힌트이다.

LEADING#

힌트에 사용된 테이블들이 먼저 조인되도록 설정

ORDERED#

FROM절에 나열된 순서대로 조인 순서를 결정

해당 힌트가 사용되지 않을 경우 비용 비교를 통하여 조인 순서를 결정한다.

조인 방법#

조인 방법을 결정하는 힌트이다. 각 조인 방법에 대한 설명은 "3. 쿼리 옵티마이저" 장을 참조하고 힌트 사용법(구문)은 SQL Reference를 참조하기 바란다.

  • USE_NL

  • USE_FULL_NL

  • USE_FULL_STORE_NL

  • USE_INDEX_NL

  • USE_SORT

  • USE_ONE_PASS_SORT

  • USE_TWO_PASS_SORT

  • USE_HASH

  • USE_ONE_PASS_HASH

  • USE_TWO_PASS_HASH

  • USE_MERGE

  • USE_ANTI

  • NO_USE_HASH

  • NO_USE_MERGE

  • NO_USE_NL

  • NO_USE_SORT

조인 방법에 대한 힌트는 조인 방법을 결정하기 위해 다음과 같이 처리된다.

  • 옵티마이저는 내부 테이블이 나열된 순서로 조인 가능성 여부를 검사한다.

    예를 들어, USE_NL(t1, t2) 힌트인 경우 t1 => t2 로의 조인 가능 여부를 검사한다.

  • 옵티마이저는 내부 테이블이 나열된 역순으로 조인 가능성 여부를 검사한다.

    위의 검사에서 해당 순서로 조인을 적용할 수 없는 경우 그 역순인 t2 => t1 의 순서로 조인 가능 여부를 검사한다.

  • 위의 두 경우에 모두 해당하지 않으면, 비용 비교를 통하여 새로운 조인 방법을 선택한다.

  • ORDERED 힌트와 상충되는 경우

    예를 들어, 다음과 같은 질의가 있다고 가정하자.

    SELECT /*+ ORDERED USE_NL(t2, t1) */
  FROM t1, t2 WHERE t1.i1 = t2.i1;

    ORDERED 힌트와 USE_NL 힌트의 테이블 순서는 서로 상충되며 우선 순위에 의하여 ORDERED 힌트를 따르게 된다.

  • 동일 테이블에 대하여 여러 가지 조인 방법 힌트가 지정될 경우 나열된 방법 중 비용 평가를 통해 가장 효율적인 힌트가 선택된다.

    /*+ USE_NL(t1, t2) USE_HASH(t2, t1) */

  • NO_USE로 시작하는 힌트일 경우

    사용하지 않은 힌트를 제외한 힌트들의 조인 방법 중에 선택된다.

중간 결과 저장 매체#

중간 결과를 저장하기 위한 임시 공간의 저장 매체를 지정하는 힌트이다.

TEMP_TBS_MEMORY#

질의 처리 중에 생성되는 모든 중간 결과를 저장하기 위해 메모리 임시 공간을 사용한다.

TEMP_TBS_DISK#

질의 처리 중에 생성되는 모든 중간 결과를 저장하기 위해 디스크 임시 공간을 사용한다.

TEMP_TBS_MEMORY의 경우 저장되는 중간 결과의 크기가 적을 경우에 성능향상을 위해 사용하는 것이 바람직하며, TEMP_TBS_DISK는 성능 저하를 감안하더라도 저장되는 중간 결과의 크기가 매우 큰 경우에 리소스 절약을 위해 사용하는 것이 바람직하다.

해시 버킷 크기#

이 힌트는 해싱 기법을 사용하는 실행 노드들의 버킷 개수를 조정하기 위하여 사용된다.

옵티마이저는 GROUP BY, UNION, INTERSECT, MINUS, DISTINCT, HASH JOIN 및 aggregate functions 같은 구문을 처리하기 위해 해싱을 사용한다. 할당된 해시 버킷의 개수가 처리될 레코드의 개수에 적합하다면 질의 처리 속도가 향상될 것이다. 비용 기반 최적화에서 해시 버킷의 적절한 개수는 내부적으로 레코드 개수에 기반해서 결정된다. 그러나, 버킷의 개수를 임의로 지정하고 싶다면 다음의 힌트를 사용하면 된다.

HASH BUCKET COUNT(n)#

HASH와 HSDS 노드의 해시 버킷 수 지정

GROUP BUCKET COUNT(n)#

GRAG와 AGGR 노드의 해시 버킷 수 지정

SET BUCKET COUNT(n)#

SITS와 SDIF 노드의 해시 버킷 수 지정

그룹 처리 방법#

GROUP BY절의 처리 방법을 지정하기 위하여 사용하는 힌트이다.

GROUP_HASH#

해싱 방식에 의한 처리

GROUP_SORT#

정렬 방식에 의한 처리

중복 제거 처리 방법#

DISTINCT의 처리 방법을 지정하기 위하여 사용하는 힌트이다.

DISTINCT_HASH#

해싱 방식에 의한 처리

DISTINCT_SORT#

정렬 방식에 의한 처리

뷰 최적화 방법#

뷰 외부의 WHERE절의 조건을 뷰 내부에서 처리할 것인지의 여부를 결정하기 위하여 사용하는 힌트이다.

NO_PUSH_SELECT_VIEW(table_name)#

외부의 WHERE 절의 조건을 뷰 내부로 이동하여 처리하지 않는다.

PUSH_SELECT_VIEW(table_name)#

외부의 WHERE 절의 조건 중 가능한 것은 모두 뷰 내부로 이동하여 처리

액세스 방법#

테이블 접근 방법을 결정하는 힌트이다.

FULL SCAN(table_name)#

테이블에 이용 가능한 인덱스가 존재하더라도 인덱스를 사용하지 않고 테이블 전체 스캔

INDEX(table_name, index_name1, index_name2, ...)#

나열된 index중 하나를 이용해 인덱스 스캔

INDEX ASC(table_name, index_name1, index_name2, ...)#

나열된 index중 하나를 이용해 인덱스 오름차순 스캔 (ascending index scan)

INDEX_ASC(table_name, index_name1, index_name2, ...)#

INDEX ASC와 같다.

INDEX DESC(table_name, index_name1, index_name2, ...)#

나열된 인덱스중 하나를 이용해 인덱스 내림 차순 스캔 (descending index scan)

그러나 desc로 생성된 인덱스를 INDEX DESC 힌트에서 사용하면 정방향으로 탐색하기 때문에, 내림차순으로 정렬된 결과가 나온다.

INDEX_DESC(table_name, index_name1, index_name2, ...)#

INDEX DESC와 같다.

NO INDEX(table_name, index_name1, index_name2, ...)#

나열된 인덱스들은 최적화 과정에서 배제

NO_INDEX(table_name, index_name1, index_name2, ...)#

NO INDEX와 같다.

해당 힌트가 사용되지 않을 경우 가장 비용이 적은 방법이 선택된다. 뷰에 대해서도 일반 테이블처럼 베이스 테이블의 인덱스를 사용하여 인덱스 힌트를 줄 수 있다.

액세스 방법을 제어하는 힌트는 여러 개가 사용될 수 있다. 이들 힌트는 다음과 같은 정책에 의하여 처리된다.

  • 나열된 힌트가 상충하는 경우, 나열된 순서대로 힌트가 적용되고 뒤의 힌트는 무시된다.

    예제: /*+ INDEX(t1, idx1) NO INDEX(t1, idx1) */

  • 나열된 힌트가 상충되지 않을 경우 힌트에 나열된 액세스 방법 중 비용 계산에 기반하여 보다 효율적인 액세스 방법이 선택된다.

    예제: /*+ FULL SCAN(t1), INDEX(t1, idx1) */

  • 액세스 방법 힌트가 조인 방법 힌트와 함께 사용될 경우 액세스 방법 힌트와 조인 방법 힌트는 별개의 것으로 처리된다.

    예제: /*+ USE_HASH(t1, t2), INDEX(t2, idx2) */

  • 인덱스를 사용하여 접근되며 Hash-based 조인 방법으로 처리된다.

쿼리 변환#

Push Predicate 방법#

뷰 외부의 WHERE절의 조인 조건을 뷰 내부에서 처리할 것인지의 여부를 결정하기 위하여 사용하는 힌트이다.

PUSH_PRED#

외부의 WHERE절의 조건 중 뷰와 관계된 조인 조건을 뷰 내부로 이동하여 처리

중첩된 부질의 중첩 풀기 여부#

SELECT 구문에 "중첩된 부질의(Nested Subquery)"가 포함되어 있을 때, 쿼리 옵티마이저가 중첩 풀기(Subquery Unnesting)를 할 것인지 여부를 결정하는 힌트이다.

WHERE절에 포함된 부질의를 중첩된 부질의(Nested Subquery)이라고 하며, 결과 집합을 한정하기 위해 주로 메인 쿼리(Main Query, 외부 질의)에 있는 칼럼을 참조하는 형태를 띈다. 이렇게 중첩된 부질의가 포함된 쿼리를 중첩되지 않은 조인 형태의 쿼리로 변환하는 것을 "Subquery Unnesting"이라고 한다.

UNNEST#

Subquery Unnesting을 할 것을 지시한다.

NO_UNNEST#

Subquery Unnesting을 하지 말 것을 지시한다.

중첩된 부질의 중첩 풀기 시 조인 방법#

중첩된 부질의의 중첩을 풀 때 조인 방법을 결정하는 힌트이다.

아래는 Inner/Semi 조인 형태로 중첩이 풀리는 경우에 사용할 수 있는 힌트이다.

NL_SJ#

Nested loop join으로 중첩을 푼다.

HASH_SJ#

Hash join으로 중첩을 푼다.

SORT_SJ#

Sort join으로 중첩을 푼다.

MERGE_SJ#

Merge join으로 중첩을 푼다.

아래는 Anti 조인 형태로 중첩이 풀리는 경우에 사용할 수 있는 힌트이다.

NL_AJ#

Nested loop join으로 중첩을 푼다.

HASH_AJ#

Hash join으로 중첩을 푼다.

SORT_AJ#

Sort join으로 중첩을 푼다.

MERGE_AJ#

Merge join으로 중첩을 푼다.

위 힌트들은 반드시 부질의 내에 명시해야 하며, Semi 또는 Anti 조인용 힌트를 구분해서 명시해야 한다.

올바른 사용예:

SELECT *
  FROM t1
 WHERE EXISTS (SELECT /*+ NO_UNNEST */ *
                 FROM t2
                WHERE t2.a1 = t1.i1 );

SELECT *
  FROM t1
 WHERE EXISTS (SELECT /*+ HASH_SJ */ *
                 FROM t2
                WHERE t2.a1 = t1.i1 );

잘못된 사용예:

  • subquery가 아닌 main query에 힌트를 사용함
SELECT /*+ NO_UNNEST */ *
  FROM t1
 WHERE EXISTS (SELECT *
                 FROM t2
                WHERE t2.a1 = t1.i1 );
  • EXISTS는 inner/semi join으로 unnesting 되어야 하는데, anti join용 힌트를 사용함
SELECT *
  FROM t1
 WHERE EXISTS (SELECT /*+ HASH_AJ */*
                 FROM t2
                WHERE t2.a1 = t1.i1 );

플랜 캐시 관련 힌트#

아래는 플랜 캐시 관련 힌트이다.

NO_PLAN_CACHE#

생성된 플랜을 플랜 캐시에 저장하지 않도록 하는 힌트이다.

KEEP_PLAN#

한 번 생성된 플랜이 참조하는 테이블의 통계 정보가 변경되더라도 플랜이 재생성되는 것을 방지하고 그대로 사용하도록 하는 힌트이다. KEEP_PLAN 힌트는 쿼리의 direct/execute 수행뿐 아니라 prepare/execute 수행시에도 사용 가능하다.

PLAN_CACHE_KEEP#

Plan을 victim 선정 과정에서 제외시켜 Plan Cache내에 유지하도록 지시하는 힌트이다. 해당 힌트는 hardprepare 과정에서 적용된다. 그래서 사용자가 해당 plan을 unkeep으로 전환했을 때 softprepare가 발생해도 다시 keep 상태로 전환되지 않는다.

RESULT_CACHE#

중간 결과를 캐시하기 위해 사용한다.

TOP_RESULT_CACHE#

최종 결과를 캐시하기 위해 사용한다.

Direct-Path INSERT 관련 힌트#

아래의 Direct-Path INSERT 관련 힌트는 INSERT 구문에만 지정할 수 있다.

APPEND#

Direct-Path INSERT가 수행되도록 하는 힌트이다. Direct-Path INSERT는 데이터가 입력될 때 페이지의 빈 공간을 찾아 들어가는 대신 새로운 페이지를 만들어 데이터를 입력하는 방식이다.

단순 쿼리 처리 방법#

SELECT, INSERT, UPDATE, DELETE의 단순한 구문이 SIMPLE QUERY로 동작할 것인지 여부를 지정하기 위하여 사용하는 힌트이다.

EXEC_FAST#

SIMPLE QUERY로 동작

NO_EXEC_FAST#

SIMPLE QUERY로 비동작

병렬 질의 처리#

파티션드 테이블을 스캔할 때 병렬 질의를 설정할 수 있는 힌트이다.

NOPARALLEL#

병렬로 처리하지 않는다.

PARALLEL integer#

integer에 명시된 개수만큼의 쓰레드가 병렬로 처리한다.

NO_PARALLEL#

NOPARALLEL과 같다.

단 병렬 질의 힌트를 사용할 때 Nested Loop Join의 우측 테이블에서는 쓰레드의 관리 비용이 증가할 수 있어 사용이 불가하다. 또한 where절이나 select 대상(target) 절에서 사용되는 부질의(Subquery)에서도 병렬 질의를 사용할 수 없다.

산술 연산 관련 힌트#

사칙 연산 및 mod 연산 시 오차 발생을 방지하기 위하여 사용하는 힌트이다.

HIGH_PRECISION#

float 데이터 타입을 사용하여 38자리까지의 사칙 연산 및 mod 연산의 정밀도를 보장한다.

실행 계획의 지연 관련 힌트#

쿼리의 프로퍼티와 상관없이 실행 계획의 그래프를 기준으로 hierarchy, sorting, windowing, grouping, set, distinction의 실행(execute)이 패치(fetch)에서 수행되도록 지연시키는 기능을 제공한다. 상위 쿼리(top query set)에만 적용할 수 있다.

NO DELAY#

실행 계획의 실행을 지연하는 기능 비활성화

DELAY#

실행 계획의 실행을 지연하는 기능을 활성화