문서유형ㅣ기술정보
분야ㅣ튜닝
적용제품버전ㅣTibero 7.2.3
문서번호ㅣTTUTI032
개요
티베로에서 제공하는 힌트 종류를 알아보고 힌트 적용방법 및 각 힌트 적용시 플랜 변경에 대해 기술한 문서입니다.
힌트 종류
보다 더 자세한 정보는 매뉴얼 'Tibero SQL 참조 안내서' 2.8절을 참고합니다.
방법
힌트 적용 방법
-- 1. SELECT SELECT /*+ 힌트 */ 칼럼들 FROM 테이블; -- 2. INSERT INSERT /*+ 힌트 */ INTO 테이블 VALUES (...); -- 3. UPDATE UPDATE /*+ 힌트 */ 테이블 SET 칼럼 = 값; -- 4. DELETE DELETE /*+ 힌트 */ FROM 테이블 WHERE 조건; -- 5. MERGE MERGE /*+ 힌트 */ INTO 테이블 USING... *테이블에 알리아스를 명시하였을 경우 테이블명 대신 해당 알리아스를 사용해야함
힌트 적용 예시
1. SELECT 힌트 위치
SELECT /*+ INDEX(e idx_emp_salary) */
emp_id, emp_name, salary
FROM employees e
WHERE salary > 5000;
SELECT /*+ FULL(e) PARALLEL(e, 4) */
emp_id, emp_name, dept_id, salary
FROM employees e
WHERE dept_id = 10;
SELECT /*+ LEADING(d e) USE_HASH(e) */
d.dept_name, e.emp_name, e.salary
FROM departments d
JOIN employees e ON d.dept_id = e.dept_id;
2. INSERT 힌트 위치
INSERT /*+ APPEND */ INTO employees
VALUES (1001, 'New Employee', 10, 5000, SYSDATE, 'Developer');
INSERT /*+ APPEND PARALLEL(employees, 4) */ INTO employees
SELECT emp_id + 1000, emp_name, dept_id, salary, hire_date, job_title
FROM employees
WHERE dept_id = 10;
3. UPDATE 문 힌트 위치
UPDATE /*+ INDEX(e idx_emp_salary) */ employees e
SET salary = salary * 1.1
WHERE salary < 4000;
UPDATE /*+ FULL(e) PARALLEL(e, 4) */ employees e
SET salary = salary * 1.05
WHERE hire_date < DATE '2022-01-01';
4. DELETE 문 힌트 위치
DELETE /*+ INDEX(e idx_emp_salary) */ FROM employees e
WHERE salary < 3000;
DELETE /*+ FULL(e) PARALLEL(e, 4) */ FROM employees e
WHERE hire_date < DATE '2020-01-01';
DELETE /*+ INDEX(sh idx_sal_hist_date) */ FROM salary_history sh
WHERE change_date < ADD_MONTHS(SYSDATE, -24);
5. MERGE 문 힌트 위치
MERGE /*+ USE_HASH(e d) */ INTO employees e
USING departments d
ON (e.dept_id = d.dept_id)
WHEN MATCHED THEN
UPDATE SET e.salary = e.salary * 1.1;
MERGE /*+ PARALLEL(e, 4) APPEND */ INTO employees e
USING (SELECT * FROM employees WHERE dept_id = 10) src
ON (e.emp_id = src.emp_id)
WHEN MATCHED THEN
UPDATE SET e.salary = src.salary * 1.2
WHEN NOT MATCHED THEN
INSERT VALUES (src.emp_id, src.emp_name, src.dept_id, src.salary, src.hire_date, src.job_title);
힌트 적용에 따른 플랜 변경 확인
테스트 유저 생성
[tibero@node1 ~]$ tbsql sys/tibero tbSQL 7 TmaxTibero Corporation Copyright (c) 2020-. All rights reserved. Connected to Tibero. SQL> create user test identified by test; User 'TEST' created. SQL> grant dba to test; Granted. SQL> conn test/test Connected to Tibero.
테스트용 테이블 및 데이터 적재
SQL> conn test/test
Connected to Tibero.
SQL> CREATE TABLE departments (
dept_id NUMBER PRIMARY KEY,
dept_name VARCHAR2(50),
location VARCHAR2(50)
); 2 3 4 5
Table 'DEPARTMENTS' created.
SQL> CREATE TABLE employees (
emp_id NUMBER PRIMARY KEY,
emp_name VARCHAR2(50),
dept_id NUMBER,
salary NUMBER,
hire_date DATE,
job_title VARCHAR2(50)
); 2 3 4 5 6 7 8
Table 'EMPLOYEES' created.
SQL> CREATE TABLE salary_history (
history_id NUMBER PRIMARY KEY,
emp_id NUMBER,
old_salary NUMBER,
new_salary NUMBER,
change_date DATE
); 2 3 4 5 6 7
Table 'SALARY_HISTORY' created.
SQL> INSERT INTO departments VALUES (10, 'IT', 'Seoul');
1 row inserted.
SQL> INSERT INTO departments VALUES (20, 'Sales', 'Busan');
1 row inserted.
SQL> INSERT INTO departments VALUES (30, 'HR', 'Incheon');
1 row inserted.
SQL> INSERT INTO departments VALUES (40, 'Finance', 'Daegu');
1 row inserted.
SQL> INSERT INTO departments VALUES (50, 'Marketing', 'Seoul');
1 row inserted.
SQL> BEGIN
FOR i IN 1..1000 LOOP
INSERT INTO employees VALUES (
i,
'Employee_' || i,
MOD(i, 5) * 10 + 10, -- 부서 ID: 10, 20, 30, 40, 50
3000 + MOD(i, 50) * 100, -- 급여: 3000 ~ 7900
ADD_MONTHS(DATE '2020-01-01', MOD(i, 48)), -- 입사일
CASE MOD(i, 4)
WHEN 0 THEN 'Manager'
WHEN 1 THEN 'Developer'
WHEN 2 THEN 'Analyst'
ELSE 'Clerk'
END
);
END LOOP;
COMMIT;
END;
/ 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19
PSM completed.
SQL> BEGIN
FOR i IN 1..500 LOOP
INSERT INTO salary_history VALUES (
i,
MOD(i, 1000) + 1, -- emp_id: 1~1000
3000 + MOD(i, 40) * 100,
3500 + MOD(i, 40) * 100,
ADD_MONTHS(SYSD 2 ATE, -MOD(i, 24))
);
END LOOP;
COMMIT;
END;
/ 3 4 5 6 7 8 9 10 11 12 13
PSM completed.
SQL> commit;
Commit completed.
테스트 테이블 인덱스 생성
SQL> CREATE INDEX idx_emp_dept ON employees(dept_id); Index 'IDX_EMP_DEPT' created. SQL> CREATE INDEX idx_emp_salary ON employees(salary); Index 'IDX_EMP_SALARY' created. SQL> CREATE INDEX idx_emp_hire ON employees(hire_date); Index 'IDX_EMP_HIRE' created. SQL> CREATE INDEX idx_emp_dept_sal ON employees(dept_id, salary); Index 'IDX_EMP_DEPT_SAL' created. SQL> CREATE INDEX idx_sal_hist_emp ON salary_history(emp_id); Index 'IDX_SAL_HIST_EMP' created. SQL> CREATE INDEX idx_sal_hist_date ON salary_history(change_date); Index 'IDX_SAL_HIST_DATE' created.
테스트 테이블 통계 수집 및 데이터 확인
SQL> EXEC DBMS_STATS.GATHER_TABLE_STATS('TEST', 'DEPARTMENTS');
PSM completed.
SQL> EXEC DBMS_STATS.GATHER_TABLE_STATS('TEST', 'EMPLOYEES');
PSM completed.
SQL> EXEC DBMS_STATS.GATHER_TABLE_STATS('TEST', 'SALARY_HISTORY');
PSM completed.
SQL> SELECT COUNT(*) AS dept_count FROM departments;
DEPT_COUNT
----------
5
1 row selected.
SQL> SELECT COUNT(*) AS emp_count FROM employees;
EMP_COUNT
----------
1000
1 row selected.
SQL> SELECT COUNT(*) AS history_count FROM salary_history;
HISTORY_COUNT
-------------
500
1 row selected.
SQL> SELECT * FROM departments;
DEPT_ID DEPT_NAME LOCATION
---------- -------------------------------------------------- --------------------------------------------------
10 IT Seoul
20 Sales Busan
30 HR Incheon
40 Finance Daegu
50 Marketing Seoul
5 rows selected.
SQL> SELECT * FROM employees WHERE ROWNUM <= 10;
EMP_ID EMP_NAME DEPT_ID SALARY
---------- -------------------------------------------------- ---------- ----------
HIRE_DATE
--------------------------------------------------------------------------------------------------------------------------------
JOB_TITLE
--------------------------------------------------
650 Employee_650 10 3000
2022/03/01
Analyst
651 Employee_651 20 3100
2022/04/01
Clerk
652 Employee_652 30 3200
2022/05/01
Manager
653 Employee_653 40 3300
2022/06/01
Developer
654 Employee_654 50 3400
2022/07/01
Analyst
EMP_ID EMP_NAME DEPT_ID SALARY
---------- -------------------------------------------------- ---------- ----------
HIRE_DATE
--------------------------------------------------------------------------------------------------------------------------------
JOB_TITLE
--------------------------------------------------
655 Employee_655 10 3500
2022/08/01
Clerk
656 Employee_656 20 3600
2022/09/01
Manager
657 Employee_657 30 3700
2022/10/01
Developer
658 Employee_658 40 3800
2022/11/01
Analyst
659 Employee_659 50 3900
2022/12/01
Clerk
10 rows selected.
힌트 적용 및 플랜 변경 확인
--기본 쿼리
SELECT
e.emp_id,
e.emp_name,
d.dept_name,
e.salary,
sh.new_salary,
sh.change_date
FROM employees e
JOIN departments d ON e.dept_id = d.dept_id
LEFT JOIN salary_history sh ON e.emp_id = sh.emp_id
WHERE e.dept_id IN (
SELECT dept_id
FROM employees
GROUP BY dept_id
HAVING AVG(salary) >= 5000
)
AND e.salary > 4000
ORDER BY e.salary DESC;
--기본 쿼리 플랜
------------------------------------------------------------------------------------------------
| ID | Operation | Name | Cost (%CPU) | Cards | Elaps. Time |
------------------------------------------------------------------------------------------------
| 1 | ORDER BY (SORT) | | 14 (0)| 157 |00:00:00.0000 |
| 2 | INDEX JOIN (LEFT OUTER) | | 14 (0)| 157 |00:00:00.0000 |
| 3 | HASH JOIN | | 11 (0)| 157 |00:00:00.0000 |
| 4 | TABLE ACCESS (ROWID) | DEPARTMENTS | 3 (0)| 5 |00:00:00.0000 |
| 5 | INDEX (FULL) | _TEST_CON54700033 | 2 (0)| 5 |00:00:00.0000 |
| 6 | INDEX JOIN | | 8 (0)| 157 |00:00:00.0000 |
| 7 | FILTER | | 4 (0)| 1 |00:00:00.0000 |
| 8 | SORT AGGR | | 4 (0)| 5 |00:00:00.0000 |
| 9 | INDEX (FULL) | IDX_EMP_DEPT_SAL | 4 (0)| 1000 |00:00:00.0000 |
| 10 | TABLE ACCESS (ROWID) | EMPLOYEES | 4 (0)| 157 |00:00:00.0000 |
| 11 | INDEX (RANGE SCAN) | IDX_EMP_DEPT_SAL | 2 (0)| 157 |00:00:00.0000 |
| 12 | TABLE ACCESS (ROWID) | SALARY_HISTORY | 3 (0)| 1 |00:00:00.0000 |
| 13 | INDEX (RANGE SCAN) | IDX_SAL_HIST_EMP | 2 (0)| 1 |00:00:00.0000 |
------------------------------------------------------------------------------------------------
--LEADING 힌트 적용 쿼리
SELECT /*+ LEADING(d e sh) */
e.emp_id,
e.emp_name,
d.dept_name,
e.salary,
sh.new_salary,
sh.change_date
FROM employees e
JOIN departments d ON e.dept_id = d.dept_id
LEFT JOIN salary_history sh ON e.emp_id = sh.emp_id
WHERE e.dept_id IN (
SELECT dept_id
FROM employees
GROUP BY dept_id
HAVING AVG(salary) >= 5000
)
AND e.salary > 4000
ORDER BY e.salary DESC;
--LEADING 힌트 적용 쿼리 플랜
------------------------------------------------------------------------------------------------
| ID | Operation | Name | Cost (%CPU) | Cards | Elaps. Time |
------------------------------------------------------------------------------------------------
| 1 | ORDER BY (SORT) | | 20 (5)| 157 |00:00:00.0000 |
| 2 | HASH JOIN | | 20 (5)| 157 |00:00:00.0000 |
| 3 | FILTER | | 4 (0)| 1 |00:00:00.0000 |
| 4 | SORT AGGR | | 4 (0)| 5 |00:00:00.0000 |
| 5 | INDEX (FULL) | IDX_EMP_DEPT_SAL | 4 (0)| 1000 |00:00:00.0000 |
| 6 | HASH JOIN (LEFT OUTER) | | 15 (0)| 786 |00:00:00.0000 |
| 7 | INDEX JOIN | | 10 (0)| 786 |00:00:00.0000 |
| 8 | TABLE ACCESS (ROWID) | DEPARTMENTS | 3 (0)| 5 |00:00:00.0000 |
| 9 | INDEX (FULL) | _TEST_CON54700033 | 2 (0)| 5 |00:00:00.0000 |
| 10 | TABLE ACCESS (ROWID) | EMPLOYEES | 4 (0)| 157 |00:00:00.0000 |
| 11 | INDEX (RANGE SCAN) | IDX_EMP_DEPT_SAL | 2 (0)| 157 |00:00:00.0000 |
| 12 | TABLE ACCESS (ROWID) | SALARY_HISTORY | 5 (0)| 500 |00:00:00.0000 |
| 13 | INDEX (FULL) | IDX_SAL_HIST_EMP | 3 (0)| 500 |00:00:00.0000 |
------------------------------------------------------------------------------------------------
--USE_NL 힌트 적용 쿼리
SELECT /*+ USE_NL(e d) USE_NL(sh) */
e.emp_id,
e.emp_name,
d.dept_name,
e.salary,
sh.new_salary,
sh.change_date
FROM employees e
JOIN departments d ON e.dept_id = d.dept_id
LEFT JOIN salary_history sh ON e.emp_id = sh.emp_id
WHERE e.dept_id IN (
SELECT dept_id
FROM employees
GROUP BY dept_id
HAVING AVG(salary) >= 5000
)
AND e.salary > 4000
ORDER BY e.salary DESC;
--USE_NL 힌트 적용 쿼리 플랜
------------------------------------------------------------------------------------------------
| ID | Operation | Name | Cost (%CPU) | Cards | Elaps. Time |
------------------------------------------------------------------------------------------------
| 1 | ORDER BY (SORT) | | 18 (5.56)| 157 |00:00:00.0000 |
| 2 | INDEX JOIN (LEFT OUTER) | | 18 (5.56)| 157 |00:00:00.0000 |
| 3 | INDEX JOIN | | 14 (0)| 157 |00:00:00.0000 |
| 4 | NESTED LOOPS | | 7 (0)| 5 |00:00:00.0000 |
| 5 | FILTER | | 4 (0)| 1 |00:00:00.0000 |
| 6 | SORT AGGR | | 4 (0)| 5 |00:00:00.0000 |
| 7 | INDEX (FULL) | IDX_EMP_DEPT_SAL | 4 (0)| 1000 |00:00:00.0000 |
| 8 | TABLE ACCESS (ROWID) | DEPARTMENTS | 3 (0)| 5 |00:00:00.0000 |
| 9 | INDEX (FULL) | _TEST_CON54700033 | 2 (0)| 5 |00:00:00.0000 |
| 10 | TABLE ACCESS (ROWID) | EMPLOYEES | 4 (0)| 157 |00:00:00.0000 |
| 11 | INDEX (RANGE SCAN) | IDX_EMP_DEPT_SAL | 2 (0)| 157 |00:00:00.0000 |
| 12 | TABLE ACCESS (ROWID) | SALARY_HISTORY | 3 (0)| 1 |00:00:00.0000 |
| 13 | INDEX (RANGE SCAN) | IDX_SAL_HIST_EMP | 2 (0)| 1 |00:00:00.0000 |
------------------------------------------------------------------------------------------------
--USE_HASH 힌트 적용 쿼리
SELECT /*+ USE_HASH(e d) USE_HASH(sh) */
e.emp_id,
e.emp_name,
d.dept_name,
e.salary,
sh.new_salary,
sh.change_date
FROM employees e
JOIN departments d ON e.dept_id = d.dept_id
LEFT JOIN salary_history sh ON e.emp_id = sh.emp_id
WHERE e.dept_id IN (
SELECT dept_id
FROM employees
GROUP BY dept_id
HAVING AVG(salary) >= 5000
)
AND e.salary > 4000
ORDER BY e.salary DESC;
--USE_HASH 힌트 적용 쿼리 플랜
------------------------------------------------------------------------------------------------
| ID | Operation | Name | Cost (%CPU) | Cards | Elaps. Time |
------------------------------------------------------------------------------------------------
| 1 | ORDER BY (SORT) | | 20 (0)| 157 |00:00:00.0000 |
| 2 | HASH JOIN (LEFT OUTER) | | 20 (0)| 157 |00:00:00.0000 |
| 3 | HASH JOIN | | 15 (0)| 157 |00:00:00.0000 |
| 4 | TABLE ACCESS (ROWID) | DEPARTMENTS | 3 (0)| 5 |00:00:00.0000 |
| 5 | INDEX (FULL) | _TEST_CON54700033 | 2 (0)| 5 |00:00:00.0000 |
| 6 | HASH JOIN | | 12 (0)| 157 |00:00:00.0000 |
| 7 | FILTER | | 4 (0)| 1 |00:00:00.0000 |
| 8 | SORT AGGR | | 4 (0)| 5 |00:00:00.0000 |
| 9 | INDEX (FULL) | IDX_EMP_DEPT_SAL | 4 (0)| 1000 |00:00:00.0000 |
| 10 | TABLE ACCESS (ROWID) | EMPLOYEES | 8 (0)| 786 |00:00:00.0000 |
| 11 | INDEX (RANGE SCAN) | IDX_EMP_SALARY | 2 (0)| 786 |00:00:00.0000 |
| 12 | TABLE ACCESS (ROWID) | SALARY_HISTORY | 5 (0)| 500 |00:00:00.0000 |
| 13 | INDEX (FULL) | IDX_SAL_HIST_EMP | 3 (0)| 500 |00:00:00.0000 |
------------------------------------------------------------------------------------------------
--INDEX 힌트 적용 쿼리
SELECT /*+ INDEX(e idx_emp_salary) */
e.emp_id,
e.emp_name,
d.dept_name,
e.salary,
sh.new_salary,
sh.change_date
FROM employees e
JOIN departments d ON e.dept_id = d.dept_id
LEFT JOIN salary_history sh ON e.emp_id = sh.emp_id
WHERE e.dept_id IN (
SELECT dept_id
FROM employees
GROUP BY dept_id
HAVING AVG(salary) >= 5000
)
AND e.salary > 4000
ORDER BY e.salary DESC;
--INDEX 힌트 적용 쿼리 플랜
------------------------------------------------------------------------------------------------
| ID | Operation | Name | Cost (%CPU) | Cards | Elaps. Time |
------------------------------------------------------------------------------------------------
| 1 | ORDER BY (SORT) | | 18 (0)| 157 |00:00:00.0000 |
| 2 | INDEX JOIN (LEFT OUTER) | | 18 (0)| 157 |00:00:00.0000 |
| 3 | HASH JOIN | | 15 (0)| 157 |00:00:00.0000 |
| 4 | TABLE ACCESS (ROWID) | DEPARTMENTS | 3 (0)| 5 |00:00:00.0000 |
| 5 | INDEX (FULL) | _TEST_CON54700033 | 2 (0)| 5 |00:00:00.0000 |
| 6 | HASH JOIN | | 12 (0)| 157 |00:00:00.0000 |
| 7 | FILTER | | 4 (0)| 1 |00:00:00.0000 |
| 8 | SORT AGGR | | 4 (0)| 5 |00:00:00.0000 |
| 9 | INDEX (FULL) | IDX_EMP_DEPT_SAL | 4 (0)| 1000 |00:00:00.0000 |
| 10 | TABLE ACCESS (ROWID) | EMPLOYEES | 8 (0)| 786 |00:00:00.0000 |
| 11 | INDEX (RANGE SCAN) | IDX_EMP_SALARY | 2 (0)| 786 |00:00:00.0000 |
| 12 | TABLE ACCESS (ROWID) | SALARY_HISTORY | 3 (0)| 1 |00:00:00.0000 |
| 13 | INDEX (RANGE SCAN) | IDX_SAL_HIST_EMP | 2 (0)| 1 |00:00:00.0000 |
------------------------------------------------------------------------------------------------
--FULL 힌트 적용 쿼리
SELECT /*+ FULL(e) FULL(d) */
e.emp_id,
e.emp_name,
d.dept_name,
e.salary,
sh.new_salary,
sh.change_date
FROM employees e
JOIN departments d ON e.dept_id = d.dept_id
LEFT JOIN salary_history sh ON e.emp_id = sh.emp_id
WHERE e.dept_id IN (
SELECT dept_id
FROM employees
GROUP BY dept_id
HAVING AVG(salary) >= 5000
)
AND e.salary > 4000
ORDER BY e.salary DESC;
--FULL 힌트 적용 쿼리 플랜
-----------------------------------------------------------------------------------------------
| ID | Operation | Name | Cost (%CPU) | Cards | Elaps. Time |
-----------------------------------------------------------------------------------------------
| 1 | ORDER BY (SORT) | | 32 (3.13)| 157 |00:00:00.0000 |
| 2 | INDEX JOIN (LEFT OUTER) | | 32 (3.13)| 157 |00:00:00.0000 |
| 3 | HASH JOIN | | 29 (3.45)| 157 |00:00:00.0000 |
| 4 | TABLE ACCESS (FULL) | DEPARTMENTS | 12 (0)| 5 |00:00:00.0000 |
| 5 | HASH JOIN | | 16 (0)| 157 |00:00:00.0000 |
| 6 | FILTER | | 4 (0)| 1 |00:00:00.0000 |
| 7 | SORT AGGR | | 4 (0)| 5 |00:00:00.0000 |
| 8 | INDEX (FULL) | IDX_EMP_DEPT_SAL | 4 (0)| 1000 |00:00:00.0000 |
| 9 | TABLE ACCESS (FULL) | EMPLOYEES | 12 (0)| 786 |00:00:00.0000 |
| 10 | TABLE ACCESS (ROWID) | SALARY_HISTORY | 3 (0)| 1 |00:00:00.0000 |
| 11 | INDEX (RANGE SCAN) | IDX_SAL_HIST_EMP | 2 (0)| 1 |00:00:00.0000 |
-----------------------------------------------------------------------------------------------