| Group By [message #216595] |
Mon, 29 January 2007 11:05  |
tookep
Messages: 12
Registered: July 2005
Location: Ipswich
|
Junior Member |
|
|
I am running the select statement below. The select without the group by returns in seconds. With the group by the select statement takes two minutes. Any advice you could give me on how to use group by efficiently would be appreciated.
Select Statement and Explain Plan
SELECT itl.user_id,
(SELECT au.notes FROM application_user au WHERE au.user_id = itl.user_id) AS description,
itl.code,
l.WORK_ZONE,
TRUNC(itl.dstamp),
COUNT(itl.KEY)
FROM
(SELECT client_id
FROM CLIENT_GROUP_CLIENTS
WHERE CLIENT_GROUP='CORBY') CLIENT,
LOCATION l,
inventory_transaction itl
WHERE itl.site_id='COR01'
AND (TRUNC(itl.dstamp) BETWEEN TO_DATE('29/DEC/2006','DD/MON/YYYY')
AND TO_DATE('29/JAN/2007','DD/MON/YYYY'))
AND itl.client_id = CLIENT.client_id
AND itl.from_loc_id = l.location_id
AND itl.site_id = l.site_id
GROUP BY itl.user_id, itl.code, l.work_zone, TRUNC(itl.dstamp)
|
|
|
|
| Re: Group By [message #216600 is a reply to message #216595] |
Mon, 29 January 2007 11:37  |
artmt
Messages: 32
Registered: October 2006
Location: Boston
|
Member |
|
|
|
Run the statement through autotrace to see where the SORT is taking place - in memory or on disk.
|
|
|
|
|
|
| Re: Group By [message #216672 is a reply to message #216601] |
Mon, 29 January 2007 20:30 |
rleishman
Messages: 3728
Registered: October 2005
Location: Melbourne, Australia
|
Senior Member |
|
|
You will probably find that even though the no-group-by version is returning in seconds, it is just returning the first page of rows in seconds. It will probably still take a while to return ALL of the rows.
Compare the Explain Plan both with and without the GROUP BY. If the only difference is the GROUP BY step, then the above is true.
Also, you might get some improvement by removing the TRUNC() function.
SELECT itl.user_id
,(SELECT au.notes
FROM application_user au
WHERE au.user_id = itl.user_id) AS description
,itl.code
,l.work_zone
,TRUNC(itl.dstamp)
,COUNT(itl.KEY)
FROM (SELECT client_id
FROM client_group_clients
WHERE client_group = 'CORBY') client
,location l
,inventory_transaction itl
WHERE itl.site_id = 'COR01'
AND (itl.dstamp BETWEEN TO_DATE('29/DEC/2006','DD/MON/YYYY')
AND TO_DATE('29/JAN/2007','DD/MON/YYYY')) + 0.99999
AND itl.client_id = client.client_id
AND itl.from_loc_id = l.location_id
AND itl.site_id = l.site_id
GROUP BY itl.user_id
,itl.code
,l.work_zone
,TRUNC(itl.dstamp)
Ross Leishman
|
|
|
|
| Re: Group By [message #216715 is a reply to message #216672] |
Tue, 30 January 2007 02:27 |
tookep
Messages: 12
Registered: July 2005
Location: Ipswich
|
Junior Member |
|
|
Please find below the results of the autotrace with the group by and without:
With the group by:
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=4769 Card=7 Bytes=54
6)
1 0 TABLE ACCESS (BY INDEX ROWID) OF 'APPLICATION_USER' (Cost=
2 Card=1 Bytes=27)
2 1 INDEX (UNIQUE SCAN) OF 'PK_APPLICATION_USER' (UNIQUE) (C
ost=1 Card=482)
3 0 SORT (GROUP BY) (Cost=4769 Card=7 Bytes=546)
4 3 NESTED LOOPS (Cost=4759 Card=7 Bytes=546)
5 4 NESTED LOOPS (Cost=4752 Card=7 Bytes=371)
6 5 TABLE ACCESS (FULL) OF 'INVENTORY_TRANSACTION' (Cost
=4752 Card=18 Bytes=684)
7 5 INDEX (UNIQUE SCAN) OF 'PK_CLIENT_GROUP_CLIENTS' (UN
IQUE)
8 4 TABLE ACCESS (BY INDEX ROWID) OF 'LOCATION' (Cost=1 Ca
rd=1 Bytes=25)
9 8 INDEX (UNIQUE SCAN) OF 'U_LOCATION_ID' (UNIQUE)
Statistics
----------------------------------------------------------
63 recursive calls
0 db block gets
81467 consistent gets
55062 physical reads
0 redo size
1244 bytes sent via SQL*Net to client
283 bytes received via SQL*Net from client
3 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
28 rows processed
Without the group by:
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=4759 Card=7 Bytes=54
6)
1 0 TABLE ACCESS (BY INDEX ROWID) OF 'APPLICATION_USER' (Cost=
2 Card=1 Bytes=27)
2 1 INDEX (UNIQUE SCAN) OF 'PK_APPLICATION_USER' (UNIQUE) (C
ost=1 Card=482)
3 0 NESTED LOOPS (Cost=4759 Card=7 Bytes=546)
4 3 NESTED LOOPS (Cost=4752 Card=7 Bytes=371)
5 4 TABLE ACCESS (FULL) OF 'INVENTORY_TRANSACTION' (Cost=4
752 Card=18 Bytes=684)
6 4 INDEX (UNIQUE SCAN) OF 'PK_CLIENT_GROUP_CLIENTS' (UNIQ
UE)
7 3 TABLE ACCESS (BY INDEX ROWID) OF 'LOCATION' (Cost=1 Card
=1 Bytes=25)
8 7 INDEX (UNIQUE SCAN) OF 'U_LOCATION_ID' (UNIQUE)
Statistics
----------------------------------------------------------
63 recursive calls
0 db block gets
81697 consistent gets
49902 physical reads
0 redo size
21003 bytes sent via SQL*Net to client
633 bytes received via SQL*Net from client
53 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
775 rows processed[Updated on: Tue, 30 January 2007 19:29] by Moderator Report message to a moderator |
|
|
|
| Re: Group By [message #216914 is a reply to message #216715] |
Tue, 30 January 2007 19:34 |
rleishman
Messages: 3728
Registered: October 2005
Location: Melbourne, Australia
|
Senior Member |
|
|
Exactly! The plan is identical except for the SORT step. Without the GROUP BY appears to be faster because it shows you the first page of rows as soon as it retrieves them. The GROUP BY forces it to wait until all rows are fetched, performs a sort, then starts sending the rows to SQL*Plus.
Ross Leishman
|
|
|
|
| Re: Group By [message #216961 is a reply to message #216595] |
Wed, 31 January 2007 01:31 |
michael_bialik
Messages: 621
Registered: July 2006
|
Senior Member |
|
|
Hi.
IMO the performance problem is caused by FULL table scan on
inventory_transaction table.
Try creating an index:
CREATE INDEX inventory_transaction$SITE_DATE
ON inventory_transaction ( site_id, dstamp ) ...
Run the query with AUTOTRACE and post results.
HTH.
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
