Home » SQL & PL/SQL » SQL & PL/SQL » improve group by query in table with partitions
improve group by query in table with partitions [message #185389] Tue, 01 August 2006 08:51 Go to next message
Messages: 4
Registered: July 2006
Location: CBA,
Junior Member

Hi people.
I have a questions for you.
I can´t improve this query:

trunc(cal_date),sum(cal_land_periods) minutos
FROM inter.calls_mar
and cal_direction='O'
and cal_tru_ope_id='METRO'
group by trunc(cal_date)
order by 1

This is to large for execute. It take a lot of time.
Watching in the table definition I saw that this have 29 partitions by cal_date. Each partition belongs to a day.
Something like this:

CALLS_01_122 TO_DATE(' 2006-03-02 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')
CALLS_02_122 TO_DATE(' 2006-03-03 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')

I can improve my query using this partitions? How?
I need your help, please.
Re: improve group by query in table with partitions [message #185467 is a reply to message #185389] Tue, 01 August 2006 22:04 Go to previous message
Messages: 3727
Registered: October 2005
Location: Melbourne, Australia
Senior Member
Yes, the problem is the TRUNC() function. It causes the GROUP BY to apply over EVERY partition. If you removed the TRUNC(), the GROUP BY would only apply WITHIN partitions, which would be more efficient.

I tried a few experiments on my database to get a GROUP BY PARTITION effect (eg. GROUP BY dbms_rowid.row_object(rowid)), but the result was the same.

Unless you can get rid of the TRUNC(), I don't see a way to improve it (except increasing TEMP space).

Ross Leishman
Previous Topic: Monitoring all indexes through execute immediate
Next Topic: update one table with the values from second table
Goto Forum:

Current Time: Mon Aug 21 07:02:17 CDT 2017

Total time taken to generate the page: 0.10183 seconds