ITPub博客

首页 > Linux操作系统 > Linux操作系统 > not-null约束对执行计划的影响

not-null约束对执行计划的影响

原创 Linux操作系统 作者:redhouser 时间:2012-05-21 10:52:39 0 删除 编辑

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/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2011-05-26

  • 博文量
    211
  • 访问量
    787122