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>

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/18922393/viewspace-730469/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/18922393/viewspace-730469/