Getting wrong/misleading info related to oracle table index

Question

1. Creating a table as follows:

CREATE TABLE "HOTEL3" 
   (    "NAMEX" VARCHAR2(4000 CHAR), 
    "CITY" VARCHAR2(4000 CHAR), 
    "PRICE" NUMBER, 
    "ID" VARCHAR2(50 BYTE) DEFAULT sys_guid()
   );

2. Creating an index as follows:

CREATE INDEX "IDX_HOTEL3_CITY_NAME" ON "HOTEL3" ("CITY" ASC, "NAMEX" DESC);   

3. Querying the index information:

SELECT *
FROM   all_ind_columns ic
WHERE  ic.index_owner = 'FOO'
    AND ic.table_name = 'HOTEL3'
ORDER  BY ic.column_position;

I am getting the below result (csv)

"INDEX_OWNER","INDEX_NAME","TABLE_OWNER","TABLE_NAME","COLUMN_NAME","COLUMN_POSITION","COLUMN_LENGTH","CHAR_LENGTH","DESCEND","COLLATED_COLUMN_ID"
"FOO","IDX_HOTEL3_CITY_NAME","FOO","HOTEL3","CITY",1,4000,4000,"ASC",
"FOO","IDX_HOTEL3_CITY_NAME","FOO","HOTEL3","SYS_NC00005$",2,2000,0,"DESC",

Observe the second record; COLUMN_NAME is SYS_NC00005$ instead of NAMEX.

Here are a few side notes. After step #1 if you query all_tab_cols you get all 4 columns. However, after step #2, all_tab_cols now has an additional entry - the same name as shown above query result. This could be far-fetched; but the ID field having DEFAULT set may be why, after step #2, all_tab_cols has that strange entry.

So what query can I issue to get the correct index information?

PS: oracle server is 19c I guess.

Answer 1

When you defined NAMEX as DESC, Oracle creates a function-based index using SYS_OP_DESCEND on an internally hidden RAW column that you can see in all_tab_cols (not all_tab_columns). The index is on this RAW column which has a system-generated name. all_ind_expressions will show you the expression that will be passed into these functions, indicating the column name.

Any time you have any kind of function-based index you have to go to *_ind_expressions to get the actual definition and swap out the system-named column with this expression to represent what you expect.

Now this cannot be done in SQL because that would require working with LONGs, which SQL can do very little with. There is a workaround, however using PL/SQL:

CREATE OR REPLACE TYPE all_ind_expressions_rectype IS OBJECT (
COLUMN_EXPRESSION   VARCHAR2(4000),
COLUMN_POSITION NUMBER
)
/
CREATE OR REPLACE TYPE all_ind_expressions_tabtype IS TABLE OF all_ind_expressions_rectype
/
CREATE OR REPLACE FUNCTION f_all_ind_expressions_ext(in_index_owner IN varchar2, in_index_name IN varchar2)
  RETURN all_ind_expressions_tabtype PIPELINED
AS
BEGIN
  FOR rec_expr IN (SELECT *
                     FROM all_ind_expressions
                    WHERE index_owner = in_index_owner
                      AND index_name = in_index_name)
  LOOP
    DECLARE
      var_column_expression varchar2(4000);
    BEGIN
      var_column_expression := rec_expr.column_expression;
      PIPE ROW(all_ind_expressions_rectype(var_column_expression,rec_expr.column_position));
    END;
  END LOOP;
  
  RETURN;
END;  
/
CREATE OR REPLACE VIEW all_ind_expressions_ext
AS
SELECT i.owner index_owner,
       i.index_name,
       i.table_owner,
       i.table_name,
       x.*
  FROM all_indexes i
       CROSS APPLY TABLE(f_all_ind_expressions_ext(i.owner,i.index_name)) x
 ORDER BY table_owner,table_name,index_owner,index_name,column_position       
/

Now you may use all_ind_expressions_ext in your SQL and use the expression in any function you'd like (CASE, REPLACE, WHERE clause, LISTAGG, anything).

Answer 2

That is how an index on a column defined with descending order is defined.

You can find the actual expression in the COLUMN_EXPRESSION column of the ALL_IND_EXPRESSIONS view.