Home » SQL & PL/SQL » SQL & PL/SQL » Distinct is killing the performance...
Distinct is killing the performance... [message #208695] Mon, 11 December 2006 22:45 Go to next message
sundarsoft
Messages: 15
Registered: October 2006
Junior Member
Hi,

Can anybody help me on this urgently...

In my Big query i called a function named XYZ 20 times with diff parameters in the select clause.I used distinct in the same clause.The result of the query contains 1000 records,it takes nearly 10 mins to execute it.

But to my surprise when i remove the distinct in the select clause ,the query executes in 3 seconds...
Why Oracle treats like this...is there any specific reason..

The function XYZ returns VARCAHR2(500) as comma separated string...

Thanks in Advance
Sundar.
Re: Distinct is killing the performance... [message #208720 is a reply to message #208695] Mon, 11 December 2006 23:59 Go to previous messageGo to next message
Littlefoot
Messages: 20888
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
See if you can modify the WHERE clause in order to get rid of duplicate values (so that you wouldn't need to use DISTINCT).
Re: Distinct is killing the performance... [message #208728 is a reply to message #208720] Tue, 12 December 2006 00:23 Go to previous messageGo to next message
sundarsoft
Messages: 15
Registered: October 2006
Junior Member
Hi ,
I tried to modify the query ,but it generates dulipates..

But i want to know why oracle kills performance in case of both usage of distinct and duplicates...
can u please explain the reason...

Sundar
Re: Distinct is killing the performance... [message #208734 is a reply to message #208695] Tue, 12 December 2006 00:32 Go to previous messageGo to next message
michael_bialik
Messages: 611
Registered: July 2006
Senior Member
Post your query and both EXPLAINs (with and without DISTINCT).
Re: Distinct is killing the performance... [message #208762 is a reply to message #208734] Tue, 12 December 2006 02:56 Go to previous messageGo to next message
tahpush
Messages: 961
Registered: August 2006
Location: Stockholm/Sweden
Senior Member

"But i want to know why oracle kills performance in case of both usage of distinct and duplicates..."
Hard to say, as michael suggest post your sql and explain plan

But if possible.....

Try to remove your distinct (if you have a join) since for example

SELECT DISTINCT dept_no, dept_name
FROM dept D,
emp E
WHERE D.dept_no = E.dept_no ;

Will fetch all rows that match the table join, then sort and filter out the duplicates


If you
SELECT dept_no, dept_name
FROM dept D
WHERE EXISTS ( SELECT 'X'
FROM emp E
WHERE E.dept_no = D.dept_no );

optimizer realizes that when the subquery has been satisfied once, there is no need to proceed any further, and the next driving row can be fetched.

Re: Distinct is killing the performance... [message #208786 is a reply to message #208762] Tue, 12 December 2006 04:43 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Did it take 3 seconds to retrieve and display all 1000+ records???
Or did it take 3 seconds to display the first n records in TOAD?
Re: Distinct is killing the performance... [message #208916 is a reply to message #208695] Tue, 12 December 2006 09:58 Go to previous messageGo to next message
k7nixen
Messages: 7
Registered: December 2006
Junior Member
First of all you should anwser Frank's question. Did you receive all data (1000 + duplicates) in 3 seconds or just first rows ?

When you use distinct, oracle performs the query for all data to filter duplicate rows, this is why it takes much more time.

How many rows does your table include ?
Are you making joins or simply getting data from 1 table ?
What about execution plan ?

It is impossible to answer your question without enough information.
Re: Distinct is killing the performance... [message #208919 is a reply to message #208695] Tue, 12 December 2006 10:04 Go to previous messageGo to next message
tahpush
Messages: 961
Registered: August 2006
Location: Stockholm/Sweden
Senior Member

i see an echo "When you use distinct, oracle performs the query for all data to filter duplicate rows, this is why it takes much more time. ";)
Re: Distinct is killing the performance... [message #208920 is a reply to message #208695] Tue, 12 December 2006 10:09 Go to previous message
Cthulhu
Messages: 381
Registered: September 2006
Location: UK
Senior Member
Quote:
But i want to know why oracle kills performance in case of both usage of distinct and duplicates...
can u please explain the reason...


Let's take an example. Suppose I have a query:

select e.ename
from emp e, dept d

Let's suppose there are 5000 records in EMP and 500 records in DEPT. I run the query and, because there is no join, I get 500 * 5000 = 2,500,000 records. I then see that there are duplicates returned by the query, so I decide to get round this problem by doing:

select distinct e.ename
from emp e, dept d

This reduces the query to what I want, but it means that Oracle has to sort through 2,500,000 records to find the distinct set of enames. Naturally, this takes somewhat longer than just going through the 5000 records that would be returned if I had joined EMP and DEPT correctly.

Previous Topic: Variable passing please explain difference between T-SQL and PL/SQL
Next Topic: Returning the weeks involved by going back from sydate
Goto Forum:
  


Current Time: Sat Dec 03 09:56:19 CST 2016

Total time taken to generate the page: 0.04354 seconds