Home » RDBMS Server » Performance Tuning » How To Eliminate Sort Unique (Oracle 10.1.0.2.0 HP-UX)
How To Eliminate Sort Unique [message #416194] Fri, 31 July 2009 02:13 Go to next message
vinniora
Messages: 56
Registered: October 2008
Location: Mumbai
Member
Hi all, I Have fired a query
SELECT DISTINCT B.BDECOILID cc FROM COILS B, DEFECTS A WHERE B.COILID=A.COILID
it's explain plan is showing sort unique and it's cost 845k

| Id |Operation | Name| Rows | Bytes |TempSpc| Cost (%CPU)|
| 0 | SELECT STATEMENT | | 10142 | 217K||845K (54)|
| 1 | SORT UNIQUE | | 10142 | 217K| 185M|845K (54)
| 2 |NESTED LOOPS | | 6882K| 144M|| 45348 (29)|
| 3 |TABLE ACCESS FULL| COILS | 10711 | 177K| 217 (50)|
|*4 |INDEX RANGE SCAN | COIL_ID_DEFECTS | 643 | 3215 |4 (25)|

It is too much time because of distinct keyword is their any way to reduce the cost.
Re: How To Eliminate Sort Unique [message #416195 is a reply to message #416194] Fri, 31 July 2009 02:24 Go to previous messageGo to next message
Michel Cadot
Messages: 64120
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
No.

Regards
Michel
Re: How To Eliminate Sort Unique [message #416403 is a reply to message #416194] Sat, 01 August 2009 10:37 Go to previous messageGo to next message
amardeep.sidhu
Messages: 7
Registered: October 2007
Junior Member
As you are asking it to return DISTINCT values so it goes for sort unique. If you are sure that there would be no duplicates then you can get rid of DISTINCT and consequently SORT UNIQUE.

Regards,
Amardeep Sidhu
Re: How To Eliminate Sort Unique [message #416424 is a reply to message #416194] Sun, 02 August 2009 04:43 Go to previous message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
If BDECOILID is unique in COILS, then
SELECT B.BDECOILID cc 
FROM COILS B
WHERE coilid IN (
  SELECT coilid
  FROM   DEFECTS A)


Ross Leishman
Previous Topic: How to optimize this plan (merged)
Next Topic: Oracle Performance Issue with dbms_stats.gather_table_stats
Goto Forum:
  


Current Time: Tue Dec 06 10:33:04 CST 2016

Total time taken to generate the page: 0.04968 seconds