/******************* postgresql database test starts***********************/
--This test is based on pg database version 10.11
postgres=# select version(); version ------------------------------------------------------------- PostgreSQL 10.11, compiled by Visual C++ build 1800, 64-bit (1 Row record)
--Create a test table
create table t_index_null(id varchar(50),name text);
--Manufacturing test data
insert into t_index_null(id,name) select t.id,'test'||t.id::varchar from (select generate_series as id from generate_series(1,100000) ) as t;
--Add a piece of data with null id
insert into t_index_null(name) values ('zqw01');--Add a piece of data whose id is null character
insert into t_index_null(id,name) values ('','zqw02');--Create index on id column
create index idx_t_index_null_id on t_index_null(id);
--Check and lock the data with id=1 to see if the execution plan will follow the index and execution plan
explain analyze select id,name from t_index_null where id = '1'; QUERY PLAN ------------------------------------------------------------------------------------------- Index Scan using idx_t_index_null_id on t_index_null (cost=0.29..8.31 rows=1 width=14) (actual time=0.061..0.062 rows=1 loops=1) Index Cond: ((id)::text = '1'::text) Planning time: 0.289 ms Execution time: 0.086 ms (4 Row record)
--Check and lock the data with id = '' (empty character) to see whether the execution plan will follow the index and the execution plan
explain analyze select id,name from t_index_null where id = ''; QUERY PLAN ------------------------------------------------------------------------------------------- Index Scan using idx_t_index_null_id on t_index_null (cost=0.29..8.31 rows=1 width=14) (actual time=0.050..0.052 rows=1 loops=1) Index Cond: ((id)::text = ''::text) Planning time: 0.114 ms Execution time: 0.078 ms (4 Row record)
--Check and lock the data with id is null (null character) to see whether the execution plan will follow the index and the execution plan
explain analyze select id,name from t_index_null where id is null; QUERY PLAN ------------------------------------------------------------------------------------------- Index Scan using idx_t_index_null_id on t_index_null (cost=0.29..10.97 rows=3 width=14) (actual time=0.105..0.106 rows=1 loops=1) Index Cond: (id IS NULL) Planning time: 0.115 ms Execution time: 0.133 ms (4 Row record)
/*********************oracle library test***************************/
-
-This test is based on oracle database version 11.2
SQL> select * from v$version;
BANNER -------------------------------------------------------------------------------- Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production PL/SQL Release 11.2.0.1.0 - Production CORE 11.2.0.1.0 Production TNS for Linux: Version 11.2.0.1.0 - Production NLSRTL Version 11.2.0.1.0 - Production
--Create a test table
create table scott.t_index_null(id varchar2(50),name varchar2(4000));
--Manufacturing test data
insert into scott.t_index_null(id,name) select level,'test'||level from dual connect by level <= 100000;
--Add a piece of data with null id
insert into scott.t_index_null(name) values ('zqw01');--Add a piece of data whose id is null character
insert into scott.t_index_null(id,name) values ('','zqw02');--Create index on id column
create index idx_t_index_null_id on scott.t_index_null(id);
--Check and lock the data with id=1 to see whether the execution plan will follow the index. The execution plan will follow the index
set auto off
explain plan for
select id,name
from scott.t_index_null
where id = '1';
select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 42603800
---------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 2029 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| T_INDEX_NULL | 1 | 2029 | 2 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | IDX_T_INDEX_NULL_ID | 1 | | 1 (0)| 00:00:01 |
---------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("ID"='1')
Note
-----
- dynamic sampling used for this statement (level=2)
--Check and lock the data with id = '' (empty character) to see whether the execution plan will go through the index. From the following execution plan, id = '' filtering does not go through the index scanning, but through the whole table scanning
explain plan for select id,name from scott.t_index_null where id = ''; select * from table(dbms_xplan.display); PLAN_TABLE_OUTPUT -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Plan hash value: 1102671017 ----------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ----------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 2029 | 0 (0)| | |* 1 | FILTER | | | | | | | 2 | TABLE ACCESS FULL| T_INDEX_NULL | 118K| 229M| 104 (2)| 00:00:02 | ----------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 1 - filter(NULL IS NOT NULL) Note ----- - dynamic sampling used for this statement (level=2)
--Check and lock the data with id is null (null character) to see whether the execution plan will go through the index. From the following execution plan, id is null also does not go through the index scanning, but goes through the whole table scanning
explain plan for
select id,name
from scott.t_index_null
where id is null;
select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 1935092368
----------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 4 | 8116 | 104 (2)| 00:00:02 |
|* 1 | TABLE ACCESS FULL| T_INDEX_NULL | 4 | 8116 | 104 (2)| 00:00:02 |
----------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("ID" IS NULL)
Note
-----
- dynamic sampling used for this statement (level=2)
/************************** mysql library test******************************/
--The version is as follows:
mysql> select version(); +-----------+ | version() | +-----------+ | 5.1.73 | +-----------+ 1 row in set (0.00 sec)
--Create a test table
mysql> create table t_index_null(id varchar(50),name varchar(4000));
--Create 100000 test data
insert into t_index_null(id,name) select @a := @a + 1 as seq, 'test'||(@a := @a + 1) from tmp_seed,(select @a:=0) as seq;
--Add a piece of data with null id
insert into t_index_null(name) values ('zqw01');--Add a piece of data whose id is null character
insert into t_index_null(id,name) values ('','zqw02');--Create index on id column
create index idx_t_index_null_id on t_index_null(id);
--Check and lock the data with id=1 to see whether the execution plan will go through the index, and the execution plan will go through the non unique index scanning
explain select id,name from t_index_null where id = '1'; +----+-------------+--------------+------+---------------------+---------------------+---------+-------+------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+--------------+------+---------------------+---------------------+---------+-------+------+-------------+ | 1 | SIMPLE | t_index_null | ref | idx_t_index_null_id | idx_t_index_null_id | 53 | const | 1 | Using where | +----+-------------+--------------+------+---------------------+---------------------+---------+-------+------+-------------+ 1 row in set (0.00 sec)
--Check and lock the data with id = '' (null character) to see whether the execution plan will go through the index. From the following execution plan, id = '' filtering, and the execution plan will go through the non unique index scanning
explain select id,name from t_index_null where id = ''; +----+-------------+--------------+------+---------------------+---------------------+---------+-------+------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+--------------+------+---------------------+---------------------+---------+-------+------+-------------+ | 1 | SIMPLE | t_index_null | ref | idx_t_index_null_id | idx_t_index_null_id | 53 | const | 1 | Using where | +----+-------------+--------------+------+---------------------+---------------------+---------+-------+------+-------------+ 1 row in set (0.01 sec)
--Check and lock the data of id is null (null character) to see whether the execution plan will go through the index. From the following execution plan, id is null is also a non unique index scan
explain select id,name from t_index_null where id is null; +----+-------------+--------------+------+---------------------+---------------------+---------+-------+------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+--------------+------+---------------------+---------------------+---------+-------+------+-------------+ | 1 | SIMPLE | t_index_null | ref | idx_t_index_null_id | idx_t_index_null_id | 53 | const | 1 | Using where | +----+-------------+--------------+------+---------------------+---------------------+---------+-------+------+-------------+ 1 row in set (0.00 sec)
Summary:
If a column in mysql or postgresql database stores null character '' or has null value, then add an index to this column. If SQL uses = '' or is null filtering, the execution plan may go through the index;
If a column in Oracle database stores null character '' or has null value, then add an index to this column. If SQL uses = 'or is null filtering, the SQL execution plan will not go through index scanning;