INDEX

Did you try reading the manuals?

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

• Database Essentials
• Getting Started with Progress ABL

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 http://knowledgebase.progress.com).

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:

• http://pugchallenge.org/ (PUG Challenge Americas) -- Look at the Downloads section
• http://www.pugchallenge.eu (EMEA PUG Challenge)

Good luck.

And the ultimate user conference session on the topic:

http://pugchallenge.org/downloads2014/374_Still_Dont_Know_About_Indices_PCA2014.pdf

Helpful documentation also on:

documentation.progress.com/.../ABL_triggers_and_indexes.pdf

Regards,

Gabriel

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 :)

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