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

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. Received on Sat Apr 12 2008 - 16:55:20 CDT