| improve group by query in table with partitions [message #185389] |
Tue, 01 August 2006 08:51  |
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  |
rleishman
Messages: 3728
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
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
