Toad World® Forums

How the 'use_merge' hint works


#1

Hi Experts,

We have seen a view in our account which is as below (Created by other resource long back)

select /*+ use_merge(sub,seg1) PARALLEL(sub) */
sub.“CATEGORY”,sub.“JE_BATCH_NAME”,sub.“JE_HEADER_DESCRIPTION”,sub.“JE_HEADER_NAME”,sub.“JE_LINE_DESCRIPTION”,sub.“DESCRIPTION”,sub.“NAME”,sub.“LOCATION”,sub.“TRANSACTION_NUMBER”,sub.“TRANSACTION_DATE”,sub.“DIST_LINE_NUMBER”,sub.“PO_NUMBER”,sub.“PAYMENT_NUMBER”,sub.“SALES_ORDER”,sub.“INVOICE_PAYMENT_STATUS”,sub.“CURRENCY_CODE”,sub.“EXCHANGE_RATE”,sub.“JE_ACCOUNTED_DR”,sub.“JE_ACCOUNTED_CR”,sub.“AMOUNT”,sub.“STATUS”,sub.“FISCAL_PERIOD”,sub.“FISCAL_YEAR”,sub.“PERIOD_NAME”,sub.“EFFECTIVE_DATE”,sub.“JE_SOURCE”,sub.“JE_CATEGORY”,sub.“TRANSACTION_TYPE”,sub.“DOCUMENT_NAME”,sub.“GL_ACCOUNT”,sub.“COMPANY”,sub.“DEPARTMENT”,sub.“ACCOUNT”,sub.“PROJECT”,sub.“PRODUCT”,sub.“CUSTOMER”,sub.“GL_DISTRIBUTION_DATE”,sub.“POSTED_DATE”,sub.“LAST_BUILD_DATE”,sub.“JE_BATCH_ID”,sub.“JE_HEADER_ID”,sub.“JE_LINE_NUM”,sub.“CODE_COMBINATION_ID”,sub.“AP_INVOICE_ID”,sub.“INVOICE_DISTRIBUTION_ID”,sub.“CUSTOMER_TRX_ID”,sub.“CUSTOMER_TRX_LINE_ID”,sub.“PO_HEADER_ID”,sub.“PO_LINE_ID”,sub.“ROW_ID”,
seg1.description Company_Desc,
seg2.description Department_Desc,
seg3.description Account_Desc,
seg4.description Project_Desc,
seg5.description Product_Desc
from
apps.xx_fnd_flex_desc_v seg1,
apps.xx_fnd_flex_desc_v seg2,
apps.xx_fnd_flex_desc_v seg3,
apps.xx_fnd_flex_desc_v seg4,
apps.xx_fnd_flex_desc_v seg5,
apps.xx_gl_subledger_mv sub
where
sub.company = seg1.flex_value
and seg1.flex_value_set_id = 1003775
and sub.department = seg2.flex_value
and seg2.flex_value_set_id = 1003774
and sub.account = seg3.flex_value
and seg3.flex_value_set_id = 1002347
and sub.project = seg4.flex_value
and seg4.flex_value_set_id = 1002348
AND SUB.PRODUCT = SEG5.FLEX_VALUE
and seg5.flex_value_set_id = 1002349

But, I am not sure how this hint works. Can anyone please help me on how this hint works. I googled, but I didn’t understood :frowning:

Thanks in Advance


#2

The “use_merge” is one of Oracle hints for join operation. It advises the Oracle optimizer to join tables with “SORT” and “MERGE” operation.

For a simple example:

select /*+ use_merge(department, employee) */ * from department, employee

where emp_id = dpt_manager

Plan

SELECT STATEMENT ALL_ROWS Cost: 10 Bytes: 198 Cardinality: 1

5 MERGE JOIN Cost: 10 Bytes: 198 Cardinality: 1

2 SORT JOIN Cost: 5 Bytes: 37,465 Cardinality: 295

1 TABLE ACCESS FULL TABLE SQLEXP.EMPLOYEE Cost: 4 Bytes: 37,465 Cardinality: 295

4 SORT JOIN Cost: 5 Bytes: 21,726 Cardinality: 306

3 TABLE ACCESS FULL TABLE SQLEXP.DEPARTMENT Cost: 4 Bytes: 21,726 Cardinality: 306

The Oracle Optimizer may go for follow steps:

  1. Sort EMPLOYEE

  2. Sort DEPARTMENT

  3. Merge two tables

As the tables had already sort in order, the search and matching records which is similar to sequential search.

Because the sorting must done before any record match start, it may delay the fast responsed time, however, the performance still depending on the database structure or data distribution.


#3

keep in mind that Oracle will NOT ALWAYS use ANY hint.
Why?
www.oracle.com

:slight_smile: