Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: SQL hints/ways of enhancing sort/GROUP BY queries...

Re: SQL hints/ways of enhancing sort/GROUP BY queries...

From: Ed <edoxenham_at_hotmail.com>
Date: 20 Sep 2003 16:19:55 -0700
Message-ID: <abf946da.0309201519.7cb23f4c@posting.google.com>


Parallel query can certainly speed things up considerably - although possibly to the detriment of other processes on the machine.

Group by queries that compress the data to a small result set respond very well to parallel query. However if the group by does not compress the data much (e.g. rolling up 12m accounts to 10m customers) then the overhead of merging the outputs from the parallel processes may make the runtime worse than a non-parallel query.

Be aware that parallel query (can) makes things happen faster - not more efficiently - so be aware of the potential impact on other processing.

hth

Ed.

domenicg_at_hotmail.com (Domenic G.) wrote in message news:<c7e08a19.0309180718.7e7c7fd9_at_posting.google.com>...
> In some cases, the ORDER BY can use an index, but I would need to see
> a specific example.
>
> You can also use simple aggregate materialized views that are refresh
> on commit ...
>
> For example:
>
> CREATE MATERIALIZED VIEW LOG ON any_table WITH PRIMARY KEY etc etc
> (can't remember exact syntax)
>
> then ...
>
> CREATE MATERIALIZED VIEW ...
> REFRESH FAST ON COMMIT
> ...
>
> SELECT <see docs for restrictions on select list>
>
> GROUP BY ...
>
> Hope this helps ...
>
> Domenic.
>
>
> spendius_at_muchomail.com (Spendius) wrote in message news:<aba30b75.0309180158.3c65f399_at_posting.google.com>...
> > Hi,
> > I'd like to know if someone knows how to specifically
> > tune queries involving sorts and/or using GROUP BY functions.
> > Are there particular hints for that purpose ? Apart from
> > playing with the sort area size, do you know any other
> > feature or way of improving this category of queries ?
> >
> > Thanks.
> > Spendius
Received on Sat Sep 20 2003 - 18:19:55 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US