| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.server -> Re: Oracle not guaranteeing HINT?
Using optimiser hints do not guarantee a specific access plan - it is possible that the optimiser in later versions of oracle may be more "stubborn" (or intelligent) and follow its own mind. The optimiser will threat your hints at well-meaning *suggestions* - if you supply some mad/weird/silly hints, it may well choose to ignore them. If you revert to the rule based optimiser, it will follow it's own "rules of thumb" no matter what.
Thus, when you use "where RowNum = 1", you have to accept that the optimiser *may* choose a different access plan than you expected - there are several ways to satisfy the rest of your query, and using ROWNUM=1 just means "Only give me the first result". If the rest of the query is executed differently, the "first" result may well be different.
The only way to ensure you get the correct result would be to use a query where you do not rely on the access plan. The optimiser is free to change its mind if you re-analyse your tables when they have a different number of rows. Generally, you can trust the optimiser to end up with a sensible access plan.
What about:
Declare
cursor temperature_curs is
Select --+First_Rows
temperature
from Tank_Readings
where time <= TO_DATE(sometime);
begin
open temperature_curs;
fetch ....
if Temperature_Curs%NotFound then
Complain, scream, cry and generally make a fuss
end if;
close ...
I feel that the above is a "clearer" way of communicating to the optimiser what you want.
PS: Off the top of my head, I'm not sure of the exact hint name - I always have to look them up anyway.
Hope this helps
-- Karl Oracle Certified DBA Brian L. Modra wrote in message <34BE7E2A.CB84388B_at_camtech.net.au>...Received on Fri Jan 16 1998 - 00:00:00 CST
>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
 |
 |