Week 2 - Major Indexing Schemes

Question 1

Hash Indexes what is it good for ?

Answer 1

1) Hash-based indexes are good for equality selections
(age =16, id=2)
2) takes keys and puts data in data bucket

Question 2

What kind of indexing scheme should be used in order to retrieve data on customers whose zip code is equal to 06902?

1) Equality index
2) Clustered index
3) Heap-based index1
4) Hash-based index

Answer 2

4) Hash-based index

Question 3

Index entries have what

Answer 3

Have pointers to where that data is.

So, the index entries help you just navigate,
so it help you search,
and the data entries are the last level where you go ahead, and retrieve the data from where it’s stored in the database.

Question 4

Ways to store data entries

Answer 4

1) key (record ID)

2) Key (list of record IDs)

Question 5

We know that data is stored in the form of records. Every record has a?

Answer 5

key field

Question 6

An ______ is a data structure that locates these key fields within the database file

Answer 6

index

Question 7

What is a clustered index

Answer 7

Data records are sorted like the data is sorted. Stored by the attribute (like an id)

Question 8

un-clustered index

Answer 8

The record is not sorted by the data.

Note same sorting order

Question 9

What type of index should you use?

Answer 9

Depends on workload

Question 10

Where should you put the index if all the queries access the same attribute

Answer 10

1) The index should be on that attribute (what is in the WHERE )
2) how what percentage of the data is selected Lower the better

Question 11

IF the query selects 95% of data is that a good place to put and index

Answer 11

No

Lower is better (more refined)

Question 12

Cost of Indexes

Answer 12

1) storage (hard disk space)
2) If there are lots of inserts can be slower
3) Maintain index (hard if updating it a lot)

Question 13

If a database has m rows and n columns, what is the maximum number of clustered indexes for this table?

1) 1
2) n
3) mn
4) m

Answer 13

1) 1

A database table can only have one clustered index.

Question 14

Four considerations for creating a new index

Answer 14

1) Consider the most important queries
2) Consider the best plan using the current indexes
3) Determine if a better plan is possible with an additional index
4) How often the index needs to be updated

Question 15

4 Index selection Guidelines

Answer 15

1) Attribute in the WHERE clause
2) Keys (primary, unique attribute)
3) Searches on multiple attribute
4) If index will benefit multiple queries

Question 16

B+ tree index are good for

Answer 16

IDs, numbers, group by

Question 17

HASHed indexs are good for

Answer 17

Equality WHERE hobby = stamps

Question 18

Composite Keys

Answer 18

Composite index (index on two or more values)

Question 19

In a composite index which is given priority?

Answer 19

age

First item is given priority

Question 20

Can you ever find all the info you need without going to the table

Answer 20

Yes, sometimes the index has everything you need.

Example:
Index based on departmentNumber

Select E.departmentNumber from Emp E GROUP BY E.departmentNumber ;

Question 21

Creating indexes is not a straightforward decision because of the costs involved. One must answer certain questions before creating indexes. Which of the following questions is NOT relevant to creating an index?

1) Which field(s) should be part of an index?
2) Which tables should be indexed?
3) Should one build multiple indexes?
4) Which record(s) should be part of an index?

Answer 21

4) Which record(s) should be part of an index?

This question is irrelevant to building indexes; an index is built using fields, not records.

Question 22

Which of the following database operations can be achieved by using hash-based indexes?

1) To retrieve student data where age is equal to 20.
2) To retrieve student data where age is between 18 and 20.
3) To retrieve student data where age is different than 20.
4) To retrieve student data where age is less than 20.

Answer 22

1) To retrieve student data where age is equal to 20.

Hash-based indexes are good for equality selections.

Question 23

If a database table has m rows and n columns, what is the maximum number of clustered indexes for this table?

1) 1
2) m
3) n
4) mn

Answer 23

1) 1

A database table can only have one clustered index.

Question 24

Which of the following is/are among the disadvantages of indexes? Choose all that apply.

1) Increased cost of storage.
2) Increased cost of maintenance.
3) Decrease in performance of select queries.
4) Decrease in performance of update queries.

Answer 24

1) Increased cost of storage.

2) Increased cost of maintenance.