Inserting an index without reconstruction
Hi!
I'd like to know if there are any issues if an index is inserted into a table without running the %BuildIndices() method.
It's important to note that data inserted before the index is not important for retrieval, so it's not a problem data inserted before the index don't show up in queries.
The reason why I'm asking this is that I'd like to avoid index reconstruction on big tables which I need to inser such index.
I'm using Cache 2018.1.
Thanks,
José
Comments
The %BuildIndices() supports partial rebuilding - see parameters pStartID, pEndID In addition, you can activate/deactivate the index: Building Indices on a READ and WRITE Active System
Hi Vitaliy,
Thank you for that valuable information! I'll take it into account.
But let's assume that I do a partial rebuild. Is it OK to use this partially rebuilt index if I don't care about data that isn't indexed? Did you do this before?
Thanks
Is it OK to use this partially rebuilt index
Sure, as long as you're OK with getting partially consistent results.
Thank you Eduard!
Is it OK to use this partially rebuilt index if I don't care about data that isn't indexed? Did you do this before?
I didn't build indexes manually, except for tests. If the index is not built for all data, then the query will see only the data for which the index exists.
Try simple example:
Class dc.test Extends %Persistent
{
Index iF On F;
Property F As %Integer;
ClassMethod Fill(N = 10)
{
k ^dc.testD,^dc.testI
s ^dc.testD=4
s ^dc.testD(1)=$lb("",22)
s ^dc.testD(2)=$lb("",11)
s ^dc.testD(3)=$lb("",44)
s ^dc.testD(4)=$lb("",33)
d $system.SQL.TuneTable($classname(),$$$YES)
d $system.OBJ.Compile($classname(),"cu-d")
k ^dc.testI
d ##class(%SQL.Statement).%ExecDirect(,"select * from dc.test").%Display()
s ^dc.testI("iF",1,22)="" w !!
d ##class(%SQL.Statement).%ExecDirect(,"select * from dc.test").%Display()
s ^dc.testI("iF",4,33)="" w !!
d ##class(%SQL.Statement).%ExecDirect(,"select * from dc.test").%Display()
}
}Thanks Vitaliy! The example was handy!
If only seeing new data suits your use case, then you are probably ok with this approach. You may wish to look at %ValidateIndices() which I believe is present in Cache2018. This will allow an online check of the index state and can either report on mismatches, or be set to autorepair. It is MUCH slower than a %BuildIndices, but does not require a full freeze for safety
https://docs.intersystems.com/latest/csp/docbook/DocBook.UI.Page.cls?KE…
@Chris Stewart it was there even earlier see my article Repairing your Index
the background then, were manually written index globals.
the BIG advantage is to slice it down and run it as slow as you need with practically no extra load