Re: Performance problem with SQL query

Any guesses to what the CBO likes about the PK_FSMT_JRMIS_POINTS index? Is it related to what you called "options blah" in your first message? Since this index doesn't include the ID column, it seems there must be something about it that is related to other parts of the WHERE clause.

Would you be able to post the actual SQL that you are trying?

-Todd

On Tue, 16 Sep 2003 13:48:39 GMT, Mark.Wright_at_bristol.ac.uk wrote:

>Todd Barry <tbarry2000_at_hotmail.com> wrote:
>: Yes, all the code examples are 8i-compatible. I would definitely try
>: the ROWNUM approach first (coupled with the first_rows hint).
>
>Hi Todd,
>I'm back from holiday now [ :-( ] and have had a chance to try these extra
>ideas.
>
>Unfortunately, none of them cause the query to hit the
>index. Interestingly, in the time that I have been away, the optimiser has
>started taking a slightly different route, which it believes to be quicker
>(Cost slightly lower), but which in fact takes over twice as long! The new
>explain plan is:
>
>PLAN oper optns obj optzer a b
>-----------------------------------------------------------------------------
>SELECT STATEMENT CHOOSE Cost = 1317
>
> SORT AGGREGATE
> HASH JOIN
> PARTITION RANGE SINGLE KEY KEY
> TABLE ACCESS BY LOCAL INDEX ROWID FSMT_JRMIS_POINTS ANALYZED KEY KEY
> INDEX RANGE SCAN PK_FSMT_JRMIS_POINTS ANALYZED KEY KEY
> VIEW VW_NSO_1
> SORT UNIQUE
> COLLECTION ITERATOR PICKLER FETCH
>
>The previous plan had a Cost of 1535, but was in fact quicker. The index
>range scan of PK_FSMT_JRMIS_POINTS is pretty useless, because it doesnt
>contain the "id" column. INDX_N3_JRMIS_POINTS is the index we want to hit,
>as was shown in the first post I made.
>
>The additions you suggested (the hint and ROWNUM) change the plan only
>very slightly, but not the Cost or the execute time:
>
>PLAN oper optns obj optzer a b
>----------------------------------------------------------------------------
>SELECT STATEMENT HINT: FIRST_ROWS Cost = 1317
>
> SORT AGGREGATE
> HASH JOIN
> PARTITION RANGE SINGLE KEY KEY
> TABLE ACCESS BY LOCAL INDEX ROWID FSMT_JRMIS_POINTS ANALYZED KEY KEY
> INDEX RANGE SCAN PK_FSMT_JRMIS_POINTS ANALYZED KEY KEY
> VIEW VW_NSO_1
> SORT UNIQUE
> COUNT
> FILTER
> COLLECTION ITERATOR PICKLER FETCH
>
>
>Just the COUNT and FILTER steps added in the subquery. I tried the SELECT
>* FROM TABLE(CAST(function_x() AS [...] aswell, but that made no
>difference at all (although it looks nicer!)
>
>Do you have any other ideas or thoughts on this?
>Cheers,
>Mark
Received on Tue Sep 16 2003 - 16:02:01 CDT