Oracle与PostgreSQL的NULL和索引使用区别是什么

2025-03-13 技术教程

这篇文章主要讲解了“Oracle与PostgreSQL的NULL和索引使用区别是什么”，文中的讲解内容简单清晰，易于学习与理解，下面请大家跟着小编的思路慢慢深入，一起来研究和学习“Oracle与PostgreSQL的NULL和索引使用区别是什么”吧！

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

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

TEST-orcl@DESKTOP-V430TU3>createtabletbl1(idint);Tablecreated.TEST-orcl@DESKTOP-V430TU3>createglobaltemporarytabletmp(idint);Tablecreated.TEST-orcl@DESKTOP-V430TU3>insertintotmpselectrownumfromdba_objects;133456rowscreated.TEST-orcl@DESKTOP-V430TU3>insertintotmpselect*fromtmp;133455rowscreated.TEST-orcl@DESKTOP-V430TU3>/266910rowscreated.TEST-orcl@DESKTOP-V430TU3>/533820rowscreated.TEST-orcl@DESKTOP-V430TU3>/1067640rowscreated.TEST-orcl@DESKTOP-V430TU3>insertintotbl1select*fromtmp;2135296rowscreated.TEST-orcl@DESKTOP-V430TU3>commit;Commitcomplete.TEST-orcl@DESKTOP-V430TU3>execdbms_stats.gather_table_stats('TEST','TBL1',cascade=>true);PL/SQLproceduresuccessfullycompleted.TEST-orcl@DESKTOP-V430TU3>selectindex_name,index_type,blevel,leaf_blocks,num_rows,status,distinct_keysfromuser_indexeswheretable_name='TBL1';INDEX_NAMEINDEX_TYPEBLEVEL-------------------------------------------------------------------LEAF_BLOCKSNUM_ROWSSTATUSDISTINCT_KEYS------------------------------------------IDX_TBL1_IDNORMAL246622103843VALID134688TEST-orcl@DESKTOP-V430TU3>insertintotbl1values(null);1rowcreated.TEST-orcl@DESKTOP-V430TU3>commit;Commitcomplete.TEST-orcl@DESKTOP-V430TU3>TEST-orcl@DESKTOP-V430TU3>execdbms_stats.gather_table_stats('TEST','TBL1',cascade=>true);PL/SQLproceduresuccessfullycompleted.TEST-orcl@DESKTOP-V430TU3>selectindex_name,index_type,blevel,leaf_blocks,num_rows,status,distinct_keysfromuser_indexeswheretable_name='TBL1';INDEX_NAMEINDEX_TYPEBLEVEL-------------------------------------------------------------------LEAF_BLOCKSNUM_ROWSSTATUSDISTINCT_KEYS------------------------------------------IDX_TBL1_IDNORMAL247712152683VALID134688

执行查询

TEST-orcl@DESKTOP-V430TU3>setautotraceonexplainTEST-orcl@DESKTOP-V430TU3>select*fromtbl1whereidisnull;ID----------ExecutionPlan----------------------------------------------------------Planhashvalue:312383637--------------------------------------------------------------------------|Id|Operation|Name|Rows|Bytes|Cost(%CPU)|Time|--------------------------------------------------------------------------|0|SELECTSTATEMENT||1|5|898(2)|00:00:11||*1|TABLEACCESSFULL|TBL1|1|5|898(2)|00:00:11|--------------------------------------------------------------------------PredicateInformation(identifiedbyoperationid):---------------------------------------------------1-filter("ID"ISNULL)TEST-orcl@DESKTOP-V430TU3>

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

testdb=#insertintotbl1selectgenerate_series(1,100000);INSERT0100000testdb=#explain(analyze,verbose)select*fromtbl1whereidisnull;QUERYPLAN--------------------------------------------------------------------------------------------------------SeqScanonpublic.tbl1(cost=0.00..1569.33rows=11width=4)(actualtime=26.052..130.752rows=1loops=1)Output:idFilter:(tbl1.idISNULL)RowsRemovedbyFilter:110000PlanningTime:1.403msExecutionTime:130.814ms(6rows)testdb=#createindexidx_tb1_idontbl1(id);CREATEINDEXtestdb=#explain(analyze,verbose)select*fromtbl1whereidisnull;QUERYPLAN--------------------------------------------------------------------------------------------------------IndexOnlyScanusingidx_tb1_idonpublic.tbl1(cost=0.42..8.56rows=4width=4)(actualtime=0.133..0.136rows=1loops=1)Output:idIndexCond:(tbl1.idISNULL)HeapFetches:1PlanningTime:1.512msExecutionTime:0.199ms(6rows)

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

感谢各位的阅读，以上就是“Oracle与PostgreSQL的NULL和索引使用区别是什么”的内容了，经过本文的学习后，相信大家对Oracle与PostgreSQL的NULL和索引使用区别是什么这一问题有了更深刻的体会，具体使用情况还需要大家实践验证。这里是亿速云，小编将为大家推送更多相关知识点的文章，欢迎关注！