a) Consider the relational schemas of the TPCHR sample database. Determine what index should be created to improve the performance of the queries listed below in the best possible way. Consider each one of the queries as an individual case. If you decide that an index should be created, then list the names of attributes that form an index key and write the 'create index' statement to create the index. Remember that the order of attributes in an index key is important. If you decide not to create an index, explain why the performance will not suffer when a new index is not available. Assume that all relational tables are large enough to make full tables scan more time consuming than accessing the tables through an index. i. ii. iii. SELECT p_brand, p_type, p_retailprice FROM part; SELECT count(*) FROM part WHERE p_brand= 'Brand# 18'; SELECT I_orderkey, count(*) FROM lineitem WHERE I_orderkey = 1184000 GROUP BY I_orderkey;
a) Consider the relational schemas of the TPCHR sample database. Determine what index should be created to improve the performance of the queries listed below in the best possible way. Consider each one of the queries as an individual case. If you decide that an index should be created, then list the names of attributes that form an index key and write the 'create index' statement to create the index. Remember that the order of attributes in an index key is important. If you decide not to create an index, explain why the performance will not suffer when a new index is not available. Assume that all relational tables are large enough to make full tables scan more time consuming than accessing the tables through an index. i. ii. iii. SELECT p_brand, p_type, p_retailprice FROM part; SELECT count(*) FROM part WHERE p_brand= 'Brand# 18'; SELECT I_orderkey, count(*) FROM lineitem WHERE I_orderkey = 1184000 GROUP BY I_orderkey;
Chapter2: Database Design Fundamentals
Section: Chapter Questions
Problem 11RQ
Related questions
Question
Hey experts, please help me out on this :( xx
Sample
For all the implemented tasks, your report or output must include a listing of all PL/SQL
statements processed. To achieve that put the following SQL*Plus commands in all
your scripts:
SPOOL file-name
SET ECHO ON
SET FEEDBACK ON
SET LINESIZE 100
SET PAGESIZE 200
SET SERVEROUTPUT ON
at the beginning of SQL script and
SPOOL OFF
at the end of SQL script.
Expert Solution
This question has been solved!
Explore an expertly crafted, step-by-step solution for a thorough understanding of key concepts.
Step by step
Solved in 4 steps
Knowledge Booster
Learn more about
Need a deep-dive on the concept behind this application? Look no further. Learn more about this topic, computer-science and related others by exploring similar questions and additional content below.Recommended textbooks for you
A Guide to SQL
Computer Science
ISBN:
9781111527273
Author:
Philip J. Pratt
Publisher:
Course Technology Ptr
A Guide to SQL
Computer Science
ISBN:
9781111527273
Author:
Philip J. Pratt
Publisher:
Course Technology Ptr