Query Optimization — Learn free

Self-check

Questions 1–10 of 10

1. What does Query Optimisation mean?
Question 1A. Finding the fastest route to execute a queryB. Rewriting a query so it returns fewer rowsC. Compressing the query text before sending it to the databaseD. Adding indexes to every column referenced in a queryE. Caching the query result for later reuse
2. What are the three phases of query processing?
Question 2A. Compile, Link and RunB. Extract, Transform and LoadC. Fetch, Filter and SortD. Parsing and Translation, Optimisation, and EvaluationE. Index, Partition and Shard
3. During the Parsing and Translation phase, what does the parser do with a SQL query?
Question 3A. Executes the query and returns the resultB. Validates the syntax of the query and the relations referenced in the databaseC. Chooses the lowest-cost execution planD. Generates machine code for the runtime processorE. Gathers table statistics
4. After parsing, into what form is a SQL query converted before optimisation?
Question 4A. A stored procedureB. A JSON documentC. A MapReduce jobD. A materialised viewE. A relational algebra expression
5. What structure is usually built from the relational algebra expression for the optimisation engine to analyse?
Question 5A. A hash tableB. A transaction logC. A query tree or graphD. A B-tree indexE. A partition map
6. Which two kinds of optimiser does Oracle use?
Question 6A. Static and dynamic optimisersB. Row-based and column-based optimisersC. Parallel and serial optimisersD. Heuristic and statistical optimisersE. Rule-based (RBO) and cost-based (CBO) optimisers
7. What does the Teradata optimiser rely on to decide how best to access, join and aggregate tables?
Question 7A. Its knowledge base of statistics and demographicsB. A fixed list of rules supplied by the DBAC. The order in which tables appear in the queryD. The size of the transaction logE. Hints embedded in the SQL text
8. How does the SQL Server optimiser choose an execution plan?
Question 8A. It always uses the first plan generatedB. It applies only rule-based transformationsC. It estimates the cost of several candidate plans and selects the lowest-cost oneD. It asks the user to pick a planE. It reuses the plan from the previous query regardless of cost
9. What is the final step of query processing once the best execution plan has been selected?
Question 9A. Re-parsing the SQL to confirm the syntaxB. Generating code for the selected plan, which the runtime database processor executes to produce the resultC. Storing the plan as a relational algebra expressionD. Building a new index on the queried tableE. Sending the plan back to the optimiser for a second pass
10. What is described as the core capability of Query Optimisation?
Question 10A. Rejecting queries that have syntax errorsB. Compressing result setsC. Enforcing access permissions on tablesD. Searching for the execution plan that needs the least time for query evaluationE. Converting SQL into relational algebra