Re: Can I define an index for a select statement.

The first query will do a table scan of the base table. The scan will just read the data blocks in the order on the disk so it can't go any faster. Unless you meant select InvoicDate instead of * to enable index only scan to be performed??

The second query has an order by so it will do a sort. The index has Invoice date defined second so the optimizer can't use this index efficiently. Even if you had an index on the Invoice date the optimizer could still choose a sort. An index scan followed by access to the table pages will always be slower than a straight table scan. Technically, when you get more than a certain percentage of rows back via an index there is a threshold point when a table scan-sort is more efficient than a index scan. Table stats often try to estimate the sorted order of table so it can efficiently work out the threshold. I'm not sure if Oracle does this.

As an aside does anyone know how efficient Oracle sorts are. How do they compare to other DBMS's. Any ideas of what percentage the threshold might be given an averagey type of table.

John Francis Lundy wrote:

> Primary key - InvoiceNo
> Secondary key - InvoiceNo,InvoiceDate
>
> 20,000 records
>
> select * from Invoice - 3 seconds
>
> select * from Invoice - 22 seconds
> order by InvoiceDate
>
> if the second query used the secondary index it should be faster as they'd
> be ordered already.
> Can I select the index? Whats my problem please help.
Received on Mon Mar 08 1999 - 04:33:17 CST