BuyFindarrow_forward

Database Systems: Design, Implemen...

13th Edition
Carlos Coronel + 1 other
Publisher: Cengage Learning
ISBN: 9781337627900

Solutions

Chapter
Section
BuyFindarrow_forward

Database Systems: Design, Implemen...

13th Edition
Carlos Coronel + 1 other
Publisher: Cengage Learning
ISBN: 9781337627900
Chapter 11, Problem 32P
Textbook Problem
15 views

Problems 29–32 are based on the following query:

SELECT CUS_CODE, MAX(LINE_UNITS*LINE_PRICE)
FROM CUSTOMER NATURAL JOIN INVOICE NATURAL JOIN LINE
WHERE CUS_AREACODE = '615'
GROUP BY CUS_CODE;

How would you rewrite the query to ensure that the index you created in Problem 31 is used?

Program Plan Intro

Indexes:

  • Indexing is a technique that is used in SQL for optimizing the performance.
  • Indexes are used when a small set of rows needs to be selected from the table that is large in size.
  • The selection of rows is made by specifying conditions.

The query of problem 29:

SELECT CUS_CODE, MAX(LINE_TOTAL)

FROM CUSTOMER NATURAL JOIN INVOICE NATURAL JOIN LINE

WHERE CUS_AREACODE = ‘615’

GROUP BY CUS_CODE;

Index creation for the attribute “CUS_AREACODE”:

The index for the given query is generated with the following name “CUS_MYNDX1”:

CREATE INDEX CUS_MYNDX1 ON CUSTOMERCUS_AREACODE);

Explanation of Solution

Rewriting the query based on the index created:

  • It is recommended to use the INDEX optimizer hint.
  • Special instructions that are embedded inside the SQL command test for the optimizer is called as optimizer hints.
  • They are three kinds of hint namely:
    • ALL_ROWS
      • It minimizes the overall time of the execution by instructing the optimizer.
    • FIRST_ROW
      • It minimizes the need for executing the first set of rows by instructing the optimizer...

Still sussing out bartleby?

Check out a sample textbook solution.

See a sample solution

The Solution to Your Study Problems

Bartleby provides explanations to thousands of textbook problems written by our experts, many with advanced degrees!

Get Started

Chapter 11 Solutions

Database Systems: Design, Implementation, & Management
Show all chapter solutions
add
Ch. 11 - What are optimizer hints, and how are they used?Ch. 11 - What are some general guidelines for creating and...Ch. 11 - Most query optimization techniques are designed to...Ch. 11 - What recommendations would you make for managing...Ch. 11 - What does RAID stand for, and what are some...Ch. 11 - SELECT SELECT EMP_LNAME, EMP_FNAME, EMP_AREACODE,...Ch. 11 - Problem 1 and 2 are based on the following query:...Ch. 11 - Using Table 11.4 as an example, create two...Ch. 11 - Problems 46 are based on the following query:...Ch. 11 - Problems 46 are based on the following query:...Ch. 11 - Problems 46 are based on the following query:...Ch. 11 - Problems 732 are based on the ER model shown in...Ch. 11 - Problems 732 are based on the ER model shown in...Ch. 11 - Problems 732 are based on the ER model shown in...Ch. 11 - Problems 732 are based on the ER model shown in...Ch. 11 - Problems 1114 are based on the following query:...Ch. 11 - Problems 1114 are based on the following query:...Ch. 11 - Problems 1114 are based on the following query:...Ch. 11 - Problems 1114 are based on the following query:...Ch. 11 - Problems 15 and 16 are based on the following...Ch. 11 - Problems 15 and 16 are based on the following...Ch. 11 - Problems 1721 are based on the following query:...Ch. 11 - Problems 1721 are based on the following query:...Ch. 11 - Problems 1721 are based on the following query:...Ch. 11 - Problems 1721 are based on the following query:...Ch. 11 - Problems 1721 are based on the following query:...Ch. 11 - SELECT SELECT P_CODE, P_DESCRIPT, P_PRICE,...Ch. 11 - Problems 2224 are based on the following query:...Ch. 11 - Problems 2224 are based on the following query:...Ch. 11 - Problems 25 and 26 are based on the following...Ch. 11 - Problems 25 and 26 are based on the following...Ch. 11 - Problems 27 and 28 are based on the following...Ch. 11 - Problems 27 and 28 are based on the following...Ch. 11 - Problems 2932 are based on the following query:...Ch. 11 - Problems 2932 are based on the following query:...Ch. 11 - Problems 2932 are based on the following query:...Ch. 11 - Problems 2932 are based on the following query:...

Additional Engineering Textbook Solutions

Find more solutions based on key concepts
Show solutions add
How do you translate business rules into data model components?

Database Systems: Design, Implementation, & Management

What system is the predecessor of almost all modern multiuser systems?

Principles of Information Security (MindTap Course List)

What is the difference between synthetic and semi-synthetic cutting fluids?

Precision Machining Technology (MindTap Course List)

What is the difference between psia and psig readings?

Automotive Technology: A Systems Approach (MindTap Course List)

In your own words, explain what is meant by ethics.

Engineering Fundamentals: An Introduction to Engineering (MindTap Course List)

Determine the clamping (vertical) force applied by the tongs at E.

International Edition---engineering Mechanics: Statics, 4th Edition

Describe the functions of an encryption algorithm and an encryption key. Differentiate between private and publ...

Enhanced Discovering Computers 2017 (Shelly Cashman Series) (MindTap Course List)

If your motherboard supports ECC DDR3 memory, can you substitute non-ECC DDR3 memory?

A+ Guide to Hardware (Standalone Book) (MindTap Course List)