This optional keyword specifies that the query optimizer ignore the specified index or list of indices. (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. You can specify an index name using either of the following formats:
%IGNOREINDEX [[schemaname.]tablename.]indexname [,...] %IGNOREINDEX [[schemaname.]tablename.]* [,...]
The schemaname and tablename are optional. If omitted, the current default schema and the table name specified as FROM table-ref are used. The asterisk (*) wildcard specifies all of the index names for the specified table. You can specify index names in any order. InterSystems SQL does not validate the index names you specify (or their schemaname and tablename); a nonexistent or duplicate index name is simply ignored.
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 Indices” in the “Optimizing Query Performance” chapter in the SQL Optimization Guide.
%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 in Using InterSystems SQL). 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 in the chapter “Horizontally Scaling InterSystems IRIS for Data Volume with Sharding” in the Scalability Guide.
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
This optional keyword is specified when using a TOP clause with an ORDER BY clause. By default, TOP with ORDER BY optimizes for fastest time-to-first-row. Specifying %NOTOPOPT (no TOP optimization) instead optimizes the query for fastest retrieval of the complete result set.
%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.
Note:
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: some queries do not support parallel processing; some queries, when optimized, may be found to not benefit from parallel processing. 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 in the “Optimizing Query Performance” chapter of the SQL Optimization Guide.
%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.
Table-Valued Functions in the FROM Clause
A table-valued function is a class query that is projected as a stored procedure and returns a single result set. A table-valued function is any class query which has SqlProc TRUE. A class query used as a table-valued function must be compiled in either LOGICAL or RUNTIME mode. When used as a table-valued function and compiled in RUNTIME mode, the table-valued function query will be called in LOGICAL mode.
A table-valued function follows the same naming conventions as a stored procedure name for a class query. Parameter parentheses are mandatory; the parentheses may be empty, enclose a literal or a host variable, or a comma-separated list of literals and host variables. If you specify no parameters (empty parentheses or the null string), the table-valued function returns all data rows.
To issue a query using a table-valued function, the user must hold the EXECUTE privilege on the stored procedure that defines the table-valued function. The user must also have SELECT privileges on the tables or views accessed by the table-valued function query.
In the following example, the class query Sample.Person.ByName is projected as a stored procedure and can thus be used as a table-valued function:
A table-valued function is a class query that is projected as
a stored procedure and returns a single result set. A table-valued
function is any class query which has SqlProc TRUE. A class query
used as a table-valued function must be compiled in either LOGICAL
or RUNTIME mode. When used as a table-valued function and compiled
in RUNTIME mode, the table-valued function query will be called in
LOGICAL mode.
A table-valued function follows the same naming conventions
as a stored procedure name for a class query. Parameter parentheses
are mandatory; the parentheses may be empty, enclose a literal or
a host variable, or a comma-separated list of literals and host variables.
If you specify no parameters (empty parentheses or the null string),
the table-valued function returns all data rows.
To issue a query using a table-valued function, the user must
hold the EXECUTE privilege on the stored procedure that defines the
table-valued function. The user must also have SELECT privileges on
the tables or views accessed by the table-valued function query.
In the following example, the class query Sample.Person.ByName
is projected as a stored procedure and can thus be used as a table-valued
function:
A table-valued function is a class query that is projected as a stored procedure and returns a single result set. A table-valued function is any class query which has SqlProc TRUE. A class query used as a table-valued function must be compiled in either LOGICAL or RUNTIME mode. When used as a table-valued function and compiled in RUNTIME mode, the table-valued function query will be called in LOGICAL mode.
A table-valued function follows the same naming conventions as a stored procedure name for a class query. Parameter parentheses are mandatory; the parentheses may be empty, enclose a literal or a host variable, or a comma-separated list of literals and host variables. If you specify no parameters (empty parentheses or the null string), the table-valued function returns all data rows.
To issue a query using a table-valued function, the user must hold the EXECUTE privilege on the stored procedure that defines the table-valued function. The user must also have SELECT privileges on the tables or views accessed by the table-valued function query.
In the following example, the class query Sample.Person.ByName is projected as a stored procedure and can thus be used as a table-valued function:
SELECT Name,DOB FROM Sample.SP_Sample_By_Name('A')
The following Dynamic SQL example specifies the same table-valued
function. It uses the %Execute() method to
supply parameter values to the ? input parameter:
SET myquery="SELECT Name,DOB FROM Sample.SP_Sample_By_Name(?)"
SET tStatement = ##class(%SQL.Statement).%New()
SET qStatus = tStatement.%Prepare(myquery)
IF qStatus'=1 {WRITE "%Prepare failed:" DO $System.Status.DisplayError(qStatus) QUIT}
SET rset = tStatement.%Execute("A")
DO rset.%Display()
WRITE !,"End of A data",!!
SET rset = tStatement.%Execute("B")
DO rset.%Display()
WRITE !,"End of B data"
A table-valued function can only be used in the FROM clause
of either a SELECT statement or a DECLARE statement. A table-valued function
name can be qualified with a schema name or unqualified (without a
schema name); an unqualified name uses the default schema. In a SELECT statement FROM clause, a table-valued function can
be used wherever a table name can be used. It can be used in a view
or a subquery, and can be joined to other table-ref items using a comma-separated list or explicit JOIN syntax.
A table-valued function cannot be directly used in an INSERT, UPDATE, or DELETE statement. You can, however, specify a subquery for these commands
that specifies a table-valued function.
InterSystems SQL does not define the EXTENTSIZE for a table-valued
function, or the SELECTIVITY for table-valued function columns.
Subqueries in the FROM Clause
You can specify a subquery in the FROM clause. This is known
as a streamed subquery. The subquery is treated the same as a table,
including its use in JOIN syntax and the optional assignment of an
alias using the AS keyword. A FROM clause can contain multiple tables,
views, and subqueries in any combination, subject to the restrictions
of the JOIN syntax, as described in JOIN.
A subquery is enclosed in parentheses. The following example
shows a subquery in a FROM clause:
SELECT name,region
FROM (SELECT t1.name,t1.state,t2.region
FROM Employees AS t1 LEFT OUTER JOIN Regions AS t2
ON t1.state=t2.state)
GROUP BY region
A subquery can specify a TOP clause. A subquery can contain an ORDER BY clause when paired with a TOP clause.
A subquery can use SELECT * syntax, subject
to the following restriction: because a FROM clause results in a value
expression, a subquery containing SELECT * must
yield only one column.
A join within a subquery cannot be a NATURAL join or take a
USING clause.
FROM Subqueries and %VID
When a FROM subquery is invoked, it returns a %VID for each
subquery row returned. A %VID is an integer counter field; its values
are system-assigned, unique, non-null, non-zero, and non-modifiable.
The %VID is only returned when explicitly specified. It is returned
as data type INTEGER. Because %VID values are sequential integers,
they are far more meaningful if the subquery returns ordered data;
a subquery can only use an ORDER BY clause when it is paired with
a TOP clause.
Because the %VID is a sequential integer, it can be used to
determine the ranking of items in a subquery with an ORDER BY clause.
In the following example, the 10 newest records are listed in Name
order, but their timestamp ranking is easily seen using the %VID values:
SELECT Name,%VID,TimeStamp FROM
(SELECT TOP 10 * FROM MyTable ORDER BY TimeStamp DESC)
ORDER BY Name
One common use of the %VID is to “window” the result
set, dividing execution into sequential subsets that fit the number
of lines available in a display window. For example, display 20 records,
then wait for the user to press Enter, then display the next 20 records.
The following example uses %VID to “window” the
results into subsets of 10 records:
SELECT %VID,* FROM
(SELECT TOP 60 Name, Age FROM Sample.Person WHERE Age > 55 ORDER BY Name)
WHERE %VID BETWEEN ? AND ?
For details on using %VID, refer to the Defining and Using Views chapter of Using InterSystems
SQL.
LATERAL Keyword
The LATERAL keyword can be used to have more explicit control over the FROM processing order by allowing views and table-valued functions to reference field values from tables listed earlier in the FROM clause. These lateral references affect the rows that the subquery or table valued-function generate.
Within the subquery or the table-valued function that the LATERAL keyword is specified on, the laterally referenced fields are treated as given values. Laterally referenced fields always come from earlier FROM items in the same FROM clause.
When used to precede a FROM subquery, the LATERAL keyword indicates that the subquery may reference fields in FROM items that semantically precede it in the query. These laterally referenced fields will be processed before the FROM subquery that LATERAL was specified on.
When used to precede a table-valued function, the LATERAL keyword indicates that fields from the previous FROM items can be used within the table-valued function. In this context, the keyword is optional, and the lateral join will be applied implicitly if such references are used within the table-valued function.
Optional FROM Clause
If no table data is referenced (directly or indirectly) by the SELECT item list, the FROM clause is optional. This kind
of SELECT may be used to return data from functions,
operator expressions, constants, or host variables. For a query that
references no table data:
-
If the FROM clause is omitted, a maximum of one row
of data is returned, regardless of the TOP keyword value; TOP 0 returns
no data. The DISTINCT clause is
ignored. No privileges are required.
-
If the FROM clause is specified, it must specify an
existing table in the current namespace. You must have SELECT privilege
for that table, even though the table is not referenced. The number
of identical rows of data returned is equal to the number of rows
in the specified table, unless you specify a TOP or DISTINCT clause,
or limit it with a WHERE or HAVING clause. Specifying a DISTINCT clause limits the output to a
single row of data. The TOP keyword limits the output to the number
of rows specified by the TOP value; TOP 0 returns no data.
With or without a FROM clause, subsequent clauses (WHERE, GROUP
BY, HAVING or ORDER BY) may be specified. A WHERE or HAVING clause
may be used to determine whether or not to return results, or how
many identical rows of results to return. These clauses may reference
a table, even if no FROM clause is specified. A GROUP BY or ORDER
BY clause may be specified, but these clauses are not meaningful.
The following are examples of SELECT statements
that reference no table data. Both examples return one row of information.
The following example omits the FROM clause. The DISTINCT keyword
is not needed, but may be specified. No SELECT clauses are permitted.
SELECT 3+4 AS Arith,
{fn NOW} AS NowDateTime,
{fn DAYNAME({fn NOW})} AS NowDayName,
UPPER('MixEd cASe EXPreSSioN') AS UpCase,
{fn PI} AS PiConstant
The following example includes a FROM clause. The DISTINCT keyword
is used to return a single row of data. The FROM clause table reference
must be a valid table. The ORDER BY clause is permitted here, but
is meaningless. Note that the ORDER BY clause must specify a valid
select item alias:
SELECT DISTINCT 3+4 AS Arith,
{fn NOW} AS NowDateTime,
{fn DAYNAME({fn NOW})} AS NowDayName,
UPPER('MixEd cASe EXPreSSioN') AS UpCase,
{fn PI} AS PiConstant
FROM Sample.Person
ORDER BY NowDateTime
The following examples both use a WHERE clause to determine
whether or not to return results. The first includes a FROM clause
and uses the DISTINCT keyword is to return a single row of data. The
second omits the FROM clause, and therefore returns at most a single
row of data. In both cases, the WHERE clause table reference must
be a valid table for which you have SELECT privilege:
SELECT DISTINCT
{fn NOW} AS DataOKDate
FROM Sample.Person
WHERE FOR SOME (Sample.Person)(Name %STARTSWITH 'A')
SELECT {fn NOW} AS DataOKDate
WHERE FOR SOME (Sample.Person)(Name %STARTSWITH 'A')
See Also
