The descending index is essentially FBI, and the specific definition can be queried by user_ind_expressions or dba_ind_expressions. Then the same, the descending index only can be used under CBO. Connected to Oracle9i Enterprise Edition Release 9.2.0.4.0 Connected As Eygle
SQL> CREATE TABLE T AS SELECT * FROM DBA_USERS;
Table created
SQL> CREATE INDEX IDX_USERNAME_DESC ON T (Username DESC);
Index created
SQL> SELECT INDEX_NAME, TABLE_NAME, INDEX_TYPE AER_INDEXES where Table_Name = 'T';
Index_name table_name index_type ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ --------------------------------------- idx_username_desc t function-based Normal
SQL> Select Column_Name, Column_Position, Descend from User_ind_columns 2 Where Table_name = 'T';
COLUMN_NAME COLUMN_POSITION DESCEND -------------------------------------------------------------------------------------------------------------------------------------------------------------------- ----- SYS_NC00013 $ 1 DESC
SQL>
SQL> Select * from user_ind_expressions where Table_name = 't';
Index_name table_name column_expression color column_position ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ------------------------------------------------ --------- idx_username_desc T "Username" 1
