Help Center> GaussDB> Centralized_3.x> SQL Optimization> Hint-based Tuning> Scan Operation Hints
Updated on 2024-05-07 GMT+08:00
View PDF

Scan Operation Hints

Description

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

Syntax

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

Parameters

  • no indicates that the specified hint will not be used for a join.
  • For details about @queryblock, see Hint Specifying the Query Block Where the Hint Is Located. This parameter can be omitted, indicating that it takes effect in the current query block.
  • 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 or indexonlyscan. You can specify only one index.
  • 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, HDFS tables, and subquery tables. HDFS internal tables include base tables and delta tables. The delta tables are invisible to users. Therefore, scan operation hints are used only for base 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.

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 Example 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.

In a centralized environment, when GSI hints are used, an alarm is reported indicating that GSI hints are not supported. Example: 
-- Create a table.
CREATE TABLE gsi_test(a int, b int);
CREATE INDEX gsi_test_idx on gsi_test(a);
-- Use indexes.
EXPLAIN SELECT /*+  GSI(gsi_test gsi_test_idx) */ * FROM gsi_test where b = 1;
WARNING:  LINE 1: unsupport distributed hint at 'gsi'
                        QUERY PLAN
----------------------------------------------------------
 Seq Scan on gsi_test  (cost=0.00..36.86 rows=11 width=8)
   Filter: (b = 1)
(2 rows)
In a centralized environment, when GSI table hints are used, an alarm is reported indicating that GSI table hints are not supported. Example: 
EXPLAIN SELECT /*+ GSITABLE(gsi_test gsi_test_idx) */ * FROM gsi_test where b = 1;
WARNING:  LINE 1: unsupport distributed hint at 'gsitable'
                        QUERY PLAN
----------------------------------------------------------
 Seq Scan on gsi_test  (cost=0.00..36.86 rows=11 width=8)
   Filter: (b = 1)
(2 rows)
Parent topic: Hint-based Tuning