VF=>user  ID=>    Login Register
Feedback   FAQ   Blog
871,009 quizzes played * 1,147 active players * US$68,662 in prizes awarded

Commentary

Actions


  On Tables: Index Organized Tables (Database Design)      Add to Favorites

Summary

When creating small lookup tables, consider using IOTs (index organised tables). These combine a table and an index into a single structure. This means that Oracle needs to do less work to fetch the records. With a normal heap table, many queries will have to visit the index and the table, causing extra overhead.

Details

Assumes Oracle Database 10g or higher
Intermediate Quiz, IDs: 8621/586428/242589

Reviewer(s): Yuan Tschang

The Question

We're creating a lookup table to hold the order statuses for our application. It will hold the following values:
 
ORDER_STATUS_ID DESCRIPTION
--------------- -----------
              1 Awaiting payment
              2 Awaiting shipment
              3 Shipped
              4 Cancelled
              5 Refunded
 
There will be a large number of queries in the application fetching the description of a status by id like:

select description from plch_order_statuses where order_status_id = 1;

We want this to be as efficient as possible, so we're testing different approaches to implementing this. 
 
Which of the following implementations below enable the query above to perform fewer consistent gets than the full-table scan (FTS) version of the query?

The Choices [↑]

Explanation of Result Icons
Choice 1 (17686)
create table plch_order_statuses (
  order_status_id integer not null,
  description     varchar2(30) not null
);

There is no index defined, so all queries against this table must be a full table scan!




95%
Choice 2 (17687) [Do Not Use]
create table plch_order_statuses (
  order_status_id integer not null,
  description     varchar2(30) not null
);

create index plch_order_status_i 
on plch_order_statuses (order_status_id);

There is an index, so the query uses fewer consistent gets than a FTS. However we've not defined an primary or unique keys, meaning we can enter duplicate status IDs! This isn't good as it can lead to data quality issues.




61%
Choice 3 (17688) [Do Not Use]
create table plch_order_statuses (
  order_status_id integer not null,
  description     varchar2(30) not null
);

create unique index plch_order_status_i 
on plch_order_statuses (order_status_id);  

Here we have a unique index, enabling the query to consume fewer consistent gets than a FTS. It is also unique so we can't enter duplicate status IDs. However it's bad practice to define the index without also creating a primary and/or unique constraint on the table. Without the constraint, we can't create foreign keys to this lookup table.




73%
Choice 4 (17689)
create table plch_order_statuses (
  order_status_id integer not null primary key,
  description     varchar2(30) not null
);

Oracle automatically creates a unique index to support the primary key constraint. The query can use this index, making this more efficient that a FTS.




76%
Choice 5 (17690) [Recommended Solution]
create table plch_order_statuses (
  order_status_id integer not null primary key,
  description     varchar2(30) not null
) organization index;

When we create an index organized table (IOT), only one object is created - the index - instead of two - the index and the table. This allows queries against the IOT to be more efficient than against normal (heap) tables, as Oracle only needs read the index to fetch all the data. In this example, this option only requires one consistent get - the most efficient of all!




75%

Answer [↑]

Small tables with only a handful of rows can still benefit from being indexed. Queries to find a single row will still perform better when a non-unique index is used over full-scanning the whole of the table. In this quiz, the FTS uses 7 consistent gets (logical IOs), but the query with a normal index uses only 3 (choice 2).

It is good practice to define primary keys on all your tables however, so these tables should have a primary key. These are backed by an automatically generated unique index (though you can specify an existing index if you wish), which Oracle can then use when executing queries. So in this quiz, both choices 3 and 4 both use "index unique scans" in the execution plan, consuming 2 consistent gets.

Query performance can be improved further by building the table as an index-organised table. This means that instead of having two structures (a table and an index), there is only one - the index! This can reduce queries against these small IOTs to a single consistent get as in this quiz. The benefits may be small, but these can add up if the table is frequently queried in a busy system.

 

Statistics [↑]

Click to View Verification Code [↑]

Link to Quiz [↑]

Commentary [↑]

Ask for Help     Raise Objection     Start New Discussion     Your Comments
Last: 295 days ago
Replies: 1
Likes: 2
ANSWERED
Unread Comments
Likes: 0
REJECTED
Unread Comments
Last: 263 days ago
Replies: 5
Likes: 0
SUBMITTED
Unread Comments
1 - 3