Objectives
- To understand physical database design as a critical element in achieving overall database objectives.
- To have a sound understanding of the use of indexes and the trade-offs that must be considered in their use.
- To understand the concepts of database query optimization.
- To tune database queries using Oracle facilities such as hint and autotrace for better performance
The assignment will be assessed as two parts:
1. Development of Oracle Database and queries
- Implementation of the SQL scripts - part one
Do the database tables have appropriate data & data type?
- Queries Design and Structure
Do the SQL queries conform to the specifications?
Do the SQL queries answer the questions correctly and in a well-designed manner?
Do the queries get an optimized execution plan?
Note: You will demonstrate your program during your lab class in Week 11-12.
2. Database tuning analysis
- The executions of spool files
Provide the spool files including all queries and the execution plans to demonstrate your execution of queries.
- Documentation of Analysis & comparison
5. The Database Description
Chemist Warehouse Group
Chemist Warehouse is the Australia's largest pharmacy retailer. It has over 1000 retail stores nationally and been growing at a rapid rate. Chemist Warehouse Groupemploys over 20,000 staff members including certified pharmacists and front selling clerks. Each store sells over 65,000 productsof healthy related products coming from different suppliers. The products are managed by related departments such as Vitamins; Beauty; Fragrances; Baby Care; Dental; Household; Hair Care; Protein etc.The prescribed medicines must be prepared by registered pharmacists.
Chemist Warehouse Group has got a distributed database systems to manage the operational business within the company. The database records all products, inventory, stores, suppliers, employees and sales information. To help the organization drives sales, marketing, loyalty, and service effectiveness, they also award customers with a VIP program. To ensure high customer numbers, management needs to carefully consider its pricing and VIP discounts. The VIP can be classified into 5 levels: 1: 2% discount for blue member; 2: 4% discount for silver member; 3: 6% discount for gold member; 4: 8% discount for diamond member; 5: 10% discount for platinum member.
The following tables are the partial Database schemaof Chemist Warehouse group.
6. The Assignment Description
Phase 2: Tuning queryfor efficient database queries
You answer the following queries. You are studying the impact of different queries. You need to write two or more queries for each question in order to find a better solution. You can use the following different techniques to tune your queries.
- Check the exaction plan to see the impact of different queries you have done. You may have used unnecessary distinct, unnecessary nested sub-queries and unnecessary join or set operators.
- Check if you should define appropriate indexes (bitmap, secondary indexes or a function based indexetc).
- When you are tuning queries, you mayuse Hints. The hints may be used after you have collected statistics on the relevant tables and evaluated the optimizer plan. Monitoring query performance enhancements to see whether any significant impact on queries by hints.
- You need to spool each query execution for the EXPLAIN PLAN of both original queries and optimized/modified queries (if there are any) to learn how the optimizer is executing a query.You understand the optimizer decisions and analyse the EXPLAINPLANand AUTOTRACE results by looking into the execution plans of one query question.
Notice: EXPLAIN PLAN is to get a planbefore the query execution.AUTOTRACEis for actual execution of query
Queries:
1. Find the total sold price for the most expensive Fragrance (category name,the highest unit price) between 01/03/2015 to 31/03/2017 at stores in suburb Bundoora. Write two different queries and find the best performance query.
(Notice: first part of the query is to get the max Product_UnitPriceof Category Fragrance; then write a subquery to get a list of the product_ids whose unit prices are the same as the max Product_UnitPrice at thestoreBundoora.
Hint: Use date function to tell your date format TO_DATE('31/03/2017', 'DD/MM/yyyy'. Using function upper(..) for string comparison)
2. Retrieve the names of Employees who have the same E_level values as Jorge Peres and also are living at the same suburb as Jorge Peres. Write two different queries with nested queries. (hint: you can get the "Jorge Peres-level table" in your FROM clause of SQL query.)
Eg.,
SELECT E.E_fname, E.E_lname
FROM Employ E
(SELCT J.E_level
FROM employ J
WHERE UPPER(J.E_lname) = 'PERES'
AND UPPER(J.E_fname) = 'JORGE') Jlevel, /Jlevel is a table/
WHERE E.E_level = Jlevel.E_level
AND UPPER(E.E_lname) != 'PERES'
AND UPPER(E.E_fname) != 'JORGE';
3. Find the list of stores in Melbourne where those stores' total salesare greater thanthose stores with the lowest total salesin the database. Write two different queries: one has a better performance. (Hint: get the lowest total sales for the store among all stores, then rest stores should be in the list for the answer. Few ways to do it)
An example for a nested subquery in FROM clause:
SELECT Store_IDMIN(ST.sum_column1)
FROM (SELECT store_ID, SUM(sale_price) AS sum_column1
FROM Sale_transct GROUP BY store_ID) ST
)
WHERE ...;
4. Find Melbourne VIP level 4customers' first name, last name who have bought the product named as"Vitamin D " at least 2 times in database. You writethreedifferent queries: one is using operator EXISTS and the other one is using operator IN. The third query with the main filter criteria in FROM clause of the main query. Find one with the better performance.
5. Display the names of products that bought by Female VIPs who are in level 3, level 4, and level 5 (not level 1 or 2) and a list of all products supplied by supplier's postcode 3083. Write two queries.
In phase two, you should have the following files in your submission:
- SQL query scripts file - can be in one YourName-SQL.sql file
- Spool files for executions of queries with their execution plans from SQL*Plus - you can concatenate all spool files into one file for submission.
- The table of your comparisons on database query execution plans and your analysis with your conclusions.