Re: Eliminating cartesian merge

From: Chuck <skilover_nospam_at_softhome.net>
Date: Wed, 05 Oct 2005 12:07:25 -0400
Message-ID: <1128524186.8f966ec28a4f96ff0ac50ff411b3926e@bubbanews>

Jonathan Lewis wrote:
> "Chuck" <skilover_nospam_at_softhome.net> wrote in message
> news:1127918214.a86aa150f9acb770b1c7365331b2c2f4_at_bubbanews...
>

>>Query_rewrite_enabled is already set to true. Here's the execution plan
>>with the RULE hint. You'll probably need to turn of line wrapping for it
>>to make any sense.
>>
>>----------------------------------------------------------------------------------------
>>| Id  | Operation                         |  Name              | Rows  | 
>>Bytes | Cost  |
>>----------------------------------------------------------------------------------------

>
>
>
> Chuck,
>
> Comparing the two plans, the most significant
> thing looks like the subquery pushing that the CBO
> has used to apply the subqueries in the view
> ps_person_name as early as possible. It's possible
> that this has resulted in a silly cardinality at that
> point in the plan.
>
> You might want to test the no_push_subq hint,
> either in the select of the main query, or in the select
> of the query definition of the view ps_person_name
> to see if this gets you back to the RBO path.

Didn't change the plan at all. The CBO can sometimes be a real PITA. Sometimes it just seems to make up it's mind that it's going to do things it's way and there's nothing you can do about it. I don't understand why you would *ever* want to choose a Cartesian product unless there is no join between two row sources. I think they need an instance level parameter along the lines of enable_cartesian_merge = FALSE.

What's frustrating about this is Oracle's own documentation states that a Cartesian product will only be chosen if there is no join criteria between two row sources. That's simply not true.

-- 
To reply by email remove "_nospam"

Received on Wed Oct 05 2005 - 11:07:25 CDT