Use the indexed misunderstanding: null value on the index
We first do some test data:
SQL> CREATE TABLE T (X INT, Y INT);
Table created
Please note that I have made a unique (joint) index for 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 completion
Below 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 lines of data are saved in the current index.
Please note that we have inserted and submitted four lines of data.
So, there is a conclusion here:
Oracle's index does not save all empty rows.
We continue to insert data, and now insert a few lines of lines:
SQL> INSERT INTO T VALUES (NULL, NULL);
1 row inserted
SQL> INSERT INTO T VALUES (NULL, NULL);
1 row inserted
We see such inserts, there is no violation of the only constraints we set up before, and Unique ON T (X, Y)).
So, here we have come to a conclusion:
Oracle thinks NULL <> NULL, in turn (null, null) <> (null, null)
In other words, Oracle believes that null value (NULL) is not equal to any value, including null values are not equal to null values.
We see the following inserts will violate the only constraint (demo.t_idx), this is very well understood because it is not all empty values, that is, it is not (null, null), only all empty priests are considered to be Different lines:
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>
Please see the example below:
SQL> SELECT X, Y, Count (*) from t group by x, y;
X y count (*)
------------ ----------
3
1 1
1 11 1 1
Executed in 0.03 seconds
SQL> SELECT X, Y, Count (*) from T where x is null and y is 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 having count (*)> 1;
X y count (*)
------ ------------------------------
3
Executed in 0.02 seconds
SQL>
It can be seen that there is three lines that are completely empty. Here we can conclude:
Oracle believes that complete empty rows in Group By clauses is the same
In other words, in Group By clause, Oracle believes (NULL, NULL) = (NULL, NULL)
The following statement uses the leader of the composite index (x, y), usually such a query uses an index, let's take a look at the example below:
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
14 rows selected
Executed in 0.06 seconds
We see that the above query does not use the index, then compare the case where no control is used:
Contrast 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
14 rows selected
Executed in 0.04 seconds
This query (where x = 1) uses T_IDX (X, Y) composite index as we want, here we can draw a conclusion:
When using is Null and IS Not Null, Oracle does not use an index (because Oracle's index does not store null values, please refer to the previous related content)
So how do we use null values?
First, try not to use null values in the front-column, please see the example below:
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
15 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
14 rows selected
There is also a way that can be varied, that is, when we create a table, specify the NOT NULL, and use the default value on the necessary columns, such as:
SQL> CREATE TABLE LUNAR (
2
c
1 varchar2 (10) Default 'EMPTY'
3 ConsTRAINT C1_NOTNULL NOT NULL,
4
c
2 Number (10) Default 0
5 ConsTRAINT C2_NOTNULL NOT NULL,
6
c
3 Date default to_date ('20990101', 'YYYYMMDD')
7 ConsTRAINT C3_NOTNULL NOT NULL);
The table has been created.
Time: 00: 00: 00.00
SQL> INSERT INTO LUNAR (C1) VALUES ('first');
It has created a row.
Time: 00: 00: 00.00
SQL> INSERT INTO LUNAR (C2) VALUES (99);
It has created a row.
Time: 00: 00: 00.00
SQL> INSERT INTO LUNAR (C3) VALUES (SYSDATE);
It has created a row.
Time: 00: 00: 00.00
SQL> INSERT INTO LUNAR (C1, C3) VALUES ('OK', SYSDATE);
It has created a row.
Time: 00: 00: 00.00
SQL> INSERT INTO LUNAR (C2, C1) VALUES (999, 'Hello');
It has created a row.
Time: 00: 00: 00.00
SQL> commit;
Submitted.
Time: 00: 00: 00.00
SQL> SELECT * from Lunar;
C
1
C
2 C
3
---------- --------------------
First 01- January -99empty 99 01- Jan -99
EMPTY 0 19-October -04
OK 0 19-October -04
Hello 999 01- Jan -99
Time: 00: 00: 00.00
SQL> SELECT C1, C2, TO_CHAR (C3, 'YYYY-MM-YY') from Lunar;
C
1
C
2 TO_CHAR (C3
---------- --------------------
First 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: 00: 00: 00.00
SQL>
Then we use them, use them, and reasonably to establish an index to improve the application's query efficiency.
