- 3,728,127 Users
- 2,245,554 Discussions
- 7,853,348 Comments
- 17.8K All Categories
- Industry Applications
- 3.2K Intelligent Advisor
- 1.7K On-Premises Infrastructure
- 594 Analytics Software
- 51 Application Development Software
- 1.8K Cloud Platform
- 700.5K Database Software
- 17.5K Enterprise Manager
- 22 Hardware
- 276 Infrastructure Software
- 142 Integration
- 75 Security Software
Hash Indexes and NULL Comparisons
We are currently introducing more indexes into our databases as instructed by the index advisor, but have noticed a change in behaviour when comparing null values.
Prior to the index the following query would return 0 results:
select * from network_set where priority3_network_element not in (select id from network_element);0 rows found.
(I know this should be enforced by a foreign key, but we currently have incompatible data types which we're trying to resolve first)
Adding the following index results in a change of behaviour where the above query will then return any entries where priority3_network_element is null - adding a is not null check first achieves the correct result:
create hash index HASH_NETWORK_ELEMENT_ID on NETWORK_ELEMENT(ID);select * from network_set where priority3_network_element not in (select id from network_element);122 rows found....select * from network_set where priority3_network_element is not null and priority3_network_element not in (select id from network_element);0 rows found.
What is the explanation for the change in query behaviour here? Is there any other potential cases that we should be aware of that also changes the behaviour of queries when adding indexes?