Re: How to optimize this query?

rgaffuri_at_cox.net (Ryan Gaffuri) wrote

> why would using sepearte indexes for each flag improve performance?

Unique index scans on multiple indexes versus an index range scan on a single table.

> why is merging or hashing better than one index?

Not sure id I understand the question correctly.. Oracle can use multiple indexes and then do an intersection of the results to determine which rows satisfy the criteria (i.e. which rows occur in all the index result sets).

> I noticed that he is using 2 indexes, so I thought he may want to
> expand the index to all 5 fields. or is that wrong?

I only noticed one index.. did I miss something again?

If you have a 5 column index (not a unique/pk one), then it will only be useful if the WHERE clause uses _all_ 5 columns. As soon as you use less than 5 columns, it will need to do an index range scan. Which can be a bad thing.

> im guessing a full table scan is faster here because with the 'range
> scan'
> and the 'not equal' you have to look at every block anyway?

The bigger the index range to scan, the slower it is. Very silly example. You have a SURNAME index.
This will be fast:
  SELECT
     count(*)
  FROM t
  WHERE surname='SMITH'
  AND age BETWEEN 20 and 30

The index is used to get all the rows for SMITH. Each row is read from the table and the AGE is checked.

This will be a bit slower, but should still be acceptable:   SELECT
     count(*)
  FROM t
  WHERE surname BETWEEN 'SMITH' and 'SMYTHE'   AND age BETWEEN 20 and 30

You are only scanning a small portion of the index.

This will be very slow:
  SELECT
     count(*)
  FROM t
  WHERE surname BETWEEN 'ARSE' and SMITH'   AND age BETWEEN 20 and 30

You are now scanning a considerable chunk of the index. At least 80% of the index value will satisfy your criteria. That means that 80% of the actual table rows will satisfy your criteria.

If you are going to read 80% of the table's rows, why not just get on with it (using a full table scan), instead of first working through the index?

Index range scans can get worse the more columns there are in the index.

> oracle 8i is pretty good at deciding when to use indexes. does this
> happen often? using an index when its inappropriate? does this
> indicate that the table is not analyzed?

Yep - that is often the case. Insufficient stats for the CBO to make an intelligent decision.

--
Billy