Use LEFT and RIGHT arrow keys to navigate between flashcards;
Use UP and DOWN arrow keys to flip the card;
H to show hint;
A reads text to speech;
12 Cards in this Set
- Front
- Back
|
What is query optimisation? |
Using a series of transformation rules the query optimiser will analyse and find the optimum execution path for a statement. |
|
|
Transformation Rules: |
Using associative and commutative distributive rules and equivalence and boolean logic the query optimizer can transform a query tree. -Performing selections as early as possible, reduce number of rows. -Performing projection as early as possible to reduce the number of columns. -If there are two or more selection operations then the one which is likely to result in a fewest rows should be done first. |
|
|
Give an example of a query tree and how it is optimised. |
Restructuring the tree can result in optimisation. |
|
|
continued: |
1. A single ANDed selection is equivalent to a sequence of selections. R WHERE cond1 AND cond2 is equivalent to: (R WHERE cond1) WHERE cond 2 |
|
|
continued: |
2. The selection operator is commutative: (R WHERE cond1) WHERE cond2 (R WHERE cond2) WHERE cond1 |
|
|
Query Plans |
The optimiser produces a series of query plans using particular algorithms. Then chooses the one with the lowest estimated cost. (Uses heuristices ) |
|
|
What are the 4 ways a JOIN can be executed? |
SUPPLIER JOIN SUPPLY can be executed as... Full Scan Join: Nested for loop, one driving table the outer one, Indexed Join: If an index exists on the join column, the other table is the driving table, each row in the driving table is retrieved. Sort-Merge Join: Sort both tables of the join columns, then merge. Hash-Join: Scan one table building a hash table on the basis of the join columns. Use hash function on each row to identify the hash table bucket and probe for matching rows. |
|
|
If only 2 pages can be held in main memory and one table has 100 row table and the other has a 2000 rows, why is it more efficient to use the smaller relation as the outer loop? |
Flushing of the memory and retrieval of the date is more expensive so the smaller table should always be the outer loop. |
|
|
Oracle Query Plan generation. |
Oracle optimiser picks an access path by uses a cost based approach. Performs statistics and estimates the cost of each access path. Taking into account I/O and CPU and memory. GOAL: Best throughput and minimum elapsed time to process all rows accessed by a statement. OR best response time. Which Join method to use can be decided by the optimiser. Sort Merge = Slow for best response Index join = Fast for best response time. |
|
|
Application Developer control and responsibility. |
Not all optimisation can be delegated to the optimiser. Using indexes and not using indexes. When to use and when not to. Which columns to index and in what order if it is a composite(example in class about the 3 keys on supply) |
|
|
Customising the optimiser. |
Optimiser mode session parameter can be specified to fine tune the optimiser for best cost based best throughput approach. |
|
|
Optimiser Hints. |
A series of special comments to direct the optimiser to generate a better query plan. |
