Homework Help is Here – Start Your Trial Now!
Engineering
Computer Science
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;

A Guide to SQL
A Guide to SQL
9th Edition
ISBN:9781111527273
Author:Philip J. Pratt
Publisher:Philip J. Pratt
Chapter2: Database Design Fundamentals
Section: Chapter Questions
Problem 11RQ
See similar textbooks
icon
Related questions
Question

Hey experts, please help me out on this :( xx 

Sample database:

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.

Indexing 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. iv. V. 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; SELECT max(ps_suppkey) FROM partsupp; SELECT ps_suppkey, ps_partkey, ps_supplycost FROM partsupp WHERE ps_suppkey = 3699 AND ps_partkey = 700; Deliverables Submit your spooled file solution1a.lst (or solution1a.pdf) that contains your SQL script and the output from the execution of the script. The report must have no errors related to the implementation of your task and it must list all PL/SQL and SQL statements processed. Remember to set ECHO option of SQL*Plus to ON!
Transcribed Image Text:Indexing 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. iv. V. 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; SELECT max(ps_suppkey) FROM partsupp; SELECT ps_suppkey, ps_partkey, ps_supplycost FROM partsupp WHERE ps_suppkey = 3699 AND ps_partkey = 700; Deliverables Submit your spooled file solution1a.lst (or solution1a.pdf) that contains your SQL script and the output from the execution of the script. The report must have no errors related to the implementation of your task and it must list all PL/SQL and SQL statements processed. Remember to set ECHO option of SQL*Plus to ON!
Expert Solution
steps

Step by step

Solved in 4 steps

SEE SOLUTIONCheck out a sample Q&A here
Blurred answer
Knowledge Booster
Dataset
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.
Similar questions
  • SEE MORE QUESTIONS
Recommended textbooks for you
A Guide to SQL
A Guide to SQL
Computer Science
ISBN:
9781111527273
Author:
Philip J. Pratt
Publisher:
Course Technology Ptr
SEE MORE TEXTBOOKS