Home » RDBMS Server » Performance Tuning » Distinct versus group by
Distinct versus group by [message #347377] Thu, 11 September 2008 07:36 Go to next message
trupti111
Messages: 29
Registered: August 2008
Location: navi mumbai
Junior Member
SELECT sr_no, agent_code, agent_name, report_to      
      FROM ams_lts_lead_summary
          WHERE agent_code IN (SELECT  ld_allocate_to_id
                                                 FROM  ams_lts_lead_details
                                              group by ld_allocate_to_id );    


Plan with group by
SELECT STATEMENT  CHOOSECost: 1 K  Bytes: 168 K  Cardinality: 2 K  				
5 HASH JOIN RIGHT SEMI  Cost: 1 K  Bytes: 168 K  Cardinality: 2 K  			
3 VIEW VIEW SYS.VW_NSO_1 Cost: 171  Bytes: 17 K  Cardinality: 2 K  		
2 HASH GROUP BY  Cost: 171  Bytes: 16 K  Cardinality: 2 K  	
1 INDEX FAST FULL SCAN INDEX AMS.IDX_LTS_LEAD_DETAILS Cost: 161  Bytes: 1 M  Cardinality: 146 K  
4 TABLE ACCESS FULL TABLE AMS.AMS_LTS_LEAD_SUMMARY Cost: 1 K  Bytes: 27 M  Cardinality: 335 K  


SELECT sr_no, agent_code, agent_name, report_to
           FROM ams_lts_lead_summary
          WHERE agent_code IN (SELECT distinct ld_allocate_to_id
                                                FROM ams_lts_lead_details);   


Plan with distinct
SELECT STATEMENT  CHOOSECost: 2 K  Bytes: 167 K  Cardinality: 2 K  			
4 HASH JOIN  Cost: 2 K  Bytes: 167 K  Cardinality: 2 K  		
2 SORT UNIQUE  Cost: 127  Bytes: 1 M  Cardinality: 146 K  	
1 INDEX FAST FULL SCAN INDEX AMS.IDX_LEAD_DTL_ALLOC_TO Cost: 127  Bytes: 1 M  Cardinality: 146 K  
3 TABLE ACCESS FULL TABLE AMS.AMS_LTS_LEAD_SUMMARY Cost: 1 K  Bytes: 27 M  Cardinality: 335 K  	

[Updated on: Fri, 12 September 2008 01:16] by Moderator

Report message to a moderator

Re: Distinct versus group by [message #347378 is a reply to message #347377] Thu, 11 September 2008 07:37 Go to previous messageGo to next message
trupti111
Messages: 29
Registered: August 2008
Location: navi mumbai
Junior Member
I wanted to know which one is faster group by or distinct?
Re: Distinct versus group by [message #347385 is a reply to message #347378] Thu, 11 September 2008 07:53 Go to previous messageGo to next message
Michel Cadot
Messages: 64109
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
If you want distinct use DISTINCT, if you want to group (and use and aggregate function) then use GROUP BY.

Also ase read OraFAQ Forum Guide, especially "How to format your post?" section.

Regards
Michel
Re: Distinct versus group by [message #347529 is a reply to message #347385] Fri, 12 September 2008 00:45 Go to previous messageGo to next message
trupti111
Messages: 29
Registered: August 2008
Location: navi mumbai
Junior Member
i have formatted it
but when clicked on create topic all formatting has gone
Re: Distinct versus group by [message #347543 is a reply to message #347529] Fri, 12 September 2008 01:15 Go to previous message
Michel Cadot
Messages: 64109
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Because you didn't use code tags as explained in forum guide.

Edit: Even with code tags that I have now added, the post appears to be not formatted.

Use SQL Formatter for query.
Use SQL*Plus and dbms_xplan for execution plan.

Regards
Michel

[Updated on: Fri, 12 September 2008 01:17]

Report message to a moderator

Previous Topic: Gather statistics
Next Topic: Doumentation of wait events
Goto Forum:
  


Current Time: Mon Dec 05 06:49:31 CST 2016

Total time taken to generate the page: 0.08806 seconds