Ignoring Hints – 2

Here’s a little puzzle that someone sent to me a couple of days ago – it’s a case where the optimizer seems to be ignoring a hint.

create table t1
as
select
	rownum id,
	object_name name
from
	all_objects
where
	rownum <= 1000
;

create table t2
as
select
	mod(rownum,20)+1 id
from
	all_objects
where
	rownum <= 1000
;

alter table t1 add constraint t1_pk primary key(id);
create index ind_t2 on t2(id);

-- gather stats at this point

set autotrace traceonly explain

select
	/*+ ordered use_hash(t2) */ *
from
	t1, t2
where
	t1.id = t2.id
and	t1.id = 1
;

select
	/*+ ordered use_hash(t2) */ *
from
	t1, t2
where
	t1.id = t2.id
and	t2.id = 1
;

set autotrace off

Notice that the only difference between these two queries is the filter predicate: t1.id = 1 in the first statement and t2.id = 1 in the second. Given the fact that the join predicate is then: t1.id = t2.id, these two queries are logically identical. So why does one of them “ignore” the use_hash() hint and do a nested loop ? Remember: the optimizer does not ignore hints (with the usual caveats about legality, spelling, syntax and bugs).

Here are the two execution plans:

------------------------------------------------+-----------------------------------+
| Id  | Operation                     | Name    | Rows  | Bytes | Cost  | Time      |
------------------------------------------------+-----------------------------------+
| 0   | SELECT STATEMENT              |         |       |       |     3 |           |
| 1   |  NESTED LOOPS                 |         |    50 |  1050 |     3 |  00:00:01 |
| 2   |   TABLE ACCESS BY INDEX ROWID | T1      |     1 |    18 |     2 |  00:00:01 |
| 3   |    INDEX UNIQUE SCAN          | T1_PK   |     1 |       |     1 |  00:00:01 |
| 4   |   INDEX RANGE SCAN            | IND_T2  |    50 |   150 |     1 |  00:00:01 |
------------------------------------------------+-----------------------------------+
Predicate Information:
----------------------
3 - access("T1"."ID"=1)
4 - access("T2"."ID"=1)

------------------------------------------------+-----------------------------------+
| Id  | Operation                     | Name    | Rows  | Bytes | Cost  | Time      |
------------------------------------------------+-----------------------------------+
| 0   | SELECT STATEMENT              |         |       |       |     4 |           |
| 1   |  HASH JOIN                    |         |    50 |  1050 |     4 |  00:00:01 |
| 2   |   TABLE ACCESS BY INDEX ROWID | T1      |     1 |    18 |     2 |  00:00:01 |
| 3   |    INDEX UNIQUE SCAN          | T1_PK   |     1 |       |     1 |  00:00:01 |
| 4   |   INDEX RANGE SCAN            | IND_T2  |    50 |   150 |     1 |  00:00:01 |
------------------------------------------------+-----------------------------------+
Predicate Information:
----------------------
1 - access("T1"."ID"="T2"."ID")
3 - access("T1"."ID"=1)
4 - access("T2"."ID"=1)

Have you spotted the critical clue ? It’s in the predicate sections.

Notice how the first plan has two predicates and the second plan has three – and the “missing” predicate is the join predicate. You’re looking at an example of transitive closure:  if t1.id1 = 1 and t2.id1 = t1.id1 then t2.id1 = 1 So Oracle has taken the predicate from one table and copied to the other table – but in just one of the cases it has also eliminated the join. Because there is no longer a join predicate it’s not possible for the optimizer to do a hash join (in earlier versions of Oracle you might have seen a cartesian merge join at this point) so the use_hash() hint has to be ignored – it’s out of context.

The question, though, is why are the two cases not symmetrical – the queries are logically equivalent (and to the human eye very obviously so) so they should have been transformed in the same way. My best guess is that the optimzer checks the significance of the existing predicate as it generates the  new predicate – and if it sees that it’s guaranteed to supply a single row it discards the join condition. But when it’s working the other way round (i.e. starting with the non-unique predicate on t2) it doesn’t check the significance of the predicates that it has generated.

It’s an odd little detail, but I can’t think of  a case where it will have any noticeable impact on performance – though someone may prove me wrong on that.

Footnote:

I’ve been reminded by a couple of the comments that I should have mentioned that I ran this example on 10.2.0.3. Others have now run it on other versions of Oracle. If you go back to 9i you’ll see that the join predicate disappears in both cases and 9i will do a cartesian merge join).

[Further reading on “ignoring hints”]