not-null約束對執行計劃的影響

1，對null值的處理（group by,distinct,max/min）

SQL> select comm,count(*) from scott.emp
  2  group by comm;

      COMM   COUNT(*)
---------- ----------
                   10
      1400          1
       500          1
       300          1
         0          1

SQL>
SQL> select distinct(comm) from scott.emp;

      COMM
----------

      1400
       500
       300
         0

SQL>
SQL>
SQL> select max(comm),min(comm) from scott.emp;

 MAX(COMM)  MIN(COMM)
---------- ----------
      1400          0
==>max/min過濾掉null值

2，not-null約束對查詢效率的影響
2.1建立測試用表，test包含not null約束，test_null無not null約束。
drop table test;

create table TEST
(
  owner       VARCHAR2(30) not null,
  object_name VARCHAR2(30) not null,
  object_type VARCHAR2(19) not null,
  created     DATE not null
);

insert into test
select owner,object_name,object_type,created
from all_objects;
commit;

create index idx_test_created on test(created);

create index idx_test_owner_oname on test(owner,object_type);

begin
  dbms_stats.gather_table_stats(user,'TEST');
end;
/ 

--
drop table test_null;

create table TEST_null
(
  owner       VARCHAR2(30),
  object_name VARCHAR2(30),
  object_type VARCHAR2(19),
  created     DATE
);

insert into test_null
select * from test;
commit;

create index idx_test_null_created on test_null(created);

create index idx_test_null_owner_oname on test_null(owner,object_type);

begin
  dbms_stats.gather_table_stats(user,'TEST_NULL');
end;
/ 

2.2 使用max函式
==>由於max/min過濾掉null值，雖然null值不在索引中維護，仍然可以使用索引查詢。

SQL> set autotrace trace

select max(created) from test;

Execution Plan
----------------------------------------------------------

Plan hash value: 1861302148

-----------------------------------------------------------------------------------------------
| Id  | Operation                  | Name             | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT           |                  |     1 |     8 |     2   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE            |                  |     1 |     8 |            |          |
|   2 |   INDEX FULL SCAN (MIN/MAX)| IDX_TEST_CREATED | 50875 |   397K|     2   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------

SQL> select max(created) from test_null;

Execution Plan
----------------------------------------------------------

Plan hash value: 4200498869

----------------------------------------------------------------------------------------------------
| Id  | Operation                  | Name                  | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT           |                       |     1 |     8 |    84   (3)| 00:00:02 |
|   1 |  SORT AGGREGATE            |                       |     1 |     8 |            |          |
|   2 |   INDEX FULL SCAN (MIN/MAX)| IDX_TEST_NULL_CREATED | 50875 |   397K|            |          |
----------------------------------------------------------------------------------------------------

2.3使用distinct：
==>由於null值不在索引中維護，無not-null約束時，不能僅使用索引查詢。

SQL> select distinct owner,object_type from test;

Execution Plan
----------------------------------------------------------
Plan hash value: 2716672475

----------------------------------------------------------------------------------------------
| Id  | Operation             | Name                 | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |                      |   483 |  7245 |    48  (15)| 00:00:01 |
|   1 |  HASH UNIQUE          |                      |   483 |  7245 |    48  (15)| 00:00:01 |
|   2 |   INDEX FAST FULL SCAN| IDX_TEST_OWNER_ONAME | 50875 |   745K|    42   (3)| 00:00:01 |
----------------------------------------------------------------------------------------------

select distinct owner,object_type from test_null;

233 rows selected.

Execution Plan
----------------------------------------------------------
Plan hash value: 2562509165

--------------------------------------------------------------------------------
| Id  | Operation          | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |           |   527 |  7905 |    90   (9)| 00:00:02 |
|   1 |  HASH UNIQUE       |           |   527 |  7905 |    90   (9)| 00:00:02 |
|   2 |   TABLE ACCESS FULL| TEST_NULL | 50875 |   745K|    84   (3)| 00:00:02 |
--------------------------------------------------------------------------------

SQL>