Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: Performance problem with SQL query

Re: Performance problem with SQL query

From: <Mark.Wright_at_bristol.ac.uk>
Date: Tue, 16 Sep 2003 13:48:39 GMT
Message-ID: <HLB7p3.4w3@bath.ac.uk>


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 - 08:48:39 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US