Question Index Important

[Rajat Sharma]

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:

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:

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:

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.

 

[TheMadDBA]

Oh boy Here is the short version, you should really read up on the documentation. There are also several knowledgebase entries about index selection and some decent powerpoints/presentations on the pug sites.

1. If you don't define any indexes on a table (very bad idea) the default index will store the RECID/ROWID of the table. Always define indexes in the real world.

2. WHOLE-INDEX means the query will read every record in that table (using that index), regardless of what is in the WHERE clause. Certain exceptions apply for FIND FIRST/LAST when you are using the indexed field properly in the WHERE. It has nothing to do with parts of the table being indexed or not. Change your for each to for each customer where name begins "C" and the WHOLE-INDEX goes away because it reading parts of the index instead of the entire index.

3. NE/<> and a variety of other things (functions, NOT,etc.) will not use an index. For your specific example you could say for each customer where balance < 0 OR balance > 0... assuming there is an index on balance it will use it twice to get to the results. If most of your records have non zero values it may not make a huge difference though.

4. Active means the index is active, the opposite of Inactive which means the index is defined but not built and ready for use. Just ignore abbreviated and don't define any more with abbreviated.

5. What makes sense for your application and usage. Indexes are a trade-off, every time you create/delete or update indexed columns the index has to be updated. But most systems are very read heavy, and indexes make the queries run faster/reduce load on the system.

The documentation covers word indexes and sequences very well. Sequences have nothing to do with indexes.

 

[Rajat Sharma]

Thanks for replying MadDBA!

My concern regarding whole-index is slight different.

Does whole-index really exist or does progress really use a data structure for whole index. if yes than what does whole-index data structure contains ( does they contain all RECID'S/ROWID'S of table)

Why custnum and name (as i mentioned in previous post) fields are coming with WHOLE-INDEX (in xref output) and by using USE-INDEX name, progress continue using WHOLE-INDEX and output of xref contains "SEARCH sports.customer name WHOLE-INDEX".

Sure, I will check all the relevant content on the PUG site.

Thanks & Regards!
Rajat.

 

[TheMadDBA]

WHOLE-INDEX is not an index... It is a warning in the XREF that something might be terribly wrong if you are not expecting to read every single record in that table.

SEARCH sports.customer custnum WHOLE-INDEX

In your example it means that it is using the custnum index to read the sports.customer table, WHOLE-INDEX means that it is reading the entire contents of the customer table, using the custnum index. If that is what you intend to do, then it is fine. If you were expecting to only get a subset of the customer table, then you have a problem with your WHERE clause and/or the proper indexes are not defined to support reading only that subset.

This is also covered in the documentation and the knowledge base.

 

[TomBascom]

You should start here: http://pugchallenge.org/downloads2014/374_Still_Dont_Know_About_Indices_PCA2014.pdf