Sunday, November 9, 2008

OBIEE Creating indexes Q&D Style

If you are the Aggregate Persistence Wizard to create your aggregate tables, you don't get any indexes. Here is a small (ORACLE) script to create them on the fly Q&D style:


BEGIN
FOR i IN (SELECT 'CREATE INDEX IDX_AGGR' SUBSTR ('0000'
TO_CHAR (ROWNUM), -5) ' ON AGGR.' table_name ' (' column_name ') NOLOGGING NOPARALLEL' AS sql_str
FROM all_tab_cols
WHERE owner = 'AGGR'
AND (table_name LIKE 'AG%' OR table_name LIKE 'SA%'))
LOOP
execute immediate (i.sql_str);
END LOOP;
END;

Till Next Time

No comments: