Indexes in 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
Comments
Yes, assuming the index exists it will be updated as the data changes.
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
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
Creating an Index with SQL, not directly in ObjectScript, will build Index by default if you don't say do not do it.