Insert query using distributed join to speed up the performance

GJSK

Member
Hi - I have this stored procedure which queries the remote DB tables to insert into local DB. Since it's taking hours together to run ,i decided to go with 'driving site' hint to speed up the performance.My question is how come we refer more than remote table name in the driving site hint?
BEGIN

INSERT /*+ append */ INTO table1
SELECT DISTINCT
table5.SECT,
table5.REG,
table4.PROJ,
table4.SOL,
table6.ID,
table6.GUID,
table6.TIME,
table6.ISSUE_ID,
table6.RULE_ID,
to_char(table6.TIME, 'YYYY-MM'),
to_char(table6.TIME, 'YYYY'),
to_char(table6.TIME, 'MM'),
CURRENT_DATE
FROM table6@DATABASE_LINK1
JOIN table2@DATABASE_LINK1 ON ID = table3.ID
JOIN table3@DATABASE_LINK1 ON table3.FILE_ID = table4.ID
JOIN table4@DATABASE_LINK1 ON table4.INFO_ID = table3.ID
JOIN table5 ON table5.PROJ = table4.PROJ
END;
Here table 1 and 5 are residing in local db ,all other are in remote db.The size of each table is more than 2 million rows.
 
Back
Top