Scan Operation Hints

Updated on 2024-12-18 GMT+08:00

View PDF

Function

These hints specify a scan operation, which can be tablescan, indexscan, or indexonlyscan.

Syntax

    
       [no] tablescan|indexscan|indexonlyscan([@block_name] table [index])

Parameter Description

no indicates that the specified hint will not be used for a join.
block_name indicates the block name of the statement block. For details, see block_name.
table specifies the table to be scanned. You can specify only one table. Use a table alias (if any) instead of a table name.
NOTE:
- The syntax format of the table is as follows:
  [schema.]table[@block_name]
  
  The table name can contain the schema name or block name before the subquery statement block is promoted. If the subquery statement block is optimized and rewritten by the optimizer, the value of block_name is different from that of block_name in leading.
- If a table has an alias, the alias is preferentially used to represent the table.
index indicates the index for indexscan or indexonlyscan. You can specify only one index.
NOTE:

indexscan and indexonlyscan hints can be used only when the specified index belongs to the table.

Scan operation hints can be used for row-store tables, column-store tables, HDFS tables, HDFS foreign tables, OBS tables, and subquery tables. HDFS tables include primary tables and delta tables. The delta tables are invisible to users. Therefore, scan operation hints are used only for primary tables.

If indexscan is specified, indexscan or indexonlyscan takes effect. indexscan and indexonlyscan can also take effect at the same time. When indexscan and indexonlyscan hints appear at the same time, indexonlyscan takes effect first.

Example

To specify an index-based hint for a scan, create an index named i on the i_item_sk column of the item table.

    
       create index i on item(i_item_sk);

Hint the query plan in Examples as follows:

   
      explain
select /*+ indexscan(item i) */ i_product_name product_name ...

item is scanned based on an index. The optimized plan is as follows:

Click to enlarge

Parent topic: Hint-based Tuning

Previous topic: Stream Operation Hints

Next topic: Sublink Name Hints

Feedback

Was this page helpful?

Helpful Not helpful

Provide feedback

Thank you very much for your feedback. We will continue working to improve the documentation.See the reply and handling status in My Cloud VOC.

The system is busy. Please try again later.

Which of the following issues have you encountered?

Content is inconsistent with the product UI

Unclear descriptions

Lack of examples or code

Incorrect steps

Can't find what I need

Lack of best practices

Feedback (optional)

0/500

Select at least one type of issue, and enter your comments or suggestions.

Enter a maximum of 500 characters.

Submit Cancel

For any further questions, feel free to contact us through the chatbot.

Chatbot