Hour 16, "Using Indexes to Improve Performance"

Quiz Answers

1: What are some major disadvantages of using indexes?
A1: Major disadvantages of an index include slowing batch jobs, storage space on the disk, and maintenance upkeep on the index.
2: Why is the order of columns in a composite important?
A2: Because query performance is improved by putting the column with the most restrictive values first.
3: Should a column with a large percentage of NULLs be indexed?
A3: No. A column with a large percentage of NULLs should not be indexed, because the speed of accessing these rows degrades when the value of a large percentage of rows is the same.
4: Is the main purpose of an index to stop duplicate values in a table?
A4: No. The main purpose of an index is to enhance data retrieval speed, although a unique index stops duplicate values in a table.
A4: False. The main reason for composite indexes is for two or more columns in the same table to be indexed.
5: True or false: The main reason for a composite index is for aggregate function usage in an index.
A5: False. The main reason for composite indexes is for two or more columns in the same table to be indexed.

Exercise Answers

1: For the following situations, decide whether an index should be used and, if so, what type of index should be used.

  1. Several columns, but a rather small table.

  2. Medium-sized table, no duplicates should be allowed.

  3. Several columns, very large table, several columns used as filters in the WHERE clause.

  4. Large table, many columns, a lot of data manipulation.

A1:

  1. Being a very small table, no index needed.

  2. A unique index could be used.

  3. A composite index on the columns used as filter in the WHERE clause should be the choice.

  4. A choice of a single-column or composite index should be considered, depending on filtering, ordering, and grouping. For the large amount of data manipulation, the index could be dropped and re-created after the INSERT, UPDATE, or DELETE jobs were done.

..................Content has been hidden....................

You can't read the all page of ebook, please click here login for view all page.
Reset