Null is a special type of value in the Boolean Type (true, false, unknown) of the relational database system. It is usually called unknown or null, which is unknown and uncertain. Because
There are countless possibilities for null, so the null value is not equal to the null value, so the operations related to the null value are also null values. Based on this feature
The tree index causes is null/is not null to leave the index. The following describes the execution plan of the null value and index and the null column of the index, and how to make the null value take the index.
Note: This article only discusses the null value on the B-tree index, and the bitmap index is not in this range.
I. Relationship between null values and Indexes
Scott @ orcl> Create Table T1 (ID number, Val varchar2 (1 )); </P> <p> --> create a unique index for table t1 <br/> Scott @ orcl> create unique index I _t1_id on T1 (ID ); </P> <p> Scott @ orcl> insert into T1 select null, 'y' from dual; </P> <p> Scott @ orcl> insert into T1 select null, 'N' from dual; </P> <p> --> as shown in the preceding operation, although the column ID has a unique index, the null value is not equal to any null value, therefore, <br/> Scott @ orcl> commit; </P> <p> --> Add a unique composite index to the table again, create unique Based on ID column and Val column <br/> Scott @ orcl> Index I _t1_id_val on T1 (ID, Val); </P> <p> index created. </P> <p> --> An error occurred while inserting null or 'N' records. A message indicating a violation of the uniqueness constraint is displayed. <br/> Scott @ orcl> insert into T1 select null, 'N' from dual; <br/> insert into T1 select null, 'n' from dual <br/> * <br/> error at line 1: ORA-00001: unique constraint (Scott. I _t1_id_val) violated </P> <p> --> inserting null, 'y' records also failed, the system prompts that the uniqueness constraint is violated. <br/> Scott @ orcl> insert into T1 select null, 'y' from dual; <br/> insert T1 select null, 'y' from dual <br/> * <br/> error at line 1: <br/> ORA-00001: unique constraint (Scott. I _t1_id_val) violated </P> <p> --> two null values are inserted successfully. <br/> Scott @ orcl> insert into T1 select null, null from dual; </P> <p> 1 row created. </P> <p> Scott @ orcl> insert into T1 select null, null from dual; </P> <p> 1 row created. </P> <p> Scott @ orcl> insert into T1 select null, 'A' from dual; </P> <p> 1 row created. </P> <p> Scott @ o RCL> commit; </P> <p> commit complete. </P> <p> Scott @ orcl> set null unknown; <br/> Scott @ orcl> select * from T1; </P> <p> ID Val <br/> ---------- ---------------------------- <br/> unknown Y <br/> unknown n <br/> unknown <br/> unknown a </P> <p> Scott @ orcl> exec dbms_stats.gather_table_stats ('Scott ', 't1', cascade => true); </P> <p> Scott @ orcl> select index_name, index_type, blevel, leaf_bloc KS, num_rows, status, distinct_keys <br/> 2 from user_indexes where table_name = 't1 '; </P> <p> index_name index_type blevel leaf_blocks num_rows status failed <br/> else ------------ ---------- ----------- ---------- ------------- <br/> I _t1_id normal 0 0 0 0 valid 0 <br/> I _t1_id_val normal 0 1 3 valid 3 </P> <p> --> as shown in the preceding figure, <br/> --> based on the unique index of a single column, the null value can be inserted multiple times, but the index does not store the null value. <Br/> --> although rows with all null values can be inserted multiple times, duplicate rows with not all null values cannot be inserted (note, this restriction does not exist for non-unique composite indexes. It is not demonstrated here ). <Br/> --> multi-Column Composite indexes are not stored for all null values. In the above case, although five records are inserted, only three records are stored in the composite index. <Br/> --> NOTE: For the uniqueness constraint, the null value is not equal to the null value, and the same (null, null) is not the same as (null, null ), therefore, the preceding two null values can be inserted.Ii. null value and execution plan
Scott @ orcl> set autot trace exp; <br/> Scott @ orcl> select * from T1 where ID is null; </P> <p> execution plan <br/> ---------------------------------------------------------- <br/> plan hash value: 3617692013 </P> <p> bytes <br/> | ID | operation | Name | rows | bytes | cost (% CPU) | time | <br/> ---------------------------------------------- ---------------------------- <Br/> | 0 | SELECT statement | 5 | 5 | 3 (0) | 00:00:01 | <br/> | * 1 | table access full | T1 | 5 | 5 | 3 (0) | 00:00:01 | <br/> -------------------------------------------------------------------------- </P> <p> predicate information (identified by Operation ID ): <br/> ------------------------------------------------- </P> <p> 1-filter ("ID" is null) </P> <p> --> as shown in the test above, due to null The value is not stored. Therefore, when ID is null is used as the predicate, full table scan </P> <p> Scott @ orcl> select * from T1 where ID is not null; </P> <p> execution plan <br/> ---------------------------------------------------------- <br/> plan hash value: 796913935 </P> <p> bytes <br/> | ID | operation | Name | rows | bytes | cost (% CPU) | time | <br/> ------------------------- ------------------------------------------------------------ <Br/> | 0 | SELECT statement | 1 | 1 | 0 (0) | 00:00:01 | <br/> | 1 | table access by index rowid | T1 | 1 | 1 | 0 (0) | 00:00:01 | <br/> | * 2 | index full scan | I _t1_id | 1 | 0 (0) | 00:00:01 | <br/> identified </P> <p> predicate information (identified by operatio N id): <br/> --------------------------------------------------- </P> <p> 2-filter ("ID" is not null) </P> <p> --> as shown in the test above, although all values in the ID column of the current table are null, columns with IDs inserted in subsequent records not null are excluded. <Br/> --> therefore, when ID is not null is used as the predicate, full index scan is performed in the execution plan. </P> <p> --> let's take a look at the composite index situation <br/> Scott @ orcl> select * from T1 where Val is null; </P> <p> execution plan <br/> ---------------------------------------------------------- <br/> plan hash value: 3617692013 </P> <p> bytes <br/> | ID | operation | Name | rows | bytes | cost (% CPU) | time | <br/> ------------------------------------------------------ -------------------- <Br/> | 0 | SELECT statement | 2 | 2 | 3 (0) | 00:00:01 | <br/> | * 1 | table access full | T1 | 2 | 2 | 3 (0) | 00:00:01 | <br/> -------------------------------------------------------------------------- </P> <p> predicate information (identified by Operation ID ): <br/> ------------------------------------------------- </P> <p> 1-filter ("Val" is null) </P> <p> Scott @ orcl> select * from T1 where Val is not null; </P> <p> execution plan <br/> -------------------------------------------------------- <br/> plan hash value: 1931510411 </P> <p> ------------------------------------------------------------------------------ <br/> | ID | operation | Name | rows | bytes | cost (% CPU) | time | <br/> -------------------------------------------------------------------------------- <br/> | 0 | selec T statement | 3 | 3 | 1 (0) | 00:00:01 | <br/> | * 1 | index full scan | I _t1_id_val | 3 | 3 | 1 (0) | 00:00:01 | <br/> -------------------------------------------------------------------------------- </P> <p> predicate information (identified by Operation ID ): <br/> ----------------------------------------------- </P> <p> 1-filter ("Val" is not null) </P> <p> --> In the case of compound unique indexes, when a single column and non-leading column predicates are used, is null and is n are used. Ot null is equivalent to a single column unique index. <Br/> --> that is, the same principle is true. Val is null performs full table scan, while Val is not null performs index. Because null values are not stored. </P> <p> --> let's take a look at the situation where both columns are used as predicates <br/> Scott @ orcl> select * from T1 where ID is null and Val is not null; </P> <p> execution plan <br/> ---------------------------------------------------------- <br/> plan hash value: 1040510552 </P> <p> bytes <br/> | ID | operation | Name | rows | bytes | cost (% CPU) | time | <br/> --------------------------- --------------------------------------------------- <Br/> | 0 | SELECT statement | 3 | 3 | 1 (0) | 00:00:01 | <br/> | * 1 | index range scan | I _t1_id_val | 3 | 3 | 1 (0) | 00:00:01 | <br/> identifier </P> <p> predicate information (identified by Operation ID): <br/> ------------------------------------------------- </P> <p> 1-Access ("ID" is null) <br/> filter ("Val" is not null) </P> <p> --> as shown in the test above, although both predicates have indexes, one is a single column unique index and the other is a compound unique index. Oracle selects the composite index I _t1_id_val. </P> <p> Scott @ orcl> select * from T1 where ID is not null and Val is null; </P> <p> execution plan <br/> ---------------------------------------------------------- <br/> plan hash value: 796913935 </P> <p> bytes <br/> | ID | operation | Name | rows | bytes | cost (% CPU) | time | <br/> -------------------------------------------- ----------------------------------------- <Br/> | 0 | SELECT statement | 1 | 1 | 0 (0) | 00:00:01 | <br/> | * 1 | table access by index rowid | T1 | 1 | 1 | 0 (0) | 00:00:01 | <br/> | * 2 | index full scan | I _t1_id | 1 | 0 (0) | 00:00:01 | <br/> identified </P> <p> predicate information (identified by Operation ID): <br/> ------ ------------------------------------------- </P> <p> 1-filter ("Val" is null) <br/> 2-filter ("ID" is not null) </P> <p> --> In the same case, the predicate order is the same as that defined in the composite index, except that the first predicate is ID is not null, the second predicate is Val is null. <Br/> --> at this time, Oracle selects the unique index I _t1_id in a single column. <br/> --> if you see this, you do not know whether the column is not null, the index of the column is used. The reason is that the index does not store null values. <Br/> --> the execution plan is similar for different combinations of the inverted ID column, Val column, and ID column, and Val column, null or not null.Iii. Use is null to take the index
Scott @ orcl> set autot off; <br/> -- delete the null value record of the original table <br/> Scott @ orcl> Delete from T1 where Val not in ('y ', 'N') or Val is null; </P> <p> 3 rows deleted. </P> <p> Scott @ orcl> Update T1 Set ID = 1 where val = 'y'; </P> <p> 1 row updated. </P> <p> Scott @ orcl> Update T1 Set ID = 2 where val = 'n'; </P> <p> 1 row updated. </P> <p> Scott @ orcl> commit; </P> <p> commit complete. </P> <p> --> Update the original record <br/> Scott @ orcl> select * from T1; </P> <p> ID Val <br/> ---------- ---------------------------- <br/> 1 y <br/> 2 n </P> <p> Scott @ orcl> exec dbms_stats.gather_table_stats ('Scott ', 't1', cascade => true); </P> <p> PL/SQL procedure successfully completed. </P> <p> --> modify the table column ID to make it have the not null constraint <br/> Scott @ orcl> alter table T1 modify (id not null ); </P> <p> table altered. </P> <p> Scott @ orcl> set autot trace exp; <br/> Scott @ orcl> select * from T1 where ID is null; </P> <p> execution plan <br/> ---------------------------------------------------------- <br/> plan hash value: 3160894736 </P> <p> ------------------------------------------------------------------------------ <br/> | ID | operation | Name | rows | bytes | cost (% CPU) | time | <br/> -------------------------------------------------------------------------------- <br/> | 0 | SELECT statement | 1 | 5 | 0 (0) | <br/> | * 1 | filter | <br/> | 2 | index full scan | I _t1_id_val | 2 | 10 | 1 (0) | 00:00:01 | <br/> -------------------------------------------------------------------------------- </P> <p> predicate information (identified by Operation ID ): <br/> ----------------------------------------------- </P> <p> 1-filter (null is not null) </P> <p> --> as shown in the preceding execution plan, when the table T1 column ID has the not null constraint, in this case, the index range scan is selected using ID is null </P> <p> --> next, let's take a look at the case where the column Val is null <br/> Scott @ orcl> select * from T1 where Val is null; </P> <p> execution plan <br/> ---------------------------------------------------------- <br/> plan hash value: 48744011 </P> <p> bytes <br/> | ID | operation | Name | rows | bytes | cost (% CPU) | time | <br/> latency <br/> | 0 | SELECT statement | 1 | 5 | 2 (0) | 00:00:01 | <br/> | * 1 | index fast full scan | I _t1_id_val | 1 | 5 | 2 (0) | 00:00:01 | <br/> identified </P> <p> predicate information (identified by Operation ID ): <br/> ------------------------------------------------- </P> <p> 1-filter ("Val" is null) </P> <p> --> although the Val column allows null values, however, the column ID has the not null constraint, and the ID column and the Val column have a unique compound index, therefore, a quick and full index scan is selected. <br/> --> the other combinations are roughly the same, no demonstration </P> <p> --> Add a record whose Val is null for table t1 <br/> Scott @ orcl> insert into T1 select 3, null from dual; </P> <p> 1 row created. </P> <p> Scott @ orcl> commit; </P> <p> commit complete. </P> <p> Scott @ orcl> exec dbms_stats.gather_table_stats ('Scott ', 't1', cascade => true ); </P> <p> PL/SQL procedure successfully completed. </P> <p> --> The following query shows that although only the column ID has the not null constraint, when all index values are stored <br/> Scott @ orcl> select index_name, index_type, blevel, leaf_blocks, num_rows, status, distinct_keys <br/> 2 from user_indexes where table_name = 't1 '; </P> <p> index_name index_type blevel leaf_blocks num_rows status restart <br/> Upgrade ------------ ---------- ----------- ---------- ------------- <br/> I _t1_id normal 0 1 3 valid 3 <br/> I _t1_id_val normal 0 1 3 valid 3 </P> <p> --> author: robinson Cheng <br/> --> blog: http://blog.csdn.net/robinson_0612Iv. Summary
Whether it is a single column unique index or a compound unique index, Oracle does not store the index value for a column that can be null or a compound null value.
Therefore, when creating a unique B-tree index or creating a B-tree compound unique index based on a single column,
When the column value is null
If the WHERE clause is null-based, its execution plan is to scan the entire table.
The where clause uses an index scan (index range scan or full index scan) based on is not null ).
When the column cannot be null, there is a non-null constraint.
The where clause uses a condition row based on is null, and its execution plan is indexed.
When the WHERE clause is based on is not null, its execution plan also uses index scanning.
Note: in Oracle 10g R2 (Linux), different optimizer versions may have deviations.
V. More references
Null Value and index (2)
SQL tuning steps
Efficient SQL statements
Parent cursor, child cursor, and shared cursor
Bind variables and their advantages and disadvantages
Use of the display_cursor function of dbms_xplan
Use of the display function of dbms_xplan
Description of each field module in the execution plan
Use explain plan to obtain the SQL statement execution plan
Enable autotrace
The function invalidates the index column.
Oracle variable binding
Oracle adaptive shared cursor
