I have this insert query in one of my proc to load the data into a target table where the distinct clause is making huge impact and taking too much time(since each table is having records more than 2 and 3 million records). Is their any way to optimize this select query with distinct?
Insert into target table
SELECT DISTINCT
tab1.col1 col1,
tab1.col2 col2,
tab2.col3 col3,
tab2.col4 col4,
tab3.col5 col5,
tab3.col6 col6,
tab3.col7 col7,
tab3.col8 col8,
tab3.col9 col9,
to_char(tab3.col7, 'YYYY-MM') col10,
to_char(tab3.col7, 'YYYY') col11,
to_char(tab3.col7, 'MM') col12,
CURRENT_DATE col13
FROM tab3@dblink t2
JOIN tab4@dblink ON tab3.col5 = tab4.col13
JOIN tab5@dblink ON tab4.col14 = tab5.col15
JOIN tab2@dblink ON tab5.col16 = tab2.col17
JOIN tab1 ON tab1.col18 = tab2.col3;
Insert into target table
SELECT DISTINCT
tab1.col1 col1,
tab1.col2 col2,
tab2.col3 col3,
tab2.col4 col4,
tab3.col5 col5,
tab3.col6 col6,
tab3.col7 col7,
tab3.col8 col8,
tab3.col9 col9,
to_char(tab3.col7, 'YYYY-MM') col10,
to_char(tab3.col7, 'YYYY') col11,
to_char(tab3.col7, 'MM') col12,
CURRENT_DATE col13
FROM tab3@dblink t2
JOIN tab4@dblink ON tab3.col5 = tab4.col13
JOIN tab5@dblink ON tab4.col14 = tab5.col15
JOIN tab2@dblink ON tab5.col16 = tab2.col17
JOIN tab1 ON tab1.col18 = tab2.col3;