Tune a *simple* join on a COUNT()

Hi,
We have a view here whose query is as simple as
> SELECT
> <fields list>
> FROM stuff stuff,
> thing_stuff thst
> WHERE stuff.thst_id=thst.id
> /

This view returns 108000 rows.
STUFF contains 108000 rows
THING_STUFF contains 96000 rows.
The field THST_ID in STUFF is an FK that points towards THING_STUFF.ID. This field is indexed (check EXPLAIN PLAN below).

When we perform a simple COUNT() (instead of specifying the required columns) it takes 17 to 20 seconds to get the result (108000). The EXPLAIN PLAN is the following:

#SELECT STATEMENT Optimizer=CHOOSE (Cost=2819 Card=1 Bytes=10)
#  SORT (AGGREGATE)
#    MERGE JOIN (Cost=2819 Card=103571 Bytes=1035710)
#      SORT (JOIN) (Cost=1339 Card=96481 Bytes=482405)
#        INDEX (FAST FULL SCAN) OF 'THST_PK' (UNIQUE) (Cost=135 
#                                      Card=96481 Bytes=482405)
#      SORT (JOIN) (Cost=1480 Card=103571 Bytes=517855)
#        INDEX (FAST FULL SCAN) OF 'STUFF_THST_FK_I' (NON-UNIQUE)
#                            (Cost=190 Card=103571 Bytes=517855)

We're on 8.1.7.4 on Sun/Solaris 8 on a Sun Enterprise E4500 with 8 CPUs (but tens of other instances run on this server as well).

I'd like to know if there are means of improving the response time on this count (I tried many hints but to no avail), the ideal would be to get an *immediate* result... Can one get at that ?

Thanks a lot !
Spendius Received on Thu Nov 20 2003 - 04:51:25 CST