"G" Moment: Supercharge Your SQL Queries with GBase Database Hints
The core design philosophy behind GBase database's high‑performance queries is to leverage its distributed architecture, achieving significant performance leaps through index design, table type adaptation, SQL rewriting, and system‑level configuration. In practice, you need to balance consistency, availability, and performance based on business scenarios, and rely on an operations platform for efficient management.
In this article, we will walk you through the approach of using hints to boost SQL performance, from the perspective of a database technical support engineer.
To demonstrate the effect of hints more vividly, let’s first create tables and insert data.
Preparation: Table Creation and Data Insertion Statements
-- Create tablesCREATE TABLE emp ( empno INT PRIMARY KEY, ename VARCHAR(50), sal DECIMAL, deptno INT);CREATE TABLE dept ( deptno INT PRIMARY KEY, dname VARCHAR(50));CREATE TABLE job ( job_id INT PRIMARY KEY, job_title VARCHAR(50));-- Insert dataINSERT INTO dept (deptno, dname) VALUES(1, 'Sales'),(2, 'Marketing'),(3, 'HR'),(4, 'IT');INSERT INTO emp (empno, ename, sal, deptno) VALUES(1001, 'John', 50000.00, 1),(1002, 'Jane', 60000.00, 2),(1003, 'Jake', 45000.00, 3),(1004, 'Julia', 55000.00, 4),(1005, 'Jim', 48000.00, 1);INSERT INTO job (job_id, job_title)VALUES(1, 'Manager'),(2, 'Developer'),(3, 'Analyst');-- Create indexesCREATE INDEX idx_emp_deptno ON emp(deptno);CREATE INDEX idx_emp_sal ON emp(sal);
Starting from the next step, we’ll demonstrate how to use hints with specific commands and query results, and introduce the troubleshooting mindset of technical support.
1. Force a Sequential Scan (Seq Scan)
EXPLAIN SELECT /*+ tablescan(e) */ * FROM emp e WHERE empno = 7369
Force a full table scan:
/*+ tablescan(table_name/alias) */
/*+ no indexscan(table_name/alias) */
/*+ set(enable_indexscan off) */
2. Disable Sequential Scan
EXPLAIN SELECT /*+ no tablescan(e) */ * FROM emp e WHERE empno > 7369;
Prevent a full table scan:
/*+ no tablescan(table_name/alias) */
/*+ set(enable_seqscan off) */
3. Force an Index Scan (with Table Access)
EXPLAIN SELECT /*+ indexscan(e idx_emp_deptno) */ * FROM emp e WHERE deptno = 10;
Force an index scan with table access:
/*+ indexscan(table_name/alias [index_name]) */ If the column in the WHERE condition has no matching index, an “unused hint” warning will appear.
4. Force an Index‑Only Scan (No Table Access)
EXPLAIN SELECT /*+ indexonlyscan(e) */ ename FROM emp e WHERE deptno = 10;
You might have noticed the eye‑catching WARNING: unused hint: IndexOnlyScan(e), indicating that the execution plan did not use the index. The reason is that the index does not contain the ename column, so it cannot return ename solely from the index. Let’s add a covering index and test again.
create index idx_emp_deptno_ename on emp(deptno,ename);
5. Force a Bitmap Index Scan
EXPLAIN SELECT /*+ no tablescan(e) no indexscan(e) */ * FROM emp e WHERE empno IN (7369, 7499, 7521);
A Bitmap Index Scan is used when the WHERE clause contains OR or IN (value1, value2), or when a JOIN condition uses OR. If there is only one filter condition and no OR/IN, but the optimizer still chooses a Bitmap Index Scan, its performance will be worse than a regular Index Scan. A Bitmap Index Scan reads all rows that satisfy the WHERE filter, builds a bitmap, and then accesses the table to fetch the remaining columns. (Bitmap indexes are generally used on low‑cardinality columns such as gender; for high‑cardinality columns like primary keys, a B‑tree index is better.)
6. Force a Nested Loop Join
EXPLAIN SELECT /*+ nestloop(e d) */ * FROM emp e, dept d WHERE e.deptno = d.deptno;
Force a nested loop join:
/*+ nestloop(table1/alias1 table2/alias2) */
/*+ set(enable_hashjoin off) set(enable_mergejoin off) */
Typically, a nested loop is best when the driving table returns few rows (after predicate filtering), the driven table has an index on the join column, and the result set is small. (In essence, nested loop passes values and requires repeated scans of the driven table. If the driven table lacks an index and performs a full table scan, the repeated scans can easily become a performance bottleneck, leading to a suboptimal execution plan and slow SQL.)
Therefore, in practice we often set enable_nestloop to off and enable_index_nestloop to on, and create indexes on the join columns of the driven table.
7. Force a Hash Join
EXPLAIN SELECT /*+ hashjoin(e d) */ * FROM emp e, dept d WHERE e.deptno = d.deptno;
Force a hash join:
/*+ hashjoin(table1/alias1 table2/alias2) */
/*+ set(enable_nestloop off) set(enable_index_nestloop off) set(enable_mergejoin off) */
Generally, a hash join is used when the join returns a large result set. Tables joined with a hash join typically do not use indexes but perform full table scans. Why? Because when returning many rows, index‑based table access is slower, and both the driving and driven tables are scanned only once (in essence, hash join uses batch probing).
8. Force a Merge Join
EXPLAIN SELECT /*+ mergejoin(e d) */ * FROM emp e, dept d WHERE e.deptno = d.deptno;
A merge join performs well when data is already sorted and the join condition uses >, =, or <. In addition, if the query does not require an ordered result (no ORDER BY), its use cases are limited. Why is it rarely chosen? When the data set is small, a nested loop is faster; when the data set is large, both tables need to be sorted, which either requires an index with table access or a full table scan with sorting. Both table‑access and sorting on a large volume of data are performance killers. (Here “small” and “large” refer to the number of rows returned after the join; it is a relative concept—roughly using 50,000 rows as a reference.)
9. Force the Driving Table in a Join
EXPLAIN SELECT /*+ nestloop(e d) leading((e d)) */ * FROM emp e, dept d WHERE e.deptno = d.deptno;
This uses the emp table as the driving table for a nested loop join. leading((e d)) specifies the join order. For hash joins and inner joins the order can be swapped freely. However, for a nested‑loop left join, the order cannot be swapped. Why?
The answer is as follows:
EXPLAIN SELECT /*+ nestloop(e d) leading((e d)) */ * FROM emp e, dept d WHERE e.deptno = d.deptno(+);
EXPLAIN SELECT /*+ nestloop(e d) leading((d e)) */ * FROM emp e, dept d WHERE e.deptno = d.deptno(+);
Look at the second statement that forces d to drive e—this time a hash join is used. The reason is the “pass‑value” mechanism. A nested loop passes values; a left join must preserve all rows from the left table (emp). If the right table (dept) contains nulls, using the right table as the driving table would lose data because null does not match any value. Therefore, the following SQL can adjust the driving table by pushing d.deptno is not null.
In short, for outer joins, the nested‑loop optimizer fixes the driving table: a left join fixes the left table as the driving table, and a right join fixes the right table as the driving table.
EXPLAIN SELECT /*+ nestloop(e d) leading((e d)) */ * FROM emp e, dept d WHERE e.deptno = d.deptno(+) where d.deptno>0;
10. Control Table Join Order
Join order 1: emp -> dept -> job
EXPLAIN SELECT /*+ LEADING((e d j)) */ e.empno, e.ename, e.sal, d.dname, j.job_titleFROM emp eJOIN dept d ON e.deptno = d.deptnoJOIN job j ON e.empno = j.job_id;
Join order 2: job -> emp -> dept
EXPLAIN SELECT /*+ LEADING((j e d)) */ e.empno, e.ename, e.sal, d.dname, j.job_titleFROM job jJOIN emp e ON j.job_id = e.empnoJOIN dept d ON e.deptno = d.deptno;
Join order 3: dept -> emp -> job
EXPLAIN SELECT /*+ LEADING((d e j)) */ e.empno, e.ename, e.sal, d.dname, j.job_titleFROM dept dJOIN emp e ON d.deptno = e.deptnoJOIN job j ON e.empno = j.job_id;
Specifying the join order aims to minimize intermediate result sets and speed up the query. In most cases the optimizer chooses the best order. The number of possible join orders for n tables is n!; for 10 tables that is 10! = 3,628,800. If we also consider join methods (hash join, nested loop, merge join), an exhaustive search becomes too expensive. The database then uses heuristic strategies to limit the search space, which may lead to a suboptimal join order. Therefore, try to avoid joining too many tables in a single SQL statement to prevent optimizer mistakes.
In summary: if the join returns few rows without data compression or sorting, but the SQL still runs for a long time, it is likely that the join order or method is wrong. Common data compression techniques include analytic functions such as row_number(), aggregate functions like count(), deduplication with DISTINCT or GROUP BY, and sorting with ORDER BY.
11. Control Semi‑Join (EXISTS)
EXPLAIN SELECT /*+ nestloop(e d) leading((e d)) */ * FROM emp e WHERE deptno IN (SELECT deptno FROM dept d WHERE d.dname = 'SALES');
Here a nested loop join is used to optimize the semi‑join query. When the result set is small, try to use a nested loop with an index on the driven table; when the result set is large, use a hash join. (This is commonly expressed as “hash join for large tables, nested loop for small tables,” but we recommend understanding the underlying reason rather than memorizing a rule.)
12. Control WITH AS Subquery
EXPLAIN WITH e AS (SELECT * FROM emp) SELECT /*+ nestloop(d emp) */ * FROM e, dept d WHERE e.deptno = d.deptno;
For a WITH AS subquery, the nestloop hint specifies the join between e and dept. Materialization can extract common parts and even pre‑materialize the temporary table. The downside is that a materialized table generally only allows a full table scan, cannot use the original table’s indexes, and cannot push predicates into the base table to leverage its indexes.
EXPLAIN WITH e AS materialized (SELECT * FROM emp where empno = 9 ) SELECT * FROM e, dept d WHERE e.deptno = d.deptno;
EXPLAIN WITH e AS not materialized (SELECT * FROM emp where empno = 9 ) SELECT * FROM e, dept d WHERE e.deptno = d.deptno;
13. Force Hard Parsing
EXPLAIN SELECT /*+ use_cplan */ * FROM emp WHERE empno = 7369;
This query forces the database to perform a hard parse instead of a soft parse, helping to avoid caching‑related issues. (Features such as bind variables and bind peeking can easily cause the execution plan to go wrong, requiring a forced hard parse.) Below is a soft parse example:
EXPLAIN SELECT /*+ use_gplan */ * FROM emp WHERE empno = 7369;
14. Enable Parallel Query
EXPLAIN SELECT /*+ set(query_dop 4) */ * FROM emp;
create table emp_bak as select * from emp;
---- Add more data to emp_bak
EXPLAIN SELECT /*+ set(query_dop 4) */ * FROM emp_bak;
This query enables parallel query execution. The query_dop parameter is set to 4, meaning a parallel degree of 4. It is typically used when a large table requires a full table scan and no other optimization is possible. Note that a higher degree does not always mean faster; the optimal value is usually related to the number of CPU cores—generally one‑quarter of the CPU core count.
15. Enable Vectorized Execution
EXPLAIN SELECT /*+ set(try_vector_engine_strategy force) */ * FROM emp;
This query forces vectorized execution, suitable for scenarios with large data volumes and high performance requirements. Due to the test environment, the performance improvement here is not noticeable.
16. Force WHERE Subquery to Use FILTER
EXPLAIN SELECT * FROM emp WHERE deptno IN (SELECT /*+ no_expand */ deptno FROM dept);
This query forces the WHERE subquery to use a FILTER instead of EXPAND. It scans the dept table for qualifying deptno values and then filters the emp table using those values. Without the hint, the optimizer does not know in advance how many rows the IN subquery will return; fearing a large result it may expand it into a hash join. In reality, when the data set is small, not expanding and using an index scan twice is faster than a hash join. The traditional thinking of “eliminate FILTER” often rewrites IN to EXISTS when the data is large, so that the execution plan uses a hash join.
Tip: Do not blindly rewrite IN to EXISTS. If neither produces a FILTER, both belong to semi‑joins. When a join is used, you need to analyze the join method and order. In fact, producing a FILTER is like fixing the execution plan to a nested loop, similar to a nestloop where the driving table is fixed to the IN subquery’s table.
17. rows: Specify the Number of Rows Returned by a Table or Result Set
EXPLAIN select /*+ rows(e #100) */ * from emp e;
/*+ rows(e #100) */ – specify that e returns 100 rows
/*+ rows(e +100) */ – add 100 rows to the original estimate for e
/*+ rows(e -100) */ – subtract 100 rows from the original estimate for e
/*+ rows(e *100) */ – multiply the original estimate for e by 100
EXPLAIN select /*+ rows(e d #100) */ * from emp e left join dept d on e.deptno = d.deptno;
A Word from GBase
The purpose of this article is to help you correct SQL execution plans by using hints. However, we do not recommend blindly intervening in the execution plan when tuning SQL. Remember: adding hints is only a means; the goal is to optimize SQL and improve data processing performance.
We generally suggest analyzing the SQL first. The most common approach is the three‑section decomposition: SELECT (first section), FROM (second section), WHERE (third section). Performance issues in the SELECT section often involve custom functions, analytic functions, scalar subqueries, or sequences (in distributed systems). In the FROM section, pay attention to large tables, views, subqueries, and table joins. In the WHERE section, check whether filter columns use indexes—functions, expressions, and implicit conversions can cause index invalidation and should be watched closely.
Ultimately, SQL boils down to joins between subqueries and tables, and all subqueries can be rewritten as table joins. The essence of SQL is table joins with deduplication. We hope you will think more during practice, validate your ideas through scenarios and examples, and avoid memorizing patterns mechanically.