Create Function-Based Index - Exercise

In this exercise, you will create a function-based index in the PETSTORE schema. The goal is to speed up queries that group or filter sales by the month a sale occurred, which can help identify seasonal buying trends.

Review the fact file to learn about the House-O-Pets application and its Oracle schema.

Click the View Petstore Tables link to see the tables used in this course project:
View Petstore Tables

Sales managers frequently request sales totals by month. Those queries are currently running slowly. You decide to add a function-based index so Oracle can use an index access path when the query applies functions to SALE_DATE.

Create a function-based index that extracts the two-digit month from SALE_DATE. Use TO_CHAR() to convert the date to a string, then SUBSTR() to extract the month portion.

1) Create the index. Use a statement shaped like this (you will supply the correct table and index name):

CREATE INDEX idx_sale_month
ON sale_header ( SUBSTR(TO_CHAR(sale_date, 'YYYY-MM-DD'), 6, 2) );

This example uses the 'YYYY-MM-DD' format so the month is consistently in characters 6–7. (Alternative approaches exist, such as using TO_CHAR(sale_date,'MM') directly, but this exercise explicitly practices SUBSTR().)

2) Answer the following questions.

1. How does this function-based index help in a production system?
2. When does the function-based index become eligible for use by the cost-based optimizer?
3. When will the optimizer actually choose it as part of an execution plan?

You can use different TO_CHAR() formats. If you use 'YYYY-MM', the month starts at position 6:

SUBSTR(TO_CHAR(sale_date, 'YYYY-MM'), 6, 2)

The syntax for SUBSTR() is:

SUBSTR(char_string, start_position, length)

Example:

SUBSTR('ABCDEF', 2, 2)  -- returns 'BC'

Type (or paste) your SQL and short written answers into the box below, then click Submit. Submit all of your work at once.