Rajat Sharma
Member
Hello everyone, hope you all are well
Please consider few of my concerns regarding indexes:
1. When we don’t define an index on the table than a default primary index is created on the table (checked), but what does this index or data structure (that index use) contains. When I tried to delete this index then progress gave me an error that “You can’t delete primary index of the table”, but how the whole table is treated as a primary index.
2. What is whole index? As per my understanding, whole index is only a naming convection because if whole index is created for whole table than it should be a wastage of memory/speed.
I tried some practice stuff for understanding WHOLE-INDEX:
XREF output of above code is:
SEARCH sports.customer custnum WHOLE-INDEX
Here, what is the meaning of custnum with whole-index(please suggest)? Initially I thought that, because custnum is a primary index on the table that’s why custnum is coming in XREF output but after checking the output I found that custnum's are coming in ascending order, apparently index on custnum field is being used there.
If I change my code something like this:
Here in above code, I thought that progress use name field as index and fetch the record faster but XREF output if different then I thought:
SEARCH sports.customer name WHOLE-INDEX
According to above XREF output, progress is still using WHOLE-INDEX with name index (really confused)
3. <> operator doesn't use index in progress, if I do something like this:
In above code, progress doesn't use balance or something as an index on first for each loop and decrease the performance. Do we have any solution of this problem?
4. What is Abbreviated and Active index in progress? I read in previous threads that abbreviated index is outdated thing and we should ignore that. Is there anything else do I need to take care regarding abbreviated index.
5. Is there any limit that we should apply on index? For ex: if number of fields in the table are n then index shouldn't be more than something.
Please suggest.
Along with this, word indexes and sequences aren't completely clear to me, could anyone please share relevant stuff for that.
Thanks & Regards!
Rajat.
Please consider few of my concerns regarding indexes:
1. When we don’t define an index on the table than a default primary index is created on the table (checked), but what does this index or data structure (that index use) contains. When I tried to delete this index then progress gave me an error that “You can’t delete primary index of the table”, but how the whole table is treated as a primary index.
2. What is whole index? As per my understanding, whole index is only a naming convection because if whole index is created for whole table than it should be a wastage of memory/speed.
I tried some practice stuff for understanding WHOLE-INDEX:
Code:
FOR EACH customer NO-LOCK NO-ERROR:
DISPLAY custnum name.
END.
XREF output of above code is:
SEARCH sports.customer custnum WHOLE-INDEX
Here, what is the meaning of custnum with whole-index(please suggest)? Initially I thought that, because custnum is a primary index on the table that’s why custnum is coming in XREF output but after checking the output I found that custnum's are coming in ascending order, apparently index on custnum field is being used there.
If I change my code something like this:
Code:
FOR EACH customer USE-INDEX name NO-LOCK NO-ERROR:
DISPLAY name.
END.
/*I know we shouldn't use USE-INDEX*/
Here in above code, I thought that progress use name field as index and fetch the record faster but XREF output if different then I thought:
SEARCH sports.customer name WHOLE-INDEX
According to above XREF output, progress is still using WHOLE-INDEX with name index (really confused)
3. <> operator doesn't use index in progress, if I do something like this:
Code:
FOR EACH customer WHERE customer.balance <> 0: /*balance or anything else with index property */
FOR EACH order of customer NO-LOCK NO-ERROR:
DISPLAY customer.name order.ordernum.
END.
END.
In above code, progress doesn't use balance or something as an index on first for each loop and decrease the performance. Do we have any solution of this problem?
4. What is Abbreviated and Active index in progress? I read in previous threads that abbreviated index is outdated thing and we should ignore that. Is there anything else do I need to take care regarding abbreviated index.
5. Is there any limit that we should apply on index? For ex: if number of fields in the table are n then index shouldn't be more than something.
Please suggest.
Along with this, word indexes and sequences aren't completely clear to me, could anyone please share relevant stuff for that.
Thanks & Regards!
Rajat.