2
  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()
   );
  1. Creating an index as follows:
CREATE INDEX "IDX_HOTEL3_CITY_NAME" ON "HOTEL3" ("CITY" ASC, "NAMEX" DESC);   
  1. 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.

2 Answers 2

2

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).

4
  • I think this answer need to be enhanced a little bit, though what I am asking further is not exactly in scope of this question. It may not be apparent to a novicer like me. Assume that all_ind_cols is labeled as ic and all_ind_expressions is labeled as ie. I cannot do a CASE WHEN IC.COLUMNAME LIKE 'SYS_NC%' THEN IE.COLUMN_EXPRESSION ELSE IC.COLUMN_NAME END. Getting an ORA-00932 error.
    – deostroll
    Aug 16, 2023 at 5:24
  • @deostroll, you can't work with LONGs like that. You'd have to create a pipelined function that uses PL/SQL to assign the COLUMN_EXPRESSION to a local varchar2(4000) in a record type and pipe it, then you can query the function and work with COLUMN_EXPRESSION as a normal varchar2. That's the frustrating reality of Oracle's historical use of LONGs in its dictionary. But once you get this technique down you can create wrapper functions around any of these dictionary objects.
    – Paul W
    Aug 16, 2023 at 12:18
  • @deostroll - I enhanced my answer with code to wrap the view and expose COLUMN_EXPRESSION as a varchar2.
    – Paul W
    Aug 16, 2023 at 12:25
  • Didn't know it was this complex. But thanks. 👍
    – deostroll
    Aug 16, 2023 at 12:58
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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.