Help Center/
GaussDB/
Developer Guide(Distributed_V2.0-8.x)/
SQL Reference/
SQL Syntax/
S/
SELECT/
Queries Using Hints
Updated on 2025-05-29 GMT+08:00
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 | -> Streaming (type: GATHER) | 30 | 24 | 61.51 2 | -> Hash Join (3,4) | 30 | 24 | 60.26 3 | -> Index Scan using it1 on t1 | 30 | 12 | 44.46 4 | -> Hash | 29 | 12 | 15.49 5 | -> Streaming(type: REDISTRIBUTE) | 30 | 12 | 15.49 6 | -> Seq Scan on t2 | 30 | 12 | 14.14 (6 rows) Predicate Information (identified by plan id) ----------------------------------------------- 2 --Hash Join (3,4) 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 | -> Streaming (type: GATHER) | 5 | 24 | 37.60 2 | -> Nested Loop (3,5) | 5 | 24 | 37.35 3 | -> Streaming(type: BROADCAST) | 90 | 12 | 17.93 4 | -> Seq Scan on t1 | 30 | 12 | 14.14 5 | -> Materialize | 30 | 12 | 14.21 6 | -> Seq Scan on t2 | 30 | 12 | 14.14 (6 rows) gaussdb=# DROP TABLE t1; gaussdb=# DROP TABLE t2;
Parent topic: SELECT
Feedback
Was this page helpful?
Provide feedbackThank you very much for your feedback. We will continue working to improve the documentation.See the reply and handling status in My Cloud VOC.
The system is busy. Please try again later.
For any further questions, feel free to contact us through the chatbot.
Chatbot