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

Given the cost of sorting just 500K of
data is about 1200 (lines 4 and 6 of the plan) I suspect you have a very small value for you sort_area_size - possibly 64K. Have you tried increasing it ?

In your hinting, did you get a plan which was

nested_loop

    index (FULL SCAN) of THST_PK
    index (RANGE SCAN) of STUFF_THST_FK_I

which might allow for a no sort option if it is possible.

Are any of the columns in the STUFF_THST_FK_I declared to be non-null, as this might (but probably shouldn't) make a difference to the choice of plans.

Have you checked wait stats and CPU used for the task. This may tell you why it is taking so long. I don't think it's likely to be a CPU problem.

--
Regards

Jonathan Lewis
http://www.jlcomp.demon.co.uk

  The educated person is not the person
  who can answer the questions, but the
  person who can question the answers -- T. Schick Jr


One-day tutorials:
http://www.jlcomp.demon.co.uk/tutorial.html
____Belgium__November (EOUG event - "Troubleshooting")
____UK_______December (UKOUG conference - "CBO")


Three-day seminar:
see http://www.jlcomp.demon.co.uk/seminar.html
____UK___November


The Co-operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html


"Spendius" <spendius_at_muchomail.com> 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