Creating indexes using a SmartGuide

Use the Index SmartGuide to determine which indexes to create for a given set of SQL statements. The SmartGuide will make recommendations based on the workload that you specify. If the workload does not accurately reflect actual statements executed against your database, the recommendations will not improve the performance of your database.

Since it takes a while for the SmartGuide to recommend the appropriate indexes, you have two options:

• You can have the SmartGuide calculate the indexes while you wait, or
• You can schedule it to run at a specific time, and then review the recommendations later. Use the Jobs page of the Journal notebook, and click the Job history push button to check the results.

After the SmartGuide recommends the indexes to create and drop and you verify the recommendations, you have three options:

• Save the script.
• Save the script and run it while you wait.
• Save the script and schedule it to run at a later time.

If you choose to run the script, now or later, the indexes will be created in the database shown on the Introduction page of the Index SmartGuide.

Authorities and privileges

To determine which indexes to create for a given set of SQL statements:

1. Open the Index SmartGuide.

2. On the Introduction page, ensure that you are linked to the database where you want the indexes to be created.

3. If no workloads exist, use the Workload page to define a new workload. If workloads do exist, use the Workload page to select an existing workload, or create a new one if you do not want to use any of the existing ones.

4. On the Limits page, set any space limitation.

5. On the Calculate page, set the maximum time to search for optimal indexes. Click Now or Later to indicate when you want the recommended indexes to be calculated.

   If you click Now:

   1. On the Recommendations page, verify the new indexes that you want to create. You can also give meaningful names to the indexes, at this point, if you do not want to use the generated names.

   2. On the Drop page, select existing indexes that you want to drop.

   3. On the Summary page, review the actions that will take place when you click Done.

   4. Click Done to have the system create a script that contains the selected indexes to be created and dropped.

   If you click Later:

   1. Click Done. The Schedule window opens. Enter the date and time when you want the indexes to be calculated.

   2. Click OK. A message box appears (with message number DBA1121) informing you of the job number. Note the job number, as it will be used later to retrieve the results.

   3. After the indexes have been calculated at the scheduled time, open the Jobs page of the Journal notebook to check the results. Click the Job History push button to view all the jobs that have completed. Then click mouse button 2 on the job number and select Show results from the pop-up menu. The Index SmartGuide opens on the Recommendations page with the list of recommended indexes.

   4. You can now complete the steps described under "If you click Now," above.

Related information

• Index SmartGuide -- Overview
• Schedule -- Overview
• Journal -- Overview
• Showing commands or SQL