Re: I can't think of the subject name (have not words).

Leonid Vorontsov wrote: > > Art S. Kagel wrote: > > > > Leonid Vorontsov wrote: {SNIP] > Do You see index3 on table1(field1, field2)? There isn't sorting needed. WRONG WRONG WRONG! > There isn't table reading needed too. index3 contains all information > queried and in right order. ALSO WRONG WRONG WRONG! The data returned from the index is NOT in the correct order. Your ORDER BY clause specified the second column in the index that can be used for filtering or the first column in the index that CANNOT be used for filtering. The point here is that FIRST_ROWS optimization forces the index that matches the ORDER BY to be used. With all four indexes available, since the 2-column index CANNOT be used for filtering on the other column anyway, since there is no filter on the ORDER BY column, the single column index is selected as more efficient. In addition, since the index cannot be used for filtering the KEY-ONLY optimization cannot be used and the actual data pages MUST be read from disk increasing the total cost. The cost is high because this essentially amounts to an index driven random table scan, all data pages MUST be read to filter the rows. If you drop the single column indexes the corresponding two column index will be used instead of a sort for ordering (again because of FIRST_ROWS) and the data pages will STILL have to be filtered manually. This is VERY costly. If you disable FIRST_ROWS optimization then the other index will be used to filter the rows quickly and the resulting selected rows will be returned, BTW KEY-ONLY optimization should then be used, and sorted. For this query the FIRST_ROWS optimization is definitely inappropriate. Without that option this query should only take a few seconds.