Optimizing SQL statements

Create an external directive to optimize an SQL statement by using the HCL OpenAdmin Tool (OAT) for Informix®.

Prerequisites:
  • In the database server onconfig file, set the EXT_DIRECTIVES configuration parameter to 1 or 2 so that the external directives can be saved. After you change the EXT_DIRECTIVES configuration parameter, restart the database server for the change to take effect.
  • Ensure that SQL tracing is turned on and that the Table Names option is selected on the Tracing Admin page. See Turning on SQL tracing and Specifying options for SQL tracing.

You can create an external directive to apply to an SQL statement. Directives are comments that tell the query optimizer how to execute a query. External directives provide a way for you to change the query execution plan of an SQL statement. After you apply external directives to an SQL statement, the query execution plan of the SQL statement changes accordingly every time you rerun the SQL statement.

Important: When you rerun an SQL statement, it must exactly match the SQL statement that you applied the directives to. For example, it must have the same letter capitalization and the same spacing.

To create an external directive:

  1. On the OAT menu, click Performance Analysis > SQL Explorer.
  2. Drill down to the SQL statement.
    1. Click the SQL tab.
    2. Click the SQL statement in the list.
    3. Click any row in the list of SQL statement executions.
  3. Click the Optimize tab.
  4. Click Add.
  5. On the Add Directives page, select the directive. Select the tables and indexes, if required. To add more directives for the SQL statement, click Add Another, or click Add to close the page.
  6. Specify how to apply the directives and click Apply. The default is Inactive: Do not apply the directive to the SQL statement.
Parent topic: How do I explore performance information?
Copyright© 2018 HCL Technologies Limited