Consultor Eletrônico

SUMMARY:

This Solutions summarizes Progress rules for generating index hints to ORACLE.

EXPLANATION:

You might sometimes expect the Progress DataServer to generate index hints to ORACLE but the DataServer doesn't do so, thus ORACLE will not use indexes and will do a full table scan. In this case performance suffers.

SOLUTION:

It's helpful to understand the rules of index hints. Note that these rules have been modified over time, and indeed, they might also change in the future. For instance, initially hints were only considered for tables with a PROGRESS_RECID column.

-- If it's a join on the server, NO index hint.

-- If there is a use-index clause, it generates a hint.

-- If there is a BY clause (or implied ordering as in a FIND
statement) and another index does NOT exist that would do a
better job of selection in the WHERE clause, then an index hint is
generated to help the ordering of the result set.

-- If the WHERE clause is ROWID = :value, a hint to retrieve by ROWID
is given (also applies to DELETE and UPDATE).

-- If the WHERE clause is PROGRESS_RECID = :value, an index hint for
<table-name>##PROGRESS_RECID is issued.