Choose the correct responses to each of the multiple-choice questions. Note that there may be more than one correct response to each question.
Performance requirements a. Should be set after the SQL statements are tuned b. Provide a way to identify statements that need tuning c. Are best when they contain complex criteria d. Provide a way to know when to stop tweaking a query e. Are developed just to make the auditors happy Disk reads and writes can be minimized by a. Allocating buffers of sufﬁcient size b. Placing all the database ﬁles on one disk drive c. Putting the entire database in memory d. Adding indexes for all the important table columns e. Spreading ﬁles across all available disk drives The computer system can be tuned by a. Collecting database statistics b. Applying available security patches c. Selecting fast and reliable hardware d. Following the DBMS tuning recommendations e. Consulting a tuning guide for the operating system Efﬁcient table design includes a. Using VARCHAR for all variable-length character columns b. Using the smallest possible numeric data type that holds the data values c. Using triggers whenever possible d. Using identical data types for primary keys and their matching foreign keys e. Using identical data types for all primary key columns A query execution plan a. Describes how the DBMS will execute a query b. Is stored in the SQL cache c. Is created using the explain plan feature of the RDBMS d. Requires a plan table to hold the explain results e. Requires the use of a stored procedure The query optimizer a. Creates a query execution plan in the plan table b. Determines the best way to execute an SQL statement c. May use statistics gathered from the database d. May use rules applied to the way the statement was written e. Can be cost-based or rule-based In order to design proper queries, the developer should a. Know the characteristics of the data in the database b. Match data types in predicates c. Use hints as much as possible d. Avoid unnecessary tables and columns e. Maximize the number of rows in each result set Table scans can be avoided by a. Including a WHERE clause that references an indexed column b. Using GROUP BY instead of DISTINCT c. Making sure that statistics are up to date d. Making at least one predicate references the leading column of an index e. Avoiding unnecessary columns An index cannot be used when a. The WHERE clause references the second column of an index b. A LIKE clause references a comparison string that contains a wildcard (except in the ﬁrst position of the string) c. The NOT operator is used in a predicate d. An SQL function is included in a column comparison (except when they match a function-based index) e. The NOT EQUAL operator is used in a predicate Considerations for using indexes include a. Placing indexes on all frequently updated columns b. Placing indexes on foreign key columns c. Avoiding overlapping indexes d. Creating indexes on columns that have only a few possible values e. Avoiding unique indexes Tuning considerations for MySQL include a. Function-based indexes b. Hash indexes c. Clustering indexes d. Storage engine options e. Bit-map indexes Tuning considerations for Oracle include a. Function-based indexes b. Hash indexes c. Clustering indexes d. Storage engine options e. Bit-map indexes Tuning considerations for Microsoft SQL Server include a. Function-based indexes b. Hash indexes c. Clustering indexes d. Storage engine options e. Bit-map indexes An explain plan in Oracle a. Requires the use of a plan table b. Contains a PLAN_ID to uniquely identify it c. Can be viewed using Enterprise Manager d. Can be viewed with SQL by selecting it from the plan table e. Is created using the CREATE PLAN statement An execution plan in Microsoft SQL Server a. Requires the use of a plan table b. Can be displayed using an option in SQL Query Analyzer c. Displays the execution plan in a text format d. Displays the execution plan in a graphical format e. Can be viewed with SQL by selecting it from the plan table When tuning INSERT statements, one should consider a. Index maintenance b. Row expansion c. The CASCADE option d. Adequate free space e. Query rewrites When tuning UPDATE statements, one should consider a. Index maintenance b. Row expansion c. The CASCADE option d. Adequate free space e. Query rewrites When tuning DELETE statements, one should consider a. Index maintenance b. Row expansion c. The CASCADE option d. Adequate free space e. Query rewrites Data types should match a. For all primary key columns b. Between primary key and corresponding secondary key columns c. Between primary key and corresponding foreign key columns d. Between column values and literal values compared in predicates e. For all function-based indexes The most likely cause of query performance problems is a. A poorly tuned operating system b. A poorly written SQL statement c. Trigger overhead d. Index maintenance overhead e. Row expansion