4 processors means the query runs in parallel.

Select /*+ parallel(4) */ T1.A 

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.

  • 6
    It's not your job to "create" an execution plan. Just run the statement and Oracle will do that. To see the execution plan use explain plan. Oh, and stop using that old outdated and error prone implicit join syntax in the where clause. Use a proper JOIN condition instead. – a_horse_with_no_name Apr 26 '14 at 14:44

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).

  • 1
    as a_horse_with_no_name stated in a comment, it does not make sense to create an execution plan – miracle173 Dec 7 '14 at 18:59
  • 1
    @miracle173: Execution plan in any case is not created by user. Hints may influence how it's created, and in some cases hints are the only way to improve performance. I can't tell for sure whether hints give any benefits in this particular case; I just gave an example of how to make Oracle join 2 tables using sort merge join. – a1ex07 Dec 8 '14 at 14:04

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.