CCNC/CCNC Module 5/Designing and creating tables/Defining Keys/Indexes
|Defining Keys and Indexes|
What is 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
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.
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
|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|