Index Awareness is the ability to take advantage of the indexes on key columns to speed up data retrieval. An index-aware prompt takes advantage of indexes on key columns in tables when accessing lists of values. You can define the index-aware prompt in IDT parameter section so that when running the query, you can see and select the user-friendly name for the object. When retrieving values, the query uses the key column for better performance.

Index-aware prompts will be listed under parameter section in the filter editor panel and you can add the index prompt to get the faster results for your prompt.

Before creating index-aware prompt, you need to create custom SQL list of values in data foundation or the business layer. If the lists of values (LOV) are not created as custom SQL then you cannot create Index-aware prompt in Parameter section and the index-aware option will be grayed out.

First create custom SQL list of values. Go to data foundation / business Layer – Parameters and list of values and click add list of values and then select “List of values based on custom SQL”.

Right side panel, enter the List of values name and description and then click “Edit SQL”.

To avail index-aware in the prompt, include both the key column and name column in the SELECT statement,

for example: SELECT ACCT_DIMIMENSION.A_ID,ACCT_DIMIMENSION.A_NUM FROM ACCT_DIMIMENSION

Click OK. In the list of values properties section under key column, select the ID for Account Name row. This is your index-aware column to retrieve the values faster. Once you selected the ID column in Key section and click hidden for ID.

Now list of values object created and need to create the parameter.

Add parameter name, description, and then select the “Prompt to users” and then enter the prompt comment which will be displayed in the prompt while refreshing the report.

Click “Associated list of values” highlighted as below and it will open the pop up box to show available list of values.

Select the respective list of values and click Ok.

Once the custom SQL List of values selected, Index-aware prompt will be enabled and you can select it.

Now your index-aware prompt is ready and you case use in your report and it will display under parameters section as shown below.

Please use the use this index-aware prompt in your project to resolve performance issue of the LOV and feel free to post your comments. I am always welcomes your comments and ready to answer the question.