Question
· Aug 10, 2017

Custom Index for Not Null Values Only

Hi

1. Is it possible do define an index like that :

create index UIX on MyTable (Column1) where Column1 is not null

2. What happens if we add an index on a property that is NOT required, meanning that not all records will be indexed because we do not allow null subscripts ?

Discussion (7)1
Log in or sign up to continue

1. How do you want to use this partial index?

2. NULL values are indexed, same as any other value (with one difference that any number of NULL values are allowed in  a Unique index). Consider the following example:

Class Sample.Person Extends %Persistent
{

Property Name As %String;

Index NameIndex On Name;

/// do ##class(Sample.Person).Test()
ClassMethod Test()
{
    kill ^Sample.PersonD, ^Sample.PersonI
    do ..AddPerson("ed")
    do ..AddPerson("ed")
    do ..AddPerson("bob")
    do ..AddPerson()
    do ..AddPerson()
    
    zw ^Sample.PersonD, ^Sample.PersonI
}

ClassMethod AddPerson(Name)
{
    set p = ..%New()
    set:$d(Name) p.Name = Name
    do p.%Save()
}

Whn I run the text method:

do ##class(Sample.Person).Test()

I get the following data global:

^Sample.PersonD=5
^Sample.PersonD(1)=$lb("","ed")
^Sample.PersonD(2)=$lb("","ed")
^Sample.PersonD(3)=$lb("","bob")
^Sample.PersonD(4)=$lb("","")
^Sample.PersonD(5)=$lb("","")

And the following index global:

^Sample.PersonI("NameIndex"," ",4)=""
^Sample.PersonI("NameIndex"," ",5)=""
^Sample.PersonI("NameIndex"," BOB",3)=""
^Sample.PersonI("NameIndex"," ED",1)=""
^Sample.PersonI("NameIndex"," ED",2)=""

As you see NULL values are indexed same as any other value.

Index global has the following structure:

^ClassIndexGlobal(IndexName, IndexValue, Id) = DataStoredInIndex

Where:

  • ClassIndexGlobal - is a global  name used for storing class globals. Defined in Storage.
  • IndexName-  is a name of the index
  • IndexValue - is a collated stored value (so "bob" becomes " BOB", and NULL  becomes " "). Some additional info on collation. Documentation.
  • Id - object id
  • DataStoredInIndex - any additional data stored in index. Documenatation.

Also for this SQL:

SELECT ID
FROM Sample.Person
WHERE Name IS NULL

The following plan that uses our index gets generated:

  • Read index map Sample.Person.NameIndex, using the given %SQLUPPER(Name), and looping on ID.
  • For each row:
    •  Output the row.

Thank you Eduard.
To answer your question (1) :
I posted my question for one of our developers who added such an index and found that the index wasn't created after rebuild.
So I thought the reason might be that an indexed property cannot be null valued, I didn't bother to ask what will be the use of such an index...
From your answer I understand that this is NOT the reason why the index was not created, so we'll have to look somewhere else...
Thanks,
Uri