Re: Tune a *simple* join on a COUNT()

spendius_at_muchomail.com (Spendius) wrote in message news:<aba30b75.0311200251.4b99384f_at_posting.google.com>...
> 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

Spendius, my mother used to say, "if wishes were nickels we would all be rich."

The count(*) may be simple but the join required to return the data or the count still has to be performed in order to determine the count. It is unlikely to be instant.

You can apply hints to the SQL to force Oracle to attempt each of the three possible join methods: sort/merge, hash, and nested loop. select --+ FULL(STUFF) INDEX(thst index_name) -- count(*) etc....

HTH -- Mark D Powell -- Received on Thu Nov 20 2003 - 09:05:01 CST