这篇文章主要讲解了“Oracle与PostgreSQL的NULL和索引使用区别是什么”，文中的讲解内容简单清晰，易于学习与理解，下面请大家跟着小编的思路慢慢深入，一起来研究和学习“Oracle与PostgreSQL的NULL和索引使用区别是什么”吧！
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.
感谢各位的阅读，以上就是“Oracle与PostgreSQL的NULL和索引使用区别是什么”的内容了，经过本文的学习后，相信大家对Oracle与PostgreSQL的NULL和索引使用区别是什么这一问题有了更深刻的体会，具体使用情况还需要大家实践验证。这里是亿速云，小编将为大家推送更多相关知识点的文章，欢迎关注！
亿速云「云服务器」，即开即用、新一代英特尔至强铂金CPU、三副本存储NVMe SSD云盘，价格低至29元/月。点击查看>>
免责声明：本站发布的内容（图片、视频和文字）以原创、转载和分享为主，文章观点不代表本网站立场，如果涉及侵权请联系站长邮箱：is@yisu.com进行举报，并提供相关证据，一经查实，将立刻删除涉嫌侵权内容。
原文链接：http://blog.itpub.net/6906/viewspace-2648364/
