Databases Reference
In-Depth Information
A bitmap join index allows a denormalization without using materialized views, but in the
index itself.
See also
F
We have talked about denormalization in the receipe
Optimizing performance with
schema denormalization
in
Chapter 2
.
Migrating to index organized tables
There are situations in which we access a table only—or mainly—using the primary key
value. Situations such as a code lookup table, or a table containing inverted indexes, fit
well in this definition.
In this recipe, we will see how to combine a heap table and a B-tree index in what is called
an index organized table, and what benefits—and caveats—we have in performance when
adopting this structure to store our data.
How to do it...
The following steps will demonstrate index organized tables:
1.
Connect to the database as user SH:
CONNECT sh@TESTDB/sh
2.
Create an index organized table based on the
COUNTRIES
table of the SH schema:
CREATE TABLE IOT_COUNTRIES (
COUNTRY_ID NUMBER NOT NULL,
COUNTRY_ISO_CODE CHAR(2) NOT NULL,
COUNTRY_NAME VARCHAR2(40) NOT NULL,
COUNTRY_SUBREGION VARCHAR2(30) NOT NULL,
COUNTRY_SUBREGION_ID NUMBER NOT NULL,
COUNTRY_REGION VARCHAR2(20) NOT NULL,
COUNTRY_REGION_ID NUMBER NOT NULL,
COUNTRY_TOTAL VARCHAR2(11) NOT NULL,
COUNTRY_TOTAL_ID NUMBER NOT NULL,
COUNTRY_NAME_HIST VARCHAR2(40),
CONSTRAINT PK_IOT_COUNTRIES PRIMARY KEY (COUNTRY_ID))
ORGANIZATION INDEX
INCLUDING COUNTRY_NAME
OVERFLOW TABLESPACE USERS;
