Home » RDBMS Server » Performance Tuning » Distinct query taking long time to run (Oracle 11.2,Linux 6)
Distinct query taking long time to run [message #669760] Thu, 10 May 2018 14:07 Go to next message
senmng
Messages: 22
Registered: April 2018
Junior Member
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;


[EDITED by LF: removed superfluous info from Product, version, OS]

[Updated on: Fri, 11 May 2018 02:11] by Moderator

Report message to a moderator

Re: Distinct query taking long time to run [message #669761 is a reply to message #669760] Thu, 10 May 2018 14:16 Go to previous messageGo to next message
John Watson
Messages: 8023
Registered: January 2010
Location: Global Village
Senior Member
You'll need to show the execution plan to determine if the DISTINCT could be the problem.
Re: Distinct query taking long time to run [message #669762 is a reply to message #669760] Thu, 10 May 2018 15:39 Go to previous messageGo to next message
joy_division
Messages: 4947
Registered: February 2005
Location: East Coast USA
Senior Member
Besides what the execution plan tells you, joining tables across databases, escecially if they are on differet servers on a network can be a cause of major slowdown regardless of distinct.
Re: Distinct query taking long time to run [message #669763 is a reply to message #669761] Thu, 10 May 2018 15:50 Go to previous messageGo to next message
senmng
Messages: 22
Registered: April 2018
Junior Member
Hi John - When i tried to restrict the rows to 1000 by using (where ronwnum<=1000),am able to retrieve the results but not able to retrieve only i don't use any condition.Also attached the explain plan report.
  • Attachment: EP1.jpg
    (Size: 127.19KB, Downloaded 816 times)
Re: Distinct query taking long time to run [message #669768 is a reply to message #669763] Fri, 11 May 2018 00:19 Go to previous messageGo to next message
John Watson
Messages: 8023
Registered: January 2010
Location: Global Village
Senior Member
THat execution plan is useless. You need to generate one the correct way.

Use EXPLAIN PLAN, and then format the results with DBMS_XPLAN.DISPLAY.

After that, set STATISTICS_LEVEL=ALL, run the statement, and get the plan with execution statistics with DBMS_XPLAN.DISPLAY_CURSOR(FORMAT=>'ALLSTATS LAST').
Re: Distinct query taking long time to run [message #669788 is a reply to message #669768] Fri, 11 May 2018 09:36 Go to previous messageGo to next message
msol25
Messages: 396
Registered: June 2011
Senior Member
Hi,

The DISTINCT operator causes Oracle to fetch all rows satisfying the table join and then sort and filter out duplicate values.I hope you can use to resolve this using
  
   SELECT <column_list>
   FROM
     (    SELECT  <column_list>
                 ,ROW_NUMBER() OVER (PARTITION BY <column_list> ORDER BY <column_list>) As RNO          
          FROM   <Table_Name>
    )
WHERE RNO = 1

[Updated on: Fri, 11 May 2018 09:37]

Report message to a moderator

Re: Distinct query taking long time to run [message #669789 is a reply to message #669788] Fri, 11 May 2018 14:52 Go to previous messageGo to next message
Alien
Messages: 278
Registered: June 1999
Senior Member
Hi,

Your analytic query would have to do the same amount of work. The only way to create the partitions is to get the whole resultset, sort it, assign a row_number and discard the rest.

The only difference is that the row_number() will likely use a 'window sort pushed rank' operation. While the distinct is likely to use a 'hash unique'. Depending on the data and available memory there might be a marginal difference between the 2. In my experience the 'window sort pushed rank' is more likely to spill to disk and start swapping, therefore substantially incrementing the runtime.

However, the amount of work being done is the same. The only difference is the algorithm used to perform the sort.

Regards,

Arian
Re: Distinct query taking long time to run [message #669887 is a reply to message #669789] Tue, 22 May 2018 14:17 Go to previous message
senmng
Messages: 22
Registered: April 2018
Junior Member
Hi - i have created a view in the remote database to get the query results of join and distinct and refer the view name in the local database. It reduced the considerable amount of time in inserting the record into the target table.



Thank you for all your suggestions.
Previous Topic: ORA-07445: exception encountered: core dump [audplsfailure()+54]
Next Topic: Wrong plan chosen by optimizer
Goto Forum:
  


Current Time: Sat Oct 19 00:00:20 CDT 2019