Re: astonishing(?) sql

From: Andre van Winssen \(andrew\) <andrew_at_info.nl>
Date: Mon, 21 Jan 2002 13:52:50 +0100
Message-ID: <3c4c0f22$0$223$4d4ebb8e@news.nl.uu.net>

optimizer bug?

It would help if you'd supply more details, like:

- which  version of the oracle Rdbms
- execution plan
- relevant init.ora parameters that influence the optimizer behaviour, e.g.

  star_transofrmation_enabled , hash_join_enabled, optimizer_index_caching,   optimizer_index_cost_adj, optimizer_max_permutations,optimizer_mode   etcetera
- is the result the same after doing 'alter session set optimizer_goal=rule' ?

If the last is not true then you are probably hitting an optimizer bug for which you should submit a testcase to Oracle support, so they can give you a fix
or log a new bug.

Regards,

"Marcin Buchwald" <Marcin.Buchwald_at_agora.pl> wrote in message news:3C4C022B.76E0B2A6_at_agora.pl...
> Let promocja and kategoryzacja by any tables with master detail
> relationship.
> The query below completely ignores the last where clause.
> It produces rows with value of the third column different from 216.
>
> select t.rowid, t.detid, t.masnic
> from (select *
> from (select k.xx detid, p.xx masnic
> from promocja p, kategoryzacja k
> where p.kat_xx=k.xx
> ) order by masnic ) t
> where t.masnic=216
> /
>
> Can You explain this?
>
> Regards,
>
> Marcin
>
Received on Mon Jan 21 2002 - 06:52:50 CST