Query hints overview
Introduction
When a query is submitted to the query processor the query processor determines an evaluation plan. It creates this plan based on a fixed set of rules. An application developer may have knowledge about the data that the query processor has not and therefore he or she may be capable of determining a more efficient execution plan. For this the application developer can add hints to the query.
Hints can be added to each select query including subqueries. A hint is supplied to a query by adding a hint clause to the query statement. The hint clause is the last clause of the query.
Syntax
<hint clause> ::= hint <hint list>
<hint list> ::= <hint>
| <hint> and <hint list>
<hint> ::= use index <nr list> on <table name>
[ asc | desc ]
| array fetching
| no array fetching
| array size <n>
| all rows
| first rows
| buffer <n> rows
| <string literal>
| ordered
| no hints
<nr list> ::= <n>
| <n> ',' <nr_list>
<n> ::= a positive integer
<table name> ::= the name of a table or a table name alias.
<string literal> ::= string enclosed in double quotes
Examples
Before we go into the semantics of the hints let us first give you some examples to illustrate the syntax.
select tfacr200.*
from tfacr200
where _index2 = {" 1001"}
and {ttyp, ninv, line, tdoc, docn, lino} >= {"",0,0,"",0,0}
order by _index2
as prepared set
hint use index 2 on tfacr200
and array fetching
select a.cuno
from tccom010 a
where a.cuno in ( select b.cuno
from tfgld106 a, tccom010 b
where a.cuno = b.cuno
hint ordered )
order by cuno
hint buffer 100 rows
Related topics
|
