Oracle BPM Flex Field Index Creation

Use the output from the following sql script, to create indexes for the WFTASK public and private flex fields. The script looks at the statistics of the WFTASK table to determine the required indexes. Replacing DEV_SOAINFRA, with the schema specified during the RCU creation step.

You can also modify the script to search for may be a bigger number of distinct rows, by changing the number '1' in the script.


set heading off
set echo off
EXEC DBMS_STATS.gather_table_stats('DEV_SOAINFRA','WFTASK');
select 'create index DEV_SOAINFRA.'||column_name||'_idx on DEV_SOAINFRA.'||table_name||' ('||column_name||');' from all_tab_columns where table_name = 'WFTASK'and num_distinct>1 and column_name like '%ATTRIBUTE%';

Output will be in the format:


create index DEV_SOAINFRA.PROTECTEDNUMBERATTRIBUTE1_bpmflex_idx on DEV_SOAINFRA.
WFTASK (PROTECTEDNUMBERATTRIBUTE1);

create index DEV_SOAINFRA.PROTECTEDTEXTATTRIBUTE13_bpmflex_idx on DEV_SOAINFRA.W
FTASK (PROTECTEDTEXTATTRIBUTE13);