Oracle order by not using index when index contains varchar2 column - NLS_SORT

user_7472819 0
Viewed 254

I am looking for a way for Oracle to use an index for sorting even if it contains a column of type VARCHAR2.

For example, if I have the following table:

CREATE TABLE test
(
   id    NUMBER,
   t     VARCHAR2(24 CHAR),
   n     NUMBER
);

with the following indices:

CREATE INDEX ix_test1
   ON test(n, id);

CREATE INDEX ix_test2
   ON test(t, id);

Then the following SELECT statement

  SELECT *
    FROM test
   WHERE     n = 0
         AND id > 100
ORDER BY n, id;

has the following execution plan:

------------------------------------------------
| Id  | Operation                   | Name     |
------------------------------------------------
|   0 | SELECT STATEMENT            |          |
|   1 |  TABLE ACCESS BY INDEX ROWID| TEST     |
|*  2 |   INDEX RANGE SCAN          | IX_TEST1 |
------------------------------------------------

Since the columns in the index IX_TEST1 correspond to the columns in the ORDER BY clause, there's no sort operation.

But, the following statement

  SELECT *
    FROM test
   WHERE     t = 'X'
         AND id > 100
ORDER BY t, id;

has this execution plan

-------------------------------------------------
| Id  | Operation                    | Name     |
-------------------------------------------------
|   0 | SELECT STATEMENT             |          |
|   1 |  SORT ORDER BY               |          |
|   2 |   TABLE ACCESS BY INDEX ROWID| TEST     |
|*  3 |    INDEX RANGE SCAN          | IX_TEST2 |
-------------------------------------------------

As you can see, there is an explicit sorting here. This is logical in that the storage of the values for column T in the index is binary (AFAIK), while the sorting depends on the currently set value for NLS_SORT.

Is there a possibility to define the index differently or to formulate the ORDER BY clause differently, so that also in this case the explicit sorting is omitted.

Edit: There's no sample data in the test table and I'm using Oracle 12.1.

sql-order-by sql-execution-plan oracle
1 Answers

Oracle will always use the cheapest method for sequence an SQL result set and the CBO will use an index if it consumes fewer resources than a sort.

I will reproduce your case using Oracle 19c and default NLS_SORT

SQL> select version from v$instance ;

VERSION
-----------------
19.0.0.0.0

SQL> CREATE TABLE test
(
   id    NUMBER,
   t     VARCHAR2(24 CHAR),
   n     NUMBER
);  2    3    4    5    6

Table created.

SQL> CREATE INDEX ix_test1
   ON test(n, id);
Index created.

SQL> CREATE INDEX ix_test2
   ON test(t, id);  2

Index created.

SQL> set lines 200 pages 0
SQL> set autotrace traceonly explain
SQL>  SELECT *
    FROM test
   WHERE     n = 0
         AND id > 100
ORDER BY n, id;  2    3    4    5

Execution Plan
----------------------------------------------------------
Plan hash value: 1505378640

----------------------------------------------------------------------------------------
| Id  | Operation                   | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |          |     1 |    76 |     0   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| TEST     |     1 |    76 |     0   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | IX_TEST1 |     1 |       |     0   (0)| 00:00:01 |
----------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("N"=0 AND "ID">100 AND "ID" IS NOT NULL)

Note
-----
   - dynamic statistics used: dynamic sampling (level=2)

SQL> SELECT *
    FROM test
   WHERE     t = 'X'
         AND id > 100
ORDER BY t, id;  2    3    4    5

Execution Plan
----------------------------------------------------------
Plan hash value: 3173568990

----------------------------------------------------------------------------------------
| Id  | Operation                   | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |          |     1 |    76 |     0   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| TEST     |     1 |    76 |     0   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | IX_TEST2 |     1 |       |     0   (0)| 00:00:01 |
----------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("T"='X' AND "ID">100 AND "ID" IS NOT NULL)

Note
-----
   - dynamic statistics used: dynamic sampling (level=2)

