NULLS and query plan.
- From: Fred Tilly <ftilly@xxxxxxxxxxxxxx>
- To: "oracle-l@xxxxxxxxxxxxx" <oracle-l@xxxxxxxxxxxxx>
- Date: Sat, 12 May 2012 17:32:08 +0100 (BST)
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
--
//www.freelists.org/webpage/oracle-l
Other related posts: