CompTIA DataSys+ (DS0-001)Database FundamentalsHard
A junior database administrator is tasked with optimizing a slow query that frequently joins two large tables, `Orders` and `OrderItems`, on their `OrderID` columns. The `OrderID` column in both tables is already a primary key. What additional database object could the administrator create to further improve the performance of this specific join operation?
- ATrigger on `OrderItems` table
- BView combining `Orders` and `OrderItems`
- CStored procedure for the join query
- DIndex on `OrderID` in `OrderItems` (if not already indexed as part of a foreign key or primary key constraint)
Show answer & explanationAnswer & explanation
Correct answer: D. Index on `OrderID` in `OrderItems` (if not already indexed as part of a foreign key or primary key constraint)
While `OrderID` is a primary key in `Orders` (implying an index), it's crucial to ensure it's also indexed in `OrderItems` for efficient joins. A foreign key constraint typically creates an index, but if it's not explicitly indexed or if the foreign key is composite, an explicit index on `OrderID` in `OrderItems` would significantly speed up the join operation.
Why the other options are wrong
- A. A trigger executes DML operations based on events and is not used for query performance optimization.
- B. A view is a virtual table and does not inherently improve query performance; it just defines a query.
- C. A stored procedure encapsulates logic but doesn't, by itself, optimize the underlying query execution plan.
Database Index
A database object that provides quick lookup of data in a database table, similar to an index in a book.
- Speeds up data retrieval operations (SELECT queries).
- Slows down data modification operations (INSERT, UPDATE, DELETE).
- Can be created on one or more columns.
- Primary keys automatically have a unique index.
Memory trick: Indexes are your query's fast lane.