Re: Tunning Select and Where statements

In article <7sq3av$9ap_at_romeo.logica.co.uk>,   "Ming" <fungs_at_logica.com> wrote:
> Given the following statement :-
>
> SELECT name FROM people WHERE age = 21;
>
> Why would an index on 'age' in WHERE clause result in a slower query
than
> having no index.

• If your data distribution is not even ( skewed ) than Oracle must scan both index AND data blocks. ( The break-even point is about 10% of the whole table ). When executing FULL table scan ( without index ) Oracle is reading a number of blocks in one I/O operation. It may happen that the total number of reads are less that way.

> Why would an index on both 'name' and 'age' result in a faster query.
>

-- I guess you meann 'age' and 'name'.

   In that case Oracle is using INDEX_ONLY access ( all fields from    select list and where clause are in the same index ).    The index entries are smaller the data rows, so in one I/O it may    read more data.

> Thanks for any Help
>
> Ming.
>

  Good luck. Michael.

Sent via Deja.com http://www.deja.com/
Before you buy. Received on Tue Sep 28 1999 - 17:03:09 CDT