Million Records tables join
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 5 of 5

Thread: Million Records tables join

  1. #1
    Join Date
    Oct 2000
    Posts
    250
    Hello all,
    I am seek for help from anyone who experience in create a table base on few joins tables which contains million records and has where conditions.

    What are the suggestion on this to fasten the table creation process ?

    Here is my current situation :
    CREATE TABLE EMP
    PARALLEL 4
    AS SELECT /*+ CHOOSE */ DEPT.a, STORE.b
    FROM dept@DBLINK, store@dblink
    WHERE a.DEPTID = b.DEPTID;

    Note: dept and store tables consists of million records.

    Thanks You.

  2. #2
    Join Date
    Nov 2000
    Location
    Baltimore, MD USA
    Posts
    1,339
    Since you are joining two tables from a dblink, you may want to try the DRIVING_SITE hint.

    Also, try it with and without the parallel hint. And if you are going to use the parallel hint, try a larger number.

    Finally, the NOLOGGING hint would probably help for the INSERT part of the process.

    - Chris
    Christopher R. Long
    ChrisRLong@HotMail.Com
    But that's just my opinion. I could be wrong

  3. #3
    Join Date
    Mar 2001
    Location
    Ireland/Dublin
    Posts
    684
    Use SQL Plus 'COPY' command.
    OR
    Create TEXT file of that sql query using 'pool' command and load it to other database.
    Best wishes!
    Dmitri

  4. #4
    Join Date
    Aug 2000
    Location
    Belgium
    Posts
    342
    see to it you use a hash join instead of merge/sort join when joining large quantities of records

    Gert

  5. #5
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    As allready suggested in http://www.dbasupport.com/forums/sho...threadid=30180 try forcing the join to be performed at remote site instaed of pulling both joined tables to your instance to be joined localy.

    BTW, any particular reason why you opened a new thread for exactly the same problem you've posted couple of days ago (and which you didn't even bothered to comment after receiving couple of suggestions)?
    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

Posting Permissions

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



Click Here to Expand Forum to Full Width