Tom,

Can you explain what is the need for the "Table access by rowid" here? why dont the index act like skinny version of table here?

rajesh@ORA11G> create table t
  2  as
  3  select a.*, object_name x
  4  from all_objects a;

Table created.

rajesh@ORA11G>
rajesh@ORA11G> insert into t
  2  select a.*,
  3     decode(rownum,1,'Ziggy',2,'ZIGGY',3,'ziggy') x
  4  from all_objects a
  5  where rownum <=3;

3 rows created.

rajesh@ORA11G> commit;

Commit complete.

rajesh@ORA11G> create index t_ind on t( nlssort(x,'nls_sort=binary_ci'));

Index created.

rajesh@ORA11G> exec dbms_stats.gather_table_stats(user,'T');

PL/SQL procedure successfully completed.

rajesh@ORA11G>
rajesh@ORA11G> alter session set nls_sort='binary_ci';

Session altered.

rajesh@ORA11G> alter session set nls_comp='linguistic';

Session altered.

rajesh@ORA11G> set autotrace on explain
rajesh@ORA11G> select x from t
  2  where x = 'ziggy';

X
------------------------------
Ziggy
ZIGGY
ziggy

3 rows selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 1376202287

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

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

   2 - access(NLSSORT("X",'nls_sort=''BINARY_CI''')=HEXTORAW('7A6967677900')
              )

rajesh@ORA11G> set autotrace off

ops$tkyte%ORA11GR2> select x, nlssort( x, 'nls_sort=''BINARY_CI''')
  2    from (select 'ziggy' x from dual
  3          union all
  4          select 'ZIGGY' x from dual)
  5  /

X
-----
NLSSORT(X,'NLS_SORT=''BINARY_CI''')
-------------------------------------------------------------------------------
ziggy
7A6967677900

ZIGGY
7A6967677900