FROM clause

The FROM clause specifies the target tables for SELECT, UPDATE, and DELETE statements.

Example Syntax:

  • FROM table [[AS] alias]

  • FROM table1 [INNER|LEFT OUTER|RIGHT OUTER|FULL OUTER] JOIN table2 ON join-criteria

  • FROM table1 CROSS JOIN table2

  • FROM (subquery) [AS] alias

  • FROM TABLE(subquery) [AS] alias. For more information, see Nested tables

  • FROM table1 JOIN /*+ MAKEDEP */ table2 ON join-criteria

  • FROM table1 JOIN /*+ MAKENOTDEP */ table2 ON join-criteria

  • FROM /*+ MAKEIND */ table1 JOIN table2 ON join-criteria

  • FROM /*+ NO_UNNEST */ vw1 JOIN table2 ON join-criteria

  • FROM table1 left outer join /*+ optional */ table2 ON join-criteria. For more information, see Optional join in Federated optimizations.

  • FROM TEXTTABLE… For more information, see TEXTTABLE.

  • FROM XMLTABLE… For more information, see XMLTABLE.

  • FROM ARRAYTABLE… For more information, see ARRAYTABLE.

  • FROM OBJECTTABLE… For more information, see OBJECTTABLE.

  • FROM JSONTABLE… For more information, see JSONTABLE.

  • FROM SELECT… For more information, see Inline views in Subqueries.

From clause hints

From clause hints are typically specified in a comment block preceding the affected clause. MAKEDEP and MAKENOTDEP may also appear after in non-comment form after the affected clause. If multiple hints apply to that clause, the hints should be placed in the same comment block.

Example hint
FROM /*+ MAKEDEP PRESERVE */ (tbl1 inner join tbl2 inner join tbl3 on tbl2.col1 = tbl3.col1 on tbl1.col1 = tbl2.col1), tbl3 WHERE tbl1.col1 = tbl2.col1
Dependent join hints

MAKEIND, MAKEDEP, and MAKENOTDEP are hints that you can use to control dependent join behavior. Use them only in situations where the optimizer does not choose the most optimal plan based upon query structure, metadata, and costing information. The hints can appear in a comment that follows the FROM keyword. The hints can be specified against any FROM clause, not just a named table.

MAKEIND

Indicates that the clause should be the independent (feeder) side of a dependent join.

MAKEDEP

Indicates that the clause should be the dependent (filtered) side of a join.

MAKENOTDEP

Prevents the clause from being the dependent (filtered) side of a join.

You can use the following optional MAX and JOIN arguments with MAKEDEP and MAKEIND:

MAKEDEP(JOIN)

Indicates that the entire join should be pushed.

MAKEDEP(NO JOIN)

Indicates that the entire join should not be pushed.

MAKEDEP(MAX:val)

Indicates that the dependent join should only be performed if there are less than the maximum number of values from the independent side.

Other hints

NO_UNNEST can be specified against a subquery FROM clause or view to instruct the planner to not to merge the nested SQL in the surrounding query. This process is known as view flattening. This hint only applies to Teiid planning and is not passed to source queries. NO_UNNEST can appear in a comment that follows the FROM keyword.

The PRESERVE hint can be used against an ANSI join tree to preserve the structure of the join, rather than allowing the Teiid optimizer to reorder the join. This is similar in function to the Oracle ORDERED or MySQL STRAIGHT_JOIN hints.

Example PRESERVE hint
FROM /*+ PRESERVE */ (tbl1 inner join tbl2 inner join tbl3 on tbl2.col1 = tbl3.col1 on tbl1.col1 = tbl2.col1)

results matching ""

    No results matching ""