Overview of this Lecture / week

Last week we commenced our exploration of Database Query Optimisation. The focus last week was on introducing the topic and covering the main indexing techniques that Enterprise Databases provide, and some/most open source databases.

This week we will look at Cost Based optimisation. This requires a fully implemented data model and the database gathers statistics about the data contained in each of the tables and the constraints between them. We will also look at some physical structures for tables and how in-memory data storage and processing can be used.


Click here to download the notes.

L4 - Query Optimisation - Part 2

Videos of Notes

None available at this time.

Lab Exercises

Building upon the exercises from last week, try come of the techniques covered this week with the same data set, or us an alternative data set. The techniques to try include

  • Parallel Queries
  • Table Partitioning
  • Materialized Views
  • Table Clustering
  • In-memory objects

You can some of the lab time to work on Assignment A, and to examine Assignment B, start working on it and ask some questions.

Assignment B

Assignment B Handout.

Questions about the assignments will only be answered in class. This is to allow all students to benefit from the answer.

Additional Reading

Jonathan Lewis – Changing the order of the table in the FROM can affect performance