Study the index definitions in sh_idx.sql. These indexes have already been created in SH2. Whatever indexes you
Question:
Study the index definitions in sh_idx.sql. These indexes have already been created in SH2. Whatever indexes you decide to create for this task should be the result of your own research and thinking and be different than those already existing in SH2 or those indexes defined in the Oracle Data Warehousing Guide
Then discuss the differences in the performance of your queries with and without the proposed indexes. You need to cite relevant database literature to support your choice of indexes and how you dealt with the issue of selectivity in your queries.
(B) There are two materialized views (MVs) defined in sh_cremv.sql, and these MVs have already been created under SH2 shared schema. You should study these two MVs and understand their benefits to the user of the SH2 data warehouse.
Then design two queries such that when you run these queries, the database optimizer will re-write these queries and instead of the tables named in your queries, the system will use the two new MVs to answer the queries. Note that the queries should return subsets of the values contained in these MVs. Moreover, you must not query your MVs directly in the FROM clause; let the database optimizer re-write these queries and answer them using the new MVs. s
Modern Database Management
ISBN: 978-0133544619
12th edition
Authors: Jeff Hoffer, Ramesh Venkataraman, Heikki Topi