Re: Performance with ORDER BY !!!

I know that you can add an index on the ORDER BY field. Then, with the hint /* +FIRST_ROWS */ you can force that the rows will be returned in already sorted order. This does not always solve the problem. Let's see the following example:

SELECT .. FROM .. WHERE Field1=x ORDER BY Field2 Index on The field used in Clause1 and index on Field1

1. Field1 = Field2, then FIRST_ROWS give real good performance
2. If not, 2 subcases a) Field1=x is sharp (i.e. only few rows are returned). FIRST_ROWS gives poor performance because it scans the Field2 index and looks if Field1=x is incidentally met. Here it would be be better to take the index on Field1 and then sort the few answer rows. b) Field1=x is not sharp (big answer set). FIRST_ROWS performs well: the index on Field2 is scanned and then looks if Field1=x is met (what will statistically happen often since the clause is not sharp).

Can I get ORACLE to apply the one or other approach depending on the case? I experienced that FIRST_ROWS FORCES the index scan independently of the sharpness of the where clauses. Received on Mon Dec 06 1999 - 04:24:48 CST