Help Center/ GaussDB/ Developer Guide(Distributed_3.x)/ SQL Optimization/ Hint-based Tuning/ Scan Hints
Updated on 2024-05-21 GMT+08:00
View PDF
Share

Scan Hints

Description

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

Syntax

1
 
[no] tablescan|indexscan|indexonlyscan|gsi( [@queryblock] table [index])

Parameters

  • table specifies the table to be scanned. You can specify only one table. Use a table alias (if any) instead of a table name.
  • index indicates the index for indexscan, indexonlyscan or gsi. You can specify only one index.
  • Index scan, index-only scan, and GSI hints can be used only when the hint index belongs to the hint table.
  • Scan operation hints can be used for row-store tables and subquery tables.
  • The index-only scan plan can be generated by the index scan hint, but the index-only hint can generate only the index-only plan.
  • When index scan is compatible with index-only scan, some plan changes may occur. For example, cost_model_version is added for escape. This parameter can be used to determine whether index scan is compatible with index-only scan. Index scan is compatible with index-only scan when the parameter value is greater than 2 or equal to 0.
  • If a GSI can be used for a query, using the GSI hint will generate an index-only scan query plan that uses the GSI, but not a query plan that uses a common index.
  • If no queryblock, table, or index is specified for a GSI hint, the GSI hint takes effect across queryblocks.
  • Valid GSIs can be directly queried on DNs.
  • In the current version, GSIs cannot be used for table access by index row ID.

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.

1
 
create index i on item(i_item_sk);

Hint the query plan in Examples as follows:

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

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

Parent topic: Hint-based Tuning