Queries Using Hints

Optimizes the query result based on the specified hint rule.

The following example demonstrates a typical hint query that optimizes the query results based on specific hint rules in a specified table.

-- Create a table.
gaussdb=# CREATE TABLE t1(c1 int, c2 int, c3 int);
gaussdb=# CREATE TABLE t2(c1 int, c2 int, c3 int); 
gaussdb=# CREATE INDEX it1 ON t1(c1,c2); 

-- Specify the scanning mode using a hint.
gaussdb=# EXPLAIN SELECT /*+ indexscan(t1 it1)*/* from t1,t2 WHERE t1.c1=t2.c2;
 id |             operation             | E-rows | E-width |     E-costs     
----+-----------------------------------+--------+---------+-----------------
  1 | ->  Hash Join (2,3)               |  18915 |      24 | 53.763..345.512
  2 |    ->  Index Scan using it1 on t1 |   1945 |      12 | 0.000..73.425
  3 |    ->  Hash                       |   1945 |      12 | 29.450..29.450
  4 |       ->  Seq Scan on t2          |   1945 |      12 | 0.000..29.450
(4 rows)

 Predicate Information (identified by plan id) 
-----------------------------------------------
   1 --Hash Join (2,3)
         Hash Cond: (t1.c1 = t2.c2)
(2 rows)

-- Specify the number of rows using a hint.
gaussdb=# EXPLAIN SELECT /*+ rows(t1 t2 #5)*/* FROM t1,t2;
 id |        operation         | E-rows | E-width |     E-costs      
----+--------------------------+--------+---------+------------------
  1 | ->  Nested Loop (2,3)    |      5 |      24 | 0.000..47351.575
  2 |    ->  Seq Scan on t1    |   1945 |      12 | 0.000..29.450
  3 |    ->  Materialize       |   1945 |      12 | 0.000..39.175
  4 |       ->  Seq Scan on t2 |   1945 |      12 | 0.000..29.450
(4 rows)

gaussdb=# DROP TABLE t1;
gaussdb=# DROP TABLE t2;