Million Records tables join

[ckwan]

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.

[chrisrlong]

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

[kgb]

Use SQL Plus 'COPY' command.
OR
Create TEXT file of that sql query using 'pool' command and load it to other database.

[denevge]

see to it you use a hash join instead of merge/sort join when joining large quantities of records

Gert

[jmodic]

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