SQL中的null

  for i in 1..9999 loop

    insert into n1 values(i,‘name‘||i);

    if mod(i,100)=0then

      commit;

    end if;

  end loop;

  insert into n1(sid)values(10000);

  commit;

end;

查询sname列值走的是索引范围扫描

SQL> explain plan for select * from n1 where sname = 'name1';
Explained.

SQL> select * from table(dbms_xplan.display);
Plan hash value: 3644017351
--------------------------------------------------------------------------------
| Id  | Operation	 | Name 	| Rows	| Bytes | Cost (%CPU)| Time
|
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT |		|     1 |    75 |     2   (0)| 00:00:01
|
|*  1 |  INDEX RANGE SCAN| N1_SNAME_IND |     1 |    75 |     2   (0)| 00:00:01
|
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - access("SNAME"='name1')
Note
-----
   - dynamic sampling used for this statement (level=2)

is null方式查询，虽然sname中为null的记录1W行中只有一行，但还是没有走索引，也就是说is null不走索引。

SQL> explain plan for select * from n1 where sname is null;
Explained.

SQL> select * from table(dbms_xplan.display);
Plan hash value: 2416923229
--------------------------------------------------------------------------
| Id  | Operation	  | Name | Rows  | Bytes | Cost (%CPU)| Time	 |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |	 |     1 |    75 |     9   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| N1	 |     1 |    75 |     9   (0)| 00:00:01 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter("SNAME" IS NULL)
Note
-----
   - dynamic sampling used for this statement (level=2)
17 rows selected.

如果实际情况确实需要is null查询走索引呢？可通过创建联合索引的方式来实现。

drop index n1_sname ;

create index n1_sname_ind on n1(sname,sid);

SQL> explain plan for select * from n1 where  sid is not null and sname is null ;

SQL> select * from table(dbms_xplan.display);
Plan hash value: 3644017351
-------------------------------------------------------------------------------
| Id  | Operation	 | Name 	| Rows	| Bytes | Cost (%CPU)| Time
|
-------------------------------------------------------------------------------
|   0 | SELECT STATEMENT |		|     1 |    75 |     3   (0)| 00:00:01
|
|*  1 |  INDEX RANGE SCAN| N1_SNAME_IND |     1 |    75 |     3   (0)| 00:00:01
|
-------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - access("SNAME" IS NULL)
       filter("SID" IS NOT NULL)
Note
-----
   - dynamic sampling used for this statement (level=2)

18 rows selected.

可以看到创建联合索引后，SQL查询所耗费的资源明显降低。

需要注意的是我们查询最频繁最经常使用列，比如sname要放在联合索引的第一列；同时要走联合索引，需要where后面的条件出现联合索引包含的所有的字段，这也是为什么加了sid这个字段的原因。

版权声明：本文为博主原创文章，未经博主允许不得转载。

SQL中的null

标签：