Results 1 to 3 of 3
  1. #1
    Join Date
    Jan 2004

    Unanswered: Merge Join over Hash Join

    Hi ALL,

    I've two tables , in one table there are 100 million of data.And in the other table there are 80 million of data , approximately.I've to join both the tables , and I've only 2 options.
    first one : USE_HASH(A,B) with PARALLEL(A,16) parallel(B,16)
    second one : USE_MERGE(A,B) with PARALLEL(A,16) parallel(B,16)
    But for Hash join , the driving table should be small enough.But in case of mine , the driving table is too big(80 millions of dat).In case of merge join , we can't get the facility of PARALLEL .
    Should I split the driving table into 4 tables and then use HASH join?
    Can anyone plz help me out? What is the best way to get the high performance?

    Thanks ........

  2. #2
    Join Date
    Apr 2003
    Minneapolis, MN
    Merge Join is Usefull Only underr the following Condition:

    (1) There is no indexes on both the tables.
    (2) when Your query retrieves majority of data from both the tables.

    If this is the case then you should go for Merge Join.

    Hash Join::

    If you are Using Oracle9i, then you have set PGA_AGGREGATE_TARGET about 8GB, then You can get almost full benifit Hash join.

    Else, Oracle will automatically partition Hash table and store it on the TEMP tablespace. You do not have to Manually parition it. In this case, performance will be slow compared to Oracle9i-pga_aggregate_target.
    But certainly more than merge Join because Merge Join requires more temp storage than Hash Table require it.


    MS Computer Science
    OCP DBA 9i/8i

  3. #3
    Join Date
    Jan 2004
    Thanks Bhabin,

    I need the cartesian product .
    In my case , both the tables have 100 millions of data and have the index on Primary key.The output of the join results 90% valid data .
    So what do u suggest for better performance?Should I go for Hash Join or Merge Join?
    Or should I split one table into 4 small tables and then go for Hash Join?
    I'm using Oracle 9i , perhaps we can't chnge the PGA_AGGREGATE_TARGET to 8GB.
    In case of joining 3 or more tables , for 100 millions of data,which would be the faster method ?

    Last edited by himridul; 01-20-04 at 02:18.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts