반응형

[오라클] 쿼리(query) Hint 내용 정리


A. Initialization Parameter중 OPTIMIZER_MODE 지정가능 값

1.ALL_ROWS

전체 RESOURCE 소비를 최소화 시키기 위한 힌트. Cost-Based 접근방식.

SELECT /*+ALL_ROWS */

EMPNO,ENAME

FROM EMP

WHERE EMPNO = 7655;


2.FIRST_ROWS

조건에 맞는 첫번째 row를 리턴하기 위한 Resource 소비를 최소화 시키기위한 힌트.

특징 : Index Scan 이 가능하다면 Optimizer가 Full Table Scan 대신 Index Scan을 선택합니다.

Index Scan 이 가능하다면 Optimizer가 Sort-Merge 보다 Nested Loop 을 선택합니다.

Order By절에의해 Index Scan 이 가능하다면, Sort과정을 피하기위해 Index Scan을 선택합니다.

Delete/Update Block 에서는 무시됩니다.

다음을 포함한 Select 문에서도 제외됩니다.

집합연산자 (UNION, INTERSECT, MINUS, UNION ALL)

Group By

For UpDate

Group 함수

Distinct


SELECT /*+FIRST_ROWS */

EMPNO,ENAME

FROM EMP

WHERE EMPNO = 7655;


3.CHOOSE

Data Dictionary가 해당테이블에 대해 통계정보를 가지고 있다면 Optimizer는 Cost-Based Approach를 선택하고, 그렇지 않다면 Rule-Based Approach를 선택합니다.


SELECT /*+CHOOSE */

EMPNO,ENAME

FROM EMP

WHERE EMPNO = 7655;


4.RULE

Rule-Based 최적화를 사용하기 위함 입니다.


SELECT /*+RULE */

EMPNO,ENAME

FROM EMP

WHERE EMPNO = 7655;


B. Access Methods 로써의 Hints


1.FULL

해당테이블의 Full Table Scan을 유도.

테이블 Alias 가 있는경우는 Alias사용.

Schema Name은 사용안함(From 에 SCOTT.EMP 라고 기술해도 hint에는 EMP사용).


SELECT /*+FULL(EMP) */

EMPNO, ENAME

FROM EMP

WHERE EMPNO = 7655;


2.ROWID

지정된 테이블의 ROWID를 이용한 Scan 유도


3.CLUSTER

지정된 테이블Access에 Cluster Scan 유도. Cluster된 Objects에만 적용가능.


SELECT /*+CLUSTER(EMP) */

ENAME,DEPTNO

FROM EMP,DEPT

WHERE DEPTNO = 10

AND EMP.DEPTNO = DEPT.DEPTNO;




4.HASH

지정된 테이블Access에 HASH Scan 유도.

/*+HASH(table) */


5.HASH_AJ

NOT IN SubQuery 를 HASH Anti-join으로 변형

/*+HASH_AJ */


6.HASH_SJ

용도 : Correlated Exists Subquery 를 Hash Semi-join으로 변형

/*+HASH_SJ */


7.INDEX

지정된 테이블access에 Index Scan 유도.

하나의 Index만 지정되면 Optimizer는 해당index를 이용.

여러개의 인덱스가 지정되면 Optimizer가 각 Index의 Scan시 Cost를 분석 한 후 최소비용이 드는 Index사용. 경우에 따라 Optimizer는 여러 Index를 사용한 후 결과를 Merge하는 Acees방식도 선택합니다.

Index가 지정되지 않으면 Optimizer는 테이블의 이용가능한 모든 Index에 대해 Scan Cost를 고려 후 최저비용이 드는 Index Scan을 선택합니다.


SELECT /*+INDEX(EMP EMPNO_INDEX) */

EMPNO, ENAME

FROM EMP

WHERE DEPTNO=10;


8.INDEX_ASC

INDEX HINT와 동일 단,ASCENDING 으로 SCAN함을 확실히 하기위함 입니다.


9.INDEX_COMBINE

Index명이 주어지지 않으면 Optimizer는 해당 테이블의 Best Cost 로 선택된 Boolean Combination Index 를 사용한다. Index 명이 주어지면 주어진 특정 Bitmap Index 의 Boolean Combination 의 사용을 시도합니다.

/*+INDEX_COMBINE(table index) */


10.index_desc

지정된 테이블의 지정된 Index를 이용 Descending으로 Scan 하고자 할 때 사용합니다.

/*+index_desc(table index) */


11.INDEX_FFS

Full Table Scan보다 빠른 Full Index Scan을 유도.

/*+INDEX_FFS(table index) */


12.MERGE_AJ

NOT IN Subquery를 Merge Anti-join으로 변형

/*+MERGE_AJ */


13.MERGE_SJ

