Distinct versus group by [message #347377] |
Thu, 11 September 2008 07:36 ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
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 #347543 is a reply to message #347529] |
Fri, 12 September 2008 01:15 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) |
![](http://www.orafaq.com/forum/images/custom_avatars/102589.gif) |
Michel Cadot
Messages: 68678 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
|
|
|