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;
| 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?
The following recommendations will help you in your SQL tuning process.
You'll find 3 sections below:
CREATE INDEX test_table_idx_type_id ON TEST_TABLE (TYPE,ID);
SELECT * FROM TEST_TABLE WHERE TEST_TABLE.TYPE = 1 ORDER BY TEST_TABLE.ID