Re: another optimizerproblem

From: Jurij Modic <jmodic_at_src.si>
Date: Mon, 11 Jan 1999 20:03:00 GMT
Message-ID: <369a58de.371585@news.arnes.si>

On Mon, 11 Jan 1999 14:58:16 +0100, Uwe Hoell <hoell_at_aeb.de> wrote:

>I have the folowing:
>(NT-Workgroupserver 7.3.3.4.0)
>
>create table test1 (a int, b int, c int);
>alter table test1 add primary key (a);
>create index testi11 on test1 (b, c);
>
>create table test2 (d int, e int, f int);
>create index testi21 on test2 (d, e);
>
>AND problems with an select:
>
>SELECT /*+ USE_NL (test2) */ b
> FROM test2, test1
> WHERE test2.d=test1.a(+)
> AND test1.b = 1234;
>

You specified the wrong table in a hint. You want the table "test1" to be the driving table of the query, otherwise RBO can't use the index "testi21". In a USE_NL hint you have to specify the driving table of the join, so your hint should have been:

.../*+ USE_NL(test1) */....

However, this still doesn't force the optimizer to use "testi2". But if you properly use the hint "INDEX" you should get the execution plan you are looking for. Here are two examples from my SQLPlus session (I created both tables and indexes with your own commands, I didn't create the second index on table2):

SQL> SELECT /*+ USE_NL (test1) INDEX(test2 testi21) */ b

  2        FROM test2, test1
  3        WHERE test2.d=test1.a(+) 
  4         AND test1.b = 1234;

Execution Plan

---

 0 SELECT STATEMENT Optimizer=RULE (Cost=41 Card=400 Bytes=20800)  1 0 NESTED LOOPS (Cost=41 Card=400 Bytes=20800)

 2    1     TABLE ACCESS (BY ROWID) OF 'TEST1' (Cost=1 Card=20 Bytes
        =780)
 3    2       INDEX (RANGE SCAN) OF 'TESTI11' (NON-UNIQUE)
 4    1     INDEX (RANGE SCAN) OF 'TESTI21' (NON-UNIQUE)

SQL> SELECT /*+ INDEX(test2 testi21) */ b
  2        FROM test2, test1
  3        WHERE test2.d=test1.a(+) 
  4         AND test1.b = 1234;

Execution Plan

---

 0 SELECT STATEMENT Optimizer=RULE (Cost=41 Card=400 Bytes=20800)  1 0 NESTED LOOPS (Cost=41 Card=400 Bytes=20800)

 2    1     TABLE ACCESS (BY ROWID) OF 'TEST1' (Cost=1 Card=20 Bytes
          =780)
 3    2       INDEX (RANGE SCAN) OF 'TESTI11' (NON-UNIQUE)
 4    1     INDEX (RANGE SCAN) OF 'TESTI21' (NON-UNIQUE)

>Why? Is this a problem of compound inexes?

As you can see, you can achive this also with compound index. And BTW, I can't see any logic in the way you use an outer join in your example!

>thanks 4 any ideas!

HTH,
Jurij Modic <jmodic_at_src.si>
Certified Oracle7 DBA (OCP)

---

The above opinions are mine and do not represent any official standpoints of my employer Received on Mon Jan 11 1999 - 14:03:00 CST