Forum: Building VoltDB Applications

Post: How can I index on null rows?

How can I index on null rows?
cfogelberg
Mar 10, 2016
How can I define an index on a column that is used when looking for rows with null values in that column? I've tried the Oracle trick of adding another column to the index but no luck...

My query is the following:

SELECT * FROM someTable WHERE someColumn IS NOT NULL ORDER BY primaryKeyColumn;


The query plan for this looks like:

RETURN RESULTS TO STORED PROCEDURE
 MERGE RECEIVE FROM ALL PARTITIONS
  inline ORDER BY (sort)
   SEND PARTITION RESULTS TO COORDINATOR
    INDEX SCAN of "someTable" using its primary key index (for sort order only)
     filter by (NOT (someColumn IS NULL))


EDIT:
For clarity, what I want to achieve is that the first step of the query plan is some sort of index scan of someColumn
cwolff
Mar 22, 2016
You can do this if you create an index with a WHERE predicate.

create index idx_nonull on sometable(somecolumn) where somecolumn is not null;


Then if you drop the ORDER BY clause from your query, or order by somecolumn instead, you'll get this plan:

42> explain SELECT * FROM someTable WHERE someColumn IS not NULL order by somecolumn;
EXECUTION_PLAN                 
-------------------------------
RETURN RESULTS TO STORED PROCEDURE
 MERGE RECEIVE FROM ALL PARTITIONS
  inline ORDER BY (SORT)
  SEND PARTITION RESULTS TO COORDINATOR
   INDEX SCAN of "SOMETABLE" using "IDX_NONULL" (for sort order only)


The planner really seems to want to use the primary key index to do the sorting if the statement has says to "order by primarykeycolumn". To get around this you could use a subquery in the FROM clause:

43> explain select * from (SELECT * FROM someTable WHERE someColumn IS not NULL) tbl order by primarykeycolumn;
EXECUTION_PLAN                 
-------------------------------
RETURN RESULTS TO STORED PROCEDURE
 ORDER BY (SORT)
  RECEIVE FROM ALL PARTITIONS
   SEND PARTITION RESULTS TO COORDINATOR
    SEQUENTIAL SCAN of "TBL"
     INDEX SCAN of "SOMETABLE" using "IDX_NONULL" (for sort order only)


Does this solve your problem?