Need help with oracle explain plans and materiliased views
- or -
Post a project like this2388
$25
- Posted:
- Proposals: 2
- Remote
- #1710380
- Awarded
Description
Experience Level: Entry
Estimated project duration: 1 day or less
General information for the business: database modelling help required
Database management system (DBMS): Oracle
Description of requirements/functionality: more information will be given
Extra notes: Note: You must submit all the SQL queries and any other code that you wrote in
answering any of the parts / questions (e.g., the use of Explain Plan statements for
the queries and their output).
(i) Study the index definitions in sh_idx.sql. Discuss in detail (using cost based analysis)
why these indexes (at least two of them) are useful for answering queries over the
SH2 and DWn versions of the database. You do not need to run the sh_idx.sql script.
(10 Marks)
(ii) Identify two new indexes and justify why they could be useful. Write the SQL code for
creating these indexes under your DWn user. Give example queries with cost-based
analysis for both DWn (which will have the new indexes) and SH2 users (which will
not have any of the new indexes).
(iii) Given the materialized views (MVs) defined in sh_cremv.sql, discuss in detail why
these MVs are useful for users of the SH database. You should provide detailed
examples (cost based analysis, e.g., using Explain Plan for running sample queries
on both SH2 and DWn) to illustrate your answer.
(iv) Identify two other MVs based on the base tables in the SH schema under your DWn
user and justify why they would be useful. Write the SQL code for creating these
MVs.
Need to use login to complete these 4 tasks
Database management system (DBMS): Oracle
Description of requirements/functionality: more information will be given
Extra notes: Note: You must submit all the SQL queries and any other code that you wrote in
answering any of the parts / questions (e.g., the use of Explain Plan statements for
the queries and their output).
(i) Study the index definitions in sh_idx.sql. Discuss in detail (using cost based analysis)
why these indexes (at least two of them) are useful for answering queries over the
SH2 and DWn versions of the database. You do not need to run the sh_idx.sql script.
(10 Marks)
(ii) Identify two new indexes and justify why they could be useful. Write the SQL code for
creating these indexes under your DWn user. Give example queries with cost-based
analysis for both DWn (which will have the new indexes) and SH2 users (which will
not have any of the new indexes).
(iii) Given the materialized views (MVs) defined in sh_cremv.sql, discuss in detail why
these MVs are useful for users of the SH database. You should provide detailed
examples (cost based analysis, e.g., using Explain Plan for running sample queries
on both SH2 and DWn) to illustrate your answer.
(iv) Identify two other MVs based on the base tables in the SH schema under your DWn
user and justify why they would be useful. Write the SQL code for creating these
MVs.
Need to use login to complete these 4 tasks
Enos J.
100% (4)Projects Completed
4
Freelancers worked with
4
Projects awarded
24%
Last project
10 Oct 2017
Uganda
New Proposal
Login to your account and send a proposal now to get this project.
Log inClarification Board Ask a Question
-
There are no clarification messages.
We collect cookies to enable the proper functioning and security of our website, and to enhance your experience. By clicking on 'Accept All Cookies', you consent to the use of these cookies. You can change your 'Cookies Settings' at any time. For more information, please read ourCookie Policy
Cookie Settings
Accept All Cookies