Creating Secondary Index on Index Organized Table - Exercise

Course Project: Creating a secondary index on an index-organized table

Objective: Navigate to installed products and answer questions about them.

Background and Overview

In this exercise, you will add a secondary index to the index-organized table you created in the last exercise. The secondary index will based on the product ID.

Fact File
Review the fact file to find out more information about the House-O-Pets and its Oracle database. Click the link below to see the contents of the tables in the House-O-Pets database.
House O-Pets database
To create a secondary index on an IOT, you need to use a special syntax with the `PCTTHRESHOLD` clause to control the overflow segment.
Correct SQL (Using Secondary Index on an IOT)
```
CREATE INDEX PROD_ID_IDX 
ON IOT_SALES(PROD_ID) 
PCTFREE 10;
```
Alternatively, if IOT_SALES includes an overflow segment, you can specify:
```
CREATE INDEX PROD_ID_IDX 
ON IOT_SALES(PROD_ID) 
INCLUDING PRIMARY KEY;
```
or use a logical rowid index:
```
CREATE INDEX PROD_ID_IDX 
ON IOT_SALES(PROD_ID) 
USING INDEX ORGANIZED TABLE;
```
Additional Considerations:
1. You can only create a secondary index if the IOT allows it.
2. Consider using IOT_MAPPING_TABLE if you need a logical rowid-based lookup.
3. If your IOT has an overflow segment, ensure the index structure aligns with the storage model.
Scenario You have discovered that some of the queries against the IOT_SALES table ask for the information returned to be sorted by product ID. You want to see if the performance of queries on the index-organized table you just created can be improved by adding a secondary index based on the product ID.
Instructions Create a secondary index on the index-organized table you created in the last exercise. This index will be based on the PRODUCT_ID column in the IOT_SALES table.
Hints
1. The syntax for creating a secondary index is the same as a standard index.
2. This index will not be unique.

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.