Indexes


Definition

 

An index in computing is like the index of a book with the chapters and the page that each chapter starts on. We are not going to get into how the indexes are internally implemented as it is not a part of this course objective, but we will give you a few brief notions on how one defines them, to help one and to know when or when not to use them.

An index is a data structure that allows us to recuperate the rows of a field in a faster manner as well as to provide a different sort to the natural sort table. An index can be defined on a column or a group of columns, and the rows are arranged according to the values contained in these columns. For example, on the City column on a table of clients, the index allows the recuperation of clients in an alphabetical order of the city.

If the index is defined on various columns, the records are arranged in the first column, within the same value as the first column is arranged, so is the second, and so on. For example if we index a column about the columns province and city, it will arrange clients by province and within the same province by city, those from CO Denver will appear, those from TX Austin, and finally those from TX Dallas.

The order of the columns within an index is important, using the previous example lets define the index on city and province, those from Austin TX appear, followed by Dallas TX, and finally those from Denver CO. Now it has arranged by city and those clients from the same city are arranged in the province field.

 

Advantages and inconvenients.

 

Advantages:

If a table has an index defined on a column, Access can locate a row that has a determined value in this column much faster.

Queries based on an indexed column are faster, so it speeds up ordering and grouping on indexed columns.

 

Inconvenients:

Being a data structure additional to the table, it spands little more space on disk.

When adding, changing, deleting rows in a table, the Sytem needs then to update the affected indexes and these changes will involve a longer process time.

For these reasons it is not advisable to define indexes in an indiscriminate manner.

The inconvenients commented at this point are nothing in comparison to the advantages if the column that is defining the index is a column that is hardly used for looking or for organising the rows in the table. For this reason it is a good rule to define indexes about columns that are going to be used often to recuperate or organise the rows in a table.

Access automatically creates indexes on primary keys columns and on fields involved in relationships.

 

How to define an index.

 

An index is part of the structure of a table, so it is defined and modified from the Table design window.

If the index is based on a single field.

Select the field and modify the Indexed property.

If we click on the arrow to the right three options appear:

No: has no index.

Yes (Duplicates OK) : it defines an index with duplicates, in other words, the table can have various rows with the same values in the same column.

Yes (No Duplicates) : it defines an index without duplicates, in other words, the table cannot have two rows with the same values in the column.

If the index is based on various fields.

We need to open the Indexes window with the button, clic, and a window like this will appear:

In the first column type the Index Name, in the second column the name of the field that the index is based on, and in the third column we can indicate the Sort Order (if we want the rows in the table to be arranged in ascending or descending order).

If the index is based on various columns we put the name of the index on the first line with the first field it contains, and on the following lines the rest of the fields that form the index are shown.

In our example we have three indexes defined on the table, the PrimaryKey index was created by Access when we defined the Client Code field as the primary key, we have defined another index that we called Two_Fields on the Client State and Client City fields. Note how the order can be different in each of the fields. In this way the index can be used to recuperate the data alphabetically by State and within each State by City starting with the letter Z (Descending order).

To define the index perform the same operation as to create fields, position yourself in a row in the Index Name column, type the name of the index, go to the next column and choose the field from the drop down list or or type the name of the field, the same in Sort Order column (as this column only has two possible values, one can double clic and pass from Ascending to Descending or vice versa).

In the lower part we have the index properties:

Primary: indicates whether the index is the primary key or not.

Unique : Set to Yes duplicate values are not allowed in the column.

Ignore Nulls: if Yes, the rows that contain a null value in the field should be excluded from the index.

To edit, delete, or create new indexes we do the same as to edit, delete, and create fields in a table.

 



   
Legal warning: Authorised on-line use only. It is not allowed the use of these courses in companies or private teaching centres.
© aulaClic. All rights reserved. Reproduction in any form whatsoever is prohibited. April-2006.