Reference no: EM133027887

INFS602 Physical Database Design - Auckland University of Technology

Assignment - Query Optimisation; Individual assignment

This assignment will develop your skills in the area of Query Optimisation. This will involve identification of the best access paths and methods for a query. You will work individually on this assignment.

You will need some large tables to query. You may use BigTablesForAssignment2.sql that will create data in a few tables. The tables will be unstructured and will not contain any indexes, and are not clustered or hashed.

1. Most important - You need to c reate a query to retrieve data from the 3 tables. The query can be an equi-join or a sub-query but a nested sub-query would work well. Your query should make it hard for the DBMS to run the query efficiently as the objective of this assignment is to optimize the query.
You should then run the query and note the execution time. The time a query with unstructured data takes to execute will be used as the baseline.

2. Plan on indexes you think you need to make the query run faster. Plan how to set up indexes and set them up.
Run the query and record the results; note whether time taken to execute has improved, or not.
Look at the explain plan and reason why this result occurred. Keep in mind the aim of this experiment which is to improve query execution time, so you may wish to alter the way you set up the structures by, for instance, adding more indexes or more appropriate indexes. Note any differenes in execution time.
Pick the experiment with the best result for your assignment report and include this along with a good explanation of the results.

3. Plan how to set up clusters for the same query, and set them up. Run the query and record the results; note whether time taken to execute has improved, or not. Look at the explain plan and reason why this result occurred. You may wish to alter the way you set up the structures by, for instance, altering the tables you clustered together. Note any differences in execution time.
Pick the experiment with the best result for your assignment report and include this along with a good explanation of the results.

4. Plan how to set up hash clusters for the same query, and set them up. Run the query and record the results; note whether time taken to execute has improved, or not. Look at the explain plan and reason why this result occurred. You may wish to alter the way you set up the structures by, for instance, altering the tables you hash clustered together. Note any differences in execution time.
Pick the experiment with the best result for your assignment report and include this along with a good explanation of the results.

You should by now have determined the optimal data structure in terms of performance. You now have some choices about how you might further improve the query.

5. Take the best optimisation so far and try to improve it by inserting hints and swapping the order of the tables in the 'from' clause of your query (See marking rubric).
Document your results along with a good explanation of the results.

Don't overlook explanations of what's happening at each stage of your experimentation. Just handing in a listing of a TRACE file without saying what it all means will leave the marker feeling ignored and incapable of awarding marks. An a ccurate brief c omment on each event is as important as the experiment itself.

6. The last section of your submission will consist of a short reflective report (max length: 300 words) that discusses your opinion on the effectiveness of Oracle's query optimizer, gathered from your experience.

Points to note when designing the experiments
• The baseline query must take a sufficiently long time to allow improvements to occur later when tuned.
• The query will need to involve join(s) of tables so that all data structures can be used.
• You must rebuild the tables and reload the data between tests when altering the data structures.
• The data must stay the same for all the tests and the first run results are to be noted; the second run will use data already loaded in memory and therefore is not a good indicator of execution time.
• You will need to make design changes to tables before all the tests will work. For example, before Experiment 2 Indexes, you will need to Alter table to add constraints and indexes, or make changes to Create table statement.

Attachment:- Physical Database Design.rar