Question
Uri Shmueli · 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 ?

00
2 0 7 444
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

Thanks Eduard,

We have just discovered that the class in question isn't a "real" class.

It is "%CacheSQLStorage" (we call it "a mapped class") so it is obvious why Cache won't let us add indices at all.

If one needs an new index for such a class - one will have to build and manage it by himself.

So - thanks, we can now close this post.

Uri

Adding an index doesn't built it for existing data. Only data added after the index is created is indexed automatically. You need to rebuild the index:

w ##class(Package.Class).%BuildIndices()

Documentation on index building.

Hi Eduard,

I did mention that the new index was NOT created after rebuild.

Maybe you missed it, or do you mean that "rebuild indices" from the management portal is not relyable and we should use ##class(Package.Class).%BuildIndices() instead ?

Uri 

Missed it. Rebuild from management portal is equal to ##class(Package.Class).%BuildIndices(), except management portal rebuild is done asynchronously, so you need to go to SMP - Menu - Background Tasks to check that it's done.  ##class(Package.Class).%BuildIndices() is done synchronously so you know immediately that indices are rebuilt.

I've just introduced new tag Indexing to tag this and other related questions and articles. Thanks Uri for the interesting question.