DBMS - File Indexing

Question 1

Index Structure

Answer 1

Data Structure and associated set of operations whose goal is the effective retrieval of data based on a criterion matched to part of the data file

Question 2

Example of index Structures

Answer 2

BTree, Extensible Hashing, Bit mapped

Question 3

Example of search key

Answer 3

Empno, deptno, sal

Question 4

Indexed key

Answer 4

not necessarily the primary key set or the foreign key

Question 5

Index File

Answer 5

The reordering of part of a data file’s record to facilitate certain retrieval patterns.

Composed of many pairs of search key values and data file record pointers to the respective record with that matching value in the data file.

Question 6

Index building and maintenance activities

Answer 6

needed to maintain its currency

Question 7

Index retrieval

Answer 7

usually meant to return an address in the data file where key matches criteria

Question 8

Index range scan

Answer 8

Oracle reads root node of index and block in each branch level. Leaf blocks scanned until the end of the range is encountered.

Question 9

Two index modality of interest

Answer 9

Indices with ordered search keys, indices with search keys hashed

Question 10

Clustered Index

Answer 10

Index with its search key specified on the ordering field of a sequential file of records.

Search key could be PK, if it is we have an index-sequential file.

Question 11

Non-Clustered Index

Answer 11

An index on the ordering field of an ordered file of records.

Can either have primary or clustering type index but not both.

Question 12

Secondary Index

Answer 12

Can be specified on any non ordering field of a data file. Doesn’t interfere with primary or clustered indexes.

Question 13

Index Density

Answer 13

The ratio of search keys to records in the data. If >=1 it is dense.

Question 14

Multilevel indexing

Answer 14

Indexes of indexes

Question 15

Why Indexing?

Answer 15

• Max access speeds
• Min access costs
• sequential traversal of data
• constraints for query, semantic and security

Question 16

Index Computational Costs

Answer 16

• Time
• Space
• Resilience
• Concurrency Support

Question 17

Index Issues

Answer 17

• Each index structure comes with a respective set of retrieval operators
• Excessive use of indexes deteriorates and floods disk IO bandwidth
• Concurrency Control Issues
• Indexes created and maintained but never used
• Index use vs Sequential Scan

Question 18

Pin the address to an actual memory/file location

Answer 18

as late as possible

Question 19

Covering Indexes

Answer 19

When index entry contains the data record too

Question 20

Delete in a PK index

Answer 20

Search key in index, get record in data file, delete and reorganize data file

Question 21

Delete in a data file with a primary index

Answer 21

Get record in data file, search key in each primary index, delete index entry and reorganize each primary index

Question 22

Delete in a non-clustered index (By search index entry)

Answer 22

Get record in data file, get index entry.
IF data file has 1 search key instance, delete data file+index entry+ reorganize both
ELSE delete data file record and reorganize data file

Question 23

Delete in a non-clustered index (By RRN entry)

Answer 23

Search key in index, get first record in data file, DO delete and reorganize data file, WHILE next RRN has same key. Delete index entry and reorganize

Question 24

Sorting Datafiles Requirement

Answer 24

• If data fits into main memory use Quicksort
• If it doesnt fit into main memory use external merge sort
• Can use indexes to help sort

Question 25

Sorting Requirements

Answer 25

ORDER BY or SELECT DISTINCT

In data cursors that is processing some subset of a table in main memory, one can sort it or leave it unordered.