Correalted EXISTS Subquery를 Merge Semi-join으로 변형

/*+MERGE_SJ */


14.AND_EQUAL

Single-column Index의 Merge를 이용한 Access Path 선택. 적어도 두개이상의 Index가 지정되어야 합니다. 

/*+AND_EQUAL(Table Index1, Index2...) */


15.USE_CONCAT

조건절의 OR 를 UNION ALL 형식으로 변형합니다. 일반적으로 변형은 비용측면에서 효율적일때만 일어납니다.

/*+USE_CONCAT */


C. JOIN 순서를 결정하는 Hints

1.ORDERED

FROM절에 기술된 테이블 순서대로 JOIN이 일어나도록 유도.

/*+ORDERED */


SELECT /*+ORDERED */

TAB1.COL1,TAB2.COL2,TAB3.COL3

FROM TAB1,TAB2,TAB3

WHERE TAB1.COL1=TAB2.COL1

AND TAB2.COL1=TAB3.COL1;


2.STAR

Star Query Plan이 사용가능하다면 이를 이용하기위한 Hint. Star Plan은 규모가 가장큰 테이블이 Query에서 Join Order상 마지막으로 위치하게 하고 Nested Loop 으로 Join이 일어나도록 유도합니다.

적어도 3개 테이블 이상이 조인에 참여해야하며 Large Table의 Concatenated Index는 최소 3컬럼 이상을 Index에 포함해야합니다.

테이블이 Analyze 되어 있다면 Optimizer가 가장효율적인 Star Plan을 선택합니다.

/*+STAR */


D. JOIN OPERATION을 결정하는 HINTS.

1.USE_NL

테이블의 Join 시 테이블의 각 Row가 Inner 테이블을 Nested Loop 형식으로 Join 한다.

/*+USE_NL(inner_table) */


SELECT /*+ORDERD USE_NL(CUSTOMER) */

FROM ACCOUNT.BALANCE,

CUSTOMER.LAST_NAME,

CUSTOMER.FIRST_NAME

WHERE ACCOUNT.CUSTNO = CUSTOMER.CUSTNO;


2.USE_MERGE

지정된 테이블들의 조인이 SORT-MERGE형식으로 일어나도록 유도.

괄호안의 테이블은 JOIN ORDER상의 뒤의 테이블

/*+USE_MERGE(table) */


3.USE_HASH

각 테이블간 HASH JOIN이 일어나도록 유도.

괄호안의 테이블은 JOIN ORDER상의 뒤의 테이블

/*+USE_HASH(table) */


4.DRIVING_SITE

QUERY의 실행이 ORACLE에 의해 선택된 SITE가 아닌 다른 SITE에서 일어나도록 유도.

/*+DRIVING_SITE(table) */


SELECT /*+DRIVING_SITE(DEPT) */

FROM EMP,DEPT@RSITE

WHERE EMP.DEPTNO = DEPT.DEPTNO;


DRIVING_SITE 힌트를 안쓰면 DEPT의 ROW가 LOCAL SITE로 보내져 LOCAL SITE에서 JOIN이 일어나지만,

DRIVING_SITE 힌트를 쓰면 EMP의 ROW들이REMOTE SITE로 보내져 QUERY가 실행된후 LOCAL SITE로 결과가 RETURN됩니다.



( 출처 database.sarang.net 내용 정리 )

반응형
반응형
[오라클] 동일 데이터 반복 조회 access에 대한 쿼리 튜닝 방법
감사합니다. 고맙습니다. 행운을 드립니다.

간혹 최소, 최대값등에 의해 값을 구해야 할경우가 있습니다.

서브쿼리를 이용하여 데이터를 조회한후에 또다시 결과를 조회할경우 테이블 풀스캔이 반복적으로 발생합니다.

이럴경우 RANK를 사용하면 성능에 좋습니다.

예제)

SELECT * FROM EMP
WHERE (JOB, ENAME) IN
(SELECT JOB, MIN(ENAME) AS ENAME FROM EMP GROUP BY JOB)
ORDER BY ENAME;

=>

SELECT * FROM
(SELECT A.*, RANK() OVER(PARTITION BY JOB ORDER BY ENAME) RNK
 FROM EMP A)
WHERE RNK=1
ORDER BY ENAME;
반응형
반응형

오라클 접속 가능한 세션의 수 변경

http://db.necoaki.net/144

오라클 안에는 세션에 관련된 두개의 파라미터 값이 있다.

show parameter processes


SESSIONS = (PROCESSES * 1.1) + 5

 ex> 150 *1.1+5= 170


현재 DB의 최대 접속 가능 세션은 170개 이다.

spfile을 사용 하는 경우 아래의 명령으로 변경 가능하다. (DB 재구동 후 반영)


