Re: Using Indexes

Use explain to see what's happening.

Don't assume that accessing an index is quicker than a sort. It just dependants how disorganized the table or index is. Adding an index should make the optimizer use the index, but it still might be quicker to scan the index sequentially and sort. Sorts, should be quite efficient (in theory) and when all the rows are being accessed you can do less IO by reading sequentially and sorting the results especially if the sort can be done in memory. Choosing the best access path is very complex and the optimizer has to make general assumptions. There will always be occasions when it gets it wrong.

As an aside does anyone know whether Oracle sorts are efficient. It seems to me that the sort merge joins often perform worse than nested joins. Is the balance right (i.e. what one might expect) Any comments????

DelphiDev wrote:

> I have submitted the following statements to SQL Plus 3.3 on Oracle 7.0:
>
> SELECT IDCODE FROM PERSONEL ORDER BY IDCODE;
>
> This query takes a fair amount of time. I then index it and run the
> query again:
>
> CREATE INDEX IDCODEINDEX ON PERSONEL(IDCODE)
> SELECT IDCODE FROM PERSONEL ORDER BY IDCODE;
>
> The query seems to take the same amount of time to "Order" the records
> before actually printing them. I assume that if a index is present then
> Oracle will take advantage of it. This doesn't seem to be the case.
> Could someone explain this?
>
> Thanks, Stan.
Received on Tue Mar 02 1999 - 06:09:22 CST