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

Scan Hints

Description

Specifies a scan operation which can be a table scan, index scan, index-only scan, GSI, or GSI table.

Syntax

1
 
[no] tablescan|indexscan|indexonlyscan|gsi|gsitable( [@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 specifies only one index when an index scan, index-only scan, GSI, or GSI table hint is used.
  • 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 the GSI for TABLE ACCESS BY INDEX ROWID can be used for a query, using the GSI table hint will generate a plan that uses the GSI for TABLE ACCESS BY INDEX ROWID.
  • If no query block, table, or index is specified for a GSI hint, the GSI hint takes effect across query blocks.
  • To use the GSI table hint, enable the enable_stream_operator parameter. Otherwise, the hint does not take effect.
  • Valid GSIs can be directly queried on DNs.

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 Optimization