SQL> alter system set PROCESSES = 300 scope=spfile;

pfile을 사용하는 경우 파라미터 파일을 직접 열어서 *.processes= 값을 변경해주고 재구동 해주면 된다.



반응형
반응형

오라클 테이블 권한 주기(oracle table grant)


1. 테이블 조회 권한 주기 [SELECT]


GRANT SELECT ON 권한 줄 테이블명 TO 권한 받을 사용자계정;


2. 테이블 입력이나 수정 삭제의 권한을 부여 [INSERT, UPDATE, DELETE]


GRANT INSERT, UPDATE, DELETE ON 권한줄 테이블명 TO 권한받을 사용자 계정;


3. FUNCTION 실행 권한 주기 [execute]


GRANT EXECUTE ON 권한 줄 함수명 TO 권한 받을 사용자계정


* SYNONM 생성


내 계정에서 다른 계정의 특정 테이브을 내 계정에서 볼때 Alias(별칭)을 정해서

그 테이블을 참조할때 사용한다.

(타 계정 테이블을 참조 또는 변경하기 위해서 권한이 부여 되면 자신의 계정에서 

 SYNONM을 생성하면 된다. )


CREATE SYNONM 시노임 명 FOR 계정명.테이블명;


[예제] 


본인계정 : MY_USER

타 계정 : OTHER_USER

테이블명 : OTHER_TABLE


SELECT * FROM OTHER_USER.OTHER_TABLE;


CREATE SYNONM OTHER FOR OTHER_USER.OTHER_TABLE;


SELECT * FROM OTHER;


결과는 같은 값이 나온다. 





반응형
반응형

SYNONYM은 오라클 객체(테이블,뷰,시퀀스,프로시저)에 대한 Alias를 말한다.

SYNONYM은 실질적으로 그차제가 Object가 아니라 Object에 대한 직접적인 참조 


- 실무에서 다른 유저의 객체를 참고할 경우 SYNONYM을 생성해서 사용을 하면

  추후에 참고하고 있는 오프젝트가 이름을 바꾸거나 이동을 할 경우 객체를 사용하는

  SQL 구문을 모두 다시 고칠 필요가 없이 SYNONYM 만 다시 정의 하면 된다.

- 긴 이름을 단순하게, 짧게 만들어 쓸 수 있다. 

- 객체를 참조하는 사용자의 오브젝트를 감출 수 있기 때문에 보안 유지가 된다. 

  SYNONYM을 사용하는 유저는 참조하고 있는 객체에 대한 사용자의 오브젝트 

  소유자,이름,서버이름을 몰라도 SYNONYM이름만 알면 사용할 수 있다. 


SYNONYM 사용하는 경우

#오브젝트의 실제 이름과 소유자 그리고 위치를 감추고 DB보안을 개선하는데 사용

#오브젝트에 Public Access를 제공

#Remote DB의 table,view, program Init를 위해 투명성 제공

#DB SQL문 단순화


SYNONYM 두가지 종류가 있다.

- Private SYNONYM

  특정사용사만 이용할 수있다.

- Publice SYNONYM

  DB에 있는 모든 사용자가 공유 하여 사용할 수 있다. 



SYNONYM 구문


CREATE [PUBLIC] SYNONYM synonm_name

FOR object_name


- Public : 모든 사용자가 접근 가능한 SYNONYM을 생성

             Publice SYNONYM 은 DBA만 생성할 수 있다.



% scott USER의 emp 테이블을 test USER가 사용하는 예제

1. 먼저 scott/tiger USER로 접속해서 test USER에게 emp 테이블의 조작할 권한을 부여한다


GRANT ALL ON emp TO test;

권한이 부여 되었습니다. 


test user에 대하여 scott의 emp테이블을 조작할 수 있는 권한을 부여한다.

권한이 있어야 select 하거나 update, insert 할 수 있다.


2. test USER로 접속해 동의어를 생성한다 


connect test/test


GREATE SYNONYM scott_emp FOR scott.emp;

시노임이 생성 됨


scott USER가 소유하고 있는 emp테이블에 대해 scott_emp라는 일반 시노임을 생성

scott 사용자의 emp 테이블을 test 사용자가 scott_emp라는 동의러로 사용한다.


-- SYNONYM을 이용한 쿼리

select empno ,ename from scott_emp;


-- 일반 테이블 쿼리

select empno ,ename from scott.emp;


두 쿼리 결과는 같다.


동의어 삭제


DROP SYNONM scott_emp;

시노임이 삭제 되었습니다. 


select empno ,ename from scott_emp;

라인 1에 오류:

ORA-00942: 테이블 또는 뷰가 존재 하지 않는다. 


http://jhbench.tistory.com/200 블로그 참조

반응형

+ Recent posts