Re: Performance problem with SQL query

From: <Mark.Wright_at_bristol.ac.uk>
Date: Wed, 17 Sep 2003 14:18:14 GMT
Message-ID: <HLD3qE.3yK@bath.ac.uk>

W.Breitling <member28455_at_dbforums.com> wrote:
: My guess is that the clustering factor on that second index is rather
: high. Have you tried lowering it. One quick way of doing that is to
: delete the statistics on that index. The CBO then uses defaults for the
: missing statistics and the default for clustering index is 800.

Hi W.
I'm not entirely sure what the clustering factor is, but the number of rows is ~3.5M, number of blocks is ~115K, and clustering_factor from dba_indexes is ~1.2M for that index. Other index has simmilar stats but is not unique.

Deleting the statistics for pk_fsmt_jrmis_points made no difference. However, deleteing the stastics for the whole table did make a difference:

PLAN oper optns obj optzer a b

SELECT STATEMENT CHOOSE Cost = 29

SORT AGGREGATE
HASH JOIN

      PARTITION RANGE  SINGLE      KEY KEY
        TABLE ACCESS  BY LOCAL INDEX ROWID  FSMT_JRMIS_POINTS    KEY KEY
          INDEX  RANGE SCAN  INDX_N3_JRMIS_POINTS    KEY KEY
      VIEW    VW_NSO_1
        SORT  UNIQUE
          COLLECTION ITERATOR  PICKLER FETCH
            TABLE ACCESS  FULL  DUAL

The plan is at last exactly what it should be from the two individual queries! But...
Although the Cost is reported to be very low - about what it should be from the sum of the costs of the two seperate queries in my first post - the query in fact still takes about 8 seconds :-( . So now I am double confused: why does it only take the "correct" path when there are no stats, and why is the theoretically zippy solutions still slow?
Cheers,
Mark

--

Received on Wed Sep 17 2003 - 09:18:14 CDT