Create Function Based Index - Exercise

Course Project: Creating an index-organized Table

Objective: Create an index-organized table and rebuild it.

Exercise Scoring

This exercise is worth 10 points.

Background and Overview

In this exercise, you will create a function-based index as part of your PETSTORE database. The index will be used to retrieve sales data based on the month that a sale occurred, which will help spot seasonal buying trends.

Fact File

Review the fact file to find out more information about the House-O-Pets and its Oracle database.
House-O-Pets Database
Click the View Petstore Tables link below to see the contents of the tables in the House-O-Pets database.
View Petstore Tables

Download files

You can download a correct version of the script for this exercise, funcind.sql, from the Resources page.

Scenario

After some investigation, you have discovered that many Sales Department managers at House-O-Pets use queries to request sales data by month, and these queries seem to be running particularly slowly. You decide to add a function-based index to see whether you can reduce the overhead of the queries.

Instructions

Create a function-based index on two characters that represent the month a sale was made. You will have to use the TO_CHAR() function on the SALE_DATE column and then use the SUBSTR() function on the result.
Then answer the following questions:

How would the function-based index you created help out in a production situation?
When will the function-based index you have just created start being used by the cost-based optimizer?
When will the function-based index you just created be used in an execution path by the cost-based optimizer?

Hints

You can use a variety of different formats with the TO_CHAR() function, such as the YYYY-MM format, and then use the SUBSTR() function on the result.
The syntax for the SUBSTR() function is:

SUBSTR(char_string, start_position, length )

where char_string is the string that is the source of the substring extraction, start_position is the position of the first character to be extracted, and length is the number of characters to be extracted. For example, the following use of the function

SUBSTR( 'ABCDEF', 2, 2)

would return the string 'BC'.

Submitting your exercise

Type (or cut and paste) your plan into the box below to submit your answers.
Click the Submit button when you are finished to submit the exercise. Remember to submit all of your answers at once.