Quiz 10 - Database Performance Tuning and Query Optimization

12/14/24, 2:01 AM Quiz: Database Performance Tuning and Query Optimization: COMP 440 DATABASE DESIGN - (17042-FA2024) Quiz: Database Performance Tuning and Query Optimization Due Dec 13 at 11:59pm Points 15 Questions 15 Time Limit 30 Minutes Allowed Attempts 2 Take the Quiz Again Attempt History LATEST Attempt Time Score Attempt 1 21 minutes 14 out of 15 * * Some questions not yet graded  Correct answers are hidden. Score for this attempt: 14 out of 15 * * Some questions not yet graded Submitted Dec 13 at 11:04pm This attempt took 21 minutes.  Question 1 1 / 1 pts The CPU handles what type of comparisons faster than character and date comparisons? Numeric Correct. The CPU handles numeric comparisons (integer and decimal) faster than character and date comparisons. character null https://canvas.csun.edu/courses/154575/quizzes/571410 1/7 12/14/24, 2:01 AM Quiz: Database Performance Tuning and Query Optimization: COMP 440 DATABASE DESIGN - (17042-FA2024) date  Question 2 1 / 1 pts If you are using an index and you want to mainly have tables in which column values repeat a relatively small number of times, what type of index would you use? B-Tree Correct. B-tree index is used mainly in tables in which column values repeat a relatively small number of times. Bitmap Hash Data  Question 3 1 / 1 pts The LIKE conditional operator is used by the _____ OPERAND1. P_QOH V_STATE V_CONTACT Correct. The LIKE conditional operator is used by the V_CONTACT OPERAND1. P_PRICE  Question 4 1 / 1 pts How can you write this phrase NOT (C_COST > 20.00) without using NOT? C_COST < = 20.00. Correct. C_COST < = 20.00 is best to transform a SQL expression that contains a NOT logical operator into an equivalent expression. C_COST > 20.00 C_COST = 20.00 C_COST => 20.00 https://canvas.csun.edu/courses/154575/quizzes/571410 2/7 12/14/24, 2:01 AM Quiz: Database Performance Tuning and Query Optimization: COMP 440 DATABASE DESIGN - (17042-FA2024)  Question 5 1 / 1 pts The data cache is where the data, read from the database data files, are stored _____ the data have been read or _____ the data are written to the database data files. before; after after; after after; before Correct. The data cache is where the data read from the database data files are stored after the data have been read or before the data are written to the database data files. before; before  Question 6 1 / 1 pts As an analyst you need to use a hash algorithm to create a hash value from a key column. What type of index is this? Hash index Correct. A hash algorithm is used to create a hash value from a key column in a hash index. Data index B-tree Index Bitmap index  Question 7 1 / 1 pts What type of plan is used when it contains the series of steps a DBMS will use to execute the query and return the result set in the most efficient way? Access plan Correct. An access plan contains the series of steps a DBMS will use to execute the query and return the result set in the most efficient way. Data Plan Action plan https://canvas.csun.edu/courses/154575/quizzes/571410 3/7 12/14/24, 2:01 AM Quiz: Database Performance Tuning and Query Optimization: COMP 440 DATABASE DESIGN - (17042-FA2024) Optimization plan  Question 8 Not yet graded / 1 pts What are the modes that an optimizer can create and describe query optimization? ANS: See the ‘Rationale' field for sample responses. REJ: Query optimization is the central activity during the parsing phase in query processing. In this phase, the DBMS must choose what indexes to use, how to perform join operations, which table to use first, and so on. Each DBMS has its own algorithms for determining the most efficient way to access the data. The query optimizer can operate in one of two modes: A rule-based optimizer uses preset rules and points to determine the best approach to execute a query. The rules assign a "fixed cost" to each SQL operation; the costs are then added to yield the cost of the execution plan. For example, a full table scan has a set cost of 10, while a table accessed by row ID has a set cost of 3.A cost-based optimizer uses sophisticated algorithms based on statistics about the objects being accessed to determine the best approach to execute a query. In this case, the optimizer process adds up the processing cost, the I/O costs, and the resource costs (RAM and temporary space) to determine the total cost of a given execution plan. LO: 11.04: Differentiate between a rule-based optimizer and a cost-based optimizer: A-Head: Optimizer Choices Bloom's: Remember 73. Your management team wants to know why they need to optimize a DBMS with SQL performance tuning, even though they automatically optimize SQL queries. Your Answer: A rule-based optimizer uses preset rules and points to determine the best approach to execute a query. A cost-based optimizer uses algorithms based on statistics about the objects being accessed to determine the best approach to execute a query. Answer: See the 'Rationale' field for sample responses. https://canvas.csun.edu/courses/154575/quizzes/571410 4/7 12/14/24, 2:01 AM Quiz: Database Performance Tuning and Query Optimization: COMP 440 DATABASE DESIGN - (17042-FA2024) Rationale: There is considerable room for improvement since the DBMS uses general optimization techniques rather than focus on specific techniques dictated by the special circumstances of the query execution. A poorly written SQL query can, and usually will, bring the database system to its knees from a performance point of view. The majority of current database performance problems are related to poorly written SQL code. Therefore, although a DBMS provides general optimizing services, a carefully written query almost always outperforms a poorly written one.  Question 9 1 / 1 pts All transaction management commands are processed during the parsing and execution phases of query processing. True Correct. All transaction management commands are processed during the parsing and execution phases of query processing. False  Question 10 1 / 1 pts If you are using the raid levels, what type of raid level is used for writing the same data blocks to separate drives? partitioning aggregating mirroring Correct. Mirroring is the raid level that is referred to writing the same data blocks to separate drives. striping  Question 11 1 / 1 pts Given the following query SELECT MilitaryID, JobTitle, Rank FROM Enlisted, Job WHERE Enlisted.JobTitle = Job.JobTitle AND https://canvas.csun.edu/courses/154575/quizzes/571410 5/7 12/14/24, 2:01 AM Quiz: Database Performance Tuning and Query Optimization: COMP 440 DATABASE DESIGN - (17042-FA2024) Enlisted.Decision = TRUE and Job.Rank > 4 Which of the following indices could NOT be useful in speeding-up the query? hash-based index on Enlisted.Decision tree-based index on Enlisted.JobTitle hash-based index on Job.JobTitle hash-based index on Job.Rank Correct. Hash-based index on Job.Rank because in this case Rank are not the same for all, or may be all ranks have the same values  Question 12 1 / 1 pts What type of analyzer would you use if you are trying to analyze the SQL query and find the most efficient way to access the data? query optimizer Correct. Query optimizer analyzes the SQL query and finds the most efficient way to access the data. query selector data optimizer data selector  Question 13 1 / 1 pts When using Operands what should you avoid using? conditional expression Correct. Conditional expression should be avoided when using operands. numeric expression unconditional expression data expression  Question 14 1 / 1 pts Bitmap indexes tend to use less space than a _____ because they use bits instead of bytes to store their data. https://canvas.csun.edu/courses/154575/quizzes/571410 6/7 12/14/24, 2:01 AM Quiz: Database Performance Tuning and Query Optimization: COMP 440 DATABASE DESIGN - (17042-FA2024) reverse index B-tree index Correct. Bitmap indexes tend to use less space than a b-tree index. hash index sparse index  Question 15 1 / 1 pts During the ____ phase, the DBMS retrieves the data and sends the result set back to the client. parse execute process fetch Correct. Fetch because during this phase the DBMS retrieves the data and sends the results back to the client. Quiz Score: 14 out of 15 * Some questions not yet graded https://canvas.csun.edu/courses/154575/quizzes/571410 7/7