Get access to thousands of high quality educational resources

Discuss some of the main reasons for selecting a column as a potential candidate for indexing. Give examples to illustrate your answer.

      

Discuss some of the main reasons for selecting a column as a potential candidate for indexing. Give examples to illustrate your answer.

  

Answers

KELVIN
(1)In general, index the primary key of a table if it’s not a key of the file organization. Although the SQL standard provides a clause for the specification of primary keys as discussed in Step 3.1 covered in the last chapter, note that this does not guarantee that the primary key will be indexed in some RDBMSs.
(2)Add a secondary index to any column that is heavily used for data retrieval. For example, add a secondary index to the Member table based on the column lName, as discussed above.
(3)Add a secondary index to a foreign key if there is frequent access based on it. For example, you may frequently join the VideoForRent and Branch tables on the column branchNo (the branch number). Therefore, it may be more efficient to add a secondary index to the VideoForRent table based on branchNo.
(4)Add a secondary index on columns that are frequently involved in:
(a) selection or join criteria;
(b) ORDER BY;
(c) GROUP BY;
(d) other operations involving sorting (such as UNION or DISTINCT).
(5)Add a secondary index on columns involved in built-in functions, along with any columns used to aggregate the built-in functions. For example, to find the average staff salary at each branch, you could use the following SQL query:
SELECT branchNo, AVG(salary)
FROM Staff
GROUP BY branchNo;
From the previous guideline, you could consider adding an index to the branchNo column by virtue of the GROUP BY clause. However, it may be more efficient to consider an index on both the branchNo column and the salary column. This may allow the DBMS to perform the entire query from data in the index alone, without having to access the data file. This is sometimes called an index-only plan, as the required response can be produced using only data in the index.
(6)As a more general case of the previous guideline, add a secondary index on columns that could result in an index-only plan.
kalvinspartan answered the question on July 4, 2018 at 17:12

Next: When would you not add any indexes to a table?
Previous: Discuss the purpose of analyzing the transactions that have to be supported and describe the type of information you would collect and analyze.

View More Computer Science Questions and Answers | Return to Questions Index


Exams With Marking Schemes

Related Questions

© 2008-2024 by KenyaPlex.com. All Rights Reserved | Home | About Us | Contact Us | Copyright | Terms Of Use | Privacy Policy | Advertise