Document Type | Technical Information
Category | Tuning
Applicable Product Version | Tibero 7.2.3
Document Number | TTUTI032
Overview
This document describes the types of hints provided by Tibero, how to apply hints, and the changes in execution plans when each hint is applied.
Types of Hints
For more detailed information, refer to section 2.8 of the manual 'Tibero SQL Reference Guide'.
Method
How to Apply Hints
-- 1. SELECT SELECT /*+ hint */ columns FROM table; -- 2. INSERT INSERT /*+ hint */ INTO table VALUES (...); -- 3. UPDATE UPDATE /*+ hint */ table SET column = value; -- 4. DELETE DELETE /*+ hint */ FROM table WHERE condition; -- 5. MERGE MERGE /*+ hint */ INTO table USING... *If an alias is specified for a table, the alias must be used instead of the table name
Examples of Hint Application
1. SELECT hint location
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 hint location
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 statement hint location
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 statement hint location
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 statement hint location
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);
Checking Plan Changes According to Hint Application
Create Test User
[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.
Create Test Tables and Load Data
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, -- Department ID: 10, 20, 30, 40, 50
3000 + MOD(i, 50) * 100, -- Salary: 3000 ~ 7900
ADD_MONTHS(DATE '2020-01-01', MOD(i, 48)), -- Hire Date
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.
Create Indexes for Test Tables
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.
Collect Statistics and Check Data for Test Tables
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.
Apply Hints and Check Plan Changes
--Basic query
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;
--Basic query plan
------------------------------------------------------------------------------------------------
| 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 |
------------------------------------------------------------------------------------------------
--Query applying LEADING hint
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;
--Query plan with LEADING hint applied
------------------------------------------------------------------------------------------------
| 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 |
------------------------------------------------------------------------------------------------
--Query applying USE_NL hint
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;
--Query plan with USE_NL hint applied
------------------------------------------------------------------------------------------------
| 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 |
------------------------------------------------------------------------------------------------
--Query applying USE_HASH hint
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;
--Query plan with USE_HASH hint applied
------------------------------------------------------------------------------------------------
| 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 |
------------------------------------------------------------------------------------------------
--Query applying INDEX hint
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;
--Query plan with INDEX hint applied
------------------------------------------------------------------------------------------------
| 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 |
------------------------------------------------------------------------------------------------
--Query applying FULL hint
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;
--Query plan with FULL hint applied
-----------------------------------------------------------------------------------------------
| 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 |
-----------------------------------------------------------------------------------------------