Final - Performance Tuning Flashcards
What is database tuning?
The process of continuously adjusting the database design and configurations to optimize performance, including tuning indexes and table structures.
What is an index in a database?
A: A data structure that provides a pointer to the location of data in a table to improve retrieval speed.
What is an ordered index?
An index where search keys are stored in a sorted structure, typically using B or B+ trees for efficient range queries.
What is a hash index?
An index that distributes search keys uniformly across buckets using a hash function, optimized for exact lookups.
What is query evaluation?
A: The process of choosing and executing a strategy to compute the result of a query efficiently.
What is an evaluation plan?
A: A plan that specifies the exact algorithms used for query operations and how they are executed.
What is SQL tuning?
The process of optimizing SQL queries to improve performance by reducing disk accesses, utilizing indexes, and optimizing joins.
What is heuristic query optimization?
An optimization technique that applies rules to improve query efficiency without guaranteeing the best possible execution plan.
What are two primary reasons for using indexes in a database?
To speed up data retrieval and reduce query execution time.
When should you avoid using indexes?
When working with small tables, columns with a high number of NULL values, or columns in queries that return a high percentage of rows.
What is the difference between a hash index and a B-tree index?
A: A hash index is optimized for exact matches, while a B-tree index is better for range queries.
Q: What are some indicators that a SQL query needs tuning?
A: High CPU usage, excessive disk reads, full table scans on large tables, and long response times.
What is a left-deep join tree, and why is it preferred?
A join tree structure where each join operation has a base relation on the right, optimizing execution in query optimizers.
What are some best practices for writing efficient SQL queries?
Use indexes, avoid unnecessary columns in SELECT, limit full table scans, and optimize joins and WHERE conditions.
Q: What is the main difference between a heuristic and a cost-based optimizer?
A heuristic optimizer follows fixed rules, while a cost-based optimizer evaluates multiple plans and picks the cheapest one.
