create table t as
select * from all_objects;

create index t_idx on t(owner, object_type, object_name);

exec dbms_stats.gather_table_stats(user, 'T', method_opt=>'for all indexed columns');

select owner, object_type from t
where owner between 'T' and 'Z'
  and object_type is not null
order by owner desc, object_type desc;

Plan hash value: 2813023843
 
----------------------------------------------------------------------------
| Id  | Operation         | Name  | E-Rows |E-Bytes| Cost (%CPU)| E-Time   |
----------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |       |    981 | 17658 |    11  (10)| 00:00:01 |
|   1 |  SORT ORDER BY    |       |    981 | 17658 |    11  (10)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN| T_IDX |    981 | 17658 |    10   (0)| 00:00:01 |
----------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   2 - access("OWNER">='T' AND "OWNER"<='Z')
       filter("OBJECT_TYPE" IS NOT NULL)
 
Note
-----
   - Warning: basic plan statistics not available. These are only collected when:
       * hint 'gather_plan_statistics' is used for the statement or
       * parameter 'statistics_level' is set to 'ALL', at session or system level

select /*+ gather_plan_statistics */ owner, object_type from t
where owner between 'T' and 'Z'
  and object_type is not null
order by owner desc, object_type desc;

select /*+ gather_plan_statistics */ owner, object_type from t
where owner between 'S' and 'Z'
  and object_type is not null
order by owner desc, object_type desc;

Plan hash value: 3276893591
 
---------------------------------------------------------------------------------------
| Id  | Operation             | Name  | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |       | 40330 |   708K|       |   348   (3)| 00:00:02 |
|   1 |  SORT ORDER BY        |       | 40330 |   708K|  1120K|   348   (3)| 00:00:02 |
|*  2 |   INDEX FAST FULL SCAN| T_IDX | 40330 |   708K|       |   142   (3)| 00:00:01 |
---------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   2 - filter("OWNER">='S' AND "OWNER"<='Z' AND "OBJECT_TYPE" IS NOT NULL)

ALTER SESSION SET NLS_SORT = BINARY; -- NLS_COMP уже был в BINARY

Plan hash value: 2685572958
 
-------------------------------------------------------------------------------------
| Id  | Operation                   | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |       | 41624 |  1707K|   302   (1)| 00:00:02 |
|*  1 |  INDEX RANGE SCAN DESCENDING| T_IDX | 41624 |  1707K|   302   (1)| 00:00:02 |
-------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   1 - access("OWNER">='S' AND "OWNER"<='Z')
       filter("OBJECT_TYPE" IS NOT NULL)

select owner, object_type from t
where owner between 'S' and 'Z'
  and object_type is not null
order by NLSSORT(owner, 'NLS_SORT=BINARY') desc,
         NLSSORT(object_type, 'NLS_SORT=BINARY') desc;

create index func_t_idx on t(NLSSORT(owner, 'NLS_SORT=BINARY'), NLSSORT(object_type, 'NLS_SORT=BINARY'));