Re: Oracle Performance

From: Robert Klemme <shortcutter_at_googlemail.com>
Date: Mon, 12 Mar 2007 15:54:40 +0100
Message-ID: <55l7stF25ggotU1@mid.individual.net>

On 12.03.2007 15:39, Mark D Powell wrote:
> On Mar 12, 9:30 am, "pbrajesh" <rajesh.tamil.in..._at_gmail.com> wrote:

>> Hi,
>>
>> We have an datawarehouse database Oracle 9i.
>> A table has 145 fields and 70 million records for a month.
>> We want to perform an group by 5 of fields in the table .
>> How to do that will minimum time ?. I wrote an oracle procedure but it
>> is very slow.
>>
>> Thanks
>> R

>
> Why would you use a procedure? This sounds like just a single SQL
> statement.
>
> Take a look at the explain plan for the current version of the query.
> Consider the effect an index on the 5 group by columns would have. If
> it seems like it would be beneficial add the index. Look at the
> plan. Compare and consider then test.
>
> Other considerations: larger sort_area_size and/or hash_area_size for
> query (set at session level)

And probably also: what index to create (bitmap, regular). Then again, if this query is done only once a FTS might be as efficient as index creation plus query execution, would it?

Kind regards

        robert Received on Mon Mar 12 2007 - 09:54:40 CDT