Tag Archives: hints

Join Method in explain plan of SQL Query

Join Method in explain plan of SQL Query

Optimizer is using the wrong join type, need to change the join method for increase the speed of the SQL Queries.
You can override the join type by placing the appropriate hint in the query. You can test the SQL Query performance by changing its Join Method like Nested loop to hash join etc.

Three Join Methods:
NESTED LOOPS
HASH
SORT MERGE

NESTED LOOPS Join
Use the USE_NL hint for using in SQL Query.
The nested loops join is consider best for joining small tables.
In a nested loops join, one table is known as driving table(outer table). For each row of the outer, each row in the inner table is searched for matching rows.

In the Example: Employee table is outer table and Department table is inner table:

SELECT /*+ use_nl(emp, dept) */ FIRST_NAME, DEPARTMENT_NAME
FROM EMPLOYEES emp JOIN DEPARTMENTS dept USING (deptno);

------------------------------------------------------
| Id | Operation                       | Name        |
------------------------------------------------------
| 0 | SELECT STATEMENT                 |             |
| 1 | NESTED LOOPS                     |             |
| 2 | NESTED LOOPS                     |             | 
| 3 | TABLE ACCESS FULL                | DEPARTMENTS |
| 4 | INDEX RANGE SCAN                 | EMP_DEP_IDX |
| 5 | TABLE ACCESS BY INDEX ROWID      | EMPLOYEES   |
------------------------------------------------------

HASH Join
Hash joins are used for joining large amounts of data or larger table. The smaller of the two tables is used by the optimizer to build a hash table on the join key between the two tables.
Use the USE_HASH hint for using in SQL Query.
In the example, the DEPT table is the smaller tablev and used to build the hash table:

SELECT /*+ use_hash(emp , dept) */ first_name, department_name
FROM employees emp JOIN departments dept USING (deptno);

-------------------------------------
| Id | Operation        | Name      |
-------------------------------------
| 0 | SELECT STATEMENT  |           |
| 1 | HASH JOIN         |           |
| 2 | TABLE ACCESS FULL | DEPT      |
| 3 | TABLE ACCESS FULL | EMPLOYEES |
-------------------------------------

SORT MERGE Join
Sort merge joins are also used to join a large data. Sort merge join is used when the join condition between the tables is not an equijoin.
Use the USE_MERGE hint for using in SQL Query.
In the following example, the input data from both tables is sorted on the join key, and then merged together.


select /*+ use_merge(emp, dept) */ first_name , emp.department_id
from hr.employees emp , hr.departments dept
where emp.department_id = dept.department_id and dept. department_id 20;

----------------------------------------------------
| Id | Operation                  | Name           |
----------------------------------------------------
| 0 | SELECT STATEMENT            |                |
| 1 | MERGE JOIN                  |                |
| 2 | TABLE ACCESS BY INDEX ROWID | DEPARTMENTS    |
| 3 | INDEX FULL SCAN             | DEPARTMENTS_PK |
| 4 | SORT JOIN                   |                |
| 5 | TABLE ACCESS FULL           | EMPLOYEES      |
----------------------------------------------------

Advertisements

Enabling Parallelism for a SQL Query

Enabling Parallelism for a SQL Query

You found a query running on large table, you want to enable parallelism to check its speed after normal execution.

For using the parallelism on SQL Query, you need to explicitly defines hints on the SQL Query for forcefully used the parallel operations.

Two way to be used parallel operation on SQL Query as
1. Parallel Hint
2. Parallel_index Hint

Parallel hint works on tables as follows:
In following queries, you are specify the parallel 4 means 4 process is used to fetch data from the employees table.

SELECT /*+ parallel(employees,4) */ emp_id, name FROM hr.employees;

In following queries, you specify nothing, Oracle detect the parallel operation based on database initialization parameters:

SELECT/*+ parallel(emp) */ emp_id, name FROM hr.employees emp;

Parallel_index hints
Parallel_index hint used for parallel access to indexes.

SELECT /*+ parallel_index(emp, emp_i4 ,4) */ emp_id, name
FROM employees WHERE deptno = 10;

Two arguments used in parallel_index hint: Table name and Index name. If you do not specify parallel number then oracle choose automatic on db settings.

NO_PARALLEL & NO_PARALLEL_INDEX Hints
Both used if you want to test the query without using parallel execution. Sometime Query is executed better in case of non parallel then parallel and your parallel level is set on table degree or instance level (PARALLEL_DEGREE_POLICY=AUTO) and you want to over come this situation for specific query. Then use No_PARALLEL and NO_PARALLEL_INDEX.

Note: As of Oracle 11g Release 2, the NOPARALLEL and NOPARALLEL_INDEX hints have been deprecated. Instead, use NO_PARALLEL and NO_PARALLEL_INDEX.

Check the parallelism of object or index with following query:

select degree from dba_tables where table_name = 'EMPLOYEES';

select index_name,degree from dba_indexes where table_name = 'EMPLOYEES';

Note: If automatic DOP is enabled (PARALLEL_DEGREE_POLICY=AUTO), then the parallelism that you set on objects is ignored.

Change the degree at object level ( table or index):

ALTER TABLE EMPLOYEES PARALLEL(DEGREE 4);

ALTER INDEX EMP_IDX PARALLEL(DEGREE 4);

ALTER TABLE EMPLOYEES PARALLEL(DEGREE 1);

ALTER TABLE EMPLOYEES NOPARALLEL;

Script for change parallel degree for all objects in schema

-- For indexes
select 'alter index '||owner||'."'||index_name||'" parallel (degree 4);' from dba_indexes where owner='IC';

-- For Tables
select 'alter table '||owner||'."'||table_name||'" parallel (degree 4);' from dba_tables where owner='IC';

Enable parallel at session level forcefully:


alter session force parallel query parallel N;
--example: Run SQL Query with parallel with 4 process
alter session force parallel query parallel 4;