Index Organized Table - 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/overview

In this exercise, you will create an index-organized table as part of your House-O-Pets database. This table will contain sales information. Once the table is created, you will rebuild the table.

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 view the tables and see the contents of the tables in the House-O-Pets database.
Pet Store Data Tables

CREATE TABLE sales_figures (
  store_id NUMBER, Quarter INTEGER, month INTEGER, amount NUMBER
CONSTRAINT pk_sales_figures PRIMARY KEY (store_id, quarter, 
month))
ORGANIZATION INDEX;

Download files

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

Scenario

You would like to deliver better performance for queries about sales totals, which are highly visible in the House-O-Pets management structure. (You do not want performance to go to the dogs.) You decide to create an index-organized table to see whether it can significantly improve performance for these types of queries.

Instructions

Create an index-organized table called IOT_SALES that looks similar to the MONTHLY_SALES view in the House-O-Pets database. This table will contain:

A column called SALES_DATE, data type DATE, which will also act as the primary key
A column called SALE_TOTAL, data type NUMBER(10,2)
A column called PRODUCT_ID, data type NUMBER(10,0)

You should create the table in a script. Once you write the DDL to create the table, add a line of SQL code that will reorganized the table once it is built. Once you have submitted the appropriate code, please answer the following questions as part of the exercise:

Why was the particular table you created appropriate as an index-organized table?
What conditions would lead you to rebuild the index-organized table you created, and why?

Hints

Remember that you must have a primary key constraint defined for an index-organized table.
The important keywords for an index-organized table come at the end of the DDL statement.

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.