Help Center/ GaussDB/ Developer Guide(Distributed_V2.0-8.x)/ SQL Optimization/ Hint-based Optimization/ Scan Hints
Updated on 2025-08-19 GMT+08:00
View PDF

Scan Hints

Description

Specifies a scan operation, which can be tablescan, indexscan, index (same as indexscan) indexonlyscan, gsi or gsitable.

Syntax

1
 
[no] tablescan|indexscan|index|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, index-only scan, GSI, or GSI table hint is used.
  • Index scan, index, 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 and index hints, but the index-only hint can generate only the index-only plan.
  • When index scan and index are 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 and index are compatible with index-only scan. Index scan and index are 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 an ordinary 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
3
4
5
 
explain
select /*+ indexscan(item i) */ i_product_name product_name ...
or
EXPLAIN
SELECT /*+ index(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