ITPub博客

首页 > 数据库 > PostgreSQL > Oracle vs PostgreSQL,研发注意事项(12) - NULL与索引

Oracle vs PostgreSQL,研发注意事项(12) - NULL与索引

原创 PostgreSQL 作者:husthxd 时间:2019-06-21 16:07:37 0 删除 编辑

Oracle在创建索引时,不会存储NULL值,而PostgreSQL在创建索引时则会存储NULL值.在查询时,如使用Column is null这样的条件查询,Oracle不会使用索引而PostgreSQL则会使用索引.

Oracle
插入数据,200w多行的数据,然后插入一行值为null的数据.


TEST-orcl@DESKTOP-V430TU3>create table tbl1(id int);
Table created.
TEST-orcl@DESKTOP-V430TU3>create global temporary table tmp(id int);
Table created.
TEST-orcl@DESKTOP-V430TU3>insert into tmp select rownum from dba_objects;
133456 rows created.
TEST-orcl@DESKTOP-V430TU3>insert into tmp select * from tmp;
133455 rows created.
TEST-orcl@DESKTOP-V430TU3>/
266910 rows created.
TEST-orcl@DESKTOP-V430TU3>/
533820 rows created.
TEST-orcl@DESKTOP-V430TU3>/
1067640 rows created.
TEST-orcl@DESKTOP-V430TU3>insert into tbl1 select * from tmp;
2135296 rows created.
TEST-orcl@DESKTOP-V430TU3>commit;
Commit complete.
TEST-orcl@DESKTOP-V430TU3>exec dbms_stats.gather_table_stats('TEST','TBL1',cascade=>true);
PL/SQL procedure successfully completed.
TEST-orcl@DESKTOP-V430TU3>select index_name,index_type,blevel,leaf_blocks,num_rows,status,distinct_keys from user_indexes  where table_name='TBL1';
INDEX_NAME                     INDEX_TYPE                      BLEVEL
------------------------------ --------------------------- ----------
LEAF_BLOCKS   NUM_ROWS STATUS   DISTINCT_KEYS
----------- ---------- -------- -------------
IDX_TBL1_ID                    NORMAL                               2
       4662    2103843 VALID           134688
TEST-orcl@DESKTOP-V430TU3>insert into tbl1 values(null);
1 row created.
TEST-orcl@DESKTOP-V430TU3>commit;
Commit complete.
TEST-orcl@DESKTOP-V430TU3>
TEST-orcl@DESKTOP-V430TU3>exec dbms_stats.gather_table_stats('TEST','TBL1',cascade=>true);
PL/SQL procedure successfully completed.
TEST-orcl@DESKTOP-V430TU3>select index_name,index_type,blevel,leaf_blocks,num_rows,status,distinct_keys from user_indexes  where table_name='TBL1';
INDEX_NAME                     INDEX_TYPE                      BLEVEL
------------------------------ --------------------------- ----------
LEAF_BLOCKS   NUM_ROWS STATUS   DISTINCT_KEYS
----------- ---------- -------- -------------
IDX_TBL1_ID                    NORMAL                               2
       4771    2152683 VALID           134688

执行查询


TEST-orcl@DESKTOP-V430TU3>set autotrace on explain
TEST-orcl@DESKTOP-V430TU3>select * from tbl1 where id is null;
        ID
----------
Execution Plan
----------------------------------------------------------
Plan hash value: 312383637
--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |     1 |     5 |   898   (2)| 00:00:11 |
|*  1 |  TABLE ACCESS FULL| TBL1 |     1 |     5 |   898   (2)| 00:00:11 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter("ID" IS NULL)
TEST-orcl@DESKTOP-V430TU3>

PostgreSQL
数据表tbl1结构与Oracle一致.


testdb=# insert into tbl1 select generate_series(1,100000);
INSERT 0 100000
testdb=# explain (analyze,verbose)  select * from tbl1 where id is null;
                                                 QUERY PLAN                                                 
--------------------------------------------------------------------------------------------------------
 Seq Scan on public.tbl1  (cost=0.00..1569.33 rows=11 width=4) (actual time=26.052..130.752 rows=1 loops=1)
   Output: id
   Filter: (tbl1.id IS NULL)
   Rows Removed by Filter: 110000
 Planning Time: 1.403 ms
 Execution Time: 130.814 ms
(6 rows)
testdb=# create index idx_tb1_id on tbl1(id);
CREATE INDEX
testdb=# explain (analyze,verbose)  select * from tbl1 where id is null;
                                                         QUERY PLAN                                                          
--------------------------------------------------------------------------------------------------------
 Index Only Scan using idx_tb1_id on public.tbl1  (cost=0.42..8.56 rows=4 width=4) (actual time=0.133..0.136 rows=1 loops=1)
   Output: id
   Index Cond: (tbl1.id IS NULL)
   Heap Fetches: 1
 Planning Time: 1.512 ms
 Execution Time: 0.199 ms
(6 rows)

使用id is null进行查询,使用的是Index Only Scan.

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

请登录后发表评论 登录
全部评论
长期从事政务、金融等行业产品研发和架构设计工作,ITPUB数据库版块资深版主,对Oracle、PostgreSQL有深入研究。现就职于广州云图数据技术有限公司,系统架构师。

注册时间:2007-12-28

  • 博文量
    1308
  • 访问量
    3785510