Re: Number 1 gripe about CBO: 0 <Cardinality< 1 (?????)

On Apr 4, 5:16 pm, sybra..._at_hccnet.nl wrote:
> On 4 Apr 2007 14:58:47 -0700, "Kevin Kirkpatrick"
>
> <kvnkrkpt..._at_gmail.com> wrote:
> >Not trying to pick on you Sybrand, but given the sample database and
> >the desired query, what steps should be taken to avoid the atrocious
> >plan?
>
> The plan isn't atrocious. The plan is to be expected.

Are you even able to read/understand execution plans? Your response that the plan isn't atrocious indicates you are not so enabled. For a good plan, see what happens when the CBO is prevented from using a cardinality of < 1 on the kk_small table with a hint:

/*+ cardinality(kk_small,1) */

> You
> - force a full table scan on kk_small

What do you mean by "force"? No hints specified; but aside from that, why would I want anything but a full scan on kk_small? Your comment is nonsensical.

> - have no join clause between kk_small and kk_big, yet you list them
> in this order, which is obeyed by CBO.
>

Did I just step into the twighlight zone? Syband, this comment makes so little sense that I'm literally stumped as to how to reply. Do you know even know what the CBO is?

> What else do you expect?
>

Certainly not a response from a "Senior Oracle DBA" who thinks that the CBO chooses join order based on the order in which tables are listed in a query...

> What you see is what you get!
> You shouldn't bash Oracle for this!
> You have lousy SQL so you get a lousy plan!
>
> --
>
> Sybrand Bakker
> Senior Oracle DBA
Received on Wed Apr 04 2007 - 19:16:34 CDT