There are two thresholds with LF indexes. First, you have the absolute max. The max is 9999 values. Second, you have the usable limit. This is the value that is open to interpretation and experience. My preference is in the 1500-2000 range. That isn't an absolute, though, so feel free to play around with this.
I, generally, advise against using HNG indexes, too. Most of this logic is done in other index structures. Also, the other indexes (HG, LF, n-bit) can allow for better performance in terms of parallelism, etc. To be honest, I haven't done a POC in the last 3+ years where I found that having an HNG index greatly improved performance. Like the LF index threshold, this is open to interpretation and experience. I would say that you try it out to see how your system reacts to having them or not. My experience would show that likely you won't see significant improvement.
Sybase IQ index growing size depends from system indexes, not from LF or HG indexes. You must 'truncate table'. Table size grows , then using 'delete table' - system indexes size is growing stabile.
Hi Mark Mumy,
Thanks so much for putting the post up. As a long time Sybase IQ user it is great to see it getting some air time in the SAP space now.
Two quick questions based off an Advanced Tuning workshop I went to on version 15.
Really would appreciate your insight here
Thanks
Clint
P.S. --> Thanks also for your sizing guide that I have used frequently over the years.. 🙂
Hi Clint Vosloo,
There are two thresholds with LF indexes. First, you have the absolute max. The max is 9999 values. Second, you have the usable limit. This is the value that is open to interpretation and experience. My preference is in the 1500-2000 range. That isn't an absolute, though, so feel free to play around with this.
I, generally, advise against using HNG indexes, too. Most of this logic is done in other index structures. Also, the other indexes (HG, LF, n-bit) can allow for better performance in terms of parallelism, etc. To be honest, I haven't done a POC in the last 3+ years where I found that having an HNG index greatly improved performance. Like the LF index threshold, this is open to interpretation and experience. I would say that you try it out to see how your system reacts to having them or not. My experience would show that likely you won't see significant improvement.
Mark
Thanks for the clarification !
Sybase IQ index growing size depends from system indexes, not from LF or HG indexes. You must 'truncate table'. Table size grows , then using 'delete table' - system indexes size is growing stabile.
RL
2017-12-08