Flashcards
SCC 201 - Query Processing and Optimisation
Cards in this deck (29)
- 1What does the cost based query optimization involve?It involves searching the solution space to find a solution that minimises cost
- 2Two important concepts of query optimisation?Compare the cost of executing a query using different execution strategies and choose the lowest cot estimate.
The cost functions used are estimates and not exact so the optimisation strategy may choose a strategy that is not the optimal one. - 3List of cost components?Access cost to secondary storage
Storage cost
Computation cost
Memory usage cost
Communication cost - 4What is computation cost?Cost of performing in-memory operations on data buffers during query execution
– Include operations such as searching for and sorting records, merging records for a join and performing computations on field values. - 5What is memory usage cost?– Cost pertaining to the number of memory buffers needed during query execution
- 6What is communication cost?Cost of shipping the query and its results to/from the database from/to the client where the query is originated
- 7Effects of query optimisation within larger databases?– Minimise access cost to secondary storage
– Minimise number of block transfers between disk and main memory - 8Effects of query optimisation within smaller databases?– Minimise computation cost
– Most of the data involved in the query can be completely stored in memory - 9Effects of query optimisation within distributed databases?– Minimise communication cost
- 10Type of constraints used in semantic query optimisation?Uses constraints specified on the
database schema
e.g. unique attributes - 11Can you describe the intermediate form of a query?The “intermediate form of query” is usually a “query tree” whose leafs are the relations involved in the query, and whose nodes are the RA operators.
- 12Can you describe the notation for query trees?– Leaf nodes: input relations of the query
– Relational algebra operations: internal nodes - 13Describe the execution of a query tree?– Execute an internal node operation whenever its operands are available.
– Replace the internal node by the resulting relation.
– Execution terminates when the root node is executed and the query result is obtained - 14The data we require may not be present in any single existing relation, how does the relational operator help us solve this?The relational operators allow us to
transform and combine existing relations
in order to produce a single relation which
holds the answer to our question. - 15What does the scanner do in query processing?– Identifies the language tokens such as SQL keywords, attribute names, relation names in the text of the query
- 16What does the parser do in query processing?– Checks the query syntax to determine whether it is formulated according to the grammar rules of the query
language - 17What does the validation do in query processing?– Check that all attribute and relation names are valid and semantically meaningful names in the database schema
- 18How can you internally represent a query?Usually a query tree
– Can also be a query graph - 19What does the Query optimizer do in query processing?– Responsible for devising an execution plan for retrieving the result of the query from the database
– A query can have several possible execution plans and the optimiser chooses a suitable one - 20What does the code generator do in query processing?– Generates the code to execute the plan produced by the optimiser
- 21What does the run time database processor do in query processing?– Responsible for running the query code to produce the query result
– Code may be executed in compiled or interpreted mode
– An error message is generated if a runtime error occurs - 22What is query optimisation?The process of selecting a suitable execution plan for the query
– May not necessarily be the optimal plan
– A reasonably efficient strategy - 23What is the problem with finding the optimal strategy?– Finding the optimal strategy is usually too costly except for the simplest of queries!
- 24What are the three main query optimisation techniques?– Cost based
– Semantics based
– Heuristic based - 25What is access cost to secondary storage?– Cost of searching for, reading and writing data blocks residing on secondary storage such as disks
– Depends on type of access structures on the data file
– Also depends on whether the file blocks are allocated contiguously - 26What is storage cost?– Cost of storing intermediate files generated by an execution strategy for a query
- 27Difference between low level navigational languages and high level relational languages?Low level navigational languages
(hierarchic and network) --> "hand
optimised" by the programmer.
• High level relational languages -->
declarative (what rather than how);
requires optimisation. - 28What must be made sure happens when a query tree is optimized?We must make sure that the transformation steps always lead to an equivalent query tree
– The query optimiser needs equivalence preserving transformation rules - 29—
Information
- Description
- Flashcards on SCC 201 - Query Processing and Optimisation, created by Ayo Olutobi on 03/04/2017.
- Created by
- Ayo Olutobi · Apr 2017
- Updated
- Apr 22, 2017
- Views
- 4
- Rating