1. One of the main functions of a database system is to provide timely answers to end users.
True
2. Which of the following is a general guideline for creating and using indices.
Do not use indices in small tables
3. What type of queries are used in most applications?
SELECT
4. If you need to compute the total sales by product, you should use a_____ clause.
GROUP BY
5. A table space has the same meaning as a file extent.
False
6. What type of statement mostly manipulates end- user data?
DML
7. The SQL cache stores the end-user written SQL.
False
8. If you want the optimized query results always the same as the original query. What type of SQL code is being used?
Fully equivalent
9. 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
10. Which of the following is not a procedure involved in database performance tuning?
Nonautomated testing
11. Fully equivalent means that the optimized query results are always the same as the original query.
True
12. If you are using the DBMS query and the DBMS executes the SQL query using the chosen execution plan, which phase would need to be implemented?
three
13. All transaction management commands are processed during the parsing and execution phases of query processing.
True
14. An index scan is less efficient than a full table scan.
False
15. Indexes do not facilitate join operations.
False
16. 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
17. The primary factor in determining the most efficient access plan is the I/O cost.
True
18. Most current-generation relational DBMSs perform automatic query optimization at the client end.
False
19. What phase in the DBMS process does the DBMS transform the data to a low-level language.
Parsing
20. RAID systems use a single disk to create storage volumes.
False
21. On the client side, the objective is to generate a SQL query that returns a correct answer in the least amount of time, using a minimum amount of resources at the server end. The activities required to achieve this goal are commonly referred to as _______ tuning.
SQL performance
22. Given the following query
SELECT MilitaryID, JobTitle, Rank
FROM Enlisted, Job
WHERE Enlisted.JobTitle = Job.JobTitle AND
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 Job.Rank
23. What type of index guarantee(s) that the number of index block accesses is equal to the level of the index structures?
B-tree
24. What type of index guarantees the access structure is always balanced when a lot of updates such as insertions and deletions of file records occur?
Hash
25. Assume that a STUDENT table has several thousand rows. The STUDENT table has two columns that are used extensively for query purposes:
STU_LNAME, which represents a student’s last name, and REGION_CODE, which can have one of four values (NE, NW, SW, and SE). Based on this information, you could conclude that the STU_LNAME column contains many different values that repeat a small number of times compared to the total number of rows in the table. What type of index would be used?
B-tree
26. Assume that a STUDENT table has several thousand rows. The STUDENT table has two columns that are used extensively for query purposes:
STU_LNAME, which represents a student’s last name, and AREA_CODE, which can have one of four values (860, 910, 560, AND 203). Based on this information, you could conclude that because the AREA_CODE column contains only a few different values that repeat a relatively large number of times compared to the total number of rows in the table, what type of index would be used?
Bitmap
27. 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.
after; before
28. To work with data, a DBMS must retrieve the data from _____ and place them in _____.
permanent storage; RAM
29. When a server doesn’t have internal statistics supporting the objects referenced by the statement, what type of optimizer gets preference?
Rule-base
30. When there is an abundance of the internal statistics, what type of optimizer gets preference?
Cost-base
31. If you use multiple AND conditions, write the condition most likely to be _________ first.
False
32. How can you write this phrase NOT (C_COST > 20.00) without using NOT?
C_COST < = 20.00.
33. The CPU handles what type of comparisons faster than character and date comparisons?
Numeric
34. The DBMS _____ the SQL query and chooses the most efficient access/execution plan.
parses
35. Which of the following is the first step of query processing at the DBMS server end?
Parsing
36. During the ____ phase, the DBMS retrieves the data and sends the result set back to the client.
fetch
37. If there is no index, the DBMS will perform a _____ scan.
full table
38. _____ refers to the number of different values a column could possibly have.
Data sparsity
39. Bitmap indexes tend to use less space than a _____ because they use bits instead of bytes to store their data.
B-tree index
40. Knowing the sparsity of a column helps you decide whether the use of _____ is appropriate.
an index
41. _____ is the central activity during the parsing phase in query processing.
Query optimization
42. When setting optimizer hints, _____ instructs the optimizer to minimize the overall execution time, that is, to minimize the time it takes to return the total number of rows in the query result set. This hint is generally used for batch mode processes.
ALL_ROWS
43. In standard SQL, the optimizer hint FAST n is generally used for _____ mode processes.
interactive
44. In standard SQL, the optimizer hint ALL_ROWS is generally used for _____ mode processes.
batch
45. The LIKE conditional operator is used by the _____ OPERAND1.
V_CONTACT
46. The _____ must be set large enough to permit as many data requests to be serviced from cache as possible.
data cache
47. Whenever possible, transform conditional expressions to use literals. For example, if your condition is C_COST- 5 =2 ,what can you change it to read?
C_COST= 7
48. What type of cache, used for temporary storage, are for ORDER BY and GROUP BY?
sort
49. When using databases from the performance point of view, what type of database eliminates disk access bottlenecks?
in-memory
50. If you are using the raid levels, what type of raid level is used for writing the same data blocks to separate drives?
mirroring
51. What type of index would you use if you need an index based on a specific SQL function or expression?
function-based
52. When using Operands what should you avoid using?
conditional expression
53. As an analyst, you should try to transform conditional expressions into what?
literals
54. A(n) __ predetermines how an application’s query will access the database at run time.
Action plan
55. Which of the bottlenecks below is caused by inferior application code?
Application code
56. If you are using DBMS queries, how many phases are there?
three
57. 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
58. If you are taking an SQL statement and transforming it, what type of plan will the DBMS create?
execution
59. 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
60. A(n) _____ is good for simple and fast lookup operations based on equality conditions.
hash index
61. _____ is a measure of the likelihood that an index will be used in query processing.
Index selectivity
62. _____ is evaluated based on client perspective.
SQL performance tuning
63. A(n)_____ is an index based on a specific SQL function or expression.
function-based index
64. A conditional expression is normally expressed within the _____ or HAVING clauses of an SQL statement.
WHERE
65. ______ helps provide a balance between performance and fault tolerance.
RAID
66. The _____ table space is the most frequently accessed table space and should be stored in its own volume.
system
67. The _____ cache is used as a temporary storage area for ORDER BY or GROUP BY operations, as well as for index-creation functions.
sort
68. The _____ segment table space is used for transaction-recovery purposes.
rollback
69. A(n) _____ table stores the end-user data and the index data in consecutive locations on permanent storage.
Index-organized
70. In a level _____ RAID, the data and the parity data is striped across separate drives.
5
Other Links:
See other websites for quiz:
Check on QUIZLET