CODEWITHSUNDEEP
oracle-databaseindexingsql-order-by
sanpwc
sanpwc

Reputation: 43

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

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?

Upvotes: 2

Views: 8767

Answers (2)

Wernfried Domscheit
Wernfried Domscheit

Reputation: 59476

Perhaps when you do ORDER BY NAME the the actual order can vary depending on your local NLS_SORT or NLS_COMP settings. Thus the order inside the index can be different than the order of your output. In case of any DATE or NUMBER data type the order ASC or DESC is always the same and most probably it is the same as the index is organized internally.

Upvotes: 0

David Aldridge
David Aldridge

Reputation: 52376

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.

Upvotes: 1

Related Questions