Indexes in IRIS

Question

Question

Pietro Montorfano · May 24, 2023

#Indexing #ObjectScript #SQL #InterSystems IRIS

Hi,
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?

Thanks

Product version: IRIS 2022.1

Discussion (4)1

Log in or sign up to continue

Eduard Lebedyuk · May 25, 2023

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

3 0

Dmitry Maslennikov · May 25, 2023

Creating an Index with SQL, not directly in ObjectScript, will build Index by default if you don't say do not do it.

1 0

score 0 · Answer 1 · 2023-05-24T15:11:15-04:00

Yes, assuming the index exists it will be updated as the data changes.

https://docs.intersystems.com/irisforhealth20231/csp/docbook/DocBook.UI.Page.cls?KEY=GSOD_indexes#GSOD_indexes_overview

score 0 · Answer 2 · 2023-05-24T23:36:41-04:00

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)

Thanks