CCNC/CCNC Module 5/Designing and creating tables/Defining Keys/Indexes

---

Creating Indexes

Outcomes

Upon completion of this tutorial the learner will be able to: Describe the different kinds of Indexes Create and rename an Index Delete an Index Save an Index Reset an Index

What is an Index

Figure 1. Designing an Index

An index improves performance by providing an alternate path to access data and speeds up searches, sorting, queries etc. An index can be applied to any data type. Indexes are appled to fields that are commonly used in searching for data (like a last name) or where data needs to be grouped together for a report. Creating and using indexes incurs a cost to the database so it is not a good idea to apply indexes to every field in a table.

To open the indexing dialog select Tools -> Index Design from the available menus.

Creating an Index

Figure 2. Designing an Index

Notice that there is an option to make this a unique index. In other words if you do not want duplicate entries here you would click on Unique. This would not work here as there are going to be instances where many people have the same surname. However, if you were creating the index on a field like an account number you would then choose not to allow duplicates by activating the Unique option.

Index Actions

In the upper left of the Indexes dialog are five buttons.

Create a New Index

This button creates a new index by prompting you for the field names you would like indexed. Give this some thought and think about the discussion of table scans covered previously.

Remember to save the index by clicking on the save button. If you have not saved and you click on the close button you will get the following prompt “Do you want to save the changes made to the current index? At this point you could save.

Delete an Index

This button deletes the highlighted index.

Rename an Index

This button renames the highlighted index.

Save an Index

This button saves the new, renamed and changed indexes.

Reset an Index

This button resets the index.

Test your knowledge

Addresses

Field Name	Data Type	Required	Description
address_id	Integer	Yes	An AutoValue field for uniqueness.
address_line1	Text	Yes	First line describing an address. Could be street name or suite number.
address_line2	Text	No	Second line describing an address.
city_name	Text	Yes	The name of the city
region	Text	Yes	The name of the region. Could be province, state, etc.
country	Text	Yes	The name of the country
postal_code	Text	Yes	The localized postal code or equivalent

Defining Indexes

1. Indexes can only be applied to text fields? TRUE → Incorrect, they can be applied to any data type. Click here to review the topic. FALSE → Absolutely Correct! 2. From the Addresses table design above which fields would you apply indexes? (a) address_id, country_name → Incorrect, address_id does not need to be indexed. Click here to review the topic. (b) city_name, country_name, postal_code → Incorrect, region should be included. Click here to review the topic. (c) address_id, address_line1, postal_code → Incorrect, click here to review the topic. (d) city_name, region, country_name, postal_code → Absolutely correct! (e) address_id, region, country_name, postal_code → Incorrect, click here to review the topic. 3. Indexes can be applied to enforce uniqueness? TRUE → Absolutely Correct! FALSE → Incorrect, they can be applied to enforce uniqueness. Click here to review the topic. Your score is 0 / 0