OPTION clause

The OPTION keyword denotes options that a user can pass in with a command. These options are specific to Teiid and are not covered by any SQL specification.

OPTION option (, option)*
Supported options
MAKEDEP table (,table)*

Specifies source tables that should be made dependent in the join.

MAKEIND table (,table)*

Specifies source tables that should be made independent in the join.

MAKENOTDEP table (,table)*

Prevents a dependent join from being used.

NOCACHE [table (,table)*]

Prevents cache from being used for all tables or for the given tables.


All tables specified in the OPTION clause should be fully qualified. However, the table name can match either the fully qualified name or an alias name.

The MAKEDEP and MAKEIND hints can take optional arguments to control the dependent join. The extended hint form is:

MAKEDEP tbl([max:val] [[no] join])
  • tbl(JOIN) means that the entire join should be pushed.

  • tbl(NO JOIN) means that the entire join should not be pushed.

  • tbl(MAX:val) means that the dependent join should only be performed if there are less than the maximum number of values from the independent side.

Teiid does not accept PLANONLY, DEBUG, and SHOWPLAN arguments in the OPTION clause. For information about how to perform the functions formerly provided by these options, see the Client Developer’s Guide.
MAKEDEP and MAKENOTDEP hints can take table names in the form of @view1.view2…table. For example, with an inline view "SELECT * FROM (SELECT * FROM tbl1, tbl2 WHERE tbl1.c1 = tbl2.c2) AS v1 OPTION MAKEDEP @v1.tbl1" the hint will now be understood as applying under the v1 view.

results matching ""

    No results matching ""