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.
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.
Questions about the assignments will only be answered in class. This is to allow all students to benefit from the answer.
Jonathan Lewis – Changing the order of the table in the FROM can affect performance