Re: performance benefits from distributing across disks

davidsonj_at_gtewd.mtv.gtegsc.com writes:
>Will distributing i/o improve performance even when the load on the
disks is light?

>In a related question....if the indexes and data are on separate disks, is
>Oracle able to request both sets of data at the same time or does Oracle request
>the index and then the data?

Here's how it works: if a column is indexed, and the SQL statement is written in such a way to utilize the index, then during data retrieval ORACLE reads the index first to find the appropriate record, then retrieves the records. Distributing the indices on a separate disk from the data improves the seek time and reduces contention.

A few warnings:

1. An index will only increase performance if you are retrieving less than 15% of the rows in a table; otherwise the overhead of reading the index and reading the table is greater than doing a full-table scan.
2. It is better to index a column that has a high-level of uniqueness that a column where there are few unique values.
3. Queries select rows having a value (not very many nulls)
4. Queries are written so that the index is used
5. Do not overload a table with indices; this will significantly reduce performance during inserts, as all the indices must be updated as well.
6. Columns in a concatenated index do not have to appear in the same order as table creation; rather, use the columns in the order the columns will be queried.

Hope this helps.

Dagmar Anne Bogan

Kopania & Komorovski            Providing Performance Engineering
Richardson, TX  75081           and Data Architecture to the World
 
dagmara_at_delphi.com