Activating an index

I have a production system that has a large dataset of about 2 million rows. I need to create an index on a property but don't want it available to queries until the index is fully populated. Is there a way I can create the indexed, fire off the build, then "activate" the index so queries can use it.

The problem I face is the minute I create the index queries start returning zero rows because it starts using the index. As I start building it starts returning qualifying rows for those it has indexed but misses those not yet processed. All of this creates incorrect results while the build is running. I don’t have the luxury of being able to disable access until the index is built.

I don’t want to have to modify the existing queries to tell them to exclude using this index as there are too many to change, then change back.

Thanks

Andy

  • 0
  • 0
  • 234
  • 2
  • 1

Answers

Hi Andy-

Take a look at this documentation, I think it may answer your questions.

It explains building both readonly and read/write indexes on an active system.

http://docs.intersystems.com/latest/csp/docbook/DocBook.UI.Page.cls?KEY=GSQL_indices#GSQL_indices_build_readonly