Reference no: EM131226865
Assignment: Query Optimisation This assignment aims to develop your skills optimizing query.
It involves identifying the suitable methods for structuring the data and writing the query.
First, you will need to create a query on some unstructured data. Unstructured data can be thought of in this context as a table which is NOT clustered or hashed. In addition the table should NOTcontain any indexes at all. The query can be an equi-join or a sub-query.
The time a query with unstructured data takes to execute (execution time) will be used as the baseline.
Plan how to set up indexes, clusters, and hashed clusters for the same query. Use different numbers of rows and graph these for each type of structure. You need to create enough rows to indicate a trend in the performance (Number of rows should be enough).
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 hashkey 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.
Please note:
You need to write explanation (Brief explanation) on each event. Just handing in a list of a TRACE without saying what it all means will make you lose your mark. Your comment on each event is as important as the experiment itself.
In your report as the conclusion part, you need to write (max length: 300 words) that discusses the effectiveness of Oracle's query optimizer.
![575_Figure.jpg](https://secure.expertsmind.com/CMSImages/575_Figure.jpg)
Points to note when designing the tests
- 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.
- Tests can vary in their total time and it is safer to run each test 2-3 times to ensure the execution time is consistent.
- The data must stay the same for all the tests otherwise 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.
What is the future value
: What is the future value of $1,200 a year for 46 years at 8 percent interest? Assume annual compounding.
|
Determine the phase margin of the new amplifier
: An open-loop amplifier can be described.- Determine β and the required dominant pole frequency.- Determine the phase margin of this new amplifier.
|
Estimate the closed-loop gain at low frequencies
: A measurement of the closed-loop gain of an amplifier shows it to be -25 VN at 120 kHz and -100 VN at S kHz. Estimate the closed-loop gain at low frequencies and the corresponding 3 dB frequency. What is f, for the op amp used? (Bc careful!)
|
Project management office establishment
: In your writing show an example of the three steps in documenting a soft return. Also, would a project management office establishment be of any assistance in monitoring the project?
|
Create a query on some unstructured data
: 406706 - Physical Database Design - create a query on some unstructured data. Unstructured data can be thought of in this context as a table which is NOT clustered or hashed. In addition the table should NOTcontain any indexes at all. The query ca..
|
Advantages of globalization and standardization of culture
: In Unit II, we learned about the concept of globalization of culture. What are the advantages and disadvantages of the globalization and standardization of culture? Provide at least one specific example of the advantage and at least one specific e..
|
What is the size of your target market
: What type of leadership and innovative qualities do you plan to implement in order to execute your marketing strategy? What is the size of your target market? What resources do you believe would be required to implement your plan?
|
Effects of cca depreciation
: The firm's cost of equity is 16% and its pre-tax cost of debt is 8%. The flotation costs of debt and equity are 2% and 8%, respectively. Assume the firm's tax rate is 34% and ignore the effects of CCA depreciation.
|
Oberon before-tax component cost of debt
: Oberon, Inc., has a $20 million (face value) 8-year bond issue selling for 99 percent of par that pays an annual coupon of 8.05 percent. What would be Oberon's before-tax component cost of debt?
|