tag:blogger.com,1999:blog-7302956329008495023.post40006772335368456..comments2022-01-04T06:37:33.013+00:00Comments on Databases and Performance: NULLs and IndexesJohn Bradyhttp://www.blogger.com/profile/08719865814813032071noreply@blogger.comBlogger2125tag:blogger.com,1999:blog-7302956329008495023.post-90858141695672898942012-06-14T20:13:35.069+01:002012-06-14T20:13:35.069+01:00The previous poster shows that indexes CAN be used...The previous poster shows that indexes CAN be used to find NULLs in some circumstances. I'll do it here as well using your tables.<br /><br />SQL> create index ix1_transaction_n_loc on transaction (ref_id_n,location_id);<br /><br />Index created.<br /><br />SQL> select count(*) from transaction where ref_id_n is null;<br /><br />Execution Plan<br />----------------------------------------------------------<br />Plan hash value: 1786339511<br /><br />-------------------------------------------------------------------------------------------<br />| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |<br />-------------------------------------------------------------------------------------------<br />| 0 | SELECT STATEMENT | | 1 | 4 | 3 (0)| 00:00:01 |<br />| 1 | SORT AGGREGATE | | 1 | 4 | | |<br />|* 2 | INDEX RANGE SCAN| IX1_TRANSACTION_N_LOC | 100 | 400 | 3 (0)| 00:00:01 |<br />-------------------------------------------------------------------------------------------<br /><br />Predicate Information (identified by operation id):<br />---------------------------------------------------<br /><br /> 2 - access("REF_ID_N" IS NULL)MrTreehttps://www.blogger.com/profile/17532720592677125072noreply@blogger.comtag:blogger.com,1999:blog-7302956329008495023.post-70264126462686452422012-06-14T15:40:17.450+01:002012-06-14T15:40:17.450+01:00PLAN_TABLE_OUTPUT
--------------------------...PLAN_TABLE_OUTPUT<br /> ---------------------------------------------------------------------------<br /> | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |<br /> ---------------------------------------------------------------------------<br /> | 0 | SELECT STATEMENT | | | | 1 (100)| |<br /> |* 1 | INDEX RANGE SCAN| T1_IDX | 4 | 20 | 1 (0)| 00:00:15 |<br /> ---------------------------------------------------------------------------<br /> <br /> Predicate Information (identified by operation id):<br /> ---------------------------------------------------<br /> <br /> 1 - access("N1">2 AND "N2" IS NULL)<br /> filter("N2" IS NULL)Anonymousnoreply@blogger.com