Shulou(Shulou.com)05/31 Report--

This article mainly explains "What is the difference between NULL and index use between Oracle and PostgreSQL". The explanation in this article is simple and clear, easy to learn and understand. Please follow the idea of Xiaobian and go deep into it slowly to study and learn "What is the difference between NULL and index use between Oracle and PostgreSQL"!

Oracle does not store NULL values when creating indexes, whereas PostgreSQL stores NULL values when creating indexes. In queries such as Column is null,Oracle does not use indexes and PostgreSQL does.

Oracle

Insert data, 200w rows of data, and then insert a null row of data.

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 134688TEST-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

execute the query

TEST-orcl@DESKTOP-V430TU3>set autotrace on explainTEST-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

Data table tbl1 structure is consistent with Oracle.

testdb=# insert into tbl1 select generate_series(1,100000);INSERT 0 100000testdb=# 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 INDEXtestdb=# 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.

Thank you for reading, the above is the content of "Oracle and PostgreSQL NULL and index use difference is what", after learning this article, I believe we have a deeper understanding of Oracle and PostgreSQL NULL and index use difference is what this problem, the specific use of the situation also needs to be verified by practice. Here is, Xiaobian will push more articles related to knowledge points for everyone, welcome to pay attention!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.