Stephen Gutknecht wrote:
> Can you provide us some examples of this unsupported feature :) ?
As I already wrote hints were only developed for internal test purpose.
So the syntax and the semantic may vary between releases but I append
a copy of some internal documentation about hints in 7.5.
I'll propose to include this into the optimizer documentation.
Best regards,
Holger
SAP Labs Berlin
Hints are embedded in SQL commands as special comments.
Hints can be included after the key words SELECT, UPDATE, and DELETE.
The syntax of the hint is as follows:
{ SELECT | UPDATE | DELETE } /*+<hint>[,<hint>]... */ FROM ....
<hint> := <access hint list> | <join hint>
<access hint list> := <access hint> [, <access hint>, ...]
<access hint> := keyaccess | indexaccess | indexaccess(<indexname>) |
l1_strategy | l2_strategy | l2_more_strategies |
l2_range_strategy |
l2_strat_no_optim | keyscan | keyrange | indexscan | indexrange
|
buildresult | enable_merge | disable_invonly | in_strategy |
subq_strategy | trace | fetchresult | ordered
<join hint> := join[(<join hint list>)]
<join hint list> := [<join access hint>];[<join access hint>];[<join access
hint>] .... (maximum number of joins)-1 times
<join access hint> := keyaccess | indexaccess | indexaccess(<indexname>) | notrans |
access=[<access hint list>]
The system analyzes the following hints:
KEYACCESS
The optimizer ignores all secondary indexes, and chooses the primary key as the access
path.
INDEXACCESS[(<INDEXNAME>)]
If indexes exist, then an index is used to choose the most cost-effective access path.
If an index name is specified in the hint, then this is the one chosen, even if it has
been deactivated by the user (ALTER INDEX <index name> ON <table name> DISABLE). If
the index does not exist in the system, then the hint is ignored.
L1_STRATEGY
Only L1 predicates restrict the search range.
L2_STRATEGY
Both L1 terms and L2 predicates restrict the search range. This can sometimes cause
more costs than the L1 strategy.
L2_MORE_STRATEGIES
The alternative strategy, which contains all L2 strategies, is not evaluated.
L2_RANGE_STRATEGY
The alternative strategy, which contains all L2 strategies, is chosen as the L2
strategy.
L2_STRAT_NO_OPTIM
L2 strategies that contain each other do not normally need to be generated; this
configuration cancels this optimization step.
KEYSCAN
The optimizer does not restrict the search area in the primary key.
KEYRANGE
The optimizer chooses a strategy that selects a range of the primary key as the search
range. This automatically controls the KEYACCESS hint.
INDEXSCAN
The optimizer does not restrict the search area in a secondary key.
INDEXRANGE
The optimizer chooses a strategy that selects a range of a secondary key as the search
range. This automatically controls the INDEXACCESS hint. If you want to use a
dedicated index, then the INDEXACCESS hint is used with an index name.
BUILDRESULT
A result set is always generated.
FETCHRESULT
A result set is not generated, if possible.
ENABLE_MERGE
This hint deactivated the parameter OPTIM_MAX_MERGE.
DISABLE_INVONLY
All data is always taken from the primary key table.
IN_STRATEGY
The possible strategy IN CONDITION FOR KEY/INDEX COLUMN is used.
SUBQ_STRATEGY
The possible strategy EQUAL CONDITION FOR KEY COLUMN (SUBQUERY) or RANGE CONDITION FOR
KEY COLUMN (SUBQUERY) is used.
TRACE
The trace of the access path component is activated.
--
MaxDB Discussion Mailing List
For list archives: http://lists.mysql.com/maxdb
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
