Databases Reference
In-Depth Information
■
Tip
Always attempt to keep the most frequently accessed columns within the table itself—and outside of the
overflow segment—for better access performance.
Compressing an Index-Organized Table
You can use a concept called
key compression
on index-organized tables in order to save storage space
and compress data. It's called “key compression” because it can eliminate repeated values of the key
columns. You can use key compression either with a
CREATE TABLE
statement or an
ALTER TABLE
statement. See the following sample DDL of a
CREATE TABLE
statement with key compression enabled:
SQL> CREATE TABLE employees_iot
2 (
3 EMPLOYEE_ID NUMBER(7) NOT NULL
4 ,FIRST_NAME VARCHAR2(20)
5 ,LAST_NAME VARCHAR2(25) NOT NULL
6 ,EMAIL VARCHAR2(25) NOT NULL
7 ,PHONE_NUMBER VARCHAR2(20)
8 ,HIRE_DATE DATE NOT NULL
9 ,JOB_ID VARCHAR2(10) NOT NULL
10 ,SALARY NUMBER(8,2)
11 ,COMMISSION_PCT NUMBER(2,2)
12 ,MANAGER_ID NUMBER(6)
13 ,DEPARTMENT_ID NUMBER(4)
15 ,CONSTRAINT employees_iot_pk PRIMARY KEY (employee_id, job_id)
17 )
18 ORGANIZATION INDEX COMPRESS 1
19 TABLESPACE empindex_s
20 PCTTHRESHOLD 40
21 INCLUDING salary
22 OVERFLOW TABLESPACE overflow_s;
Table created.
If you have a pre-existing table on which you want to enable key compression, you can simply use
the
ALTER TABLE...MOVE
statement to enable the compression.
SQL> ALTER TABLE employees_iot MOVE TABLESPACE empindex_s COMPRESS 1;
Table altered.
You can only use key compression when there are multiple columns as part of the primary key, or
else will you receive the following message when creating the table:
CREATE TABLE employees_iot
*
ERROR at line 1:
ORA-25193: cannot use COMPRESS option for a single column key
