Hinting ORACLE

Hi,
We have taken an example in the ORACLE 7 Application Developer's Guide
(December 1992) "Tuning SQL Statements" page 5-23:

Quote:
"Using the INDEX_DESC hint, you can write an equivalent query that reads one TIME value from the index:
SELECT /*+ INDEX_DESC(tank_readings un_time) */ temperature   FROM tank_readings
  WHERE time <= TO_DATE(:t)
    AND ROWNUM = 1" This example selects the most recent temperature reading taken as of a particular time ":t".

Now ORACLE have told us they do not guarantee this will work. I wonder if they know it is in the manual?

We are trying to implement functionality so that a legany application can access ORACLE as if it were a hierachical database.

Say we have a table with the following:
A
-
1
3
5 <-- current point of access
7
8
9
12
20
...etc for 6 million rows

• the order above is indexed order rather than insertion order. Actual table sequence would be far different to the above.

We need to be able to access the next row in key sequence (7), or in reverse key sequence (3). Notice that this is a lot different to getting the next row by row id (insertion sequence.)

We've implemented an SQL like the following, hinting for it to use the index on column A:
  SELECT /*+ INDEX_DESC(...) */ ... FROM ...     WHERE A < :X AND ROWNUM = 1;

This works for finding the previous element in key sequence. (Note that in this case INDEX_DESC must be used, otherwise it will get the first row which has A < X, eg 1 rather than 3.)

This is far faster than say:
  SELECT MAX(A) FROM ...
    WHERE A < :X;

Oracle have said they won't guarantee that it will take the hint.
(Although this is used in an example in their manual!)

If the hint is ignored, then the first row returned will probably be in table sequential order. Although it will be less that X, it probably won't be the next number less than X in the index.

We think that in a simple SELECT with no joins, that it probably will always use the hint, and we are proposing to use the following SQL to test that:

Given that the above SQL returned AVAL:
  SELECT /*+ INDEX_DESC(...) */ A FROM ...     WHERE A < :X AND A > :AVAL;

If this SQL returns a row, then we know that the first SQL failed. In that case we retry a few times (in case an insert has occurred which changes the way ORACLE works) and then if that still fails, throw an exception.

This will guarantee that we either get a correct result, or no result. If there is a way to be guaranteed of always getting a result, that would be better.

Does anyone have any ideas which will give us the functionality and performance? (It should be noted that we have several tables with millions of rows. The system has 300 or so tables, with an average of 3 indexes per table.)

--
Brian Modra
http://www.adelaide.net.au/~bmodra