Re: Hinting ORACLE

This will always be a difficult thing. Even tricks within PL/SQL (open a cursor and then only fetch one row) will not work, because the open statement will take the long time, not the fetch.

In general, you cannot expect a defined behaviour because relational databases do not know the notion of a defined sequence of result records except for the "order by" clause. So you may try expanding your statement by an "order by a desc" clause. But I am afraid this is not an enforcement of sequence in connection with rownum.

Martin Haltmayer

Brian L. Modra wrote:
>
> 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
Received on Fri Jan 23 1998 - 00:00:00 CST