Re: another optimizerproblem

> 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:

I thought, that I have to put the inner table in an USE_NL-hint: (since Oracle Server Tuning Manual says: The USE_NL hint causes Oracle to join each specified table to another row source with a nested loops join using the specified table as the inner table. The syntax of the USE_NL hint is...) So my hint should be right... or not?

And I've checked out the same now with 'Oracle8 Release 8.0.4.0.0 - Production' (It was not instelled yet here). In this release the optimizer take's indexi21! (yea:)) like in your plans.
But only if I force him with an hint (but this can be because both tables are empty - fulltablescan is here the best choise). So the problem is a patched in newer than our '7.3.3.4.0' release and we (our customers) have to wait for better times (releases). The current workarround is a aditional index (testi22).

> 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!

The example is only a simplified version of our 'real-appl-word', where it selects some positions of an consignment. Received on Tue Jan 12 1999 - 05:02:54 CST