Solved

Tuning Question

Posted on 2006-11-14
1
688 Views
Last Modified: 2008-02-01
select /*+ USE_MERGE(B,A) */ a.prod_id,
       a.prod_number,
       a.prod_name,
       a.update_date,
       b.term,
       b.term_type
  from merch a,
       term_details b
 where a.prod_id = b.prod_id

Experts,
  I just used TOAD to tune the following query and received this result as the best option. Before I use the code I wanted to understand what the solution was. I was hoping someone could explain the /* + USE_MERGE(B,A) */ comment? I don't want to use code and not fully understand what it's doing. Thanks!
0
Comment
Question by:ACSDBA
1 Comment
 
LVL 47

Accepted Solution

by:
schwertner earned 500 total points
ID: 17938715
Causes Oracle to join each specified table with another row source with a sort-merge join.

http://www.psoug.org/reference/hints.html

Merge join
Also called sort merge join. A merge join basically sorts all relevant rows in the first table by the join key , and also sorts the relevant rows in the second table by the join key, and then merges these sorted rows. Take an example! At a garage sale you can buy 400 books. The deal is to take all or none. You take all. Now, you have to find the books that you already have at home. How would you go about it? Probably, you'd do a merge join: first, you sort your books by the primary key (author, title), then you sort the 400 books by their primary key (auther, title). Now, you start at the top of both piles. If the value of the left piles primary key is higher, then you take a book from the right pile and vice versa. When both values are equal, then you have found a dublicate. To demonstrate a MERGE JOIN, two tables need to be created: The behaviour of merge joins is influenced by the initialization parameters sort_area_size and db_file_mutliblock_read_count.

http://www.adp-gmbh.ch/ora/sql/join/join_methods.html
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Configuring and using Oracle Database Gateway for ODBC Introduction First, a brief summary of what a Database Gateway is.  A Gateway is a set of driver agents and configurations that allow an Oracle database to communicate with other platforms…
Checking the Alert Log in AWS RDS Oracle can be a pain through their user interface.  I made a script to download the Alert Log, look for errors, and email me the trace files.  In this article I'll describe what I did and share my script.
This video shows how to Export data from an Oracle database using the Original Export Utility.  The corresponding Import utility, which works the same way is referenced, but not demonstrated.
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.

910 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now