opt Flashcards
(27 cards)
What is query optimization?
Process of improving query execution performance
Query optimization focuses on enhancing the efficiency of executing database queries.
Which of the following is a goal of query optimization?
Minimize disk I/O
Reducing disk input/output operations can significantly enhance performance.
What does the term ‘cardinality’ refer to in query optimization?
The number of rows in a table
Cardinality is a critical factor in understanding query performance.
Which of the following can improve query performance?
Proper indexing
Proper indexing allows the database to quickly locate the requested data.
Which of the following is a heuristic optimization strategy?
Choosing the most selective condition first
This strategy aims to minimize the result set early in the query execution.
What is an execution plan?
A detailed description of how a query will be executed
Execution plans help database administrators understand query performance.
Which of the following is NOT a factor in query optimization?
Network speed
While network speed can affect overall performance, it is not a direct factor in query optimization.
What does ‘normalization’ aim to achieve?
Reduce data duplication
Normalization is essential for ensuring data integrity in databases.
Which SQL clause is primarily used to filter records?
WHERE
The WHERE clause is crucial for specifying conditions in SQL queries.
Which of the following is a common query optimization technique?
Query rewriting
Query rewriting involves modifying queries for better performance without changing their meaning.
What does the ‘GROUP BY’ clause do?
Aggregates data based on specified columns
GROUP BY is commonly used with aggregate functions like SUM or COUNT.
Which of the following is NOT a benefit of using indexes?
Increased data redundancy
Indexes are intended to optimize performance rather than increase redundancy.
What is ‘denormalization’?
Combining tables to improve performance
Denormalization can enhance read performance at the cost of write performance.
What does the ‘HAVING’ clause do?
Filters records after grouping
HAVING is used in conjunction with GROUP BY to filter aggregated results.
Which of the following can cause a full table scan?
A missing index
A full table scan occurs when no indexes are available to optimize the query.
What is the purpose of query rewriting?
To improve query performance without changing semantics
This technique can lead to more efficient execution plans.
In SQL, what does ‘EXPLAIN’ do?
Shows the execution plan for a query
The EXPLAIN command provides insight into how a SQL query will be executed.
Which of the following can be used to analyze query performance?
Execution plan
An execution plan outlines the steps the database will take to execute a query.
Which type of join returns all rows from the left table?
Left Outer Join
Left Outer Join includes unmatched rows from the left table in the result.
Which of the following is an aspect of the query optimization process?
Query planning
Query planning involves determining the most efficient way to execute a query.
What is ‘query caching’?
Storing query results for faster retrieval
Query caching can significantly speed up repeated query executions.
Which SQL keyword is used to remove duplicate rows from a result set?
DISTINCT
DISTINCT ensures that only unique rows are returned in the result set.
Which operation is typically more efficient with an indexed column?
SELECT
Indexes enhance the performance of SELECT operations by allowing quicker lookups.
Which of the following is NOT a type of JOIN in SQL?
Logical Join
Logical Join is not a recognized type of JOIN in SQL; valid types include Inner, Outer, and Cartesian Joins.
