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.
>
> You mention in another post that the query runs quickly
> when you don't have the distinct - does the plan change
> significantly in that case ?
>
>
Good news. I was able to get an even better execution plan than the rule
hint provided with a global /*+ leading(ps_empl_comp_srch4.job) */ hint.
I just wish the CBO could figure these things out on it's own.
--
To reply by email remove "_nospam"
Received on Wed Oct 05 2005 - 11:52:24 CDT
