NULLS and query plan.

From: Fred Tilly <>
Date: Sat, 12 May 2012 17:32:08 +0100 (BST)
Message-ID: <>


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.



Received on Sat May 12 2012 - 11:32:08 CDT

Original text of this message