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: 65153
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: 3727
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 Aug 22 17:50:31 CDT 2017

Total time taken to generate the page: 0.05605 seconds