Using Nested Table Expressions (NTE)

The nested-table-expression (NTE) is part of the table-reference functionality in a query. A table-reference specifies an intermediate result table. If a nested-table-expression is specified, the result table is the result of the specified subselect.
datacom151
The 
nested-table-expression
 (NTE) is part of the
table-reference
functionality in a 
query. 
A table-reference specifies an intermediate result table. If a 
nested-table-expression
 is specified, the result table is the result of the specified
subselect
.
A
subselect
in parentheses is called a nested table expression. If you specify a 
nested-table-expression
, the result table is the result of that 
nested-table-expression
. At any time, the table consists of the rows that would result if the
sub
select
were executed.
 Syntax:
►►─── 
TABLE 
────(sub
select
) ──
correlation-clause
──────────────────────────────►◄    
You can specify
nested-table-expressions
in any FROM clause. Except an NTE cannot be used in the definition of a view, and an NTE can not reference a view.
Use NTEs in place of a view to avoid creating a view when general use of the view is not required.
The result of the NTE becomes a table that is used as any other table, except it is read-only.
Logically, an NTE produces an intermedia table, and often a temporary table is physically created in the TTM area.
NTEs can eliminate the need for the application to create temporary tables to accomplish an application task.
 
Performance Issues with NTEs
Considerable resources can be required to create an NTE. The result table can have many rows, and many input rows and sorts can be required. Therefore, when the NTE is an inner table to a nested-loop join, and the NTE is recomputed for each outer table row, the cost can be high.
By using a merge join, the NTE is read only once. A merge join may require sorting the NTE result table in join column sequence to perform the merge, but this is usually less expensive than recomputing the NTE many times.
 
Using Merge Join
MUF option
SQL_OPTIMIZATION_LEVEL 1
prevents a merge join from being used – except on the first join. However, with PTF SO04034, the SQL Optimizer is free to use a merge join for an NTE.
Sometimes the cost estimates used by the SQL Optimizer are inaccurate. You can override the Optimizer's choice in these cases using the SQL_OPTIMIZATION special register to instruct the Optimizer to use either nested-loop or merge join.
Example:
SELECT COUNT(*) FROM AVE2122 T1 INNER JOIN AVE2122 T2 ON T1.ROLE = T2.ROLE 
AND DATACOM_OPTIMIZATION = 'USE MERGE JOIN' 
INNER JOIN TABLE (SELECT * FROM AVE2122B T3) AS NTE3 ON NTE3.ROLE = T2.ROLE
AND DATACOM_OPTIMIZATION = 'USE MERGE JOIN'
  ;
In this example, both the (T1, T2) and ((T1,T2), NTE3) joins will use a merge join.