Hi Experts,
I noticed strange optimizer behavior trying to apply global hint to my SQL query.
It looks global hint is ignored when SQL is using ANSI-style syntax for joins, see details below.
Test case setup:
create table t1
(
id number(10),
a number(10)
);
create index t1_i on t1(id);
create table t2
(
id number(10),
b number(10)
);
create or replace view vx as
select
t1.*
from t1, t2
where t1.id = t2.id;
Exec plan without hint being applied - note, index on T1 is used.
select
*
from vx, t2
where vx.id = t2.id;
-------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 65 | 4 (0)| 00:00:01 |
| 1 | NESTED LOOPS | | 1 | 65 | 4 (0)| 00:00:01 |
| 2 | NESTED LOOPS | | 1 | 65 | 4 (0)| 00:00:01 |
| 3 | MERGE JOIN CARTESIAN | | 1 | 39 | 4 (0)| 00:00:01 |
| 4 | TABLE ACCESS FULL | T2 | 1 | 13 | 2 (0)| 00:00:01 |
| 5 | BUFFER SORT | | 1 | 26 | 2 (0)| 00:00:01 |
| 6 | TABLE ACCESS FULL | T2 | 1 | 26 | 2 (0)| 00:00:01 |
|* 7 | INDEX RANGE SCAN | T1_I | 1 | | 0 (0)| 00:00:01 |
| 8 | TABLE ACCESS BY INDEX ROWID| T1 | 1 | 26 | 0 (0)| 00:00:01 |
-------------------------------------------------------------------------------------
Let's apply global hint to force full-scan on T1. Note, non-ANSI join style is used here.
select /*+ full(vx.t1) */
*
from vx, t2
where vx.id = t2.id;
------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 65 | 6 (0)| 00:00:01 |
|* 1 | HASH JOIN | | 1 | 65 | 6 (0)| 00:00:01 |
| 2 | MERGE JOIN CARTESIAN| | 1 | 39 | 4 (0)| 00:00:01 |
| 3 | TABLE ACCESS FULL | T2 | 1 | 13 | 2 (0)| 00:00:01 |
| 4 | BUFFER SORT | | 1 | 26 | 2 (0)| 00:00:01 |
| 5 | TABLE ACCESS FULL | T2 | 1 | 26 | 2 (0)| 00:00:01 |
| 6 | TABLE ACCESS FULL | T1 | 1 | 26 | 2 (0)| 00:00:01 |
------------------------------------------------------------------------------
Success, hint works as expected!
Let's change join syntax to ANSI.
select /*+ full(vx.t1) */
*
from vx inner join t2 on vx.id = t2.id;
-------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 65 | 4 (0)| 00:00:01 |
| 1 | NESTED LOOPS | | 1 | 65 | 4 (0)| 00:00:01 |
| 2 | NESTED LOOPS | | 1 | 65 | 4 (0)| 00:00:01 |
| 3 | MERGE JOIN CARTESIAN | | 1 | 39 | 4 (0)| 00:00:01 |
| 4 | TABLE ACCESS FULL | T2 | 1 | 13 | 2 (0)| 00:00:01 |
| 5 | BUFFER SORT | | 1 | 26 | 2 (0)| 00:00:01 |
| 6 | TABLE ACCESS FULL | T2 | 1 | 26 | 2 (0)| 00:00:01 |
|* 7 | INDEX RANGE SCAN | T1_I | 1 | | 0 (0)| 00:00:01 |
| 8 | TABLE ACCESS BY INDEX ROWID| T1 | 1 | 26 | 0 (0)| 00:00:01 |
-------------------------------------------------------------------------------------
Bad luck, hint is ignored now.
This looks weird to me.
Google and Oracle Support did not help - I could not find anything related.
I have tried this on different db versions, problem remains:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit
If you know any details regarding this issue (e.g. it is known and expected behavior, bug, documented feature, resolution etc) - please share.
I know, there are workarounds present (e.g. using query block qualifier helps to solve it), though I'd to check if direct solution exists.
Thank you in advance!