28.07.2014 06:15, Robert martin wrote:

>
> Have the following (cut down) ordered SQL
>
> SELECT *
> FROM Document d
> LEFT JOIN DocumentCreator dc ON dc.DocCreatorRef = d.DocCreatorRef
> ORDER BY dc.Description
>
> There is an Index on dc.Description but it is not used. Plan shown below..
>
> PLAN SORT (JOIN (D NATURAL, DC INDEX (DOCUMENTCREATOR_PK)))

Correct, such a query cannot use an index for ordering.

> 555431 fetches, 0 marks, 2206 reads, 0 writes.
> 0 inserts, 0 updates, 0 deletes, 77830 index, 79193 seq.
> Delta memory: 680 bytes.
> Total execution time: 0.962s

vs

> SELECT *
> FROM Document d
> JOIN DocumentCreator dc ON dc.DocCreatorRef = d.DocCreatorRef
> ORDER BY dc.Description
>
> PLAN JOIN (DC ORDER DOCCREATOR_DESCRIPTION, D INDEX
> (DOCUMENT_DOCCREATORREF))
>
> 527 fetches, 0 marks, 30 reads, 0 writes.
> 0 inserts, 0 updates, 0 deletes, 242 index, 0 seq.
> Delta memory: -3141376 bytes.
> Total execution time: 0.062s

7000 records inside DocumentCreator don't match 242 index reads. I bet
you didn't fetch all the records produces by the query. Performing a
complete fetch or testing with select(*) instead of select * may show
you quite different timings.


Dmitry