oracle - [Hint]ACCESS 경로를 변경하는 힌트(HASH)

[Hint]ACCESS 경로를 변경하는 힌트(HASH)
 
 
[Hint]ACCESS 경로를 변경하는 힌트(HASH)

인자로 기술한 테이블에 대해 HASH SCAN이 일어나도록 하는 힌트이며 USE_HASH(해시 조인이 일어나도록 하는 힌트)와 구별되며 HASHKEYS parameter를 가지고 만들어진 CLUSTER내에 저장된 테이블에서만 적용 됩니다.

[형식]
/*+ HASH ( table명 ) */

[예]
[먼저 클러스터를 생성]
CREATE CLUSTER EMP_DEPT_CLUSTER (deptno NUMBER(2))
   PCTUSED 80
   PCTFREE 5
   TABLESPACE users
   STORAGE ( INITIAL 250K NEXT 50K
             MINEXTENTS 1 MAXEXTENTS 3 PCTINCREASE 0)
   SIZE 2K
   HASH IS deptno HASHKEYS 150;
  

[실습 테이블을 만들면서 클러스테어 저장하기 위해 옵션 정의]
   CREATE TABLE MYDEPT (
      deptno NUMBER(2) PRIMARY KEY,
          dname  VARCHAR2(20) NOT NULL)
   CLUSTER EMP_DEPT_CLUSTER (deptno);
  
  
   CREATE TABLE MYEMP (
      empno  NUMBER(5) PRIMARY KEY,
          ename  VARCHAR2(50) NOT NULL,
          sal    NUMBER(9),
          deptno NUMBER(2) NOT NULL)
   CLUSTER EMP_DEPT_CLUSTER (deptno);

[이번에는 클러스터 인덱스를 만듭니다]
CREATE INDEX clu_emp_dept ON CLUSTER EMP_DEPT_CLUSTER;
  
[테스트를 위한 데이터를 만듭니다.]
   INSERT INTO MYEMP VALUES (1, '1±æμ¿', 5000, 10);
   INSERT INTO MYEMP VALUES (2, '2±æμ¿', 8000, 20);
   INSERT INTO MYEMP VALUES (3, '3±æμ¿', 9000, 30);
  
   INSERT INTO MYDEPT VALUES (10, '영업부’);
   INSERT INTO MYDEPT VALUES (20, '관리부');
   INSERT INTO MYDEPT VALUES (30, '해외영업부');
  
 
   SELECT  /*+ HASH(E) */
           E.ename,
           E.deptno,
           D.dname
   FROM    MYEMP E, MYDEPT D
   WHERE   D.deptno = E.deptno;

Execution Plan
--------------------------------------------------------------------
Operation        Object Name        Rows        Bytes        Cost       
SELECT STATEMENT Optimizer Mode=ALL_ROWS                3                   14
  NESTED LOOPS                3          195          14                                 
    TABLE ACCESS FULL        SCOTT.MYDEPT        3          75          14                   
    TABLE ACCESS HASH        SCOTT.MYEMP        1          40                                                              

[Query 결과]
1길동                                              10 관리부             
2길동                                              20 영업부             
3길동                                              30 해외영업부          ……

---------------------------------------------------------------

Oracle 10g에서 테스트 했을 때 위의 경우 NESTEDD LOOP로 풀리며 /*+ HASH(D) */ 또는 힌트를 사용하지 않더라도 EMP TABLE을 HASH SCAN을 하는 것으로 나타났습니다.

CLUSTER를 확인 할수 있는 VIEW는 다음과 같구요,

DBA_CLUSTERS         ALL_CLUSTERS         USER_CLUSTERS

아래에 간단한 HASH CLUSTER TABLE에 대한 설명이 있으니 참고 하세요~

다량의 범위를 자주 엑세스해야 하는 경우나 인덱스를 사용한 처리가 부담이 되는 범위(넓은 분포도), 수정이 자주 발생하지 않는 Column, 대규모 테이블, 여러 개의 테이블이 빈번한 조인을 일으킬 때 CLUSTER INDEX를 사용하시면 되는데 HASH CLUSTER에 테이블을 저장하는 것은 데이타 검색의 성능을 향상하기 위한 선택적인 방법 입니다. Hash Cluster는 인덱스나 인덱스 Cluster를 가지는 Cluster되지 않은 테이블의 대용이며 인덱스 테이블, 인덱스 Cluster와 함께 오라클은 별도의 인덱스에 저장된 키값을 사용하는 테이블 내의 로우(row)에 위치 합니다. 오라클은 물리적으로 Hash Cluster내의 테이블의 로우에 저장하고 Hash function의 결과에 의하여 검색 하는데 특정 Cluster 키값을 바탕으로 Hash Values라 불리는 분산된 수치 값을 생성하는 Hash Function을 사용하는 것입니다.