您好,登錄后才能下訂單哦!
這篇文章主要講解了“Oracle與PostgreSQL的NULL和索引使用區(qū)別是什么”,文中的講解內(nèi)容簡單清晰,易于學(xué)習(xí)與理解,下面請大家跟著小編的思路慢慢深入,一起來研究和學(xué)習(xí)“Oracle與PostgreSQL的NULL和索引使用區(qū)別是什么”吧!
Oracle在創(chuàng)建索引時(shí),不會存儲NULL值,而PostgreSQL在創(chuàng)建索引時(shí)則會存儲NULL值.在查詢時(shí),如使用Column is null這樣的條件查詢,Oracle不會使用索引而PostgreSQL則會使用索引.
Oracle
插入數(shù)據(jù),200w多行的數(shù)據(jù),然后插入一行值為null的數(shù)據(jù).
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
執(zhí)行查詢
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
數(shù)據(jù)表tbl1結(jié)構(gòu)與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進(jìn)行查詢,使用的是Index Only Scan.
感謝各位的閱讀,以上就是“Oracle與PostgreSQL的NULL和索引使用區(qū)別是什么”的內(nèi)容了,經(jīng)過本文的學(xué)習(xí)后,相信大家對Oracle與PostgreSQL的NULL和索引使用區(qū)別是什么這一問題有了更深刻的體會,具體使用情況還需要大家實(shí)踐驗(yàn)證。這里是億速云,小編將為大家推送更多相關(guān)知識點(diǎn)的文章,歡迎關(guān)注!
免責(zé)聲明:本站發(fā)布的內(nèi)容(圖片、視頻和文字)以原創(chuàng)、轉(zhuǎn)載和分享為主,文章觀點(diǎn)不代表本網(wǎng)站立場,如果涉及侵權(quán)請聯(lián)系站長郵箱:is@yisu.com進(jìn)行舉報(bào),并提供相關(guān)證據(jù),一經(jīng)查實(shí),將立刻刪除涉嫌侵權(quán)內(nèi)容。