2

I am reverse-engineering the schema of an Oracle database. I have a table and I created a function-based index on it.

DROP TABLE Fbi_tab;
CREATE TABLE Fbi_tab (
  a INTEGER, 
  b INTEGER, 
  c INTEGER
);

CREATE INDEX Idx ON Fbi_tab (a+b*(c-1), a, b);

How can I generate the DDL used to create this index?

3

You can use DBMS_METADATA package.

The DBMS_METADATA package provides a way for you to retrieve metadata from the database dictionary as XML or creation DDL and to submit the XML to re-create the object.

For details: Oracle Documentation
DBMS_METADATA

SQL> create table fbi_tab(
  2  a number,
  3  b number,
  4  c number
  5  );

Table created.

SQL> create index idx on fbi_tab(a+b*(c-1),a,b);

Index created.


SQL> select dbms_metadata.get_ddl('INDEX','IDX','HR') from dual;

DBMS_METADATA.GET_DDL('INDEX','IDX','HR')
--------------------------------------------------------------------------------

  CREATE INDEX "HR"."IDX" ON "HR"."FBI_TAB" ("A"+"B"*("C"-1), "A", "B")
  PCTF


SQL> 
2

If you don't want to use dbms_metadata, you can collect information about indexes from all_indexes, join it with all_ind_columns and finally join with all_ind_expressions view to get column expression. It may be more difficult, but allows you build statement in your own format.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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