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
because the index has nlssort(x) in it, NOT X. You selected X, X only resides in the table
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