Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Re: Performance problem with SQL query
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
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
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 - 08:48:39 CDT