Using SQL hints - Sort merge

[chrisgarvey]

Hi forum,

I'm trying to force oracle to use the sort merge using hints.

Can anyone suggest why the below code is working correctly?
Please see the explain plan.

Regards, Chris.

set autotrace on
timing start
select /*+ use_merge(e) */ e.fk, d.fk
FROM girl d, boy e
WHERE e.fk = d.fk;
timing stop
spool off
set termout on

SQL> timing stop
Elapsed: 00:00:00.42
SQL> spool off
not spooling currently
SQL> set termout on
SQL> select * from girl WHERE fk = 1;

PK NAME FK
---------- -------------------- ----------
1 Hayley 1
1 julie 1
1 sian 1


Execution Plan
----------------------------------------------------------

----------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost |
----------------------------------------------------------
| 0 | SELECT STATEMENT | | 3 | 114 | 3 |
| 1 | TABLE ACCESS FULL| GIRL | 3 | 114 | 3 |
----------------------------------------------------------

Note
-----
- 'PLAN_TABLE' is old version


Statistics
----------------------------------------------------------
5 recursive calls
0 db block gets
16 consistent gets
0 physical reads
0 redo size
573 bytes sent via SQL*Net to client
381 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
3 rows processed

 

[poehee]

First of all.

You can't force Oracle to do things it doesn't want to.


First you should ask yourself a few questions:

* Are tables/indexes properly analyzed
* Is Oracle choosing the wrong path (if no, stop)
* If yes, what should it do
* How can I help Oracle to process more efficiently

Problably your tables are small (in terms of rows), Oracle ignores often hints/indexes of small tables. Full scan will do.