· May 24, 2023

Indexes in IRIS

just want to know if indexes in IRIS / SQL are autobuilding.

I define an index in a table and then i costantly do a lot of insert/delete in that table. Will i face index problem? Do i need to rebuild it often?



Product version: IRIS 2022.1
Discussion (4)1
Log in or sign up to continue

yeah, i've read that and saw this in a lot of other SQL DB, but when it comes to reality with a large amount of insert/delete made to the table the index is corrupted and there's the need to rebuild it like once a week.

Have you got any experiece like this? I need to create an index and use it on a table that will costantly have insert and delete (transactional data, a list/queue of transactions that needs to be processed)


As long as you insert only with SQL (without %NOINDEX flag) or objects (%Save), you don't have to rebuild indices. But some gotchas to remember:

  • If the table has some data before you added an index, you need to build the index after adding it (as adding an index does not build it for preexisting data)
  • If you ran sql queries which filtered based on an indexed column value, they won't automatically take advantage of the index, you need to purge all queries associated with the newly indexed table.
  • If you use %NOINDEX or direct global access to add rows, indexes must be built manually later