Four myths about using indexes: effects of null values on indexes

Four myths about indexing using indexes: Effects of null values on indexes
Let's do some test data first:

Sql> CREATE table t (x int, y int);



Table created



Note that here I do a unique (federated) index of table T:

Sql> Create unique index t_idx on T (x,y);



Index created



Sql> INSERT into T values (1,1);



1 row inserted



Sql> INSERT into T values (1,null);



1 row inserted



Sql> INSERT into T values (null,1);



1 row inserted



Sql> INSERT into T values (null,null);



1 row inserted



Sql> commit;



Commit Complete



Here we analyze the index:

Sql> Analyze index T_IDX validate structure;



Index analyzed



Sql> select Name,lf_rows from Index_stats;



NAME lf_rows

------------------------------ ----------

T_idx 3



Sql>

Then we can see that only 3 rows of data are saved in the current index.

Notice that we inserted and submitted four rows of data above.

So here's a conclusion:

Oracle's index does not save all rows that are empty.





We continue inserting the data, and now insert a few lines that are all empty:

Sql> INSERT into T values (null,null);



1 row inserted



Sql> INSERT into T values (null,null);



1 row inserted

We see such inserts that do not violate the unique constraint we set previously (unique on T (x,y)),

So here we come to a conclusion:

Oracle considers Null<>null, and then (Null,null) <> (null,null)

In other words, Oracle considers null values (NULL) to be unequal to any value, including null values and not equal null values.



We see that the following inserts violate the unique constraint (DEMO. T_IDX), this is well understood, because it is not all null values, that is, it is not (null,null), only rows that are all empty are considered to be different rows:

Sql> INSERT into T values (1,null);



INSERT into T values (1,null)



ORA-00001: Violation of UNIQUE constraints (DEMO. T_IDX)



Sql> INSERT into T values (null,1);



INSERT into T values (null,1)



ORA-00001: Violation of UNIQUE constraints (DEMO. T_IDX)



Sql>



Take a look at the following example:

Sql> Select X,y,count (*) from the T Group by X,y;



X Y COUNT (*)

----- -------- ----------

3

1 1

1 1

1 1 1

Executed in 0.03 seconds



Sql> Select X,y,count (*) from t where x is null and y are null GROUP by X,y;



X Y COUNT (*)

---- ------- ----------

3



Executed in 0.01 seconds



Sql>

Sql> Select X,y,count (*) from T GROUP by X,y has count (*) >1;



X Y COUNT (*)

------ -------------------- ----------

3



Executed in 0.02 seconds

Sql>

As you can see, there are three lines of completely empty lines, and here we can draw a conclusion:

Oracle considers completely empty rows in the GROUP BY clause to be the same row

In other words, in the GROUP BY clause, Oracle thinks (null,null) = (null,null)







The following statement uses the leading column of the composite Index (X,Y), which is usually indexed, so let's take a look at the following example:

SELECT * from t where x is null;



Plan_table_output

--------------------------------------------------------------------------------

--------------------------------------------------------------------

| Id |  Operation | Name | Rows | Bytes | Cost |

--------------------------------------------------------------------

| 0 |             SELECT STATEMENT |       |       |       | |

|* 1 | TABLE ACCESS Full |       T |       |       | |

--------------------------------------------------------------------

predicate information (identified by Operation ID):

---------------------------------------------------

1-filter ("T".) X "is NULL)

Note:rule Based Optimization



Rows selected



Executed in 0.06 seconds



We see that the above query does not use an index, then compare the situation without using control:

Compare the following query:

SELECT * from t where x=1;



Plan_table_output

--------------------------------------------------------------------------------

--------------------------------------------------------------------

| Id |  Operation | Name | Rows | Bytes | Cost |

--------------------------------------------------------------------

| 0 |             SELECT STATEMENT |       |       |       | |

|* 1 | INDEX RANGE SCAN |       T_idx |       |       | |

--------------------------------------------------------------------

predicate information (identified by Operation ID):

---------------------------------------------------

1-access ("T".) X "=1)

Note:rule Based Optimization



Rows selected



Executed in 0.04 seconds

This query (where x=1) uses the T_IDX (x,y) composite Index as we would like, and here we can draw a conclusion:

Oracle does not use indexes (because Oracle indexes do not store null values) when using is null and is-not null conditions, see previous related content in detail



So how do we use null values for comparison conditions?

First, try not to use null values on the leading columns, see the following example:

SELECT * from t where x=1 and y is null;



Plan_table_output

--------------------------------------------------------------------------------

--------------------------------------------------------------------

| Id |  Operation | Name | Rows | Bytes | Cost |

--------------------------------------------------------------------

| 0 |             SELECT STATEMENT |       |       |       | |

|* 1 | INDEX RANGE SCAN |       T_idx |       |       | |

--------------------------------------------------------------------

predicate information (identified by Operation ID):

---------------------------------------------------

1-access ("T".) X "=1)

Filter ("T".) Y "is NULL)

Note:rule Based Optimization



Rows selected



SELECT * from t where x is null and y=1;



Plan_table_output

--------------------------------------------------------------------------------

--------------------------------------------------------------------

| Id |  Operation | Name | Rows | Bytes | Cost |

--------------------------------------------------------------------

| 0 |             SELECT STATEMENT |       |       |       | |

|* 1 | TABLE ACCESS Full |       T |       |       | |

--------------------------------------------------------------------

predicate information (identified by Operation ID):

---------------------------------------------------

1-filter ("T".) Y "=1 and" T "." X "is NULL)

Note:rule Based Optimization



Rows selected



There is also a workaround, that is, when we create a table, each column is specified as a non-empty constraint (not null), and the default value is used on the necessary columns, such as:

Sql> CREATE TABLE Lunar (

2 C1 varchar2 (+) Default ' empty '

3 constraint c1_notnull NOT NULL,

4 C2 Number (a) default 0

5 constraint c2_notnull NOT NULL,

6 C3 Date Default to_date (' 20990101 ', ' YYYYMMDD ')

7 constraint c3_notnull not NULL);



Table has been created.



Time used: 00:00:00.00

sql> INSERT into Lunar (c1) VALUES (' a ');



1 lines have been created.



Time used: 00:00:00.00

sql> INSERT into Lunar (C2) values (99);



1 lines have been created.



Time used: 00:00:00.00

sql> insert into Lunar (C3) values (sysdate);



1 lines have been created.



Time used: 00:00:00.00

sql> insert into lunar (C1,C3) VALUES (' OK ', sysdate);



1 lines have been created.



Time used: 00:00:00.00

sql> insert into lunar (C2,C1) VALUES (999, ' hello ');



1 lines have been created.



Time used: 00:00:00.00

Sql> commit;



Submit completed.



Time used: 00:00:00.00

Sql> select * from lunar;



C1 C2 C3

---------- ---------- ----------

0 January-January-99

Empty 99 January-January-99

Empty 0 1 September-October-04

OK 0 1 September-October-04

Hello 999 January-January-99



Time used: 00:00:00.00

Sql> Select C1,c2,to_char (C3, ' Yyyy-mm-yy ') from lunar;



C1 C2 To_char (C3

---------- ---------- ----------

0 2099-01-99

Empty 99 2099-01-99

Empty 0 2004-10-04

OK 0 2004-10-04

Hello 999 2099-01-99



Time used: 00:00:00.00

Sql>

Then we use them as usual, and we can build them properly, and we believe that it will improve the query efficiency of the application.