Re: Yes, this is probably a stupid question but...

On Apr 12, 5:55 pm, Pat <pat.ca..._at_service-now.com> wrote:
> We have an application that stores operational data as a single master
> table and a set of child tables. Based on the class of data, a
> different child table gets joined in e.g.
>
> master inner_join pets -> gives us pet data
> master inner_join cars -> gives us car data
>
> It works reasonably well, and it makes the developer's lives easier.
> Normally the working set of records is quite small as well e.g. < 1k
> records at a time so the actual queries being issued are something
> like:
>
> select <some fields> from a inner join b on a .sys_id = b.sys_id where
> a.<some indexed column> = <some fairly selective criteria>
>
> There's one part of the application though that computes counts via
> the not terribly complex:
>
> select count(*) from a inner join b on a sys_id = b.syss_id where
> a.<some indexed column> = <some selective criteria>
>
> Recently though we've had some customers with some unusual data shape
> such that the particular count query (above) is joining 200k records
> or so together to implement the count.
>
> Looking at profile on the database, that count alone is something like
> 68% of the server CPU load. Not surprisingly, I'd dearly love to do
> something about this. Problem is, I'm stumped.
>
> Question I have is:
>
> Short of constructing a summary table (which is probably a non starter
> in this environment), is there anything I could to to speed up the
> return time of a count query like the above?
>
> Apologies if this is a stupid question, but as you can tell from the
> batch of posts I've been putting on the newsgroup I'm on something of
> a jihad to clean up some of the Oracle performance bottlenecks in this
> app, that that is definately one of them.

Another option to consider is looking at is using/creating a materialized view to support the count query.

Going down the posts in the other part of this thread, if you force the count query to use a hash join, this might help the queries that return a high count ( 200k ) but could ( possibly severely ) impact the queries that ( running now with a nested loop ) return a small count. Received on Sun Apr 13 2008 - 13:20:06 CDT