How to prepare executon plan for given sql query on a server with 4 processors (Oracle database)

optimizationoracleperformancequery-performance

4 processors means the query runs in parallel.

Select /*+ parallel(4) */ T1.A 
FROM T1,T2,T3
WHERE T1.A = T2.A
AND T1.B=T3.B
AND T1.C=T3.C

Tables T1,T2 and T3 have 4,8, and 16 partitions respectively.

Using nested loops,sort merge and hash join as available methods of join. How to create execution plan for above query? I am not able to understand when to apply sort merge in execution plan.

Best Answer

Hints for join operations are used the same way as other hints, for instance

SELECT /*+ parallel(4) USE_MERGE(T1 T2)*/ T1.A
FROM ....

Optimizer chooses join algorithm based on different criteria. There are many things to be considered, but in general merge join is used when optimizer mode is ALL_ROWS (it prefers nested loops for FIRST_ROWS) , both tables involved in the operation have index on join column[s] ( it may still use merge join even if you don't have indexes, but hash_join_enabled set to false or using hash join estimated as more expensive).

The query with hint parallel(4) may potentially allocate 8 parallel servers (4 consumers, 4 producers).

Related Question