Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I have a query that looks like

SELECT *
FROM table0
WHERE id IN (SELECT id FROM table1 JOIN table2)

Oracle is choosing to join table0 with the result of (table1 x table2) using nested loops and takes hours. I'm trying to figure out whether I can hint it to use HASH instead, but don't understand which hint and where to use. I tried sticking HASH_SJ and HASH_AJ in various places and it didn't help...

share|improve this question
    
Did you try EXISTS instead of IN ? –  a1ex07 Jun 20 '12 at 23:17
    
@a1ex07 I didn't. What is the difference? –  MK01 Jun 20 '12 at 23:57
    
Can you provide a simple test case with CREATE statements so that we can reproduce your problem? –  Vincent Malgrat Jun 21 '12 at 9:27
    
@VincentMalgrat not really. As a matter of fact the same query results in "correct" execution plan on another Oracle instance... –  MK01 Jun 21 '12 at 13:04

2 Answers 2

You can use hints with subqueries, after having them qualified with the QB_NAME hint for example.

In this case however a simple USE_HASH hint should be ok. Here's my setup:

create table t0 (id integer primary key, pad char(500));
insert into t0 select rownum, 'x' from dual connect by level <= 2000;

create table t1 (t1_id integer primary key, id integer references t0, pad char(500));
insert into t1 select rownum, round(rownum/2), 'x' from dual connect by level <= 100;
create index i0 on t1(id);

create table t2 (t2_id integer primary key, t1_id integer references t1, pad char(500));
insert into t2 select rownum, round(rownum/2), 'x' from dual connect by level <= 100;
create index i1 on t2(t1_id);

exec dbms_stats.gather_table_stats(user, 'T0', cascade=>true);
exec dbms_stats.gather_table_stats(user, 'T1', cascade=>true);
exec dbms_stats.gather_table_stats(user, 'T2', cascade=>true);

With this setup the following query runs a NESTED LOOP join:

SQL> EXPLAIN PLAN FOR
  2  SELECT * FROM t0
  3   WHERE id IN (SELECT id
  4                  FROM t1 JOIN t2 ON t1.t1_id = t2.t1_id
  5                 WHERE t2.pad LIKE :x);

Explained.

SQL> SELECT * FROM TABLE(DBMS_XPLAN.display);

-------------------------------------------------------------------------------
| Id  | Operation                    |  Name          | Rows  | Bytes | Cost  |
-------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |                |     5 |  2585 |    37 |
|   1 |  NESTED LOOPS                |                |     5 |  2585 |    37 |
|   2 |   VIEW                       | VW_NSO_1       |     5 |    65 |     7 |
|   3 |    SORT UNIQUE               |                |     5 |  2550 |       |
|   4 |     HASH JOIN                |                |     5 |  2550 |     7 |
|   5 |      TABLE ACCESS FULL       | T1             |   100 |   600 |     3 |
|   6 |      TABLE ACCESS FULL       | T2             |     5 |  2520 |     3 |
|   7 |   TABLE ACCESS BY INDEX ROWID| T0             |     1 |   504 |     1 |
|   8 |    INDEX UNIQUE SCAN         | SYS_C00746321  |     1 |       |       |
-------------------------------------------------------------------------------

With the USE_HASH hint:

SQL> EXPLAIN PLAN FOR
  2  SELECT /*+ USE_HASH (t0) */ * FROM t0
  3   WHERE id IN (SELECT id
  4                  FROM t1 JOIN t2 ON t1.t1_id = t2.t1_id
  5                 WHERE t2.pad LIKE :x);

Explained.

SQL> SELECT * FROM TABLE(DBMS_XPLAN.display);

--------------------------------------------------------------------
| Id  | Operation            |  Name       | Rows  | Bytes | Cost  |
--------------------------------------------------------------------
|   0 | SELECT STATEMENT     |             |     5 |  2585 |    44 |
|   1 |  HASH JOIN SEMI      |             |     5 |  2585 |    44 |
|   2 |   TABLE ACCESS FULL  | T0          |  2000 |   984K|    23 |
|   3 |   VIEW               | VW_NSO_1    |     5 |    65 |     7 |
|   4 |    HASH JOIN         |             |     5 |  2550 |     7 |
|   5 |     TABLE ACCESS FULL| T1          |   100 |   600 |     3 |
|   6 |     TABLE ACCESS FULL| T2          |     5 |  2520 |     3 |
--------------------------------------------------------------------
share|improve this answer
    
Thanks. QB_NAME sounds interesting. It doesn't work for me, but I suspect now that something is screwed up in the Oracle config because same query on the same tables on another server works "correctly" -- i.e. doesn't go for NL. –  MK01 Jun 21 '12 at 13:06
2  
There are so many things that can change between two instances (and sometimes even in the same instance) with the same logical data that can produce different plans. Don't assume that your install is screwed because two different servers produce different plans. The statistics for example could be just a little bit different and thus pushing one instance to run a nested loop while the other runs a hash join. This is perfectly normal. –  Vincent Malgrat Jun 21 '12 at 13:12
1  
Most of the time, the optimizer will make the right choice. Sometimes it doesn't and in that case one of the method available is hints. Once again if you provide us with a simple test case I can show you how hints work with or without subqueries. –  Vincent Malgrat Jun 21 '12 at 13:14

I'd try using a WITH clause in combination with the MATERIALIZE hint to force materialization of the subquery first. Something like this:

WITH x as (select /*+ MATERIALIZE */
           from [your subquery join])
SELECT *
FROM table0, x
WHERE table0.id =x.id
share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.