In a GBase database, the query optimizer usually selects an efficient execution plan automatically.
However, in complex scenarios, the optimizer may choose a suboptimal plan—leading to slow queries.
👉 This is where SQL Hints come in.
Hints allow developers to guide the optimizer and achieve significant performance improvements.
🚀 What Are Hints in GBase Database?
Hints are special directives embedded in SQL:
SELECT /*+ hint */ * FROM table;
`
👉 They influence:
- Scan methods
- Join strategies
- Execution plans
- Parallelism
📊 1. Controlling Table Access Methods
Force Full Table Scan
sql
EXPLAIN SELECT /*+ tablescan(e) */ * FROM emp e;
👉 Useful when:
- Table is small
- Index is inefficient
Disable Full Table Scan
sql
EXPLAIN SELECT /*+ no tablescan(e) */ * FROM emp e;
👉 Forces index usage when available
Force Index Scan
`sql
EXPLAIN SELECT /*+ indexscan(e idx_emp_deptno) */
- FROM emp e WHERE deptno = 1;
`
Index-Only Scan
sql
EXPLAIN SELECT /*+ indexonlyscan(e) */
ename FROM emp e WHERE deptno = 1;
👉 Requires all selected columns in the index
🔗 2. Controlling Join Strategies
Nested Loop Join
`sql
EXPLAIN SELECT /*+ nestloop(e d) */
- FROM emp e, dept d
WHERE e.deptno = d.deptno;
`
✅ Best for:
- Small result sets
- Indexed join columns
Hash Join
`sql
EXPLAIN SELECT /*+ hashjoin(e d) */
- FROM emp e, dept d
WHERE e.deptno = d.deptno;
`
✅ Best for:
- Large datasets
- Full table scans
Merge Join
`sql
EXPLAIN SELECT /*+ mergejoin(e d) */
- FROM emp e, dept d
WHERE e.deptno = d.deptno;
`
👉 Works well on pre-sorted data
🔄 3. Controlling Join Order
`sql
EXPLAIN SELECT /*+ LEADING((e d)) */
- FROM emp e, dept d
WHERE e.deptno = d.deptno;
`
👉 Reduces intermediate result size
⚙️ 4. Parallel Execution
`sql
EXPLAIN SELECT /*+ set(query_dop 4) */
- FROM emp;
`
👉 Enables parallel query execution
⚡ 5. Advanced Optimization Techniques
Force Hard Parse
`sql
EXPLAIN SELECT /*+ use_cplan */
- FROM emp WHERE empno = 1001;
`
Enable Vectorized Execution
`sql
EXPLAIN SELECT /*+ set(try_vector_engine_strategy force) */
- FROM emp;
`
Control Subquery Execution
sql
EXPLAIN SELECT *
FROM emp
WHERE deptno IN (
SELECT /*+ no_expand */ deptno FROM dept
);
🔍 6. Row Estimation Control
`sql
EXPLAIN SELECT /*+ rows(e #100) */
- FROM emp e;
`
👉 Helps optimizer choose better plans
⚠️ When Should You Use Hints?
Use hints when:
- Execution plan is incorrect
- Statistics are misleading
- Complex joins confuse the optimizer
❌ When NOT to Use Hints
Avoid hints if:
- Query can be fixed via indexing
- Data model is poorly designed
- Statistics are outdated
👉 Hints are a last-mile optimization tool, not a first choice.
🧠 Real-World Insight
From real GBase database scenarios:
- Most performance issues come from incorrect join strategies
- Join order often impacts performance more than indexing
- Hints can dramatically improve performance—but misuse can make it worse
📌 Final Thoughts
Hints in a GBase database give you powerful control over SQL execution.
But remember:
Hints are tools for correction—not shortcuts for poor design.
Top comments (0)