Re: NULLS and query plan.

From: Carlos Sierra <carlos.sierra.usa_at_gmail.com>
Date: Sat, 12 May 2012 12:39:14 -0400
Message-ID: <CAGzKQQcbn82-27L=yn=E6N-DAfFCAY-_8Gs=b2rSKB6PL=fvXw_at_mail.gmail.com>



Fred,
I offer to help. Just get me a SQLT XTRXEC (MOS 215187.1) from both cases (from where it performs well and from where it does not)

Carlos

On Sat, May 12, 2012 at 12:32 PM, Fred Tilly <ftilly_at_btinternet.com> wrote:

> All,
>
> Looking at an issue with a particular sql query which runs fine on other
> customer sites but one customer is having a problem with it. On most
> customer sites when it joins to a particular table it uses an index, but on
> this site it just does a hash join to the table. The major difference is
> not on the volume of data between the sites, but the customer with the bad
> performance has more NULL values in the join column, in fact nearly half
> the rows in the table have nulls on the join column.
>
>
> The table has 2128094 rows, and the join column has 1132709 distinct
> values and 995385 nulls.
>
> The table feeding the join is passing in approximately 5000 rows and its a
> one to one match so there would be 5000 rows coming out of the join.
>
> So would the fact that half the rows in the table have null values on the
> join column lead the optimizer to a hash join to the table rather than
> using an index.
>
> Thanks
>
> Fred
> --
> http://www.freelists.org/webpage/oracle-l
>
>
>

-- 
Cheers -- Carlos Sierra
http://carlos-sierra.net/


--
http://www.freelists.org/webpage/oracle-l
Received on Sat May 12 2012 - 11:39:14 CDT

Original text of this message