Indexing and Query Optimization Lecture Notes

Flashcards about Indexing and Query Optimization.

What is the primary purpose of indexing in a database?

To improve query performance by reducing data retrieval time without scanning the entire table.

What is a clustered index?

An index that physically sorts the data rows in the table based on key values.

How many clustered indexes can exist per table and why?

Only one, because data rows can be physically sorted in only one way.

What is a non-clustered index?

An index that contains a separate structure to store index keys and pointers to actual data rows.

How many non-clustered indexes can exist per table?

Multiple non-clustered indexes can exist per table.

What is a B-Tree index?

A balanced tree structure that maintains sorted data for quick data retrieval, commonly used for both clustered and non-clustered indexes.

For what type of searches are hash indexes most efficient?

Equality searches.

Name three actions involved in query processing.

Validating SQL statements, query rewriting for simplification, and generating execution plans.

What is predicate pushdown in query optimization?

Filtering data early in the query process.

Why is it important to regularly update statistics in a database?

To assist the optimizer in selecting the most efficient execution plan.