Re: another optimizerproblem

On 11-Jan-99 14:58:16 Uwe Hoell 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;

>The plan is (we have to use the rulebased optimizer)

>SELECT STATEMENT Optimizer=RULE (Cost=2 Card=1 Bytes=52)
> NESTED LOOPS (Cost=2 Card=1 Bytes=52)
> TABLE ACCESS (BY ROWID) OF 'TEST1' (Cost=1 Card=1 Bytes=39)
> INDEX (RANGE SCAN) OF 'TESTI11' (NON-UNIQUE)
> TABLE ACCESS (FULL) OF 'TEST2'
[...]

>Why? Is this a problem of compound inexes?

>thanks 4 any ideas!

I observed similar things happen, when i created additional indexes on analyzed tables. Try analyzing the tables. This helped in my case.

Hope that helps,
Lothar

--

Lothar Armbrüster       | lothar.armbruester_at_rheingau.netsurf.de
Schulstr. 12            | lothar.armbruester_at_t-online.de
D-65375 Oestrich-Winkel |