Re create user defined indexes

0
Hi. Is there a way (maybe a script) to recreate all indexes that you have set on the entities in the domain model? So the indexes starting with (if i'm not mistaken) ind.indexname LIKE 'IDX%' More specific, on a Oracle database. Thanks in advance
asked
1 answers
0

Best practice is to never create anything ad-hoc in the database, but use the scripts generated by mendix (m2ee).

In the absence of scripts something like the following might help:

SELECT 'CREATE INDEX ' || INDEX_NAME || ' ON ' 
        || TABLE_NAME || ' (' || COL_LIST  || ',NEWCOLUMNNAME)' 
FROM ( 
       Select index_name,table_name,
       LISTAGG(COLUMN_NAME || DECODE(DESCEND,'ASC','', ' ' || DESCEND), ',') 
       WITHIN GROUP (ORDER BY COLUMN_POSITION) AS COL_LIST 
       FROM SYS.ALL_IND_COLUMNS
       GROUP BY index_name,table_name
);

This example assumes you may have indexes on multiple columns, indexes on single columns can also be achieved by a simpler statement.

You can spool the output to a file in sqlplus, edit it if needed, and run it in sqlplus (or some more advanced new tool...).

(example comes from stackoverflow: How to alter all indexes on an Oracle schema in one script (10G))

answered