Oracle doesn't use index on varchar2 column in case of of order by clause

Question

Here's my table with several millions of rows:

  CREATE TABLE "TEST_TABLE"
  ( "ID" NUMBER(*,0),
    "TYPE" NUMBER(*,0),
    "NAME" VARCHAR2(10 BYTE)
  );

and a couple of indexes:

   CREATE INDEX TEST_INDEX_T_N ON TEST_TABLE (TYPE, NAME);
   CREATE INDEX TEST_INDEX_T_I ON TEST_TABLE (TYPE, ID);

In case of following query

   SELECT * FROM TEST_TABLE WHERE TYPE = 1 ORDER BY ID

I got following plan

   |   0 | SELECT STATEMENT |                |   886K|    21M|  2192   (1)| 00:00:27 |
   |*  1 |  INDEX RANGE SCAN| TEST_INDEX_T_I |   886K|    21M|  2192   (1)| 00:00:27 |

However, in case of:

   SELECT NAME FROM TEST_TABLE WHERE TYPE = 1 ORDER BY NAME;

I got

   |   0 | SELECT STATEMENT  |                |   886K|    16M|       |  7826   (1)| 00:01:34 |
   |   1 |  SORT ORDER BY    |                |   886K|    16M|    23M|  7826   (1)| 00:01:34 |
   |*  2 |   INDEX RANGE SCAN| TEST_INDEX_T_N |   886K|    16M|       |  2415   (1)| 00:00:29 |

So, as far as I understand, oracle doesn't use index for sorting varchar column (cause there's extra SORT ORDER BY operation within explain plan), but everything is fine in case of INT or DATE types. How it is possible to "use index for sorting" varchar columns?

Why do you want Oracle to use an Index for sorting? When Oracle have to sort a result set, then everything is already available in memory or TEMP tablespace. There is no need to read anything additional from disc. — Wernfried Domscheit
just a note, your suggestion that it is not working with varchars is not right. I tried to reproduce your case (however on much smaller set like 100k rows) and for the second query which is ordered by NAME I'm getting plan like yours for the first query, i.e. only select statement and INDEX RANGE SCAN by test_index_t_n — Martina
@Wernfried Cause it's much faster. In case of following query SELECT * FROM TEST_TABLE WHERE TYPE = 1 ORDER BY ID, in my particular case (on develiper machine, 10.000.000 records in table and 1000.000 in result set) it's 50 times faster. — sanpwc
@san DDL exactly like yours, then I populated table with sample data, where first two colums are random numbers 1-500 and third column one of five predefined strings. For sure it doesn't match your case, however just to demonstrate that execution plan is very much data dependent. — Martina

David Aldridge David Aldridge · Accepted Answer · 2015-03-24T11:44:20

If you have very few distinct value of TYPE then the optimiser might calculate that it is faster to just perform a full scan of the table and sort the result, rather than reading a high proportion of the table via the index.

Oracle can use an index for providing an ordered sort on a character column, but you also have to consider whether the ORDER BY is binary (ie. according to ASCII or other character code) or linguistic (which varies by language). You might have to create the index using particular NLS parameters that will match the NLS language sort order, or modify the ORDER BY so that it is based on the binary code of the text.

The approach you should take depends on whether you are using character sets other than ASCII for the text, and maybe even whether you care about ordering upper and lower case characters correctly.

Oracle doesn't use index on varchar2 column in case of of order by clause

2 Answers