Sublink Name Hints

Function

These hints specify the name of a sublink block.

Syntax

1

blockname ([@block_name] table)

Precautions

• This block name hint is used by an outer query only when a sublink is pulled up. Currently, only the Agg equivalent join, IN, and EXISTS sublinks can be pulled up. This hint is usually used together with the hints described in the previous sections.

• The subquery after the FROM keyword is hinted by using the subquery alias. In this case, block_name hint becomes invalid.
• If a sublink contains multiple tables, the tables will be joined with the outer-query tables in a random sequence after the sublink is pulled up. In this case, blockname also becomes invalid.

Parameter Description

• block_name indicates the block name of the statement block. For details, see block_name.
• table indicates the name you have specified for a sublink block.
  

  • The syntax format of tables is as follows:

    [schema.]table[@block_name]

    The table name can include the schema or the block_name prior to the lifting of the subquery statement block. If the subquery statement block is lifted during optimization and rewriting by the optimizer, this block_name will differ from the block_name in leading.

  • If a table has an alias, the alias is preferentially used to represent the table.

Examples

1

explain select /*+nestloop(store_sales tt) */ * from store_sales where ss_item_sk in (select /*+blockname(tt)*/ i_item_sk from item group by 1);