Intelligence Options (Index Generation)
To set intelligence options
-
Click
.
- Select Optimize SQL | Index Generation | Intelligence.
-
Select whether to use custom or predefined intelligence levels. If you select predefined, the levels range as follows:
Index Option
Option Section
Top percentage rows from table = 5
Minimum number of rows = 300
Maximum number of rows = 600
Maximum number of columns in a composite index = 2
Maximum number of indexes to generate per alternative = 2
Quota Section
Index Generation Quota = 20
Index Set Generation Quota = 30
Index Option
Option Section
Top percentage rows from table = 10
Minimum number of rows = 500
Maximum number of rows = 1000
Maximum number of columns in a composite index = 4
Maximum number of indexes to generate per alternative = 4
Quota Section
Index Generation Quota = 50
Index Set Generation Quota = 100
Index Option
Option Section
Top percentage rows from table = 15
Minimum number of rows = 800
Maximum number of rows = 1600
Maximum number of columns in a composite index = 5
Maximum number of indexes to generate per alternative = 5
Quota Section
Index Generation Quota = 80
Index Set Generation Quota = 160
Index Option
Option Section
Top percentage rows from table = 20
Minimum number of rows = 1500
Maximum number of rows = 3000
Maximum number of columns in a composite index = 6
Maximum number of indexes to generate per alternative = 6
Quota Section
Index Generation Quota = 200
Index Set Generation Quota = 400
Index Option
Option Section
Top percentage rows from table = 25
Minimum number of rows = 4000
Maximum number of rows = 8000
Maximum number of columns in a composite index = 8
Maximum number of indexes to generate per alternative = 8
Quota Section
Index Generation Quota = 500
Index Set Generation Quota = 1000
Intelligence Level 1
Test Run
Test Run Options
Note: This topic focuses on information that may be unfamiliar to you. It does not include all step and field descriptions.
To set test run settings options
- Click
-
Select Optimize SQL | Test Run | Test Run Settings
-
Review the following for additional information:
Test Run Settings
Description
Custom
Enables you to customize the settings.
Predefined
Uses the predefined set of options.
Execution Method Options
Note: This topic focuses on information that may be unfamiliar to you. It does not include all step and field descriptions.
To set execution method options
- Click
-
Select Optimize SQL | Test Run | Execution Method
-
Review the following for additional information:
Execution Method Description Maximum rows to be retrieved (records)
Select this checkbox and then enter the maximum number of rows to retrieve for a test run of all records. Run Time Retrieval Method Description Run SQL options
Select one of the following options:
- Run all SQL twice if original SQL runs faster than (seconds)—Combines the Original SQL twice and all others once and the All SQL twice options into one option and allows you to determine (by the number of seconds a SQL statement runs) which option to use. The original SQL statement always runs twice. The SQL alternatives run twice if the original SQL statement runs in less time than the value specified. Otherwise, the SQL alternatives all run once.
- Original SQL twice and all others once—Caches data from a table into memory the first time you access it. The next time you access that data, it is already in memory so the following SQL statements run faster. To provide an accurate comparison, the first SQL statement runs twice but only the time from the second run is compared to the times for the other statements.
- All SQL twice—Executes all SQL statements twice to eliminate factors that can affect the accuracy of the results. If you recently executed a SQL statement, the information for that statement may be cached and the statement may execute faster. This option eliminates time variation caused by caching since it runs all SQL statements twice but only uses the second run time for comparison.
- All SQL once—Executes all SQL statements once. For long running SQL, you do not need to run any statement twice since the effect from caching diminishes over time.