Assignment 2 Query Optimisation
report | 作业Data structure | 代写assignment | sql | oracle – 这道题目是利用report进行的编程代写任务, 是比较典型的report/Data structure/sql/oracle等代写方向, 这个项目是assignment代写的代写题目
This part of the assignment will develop your skills in the area of Query Optimisation. This will
involve identification of the best methods for structuring the data and writing the query.
You will need to start by creating a query on some unstructured data in 3 tables. Unstructured data can be thought of in this context as a table which does not contain any indexes, and is not
clustered or hashed. The query can be an equi-join or a sub-query. Your query should aim to
retrieve 10-15% of the rows in the tables.
The time a query with unstructured data takes to execute will be used as the baseline.
Next, plan how to set up indexes, clusters, and hash clusters for the same query.
You should then run the query and note the execution times for each of these structures. Record
the results. When doing this you could alter the way you set up some of these structures by, for
instance, altering the hash key or what you cluster together and note any differences in execution
time.
You should by now have determined the optimal Data structure in terms of performance. To be
confident that your data will make things difficult for Oracle, ensure that:
- the data is as unordered as possible
- you retrieve a number of rows
You now have some choices about how you might improve the sql statement. Take the best
optimisation and try to improve it by inserting hints and swapping the order of the tables in the
‘from’ clause of your query.
The best of these ‘improved’ statements then needs to be tried out using Oracle’s Parallel Query
Option.
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 accurate brief comment on each event is as
important as the experiment itself.
The last section of your submission will consist of a short reflective report (max length: 300 words)
that discusses the effectiveness of Oracles query optimizer.
Apply
best
Apply
best
Create a
Query
Do ALL
Structures
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 a 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 or it will not be possible to make comparisons.
- You may need to make design changes to your tables before all the tests will work.
- If you leave the assignment to the last minute you will find the server responses slow because
of all the other students also completing assignments at this time
Useful References: oracle Administrators Guide Chapters 16, 18 & 19.
Oracle Performance Tuning Guide Chapters 13, 15 & 16.
Query Type
Equijoin
Subquery
Structure
Nil
Indexed
Clustered
Hash Clusters
Parallel
Query
Option
Statement
Tuning
Hints,
Table
Order
Assignment 2 Marking Schedule
Name:
Item Max
Mark
Your
Mark
Comments
Preliminary work:
SQL statement complexity 10^
tables populated properly 5^
No structures (eg.indexes) in data
Data unordered, data volume
Structure:
nil setup 4^
Design of experiment
observations/conclusions
indexed setup 10^
Design of experiment
observations/conclusions
clustered setup 12^
Design of experiment
observations/conclusions
hash cluster setup 12^
Design of experiment
observations/conclusions
Statement Tuning:
Hints 9^
Design of experiment
observations/conclusions
Table Order 9^
Design of experiment
observations/conclusions
Parallel Query Option 9^
Setup/ Design of experiment
observations/conclusions
Overall Reflection on Oracles
Query Optimiser
####### 12
General Documentation 8^
Total 100^