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
aleplus
Messages: 4
Registered: July 2006
Location: CBA,
Junior Member

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

SELECT
trunc(cal_date),sum(cal_land_periods) minutos
FROM inter.calls_mar
where
cal_rating_status='B'
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
rleishman
Messages: 3724
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: PLSQL update loop takes a looooong time
Next Topic: DML through view
Goto Forum:
  


Current Time: Wed Dec 07 22:18:26 CST 2016

Total time taken to generate the page: 0.20124 seconds