In general hints are suggestions for the Oracle cost based optimizer (CBO) that can influence performance but will never change the result. The CBO will comply with the hint at all costs – if it is possible.
True? Well not always!
There is a very small set of hints which can change the result of a select or DML statement.
The CBO is an extremly complicated piece of software, so it can happen that there are bugs in it, which in turn lead to different results. This is not what I am considering here.
So here is my collection of hints that are able to change the output of a statement.
ignore_row_on_dupkey_index
This hint allows to avoid UK errors during an insert. So only those rows are inserted that do not violate the unique index. The other rows are silently ignored.
See this example by Richard Foote about the hint:
https://richardfoote.wordpress.com/2010/12/20/oracle11g-ignore_row_on_dupkey_index-hint-micro-cuts/
Similar hints are
(CHANGE_DUPKEY_ERROR_INDEX and RETRY_ON_ROW_CHANGE)
They are all called semantic hints. Which has the side effect that they are not disabled by setting _optimizer_ignore_hints=TRUE
For more examples see this older post from Rob van Wijk
http://rwijk.blogspot.de/2009/10/three-new-hints.html
driving_site
I consider the driving_site hint to be one of the “good” hints. There are many cases where we need it. It is good to set it, because it enables behaviour that otherwise the optimizer can not choose.
However there is one strange special issue that I found.
I described this behaviour in an older blog post: https://wordpress.com/post/svenweller.wordpress.com/108
If we have a distributed query then SYSTIMESTAMP and also DBTIMEZONE is affected by the driving site hint. It will return the timezone from the local (no hint) or the remote (with hint) database. SYSDATE however was not affected.
This effect happend on an older 10g database, I wasn’t able to test if the behaviour still exists in 12c.
A similar effect can be reached using the materialize hint.
opt_param(‘OPTIMIZER_SECURE_VIEW_MERGING’,’true|false’)
The opt_param hint will allow to set instance parameters only for the time while the select is running. Some of those parameters can potentially influence the outcome of queries.
One of them is OPTIMIZER_SECURE_VIEW_MERGING
https://docs.oracle.com/en/database/oracle/oracle-database/12.2/refrn/OPTIMIZER_SECURE_VIEW_MERGING.html
An example from Tom Kyte:
the goal of this parameter [OPTIMIZER_SECURE_VIEW_MERGING] is to prevent a function owned by some user B from seeing data of some other user A that is should not see. For example, …
and after some setup…
b%ORA11GR2> set autotrace on explain
b%ORA11GR2> alter system set optimizer_secure_view_merging = true;System altered.
b%ORA11GR2> select * from a.v v1 where f(y) = ‘ok to see’;
X Y
———- ——————————
1 ok to seeI see: ok to see
Execution Plan
———————————————————-
Plan hash value: 1931062764—————————————————————————
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
—————————————————————————
| 0 | SELECT STATEMENT | | 10000 | 292K| 27206 (1)| 00:05:27 |
|* 1 | VIEW | V | 10000 | 292K| 27206 (1)| 00:05:27 |
|* 2 | TABLE ACCESS FULL| T | 10000 | 292K| 27206 (1)| 00:05:27 |
—————————————————————————Predicate Information (identified by operation id):
—————————————————1 – filter(“F”(“Y”)=’ok to see’)
2 – filter(“Y_F”(“X”)=1)there it is clear that the Y_F(X)=1 is evaluated and then f(y) is
b%ORA11GR2> alter system set optimizer_secure_view_merging = false;
System altered.
b%ORA11GR2> select * from a.v v1 where f(y) = ‘ok to see’;
X Y
———- ——————————
1 ok to seeI see: ok to see
I see: NOT ok to seewhoops, my function saw data that it should not
Execution Plan
———————————————————-
Plan hash value: 1601196873————————————————————————–
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
————————————————————————–
| 0 | SELECT STATEMENT | | 100 | 3000 | 27207 (1)| 00:05:27 |
|* 1 | TABLE ACCESS FULL| T | 100 | 3000 | 27207 (1)| 00:05:27 |
————————————————————————–Predicate Information (identified by operation id):
—————————————————1 – filter(“F”(“Y”)=’ok to see’ AND “Y_F”(“X”)=1)
and now we can see the converse is true
b%ORA11GR2> alter system set optimizer_secure_view_merging = true;
System altered.
b%ORA11GR2> set autotrace off
There are also some side effects with VPD. See https://antognini.ch/2011/09/optimizer_secure_view_merging-and-vpd/
Also in some cases this parameter can result in errors.
Here is a report of such a case: https://support.esri.com/en/technical-article/000010620
Another example for a parameter that can influence result sets would be RESULT_CACHE_REMOTE_EXPIRATION
https://docs.oracle.com/cd/B28359_01/server.111/b28320/initparams208.htm#REFRN10294
The default is 0. Changeing it to a positiv integer, will allow us to get “stale” data from the cache instead of fetching a fresh value from the remote source.
fresh_mv
This 12.2 hint can make a real time materialised view to refresh itself. So if the data is stale then the hint will use the MV and somehow add the missing data to it. Without the hint we would still see old data, but with it we will see new data.
The FRESH_MV hint applies when querying a real-time materialized view. This hint instructs the optimizer to use on-query computation to fetch up-to-date data from the materialized view, even if the materialized view is stale.
A similar connected hint would be
no_rewrite/rewrite_or_error
All of those hints have the ability to change the behaviour, especially the usage of stale materialized views. Which then can give a different result.
Disclaimer
This list has no pretence to be complete. Also some of the mentioned behaviours might change in different database versions. Especially if it is not documented and not intended behaviour.
There are more such hints. I have recorded some more, but couldn’t consistently reproduce the issue. It might have been related to some bug or some other obscure circumstance, that I can’t remember.
Here is the current list for those “watch out” candidates:
- first_rows
- (no_)query_transformation
- (no_)result_cache
Result cache in combination with deterministic functions, that are not truely deterministic can easily produce wrong/different results. However the main culprit then is the wrong usage of the deterministic pragma.
And some more strange things
The SELECT clause can also influence the number of rows. And not only via distinct.
with tbl as (select 1 val from dual union all
select 2 val from dual union all
select 3 val from dual )
SELECT CASE 0
WHEN 0
THEN 'Aardvark'
WHEN SUM (val)
THEN 'Baracuda'
END AS c
FROM tbl;
Aardvark
Aardvark
Aardvark
3 rows selected.
with tbl as (select 1 val from dual union all
select 2 val from dual union all
select 3 val from dual )
SELECT CASE 6
WHEN 0
THEN 'Aardvark'
WHEN SUM (val)
THEN 'Baracuda'
END AS c
FROM tbl;
Baracuda
1 row selected.
Discovered by Frank Kulash and discussed and tested in this OTN thread
https://community.oracle.com/thread/4097998?start=15&tstart=0
svenweller 