Posted by rahul on 16-Aug-2014 10:06

what is the usage of INDEX ?

1 ) default index

2) primary index

3) unique

4) word

5) whole index

Difference between find first and can find ?

Some anyone had made notes if possible ?


All Replies

Posted by Thomas Mercer-Hursh on 16-Aug-2014 10:37

Did you try reading the manuals?

Posted by Bill Wood on 16-Aug-2014 14:13

I second Dr. Mercer-Hursh's suggestion.  Understanding INDEXES is the best thing you can do if you want to use a relational database.  INDEX concepts are important generally for getting good performance as your database grows, and not just for OpenEdge.

There is lots of detail in the OpenEdge Documentation (Communites has a link to it here).  Two good starting places are

These will tell you that an INDEX is a concept used by the database engine to speed up the retrieval of information.  Indexes have a downside. They impose additional writes and storage consumption by the data structures used to maintain the index whenever a record is updated.  Without an index, the database engine must read and compare every record in the database table when a query is performed.  With an index, 'random' records can be accessed quickly, and in order.

You do need to understand INDEXES to use them correctly.  Too many indices will increase database size, and increase the time to do Create, Update, and Delete operations.   For a beginner with Relational Databases, limit their use until you have more expertese.

A database table can have many indices.  Typically, if there is a  single key field that will be used to look up records (or used to relate other tables), then this is PRIMARY KEY.   It is good practice to INDEX your primary key.   

You can index fields that are not unique if you want to commonly search for the values of these fields.  Looking for residents or customers by zip code, or state, may be done, even though many records share the same state or zip code.  Indexing the customer.zipCode or resident.state would have an index that is not unique.  A UNIQUE INDEX would be something like Social Security Number, or EmployeeID -- fields that are not shared by more than one record.

OpenEdge supports a type of index called a WORD INDEX.  A word index is used to find 'words' and phrases in large text fields that contain free-form text (like a Product.Description, or a Employee.Resume field).  The word index parses the long text fields and stores a list of the words in each.  You can then more efficiently look for records that contain collections of natural language words (even if the fields are not 'exactly equal' to those words or phrases.   

Again the first place you should look is the documentation.  There are also a number of good Knowledge Base articles on the Progress web site. (see

Other great resources are presentations from past Progress Exchanges, or from the Progress User Group PUG Challenges.    See the links and downloads which often include Audio as well as the Presentations at:

  • (PUG Challenge Americas) -- Look at the Downloads section
  • (EMEA PUG Challenge)

Good luck.

Posted by Mike Fechner on 16-Aug-2014 14:17

And there is the classic article now in the K-Base – a must read:


Posted by ChUIMonster on 17-Aug-2014 08:47

And the ultimate user conference session on the topic:

Posted by gabriel.lucaciu on 20-Aug-2014 12:32

Helpful documentation also on:



Posted by Marian Edu on 20-Aug-2014 12:47

best documentation is probably that no one even need :)

now that the abl was more a move to the 3-rd than 4-th generation languages wonder how a database that doesn't need any indexes defined (let aside the primary key and other unique constraints) would look like... while might still be true a progress database can run with no dba (at least not full-time) the performance is often impacted by poor database design.

thing is, you're clever guys so why not a 4gl alike database that can add/remove indexes as needed... see constant filters on some fields add a new index to avoid full table scan for those, other indexes (again, not unique) that are used only once a year go ahead ad drop them.

that can be more than just a cost base optimizer although that would definitively be a great step toward, especially given the usage of dynamic queries... late already here so not just a day time dream :)

Posted by gus on 21-Aug-2014 09:03

@marian: these are good ideas, worth considering. even with such features, people are very ingenious and will find new ways to achieve bad performance.

This thread is closed