Specify Optimization Hints in Queries
By default, the InterSystems SQL query optimizer uses sophisticated and flexible algorithms to optimize the performance of complex queries involving multiple indexes. In nearly all cases, these defaults provide optimal performance. However, InterSystems SQL provides hints that can be used to manually modify the execution plan. You will most typically use these hints at the instruction of the InterSystems Worldwide Resource Center (WRC) to configure an optimal query execution plan. To get help from the WRC with SQL performance, refer to Generate Report.
There are two ways to provide such hints to the query optimizer to force it to employ certain optimizations or avoid others. The first is employing keywords in a FROM clause of a SELECT statement; the second is specifying comment options.
FROM Clause Keywords
SELECT statements can make use of the FROM clause, to which you may supply a keyword that will specify certain query optimization behaviors. Multiple keywords may be provided in any order, separated by blank spaces.
%ALLINDEX
This optional keyword specifies that all indexes that provide any benefit are used for the first table in the query join order. This keyword should only be used when there are multiple defined indexes. The optimizer default is to use only those indexes that the optimizer judges to be most beneficial. By default, this includes all efficient equality indexes, and selected indexes of other types. %ALLINDEX uses all possibly beneficial indexes of all types. Testing all indexes has a larger overhead, but under some circumstances it may provide better performance than the default optimization. This option is especially helpful when using multiple range condition indexes and inefficient equality condition indexes. In these circumstances, accurate index selectivity may not be available to the query optimizer. %ALLINDEX can be used with %IGNOREINDEX to include/exclude specific indexes. Generally, %ALLINDEX should not be used with a TOP clause query.
You can use %STARTTABLE with %ALLINDEX to specify which table the %ALLINDEX applies to.
You can specify exceptions to %ALLINDEX for specific conditions with the %NOINDEX condition-level hint. The %NOINDEX hint is placed in front of each query selection condition for which no index should be used. For example, WHERE %NOINDEX hiredate < ?. This is most commonly used when the overwhelming majority of the data is not excluded by the condition. With a less-than (<) or greater-than (>) condition, use of the %NOINDEX condition-level hint is often beneficial. With an equality condition, use of the %NOINDEX condition-level hint provides no benefit. With a join condition, %NOINDEX is supported for ON clause joins. For further details, refer to Using Indexes in Query Processing.
%FIRSTTABLE
%FIRSTTABLE tablename
This optional keyword specifies that the query optimizer should start to performs joins with the specified tablename. The tablename names a table that is specified later in the join sequence. The join order for the remaining tables is left to the query optimizer. This hint is functionally identical to %STARTTABLE, but provides you with the flexibility to specify the join table sequence in any order.
The tablename must be a simple identifier, either a table alias or an unqualified table name. A qualified table name (schema.table) cannot be used. If the query specifies a table alias, the table alias must be used as tablename. For example:
FROM %FIRSTTABLE P Sample.Employee AS E JOIN Sample.Person AS P ON E.Name = P.Name
%FIRSTTABLE and %STARTTABLE both enable you to specify the initial table to use for join operations. %INORDER enables you to specify the order of all tables used for join operations. These three keywords are mutually exclusive; specify one and one only. If these keywords are not used the query optimizer performs joins on tables in the sequence it considers optimal, regardless of the sequence in which the tables are listed.
You cannot use %FIRSTTABLE or %STARTTABLE to begin the join order with the right-hand side of a LEFT OUTER JOIN (or the left-hand side of a RIGHT OUTER JOIN). Attempting to do so results in an SQLCODE -34 error: “Optimizer failed to find a usable join order”.
For further details, refer to the %STARTTABLE query optimization option.
%FULL
This optional keyword specifies that the compiler optimizer examines all alternative join sequences to maximize access performance. For example, when creating a stored procedure, the increased compile time may be worthwhile to provide for more optimized access. The default optimization is to not examine less likely join sequences when there are many tables in the FROM clause. %FULL overrides this default behavior.
You might specify both the %INORDER and the %FULL keywords when the FROM clause includes tables accessed with arrow syntax, which lead to tables whose order is unconstrained.
%IGNOREINDEX
This optional keyword specifies that the query optimizer ignore the specified index or list of indexes. (The deprecated synonym %IGNOREINDICES is supported for backwards compatibility.)
Following this keyword, you specify one or more index names. Multiple index names must be separated by commas. Indexes that are ignored can be specified in a few ways:
-
%IGNOREINDEX [[SchemaName.]TableName.]IndexName — Ignores a particular index in the table.
-
%IGNOREINDEX [[SchemaName.]TableName.]*— Ignores all indexes on the specified table in the specified schema. If SchemaName and TableName are omitted, ignores indexes on the tables in the current default schema on the table name specified in the FROM clause..
The following example query ignores all indexes that would otherwise be used in the query:
SELECT ID FROM %IGNOREINDEX * Opus.Fact WHERE Date > '2023-01-22'
By using this optimization constraint, you can cause the query optimizer to not use an index that is not optimal for a specific query. By specifying all index names but one, you can, in effect, force the query optimizer to use the remaining index.
You can also ignore a specific index for a specific condition expression by prefacing the condition with the %NOINDEX keyword. For further details, refer to Using Indexes in Query Processing.
%INORDER
This optional keyword specifies that the query optimizer performs joins in the order that the tables are listed in the FROM clause. This minimizes compile time. The join order of tables referenced with arrow syntax is unrestricted (for information on using arrow syntax, refer to Implicit Joins). Flattening of subqueries and index usage are unaffected.
%INORDER cannot be used with a CROSS JOIN or a RIGHT OUTER JOIN. If the table order specified is inconsistent with the requirements of an outer join, an SQLCODE -34 error is generated: “Optimizer failed to find a usable join order.” To avoid this, it is recommended that %INORDER, when used with outer joins, only be used with ANSI-style left outer joins or full outer joins.
%INORDER cannot be used when querying a sharded table. See Querying the Sharded Cluster.
Views and table subqueries are processed in the order that they are specified in the FROM clause.
-
Streamed View: %INORDER has no effect on the order of processing of tables within the view.
-
Merged View: %INORDER causes the view tables to be processed in the view’s FROM clause order, at the point of reference to the view.
Compare this keyword with %FIRSTTABLE and %STARTTABLE, both of which specify only the initial join table, rather than the full join order. See %STARTTABLE for a table of merge behaviors with different join order optimizations.
The %INORDER and %PARALLEL optimizations cannot be used together; if both are specified, %PARALLEL is ignored.
%NOFLATTEN
This optional keyword is specified in the FROM clause of a quantified subquery — a subquery that returns a boolean value. It specifies that the compiler optimizer should inhibit subquery flattening. This optimization option disables “flattening” (the default), which optimizes a query containing a quantified subquery by effectively integrating the subquery into the query: adding the tables of the subquery to the FROM clause of the query and converting conditions in the subquery to joins or restrictions in the query's WHERE clause.
The following are examples of quantified subqueries using %NOFLATTEN:
SELECT Name,Home_Zip FROM Sample.Person WHERE Home_Zip IN
(SELECT Office_Zip FROM %NOFLATTEN Sample.Employee)
SELECT Name,(SELECT Name FROM Sample.Company WHERE EXISTS
(SELECT * FROM %NOFLATTEN Sample.Company WHERE Revenue > 500000000))
FROM Sample.Person
The %INORDER and %STARTTABLE optimizations implicitly specify %NOFLATTEN.
%NOMERGE
This optional keyword is specified in the FROM clause of a subquery. It specifies that the compiler optimizer should inhibit the conversion of a subquery to a view. This optimization option disables the optimizing of a query containing a subquery by adding the subquery to the FROM clause of the query as an in-line view; comparisons from the subquery to fields of the query are moved to the query's WHERE clause as joins.
%NOREDUCE
This optional keyword is specified in the FROM clause of a streamed subquery — a subquery that returns a result set of rows, a subquery in the enclosing query’s FROM clause. It specifies that the compiler optimizer should inhibit the merging of the subquery (or view) into the containing query.
In the following example, the query optimizer would normally “reduce” this query by performing a Cartesian product join of Sample.Person with the subquery. The %NOREDUCE optimization option prevents this. InterSystems IRIS instead builds a temporary index on gname and performs the join on this temporary index:
SELECT * FROM Sample.Person AS p,
(SELECT Name||'goo' AS gname FROM %NOREDUCE Sample.Employee) AS e
WHERE p.name||'goo' = e.gname
%NOSVSO
This optional keyword is specified in the FROM clause of a quantified subquery — a subquery that returns a boolean value. It specifies that the compiler optimizer should inhibit Set-Valued Subquery Optimization (SVSO).
In most cases, Set-Valued Subquery Optimization improves the performance of [NOT] EXISTS and [NOT] IN subqueries, especially with subqueries with only one, separable correlating condition. It does this by populating a temporary index with the data values that fulfill the condition. Rather than repeatedly executing the subquery, InterSystems IRIS looks up these values in the temporary index. For example, SVSO optimizes NOT EXISTS (SELECT P.num FROM Products P WHERE S.num=P.num AND P.color='Pink') by creating a temporary index for P.num.
SVSO optimizes subqueries where the ALL or ANY keyword is used with a relative operator ( >, >=, <, or <=) and a subquery, such as ...WHERE S.num > ALL (SELECT P.num ...). It does this by replacing the subquery expression sqbExpr (P.num in this example) with MIN(sqbExpr) or MAX(sqbExpr), as appropriate. This supports fast computation when there is an index on sqbExpr.
The %INORDER and %STARTTABLE optimizations do not inhibit Set-Valued Subquery Optimization.
%NOTOPOPT
%NOUNIONOROPT
This optional keyword is specified in the FROM clause of a query or subquery. It disables the automatic optimizations provided for multiple OR conditions and for subqueries against a UNION query expression. These automatic optimizations transform multiple OR conditions to UNION subqueries, or UNION subqueries to OR conditions, where deemed appropriate. These UNION/OR transformations allow EXISTS and other low-level predicates to migrate to top-level conditions where they are available to InterSystems IRIS query optimizer indexing. These default transformations are desirable in most situations.
However, in some situations these UNION/OR transformations impose a significant overhead burden. %NOUNIONOROPT disables these automatic UNION/OR transformations for all conditions in the WHERE clause associated with this FROM clause. Thus, in a complex query, you can disable these automatic UNION/OR optimizations for one subquery while allowing them in other subqueries.
The UNION %PARALLEL keyword disables automatic UNION-to-OR optimizations.
The %INORDER and %STARTTABLE optimizations inhibit OR-to-UNION optimizations. The %INORDER and %STARTTABLE optimizations do not inhibit UNION-to-OR optimizations.
%PARALLEL
This optional keyword is specified in the FROM clause of a query. It suggests that InterSystems IRIS perform parallel processing of the query, using multiple processors (if applicable). This can significantly improve performance of some queries that uses one or more COUNT, SUM, AVG, MAX, or MIN aggregate functions, and/or a GROUP BY clause, as well as many other types of queries. These are commonly queries that process a large quantity of data and return a small result set. For example, SELECT AVG(SaleAmt) FROM %PARALLEL User.AllSales GROUP BY Region would likely use parallel processing.
A query that specifies both individual fields and an aggregate function and does not include a GROUP BY clause cannot perform parallel processing. For example, SELECT Name,AVG(Age) FROM %PARALLEL Sample.Person does not perform parallel processing, but SELECT Name,AVG(Age) FROM %PARALLEL Sample.Person GROUP BY Home_State does perform parallel processing.
%PARALLEL is intended for SELECT queries and their subqueries. An INSERT command subquery cannot use %PARALLEL.
Specifying %PARALLEL may degrade performance for some queries. Running a query with %PARALLEL on a system with multiple concurrent users may result in degraded overall performance.
A query that specifies %PARALLEL must be run in a database that is read/write, not readonly. Otherwise, a <PROTECT> error may occur.
Regardless of the presence of the %PARALLEL keyword in the FROM clause, some queries may use linear processing, not parallel processing. In particular, on columnar tables, queries that use DISTINCT, ORDER BY, TOP, or UNION will not perform parallel processing. In other cases queries may be found to not benefit from parallel processing, when optimized. You can determine if and how InterSystems IRIS has partitioned a query for parallel processing using Show Plan. To determine the number of processors on the current system use the %SYSTEM.Util.NumberOfCPUs()Opens in a new tab method.
For further details, refer to Parallel Query Processing.
%STARTTABLE
This optional keyword specifies that the query optimizer should start to performs joins with the first table listed in the FROM clause. The join order for the remaining tables is left to the query optimizer. Compare this keyword with %INORDER, which specifies the complete join order.
%STARTTABLE cannot be used with a CROSS JOIN or a RIGHT OUTER JOIN. You cannot use %STARTTABLE (or %FIRSTTABLE) to begin the join order with the right-hand side of a LEFT OUTER JOIN (or the left-hand side of a RIGHT OUTER JOIN). If the start table specified is inconsistent with the requirements of an outer join, an SQLCODE -34 error is generated: “Optimizer failed to find a usable join order.” To avoid this, it is recommended that %STARTTABLE, when used with outer joins, only be used with ANSI-style left outer joins or full outer joins.
The following table shows the merge behavior when combining a superquery parent and an in-line view with %INORDER and %STARTTABLE optimizations:
Superquery with no join optimizer | Superquery with %STARTTABLE | Superquery with %INORDER | |
View with no join optimizer | merge view if possible |
If the view is the superquery start: don't merge. Otherwise, merge view if possible. |
merge if possible; view's underlying tables are unordered. |
View with %STARTTABLE | don't merge |
If the view is the superquery start: merge, if possible. View's start table becomes superquery's start table. Otherwise, don’t merge. |
don't merge |
View with %INORDER | don't merge | don't merge |
If the view is not controlled by the %INORDER: don't merge. Otherwise, merge view if possible; view's order becomes substituted into superquery join order. |
The %FIRSTTABLE hint is functionally identical to %STARTTABLE, but provides you with the flexibility to specify the join table sequence in any order.
Comment Options
You can specify one or more comment options to the Query Optimizer within a SELECT, INSERT, UPDATE, DELETE, or TRUNCATE TABLE command. A comment option specifies a option that the query optimizer uses during the compile of the SQL query. Often a comment option is used to override a system-wide configuration default for a specific query.
Syntax
The syntax /*#OPTIONS */, with no space between the /* and the #, specifies a comment option. A comment option is not a comment; it specifies a value to the query optimizer. A comment option is specified using JSON syntax, commonly a key:value pair such as the following: /*#OPTIONS {"optionName":value} */. More complex JSON syntax, such as nested values, is supported.
A comment option is not a comment; it may not contain any text other than JSON syntax. Including non-JSON text within the /* ... */ delimiters results in an SQLCODE -153 error. InterSystems SQL does not validate the contents of the JSON string.
The #OPTIONS keyword must be specified in uppercase letters. No spaces should be used within the curly brace JSON syntax. If the SQL code is enclosed with quote marks, such as a Dynamic SQL statement, quote marks in the JSON syntax should be doubled. For example: myquery="SELECT Name FROM Sample.MyTest /*#OPTIONS {""optName"":""optValue""} */".
You can specify a /*#OPTIONS */ comment option anywhere in SQL code where a comment can be specified. In displayed statement text, the comment options are always shown as comments at the end of the statement text.
You can specify /*#OPTIONS */ comment options in SQL code. They are shown in returned Statement Text in the order specified. If multiple comment options are specified for the same option, the last-specified option value is used.
The following comment options are documented:
-
/*#OPTIONS {"NoTempFile":1} */: By default, a module performs processing and populates an internal temp-file (internal temporary table) with its results. You can force the query optimizer to create a query plan that does not generate internal temp-files by specifying 1 in the NoTempFile comment option.
Cosharding Comment Option
If an SQL query specifies multiple sharded tables, the SQL preprocessor generates a Cosharding comment option, which it appends to the end of the cached query text. This Cosharding option shows whether or not the specified tables are cosharded.
The Cosharding option is applied automatically. Users should not manually supply this option.
In the following example, all three specified tables are cosharded:
/*#OPTIONS {"Cosharding":[["T1","T2","T3"]]} */
In the following example, none of the three specified tables are cosharded:
/*#OPTIONS {"Cosharding":[["T1"],["T2"],["T3"]]} */
In the following example, table T1 is not cosharded, but tables T2 and T3 are cosharded:
/*#OPTIONS {"Cosharding":[["T1"],["T2","T3"]]} */
DynamicSQLTypeList Comment Option
If an SQL query contains literal values, the SQL preprocessor generates a DynamicSQLTypeList comment option, which it appends to the end of the cached query text. This comment option assigns a data type to each literal. Data types are listed in the order that the literals appear in the query. Only actual literals are listed, not input host variables or ? input parameters. The following is a typical example:
SELECT TOP 2 Name,Age FROM Sample.MyTest WHERE Name %STARTSWITH 'B' AND Age > 21.5
generates the cached query text:
SELECT TOP ? Name , Age FROM Sample . MyTest WHERE Name %STARTSWITH ? AND Age > ? /*#OPTIONS {"DynamicSQLTypeList":"10,1,11"} */
In this example, the literal 2 is listed as type 10 (integer), the literal “B” is listed as type 1 (string), and the literal 21.5 is listed as type 11 (numeric).
Note that the data type assignment is based solely on the literal value itself, not the data type of the associated field. For instance, in the above example Age is defined as data type integer, but the literal value 21.5 is listed as numeric. Because InterSystems IRIS converts numbers to canonical form, a literal value of 21.0 would be listed as integer, not numeric.
DynamicSQLTypeList returns the following data type values:
Data Type Value | Meaning |
---|---|
1 | String of length 1 to 32 (inclusive) |
2 | String of length 33 to 128 (inclusive) |
3 | String of length 129 to 512 (inclusive) |
4 | String of length > 512 |
10 | Integer |
11 | Numeric |
Because the DynamicSQLTypeList comment option is part of the query text, changing a literal so that it results in a different data type results in creating a separate cached query. For example, increasing or decreasing the length of a literal string so that it falls into a different range.
Display
The /*#OPTIONS */ comment options display at the end of the SQL statement text, regardless of where they were specified in the SQL command. Some displayed /*#OPTIONS */ comment options are not specified in the SQL command, but are generated by the compiler pre-processor.
The /*#OPTIONS */ comment options display in the Show Plan Statement Text, in the Cached Query Query Text, and in the SQL Statement Statement Text.
A separate cached query is created for queries that differ only in the /*#OPTIONS */ comment options.