Skip to Main Content

Support reading of indexes to determine null values

AndrewSayerDec 16 2015 — edited Jan 11 2016

By this I mean reading an index (via full scan) and not finding a rowid which is known to belong to the table should tell the rdms that the indexed column/expression is null for a particular row.

This could be used to count nulls of a columm:

select count(*) from table_a where col1 is null

Could be treated asĀ 

Select (select count(*) from table_a)-(select count(col1) from table_a) from dual

Or, more involved: for a table with columns A to Z, all individually indexed and nullable. The query

select b from table_a where a=:a

could transform to

select b.b

from table_a a

left join

(select rowid rowid_b, b

from table_a

where b is is not null) b

On a.rowid = b.rowid_b

where a.a = :a

Post Details
Added on Dec 16 2015