desc index costing in sort

set termout on echo on feed on
drop table emp purge;

create table emp as select * from hr.employees;
create index i5 on emp(last_name,to_char(hire_date,'yyyy') desc);

exec dbms_stats.gather_table_stats(null, 'EMP', method_opt=>'for all columns size 1');
exec dbms_stats.gather_table_stats(null, 'EMP', method_opt=>'for all hidden columns size 1');

alter session set statistics_level = all;

set serveroutput off linesize 200 pagesize 9999
select value from v$diag_info where name = 'Default Trace File';

set feedback only
select last_name, to_char(hire_date,'yyyy') from emp order by last_name, to_char(hire_date,'yyyy') desc;
set feedback on
select * from table(dbms_xplan.display_cursor(null,null,'allstats last +cost +projection'));

set feedback only
alter session set events '10053 trace name context forever, level 1';
select /*+ index(emp i5) */ last_name, to_char(hire_date,'yyyy') from emp order by 1, 2 desc;
alter session set events '10053 trace name context off';
set feedback on
select * from table(dbms_xplan.display_cursor(null,null,'allstats last +cost +projection'));

DOC
select last_name, to_char(hire_date,'yyyy') from emp order by last_name, to_char(hire_date,'yyyy') desc

Plan hash value: 3127847031

------------------------------------------------------------------------------------------------
| Id  | Operation        | Name | Starts | E-Rows | Cost (%CPU)| A-Rows |   A-Time   | Buffers |
------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT |      |      1 |        |     1 (100)|    107 |00:00:00.01 |       9 |
|   1 |  INDEX FULL SCAN | I5   |      1 |    107 |     1   (0)|    107 |00:00:00.01 |       9 |
------------------------------------------------------------------------------------------------

Column Projection Information (identified by operation id):
-----------------------------------------------------------

   1 - "LAST_NAME"[VARCHAR2,25], "EMP"."SYS_NC00012$"[RAW,7]


select /*+ index(emp i5) */ last_name, to_char(hire_date,'yyyy') from emp order by 1, 2 desc

Plan hash value: 3127847031

------------------------------------------------------------------------------------------------
| Id  | Operation        | Name | Starts | E-Rows | Cost (%CPU)| A-Rows |   A-Time   | Buffers |
------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT |      |      0 |        |    16 (100)|      0 |00:00:00.01 |       0 |
|   1 |  INDEX FULL SCAN | I5   |      1 |    107 |     1   (0)|    107 |00:00:00.01 |       9 |
------------------------------------------------------------------------------------------------

Column Projection Information (identified by operation id):
-----------------------------------------------------------

   1 - "EMP".ROWID[ROWID,10], "LAST_NAME"[VARCHAR2,25], "EMP"."SYS_NC00012$"[RAW,7]
#