★ Use the index of the misunderstanding of the four: the impact of NULL value on the index

four myths about 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 the 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 think 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 rows that are considered completely empty in the GROUP BY clause are 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;