Study your flashcards anywhere!

Download the official Cram app for free >

  • Shuffle
    Toggle On
    Toggle Off
  • Alphabetize
    Toggle On
    Toggle Off
  • Front First
    Toggle On
    Toggle Off
  • Both Sides
    Toggle On
    Toggle Off
  • Read
    Toggle On
    Toggle Off
Reading...
Front

How to study your flashcards.

Right/Left arrow keys: Navigate between flashcards.right arrow keyleft arrow key

Up/Down arrow keys: Flip the card between the front and back.down keyup key

H key: Show hint (3rd side).h key

A key: Read text to speech.a key

image

Play button

image

Play button

image

Progress

1/12

Click to flip

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.