Nested Queries

Nested queries, or subqueries, are SELECT statements "nested" within another SELECT statement. Nested queries are supported in a WHERE clause, an EXISTS clause within a WHERE clause, a FROM clause, a HAVING clause, and as a Select-item in the SQL SELECT item list. See Nested Queries in the SQL Reference for more details on nested query support.

The nested query is processed first, then the results are converted to literals and applied as criteria in the outer query.

• Nested queries can be used to avoid cartesian products. Cartesian products are caused when applying criteria to multiple child tables or when referencing two tables without joining them. Cartesian products inhibit query performance and can distort aggregation results. Using a nested query to qualify records in one of the tables resolves this problem.
• Nested queries can be used to segment an SQL statement. This is useful for an SQL statement that is very large and difficult to read. The statement can be broken into logical segments and still achieve the same results.
• Nested queries can be used to govern query performance. Re-wording a query to use nested queries can change the way a query is processed and optimized.

Omnidex may behave differently depending on the location of the nested query. The following examples use the Explain Plan to show exactly how Omidex optimized each query.

One Pass Optimization

SELECT * FROM orders
WHERE tax_state in (SELECT state FROM states
WHERE description='colorado')

The orders table is linked to the states table by tax_state=state in the Omnidex indexes. The inner query (SELECT state FROM states WHERE description='colorado'), qualifies a list of states then uses that qualified list as criteria in the outer query (SELECT * FROM orders WHERE state in...).

This query is completed in a single pass because an MDK index is installed on the description field of the states table, which is linked in the indexes to the tax_state table. The underlying database is not touched until a final qualified list of orders is complete.

Explain Plan

The SUMMARY section shows the SQL statement formatted to make it easier to read.
Version information is displayed, showing exactly which version of Omnidex is being used.
The optimization used for this query is shown. If the query could not be optimized, this would say NONE.

------------------------ SUMMARY --------------------------

The DETAILS section shows each step taken to process this query. Using the indexes, states were "qualified" by the given criteria. The qualified subset was then joined to the orders table using the tax_state field.

The database was not touched until all of the quallifications were complete, completing this query using one-pass optimization.

"on 1" indicates that this statement was processed on cursor 1. NOAUTORESET is a default option that instructs Omnidex to retain the qualified list for possible further manipulation.

------------------------ DETAILS --------------------------

Qualify (STATES)STATES where DESCRIPTION = 'Colorado' on 1 with NOAUTORESET
Join STATES using STATE to (ORDERS)ORDERS using TAX_STATE on 1 with NOAUTORESET
Fetchkeys $ROWID 1000 at a time on 1
Retrieve ORDERS using $ROWID = $ODXID
Return ORDERS.ACCT, ORDERS.PRODUCT_NO, ORDERS.ORDERS_DATE, ORDERS.STATUS, \
ORDERS.TAX_STATE, ORDERS.SOURCE, ORDERS.PMT_METHOD, ORDERS.DISCOUNT, \
ORDERS.QUANTITY, ORDERS.SALES_TAX, ORDERS.AMOUNT, ORDERS.TOTAL

Deferred Optimization

SELECT acct, company FROM prospects
WHERE acct in (SELECT EVERY 10 DISTINCT acct FROM orders
WHERE quantity > 1)

The prospects table is linked in the Omnidex indexes to the orders table by the common field acct. The inner query (SELECT EVERY 10 DISTINCT acct FROM orders WHERE
quantity > 1), qualifies every 10th distinct acct from the orders table that has a quantity greater than 1. This qualified list is then used as criteria in the outer query (SELECT acct, company FROM prospects WHERE acct in...).

Explain Plan

In addition to the formatted SQL statement, version information, and optimization used for this query, the SUMMARY section also displays "Notes" which explains that one-pass optimization could not be achieved in this statement because the inner query contained one of "TOP", "RANDOM", or "EVERY".

These functions are processed after the data has been retrieved from the database. Therefore, the inner query was processed as if "EVERY" had not been a part of the statement, the qualified orders were retrieved, EVERY was applied to the retrieve orders, and then the outer query was processed using the results from the inner query.

------------------------ SUMMARY --------------------------

The first part of the DETAILS section processes the inner query. Orders are qualified where QUANTITY > 1, then using an aggregation index

{SQ1} is a variable representing the results of the inner query. "on 2" indicates that this statement was processed on cursor 2. NOAUTORESET is a default option that instructs Omnidex to retain the qualified list for possible further manipulation.

------------------------ DETAILS --------------------------

Processing subquery 1 into {SQ1}
Qualify (ORDERS)ORDERS where QUANTITY > 1 on 2 with NOAUTORESET
Aggregate ORDERS using ACCT for GROUP(ACCT) on 2
Returning subquery 1 into {SQ1}

Retrieve ORDERS using $ROWID = $ODXID
Return ORDERS.ACCT, ORDERS.PRODUCT_NO, ORDERS.ORDERS_DATE, ORDERS.STATUS, \
ORDERS.TAX_STATE, ORDERS.SOURCE, ORDERS.PMT_METHOD, ORDERS.DISCOUNT, \
ORDERS.QUANTITY, ORDERS.SALES_TAX, ORDERS.AMOUNT, ORDERS.TOTAL

Top