Help Center/ GaussDB/ Centralized_8.x/ SQL Optimization/ Hint-based Tuning/ Scan Operation Hints
Updated on 2024-06-03 GMT+08:00
View PDF

Scan Operation Hints

Description

Specifies a scan operation, which can be tablescan, indexscan, or indexonlyscan.

Syntax

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

Parameters

  • no indicates that the specified hint will not be used for a join.
  • For details about @queryblock, see Hint for 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.
  • 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.
  • The centralized environment does not support GSI and GSI table hints.

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.

In a centralized environment, when GSI hints are used, an alarm is reported indicating that GSI hints are not supported. The following is an example: 
-- Create a table.
create table gsi_test(a int, b int);
create index gsi_test_idx on gsi_test(a);
-- Use indexes.
gaussdb=# 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. The following is an example:
  
gaussdb=# 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