In both cases, I don't see any sort order by operation performed by the CBO. Now let's try to add sample data

SQL> set autotrace off
SQL>
SQL> insert into test values ( 101 , 'A' , 0 );

1 row created.

SQL> insert into test values ( 102 , 'B' , 1 );

1 row created.

SQL> insert into test values ( 103 , 'X' , 0 ) ;

1 row created.

SQL> insert into test values ( 104 , 'X' , 1 ) ;

1 row created.

SQL> commit ;

Commit complete.

SQL> exec dbms_stats.gather_table_stats( ownname => 'MYSCHEMA' , tabname => 'TEST' ) ;

PL/SQL procedure successfully completed.

SQL> set autotrace traceonly explain
SQL> SELECT *
    FROM test
   WHERE     t = 'X'
         AND id > 100
ORDER BY t, id;  2    3    4    5

Execution Plan
----------------------------------------------------------
Plan hash value: 3173568990

----------------------------------------------------------------------------------------
| Id  | Operation                   | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |          |     2 |    18 |     2   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| TEST     |     2 |    18 |     2   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | IX_TEST2 |     2 |       |     1   (0)| 00:00:01 |
----------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("T"='X' AND "ID">100 AND "ID" IS NOT NULL)

As you can see, there is no such sort order by, as Oracle does not need to order the result as it is inherited by the index. I might be able to evolve the answer, if you can provide your sample data when you get this sort order by. In normal conditions, it does not happen because is already sorted.

UPDATE

Changing NLS_SORT to a language ( in this case GERMAN ) will produce a sort operation as that is a consequence of linguistic collation.

SQL> ALTER SESSION SET NLS_SORT='GERMAN' ;

Session altered.
SQL> set autotrace traceonly
SQL> SELECT *
    FROM test
   WHERE     t = 'X'
  2    3    4  AND id > 100
  5   ORDER BY t, id;


Execution Plan
----------------------------------------------------------
Plan hash value: 3867551970

-------------------------------------------------------------------------------------------------
| Id  | Operation                            | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                     |          |     2 |    18 |     3  (34)| 00:00:01 |
|   1 |  SORT ORDER BY                       |          |     2 |    18 |     3  (34)| 00:00:01 |
|   2 |   TABLE ACCESS BY INDEX ROWID BATCHED| TEST     |     2 |    18 |     2   (0)| 00:00:01 |
|*  3 |    INDEX RANGE SCAN                  | IX_TEST2 |     2 |       |     1   (0)| 00:00:01 |
-------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   3 - access("T"='X' AND "ID">100 AND "ID" IS NOT NULL)


Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
          2  consistent gets
          2  physical reads
          0  redo size
        751  bytes sent via SQL*Net to client
        434  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
          2  rows processed

As shown, changing the behaviour of NLS_SORT to a linguistic value, introduces a sort order by in execution plan.

If the value is a named linguistic sort, then comparison is defined by this sort. A linguistic sort uses various rules to achieve ordering expected by speakers of one or more natural languages. This is usually the same ordering that is used in dictionaries and telephone directories in those languages.

That is the same that running this query

SELECT *
    FROM test
   WHERE     t = 'X'
 AND id > 100
 ORDER BY nlssort(t,'NLS_SORT=XGerman'), id
/

But if you want to avoid the sort order by , then do this

SQL>  create index ix_test2 on test ( id , nlssort(t,'NLS_SORT=XGerman') ) ;

Index created.

SQL> SELECT *
    FROM test
   WHERE     t = 'X'
 AND id > 100
 ORDER BY t, id  2    3    4    5  ;


Execution Plan
----------------------------------------------------------
Plan hash value: 3173568990

----------------------------------------------------------------------------------------
| Id  | Operation                   | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |          |     2 |    18 |     2   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS BY INDEX ROWID| TEST     |     2 |    18 |     2   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | IX_TEST2 |     4 |       |     1   (0)| 00:00:01 |
----------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("T"='X')
   2 - access("ID">100 AND "ID" IS NOT NULL)
user_137555380
Related