lecture 8 index organized tables clusters index compression bitmap indexes

Lecture 8

• Index Organized Tables• Clusters• Index compression• Bitmap Indexes

Index Organized Tables

• In Oracle Tables can be organized as:– HEAP (default, meaning unorganized)– INDEX (meaning Index Organized Table)

• Index Organized Table:– In MsSQL called: CLUSTER INDEX,

clusters in Oracle mean something else– Table data is stored together with primary

key index

IOT tables - details

• Index Organized Table:– There is no separate table data storage,

everything is stored in index data– Index structure is dynamic, physical

location of the row can change → ROWIDs can change

– Table access by primary key is very fast, faster than for HEAP organized tables

IOT tables - syntax

• To create IOT table, add ORGANIZATION INDEX when creating a table

• IOT table must have primary keyCREATE TABLE emp_iot (

id NUMBER PRIMARY KEY,

...

hire_date DATE

) ORGANIZATION INDEX;

IOT tables - overflow

• Storing table data with index can improve performance – there is no separate data storage to look up

• Storing data with index makes index larger – this degrades index performance

• In some cases record data is large, it is not practical to store that in the PK index

• IOT tables can have OVERFLOW area – if there is too much data it is stored in separate location

IOT overflow - syntax

CREATE TABLE emp_iot (


name VARCHAR2(100),

hire_date DATE,

description VARCHAR2(4000)

) ORGANIZATION INDEX

PCTTHRESHOLD 20

INCLUDING hire_date

OVERFLOW TABLESPACE users;

IOT tables – secondary indexes

• For HEAP table, index contains ROWID

• For IOT tables rows can be moved as index structure changes, ROWID cannot be fixed

• IOT table always contains Primary Key index, other indexes are called Secondary Indexes

• Secondary Index contains Logical ROWID

• Logical ROWID contains:– Primary key of the row– ROWID guess – probable location of the row

IOT - secondary indexes, cont.

• When using secondary index:– Database finds the Logical ROWID– It tries to locate the row by ROWID guess– If that fails, it searches for the row using

primary key index

• As a result, secondary indexes on IOT perform worse than indexes on HEAP organized tables

Clustered Tables In Oracle

Tables with common fields are stored together.

The common field is called a cluster key.Records with the same cluster key arestored together for faster access.

Oracle implements two types of clusters:– Index Clusters– Hash Clusters

Indexed Clusters

• Recommended for situations where:– Queries retrieve records over a range of

values, for exampleSELECT * FROM emp WHERE dept_id = 1

not justSELECT * FROM emp WHERE id = 1

– Tables do not have fixed size, they grow unpredictably

Indexed Clusters example

CREATE CLUSTER emp_phones_cluster (emp_id NUMBER) size 1024;

CREATE INDEX idx_emp_cluster ON CLUSTER emp_phones_cluster;

Indexed Clusters example, cont.

CREATE TABLE emp_clust (


...

hire_date DATE

) CLUSTER emp_phones_cluster(id);

CREATE TABLE emp_phones_clust (

emp_id NUMBER REFERENCES emp_clust(id),

phone_number VARCHAR2(15),

...

) CLUSTER emp_phones_cluster(emp_id);

Using Indexed Clusters

• Indexed clusters – advantages:– Rows stored together can be retrieved

without additional join effort– Queries are faster when joining tables by

cluster key

• Disadvantages:– It can take longer when updating cluster

key column in a table

Hash Clusters

• Hash Clusters:– Useful when queries retrieve records based on an

equality condition on cluster key: SELECT * FROM products WHERE id = 5;

– Not useful for non-equality conditions:SELECT * FROM products WHERE id < 5;

– Useful when tables are static - they have fixed size. Maximum number of records and maximum space can be predicted.– Useful for dictionary tables, lookup tables etc.

Hash Clusters - example

CREATE CLUSTER products_cluster (

id number

) HASHKEYS 20;

CREATE TABLE products (

id number not null,

name varchar2(100),

...

) CLUSTER products_cluster(id);

Hash Clusters - details

• Using Hash Clusters:– Do not create Cluster Index for hash cluster– Do not create index on cluster key for hash

cluster– When using condition like:SELECT * FROM zip_codes WHERE id = 5;

access is faster than index cluster or table with index.

Index Compression

• Oracle multi column indexes can be COMPRESSED

• Uncompressed index stores values:COL1, COL2, rowid

(123, 1, ROWID)

(123, 2, ROWID)

(123, 3, ROWID)

(200, 1, ROWID)

(200, 2, ROWID)

(200, 3, ROWID)

Index Compression, cont

• Compressed index stores values:COL1, COL2, rowid

Prefix: 123

(1, ROWID)

(2, ROWID)

(3, ROWID)

Prefix: 200

(1, ROWID)

(2, ROWID)

(3, ROWID)

Using Index Compression

• Index compression is useful:– For indexes with 2 or more columns. The

first column must have many duplicated values.

– Index organized tables can be compressed

Index Compression syntax

CREATE TABLE emp_phones_iot (

emp_id number not null,

phone_number varchar2(15),

PRIMARY KEY (emp_id, phone_number)

) ORGANIZATION INDEX COMPRESS 1;

CREATE INDEX emp_dept_idx ON

emp(dept_id, id) COMPRESS 1;

Bitmap Indexes

• Bitmap Indexes are intended for:– low cardinality columns (many duplicated

values). This is the oposite of BTree indexes, which are best for unique or close to unique columns

– tables not very frequently updated

• Bitmap Indexes are only available in Oracle Enterprise Edition

Bitmap Indexes Characteristics

• Bitmap Indexes:– Must be non-unique– Include NULL values (BTree indexes do

not)– Can be IOT secondary indexes– Cannot be compressed

Bitmap Indexes example

CREATE TABLE properties (


bedrooms NUMBER,

bathrooms NUMBER,

garages NUMBER );

CREATE BITMAP INDEX idx_beds ON properties (bedrooms);

CREATE BITMAP INDEX idx_bths ON properties (bathrooms);

CREATE BITMAP INDEX idx_gar ON properties (garages);

SELECT id FROM properties

WHERE bedrooms = 3

AND bathrooms = 2

AND garages = 1;

Bitmap Indexes

• When creating bitmap index:– Oracle creates bitmap for each unique

value in a column (plus NULL value)– Each bitmap has as many values as

there are rows in the table


• Example table PROPERTIES

• Bedroom index bitmap

id Bedrooms

100 1

101 2

102 3

103 1

104 1

105 2

106 1100 101 102 103 104 105 106

1 1 0 0 1 1 0 1

2 0 1 0 0 0 1 0

3 0 0 1 0 0 0 0


• Bedroom index bitmap

SELECT id FROM properties WHERE bedrooms=1

– Get all 1-s from the first bitmapSELECT id FROM properties

WHERE bedrooms = 1 OR bedrooms = 2

– Combine bitmap 1 and 2, then get all 1-s

100 101 102 103 104 105 106

1 1 0 0 1 1 0 1

2 0 1 0 0 0 1 0

3 0 0 1 0 0 0 0

Bitmap Indexes

• Oracle supports many bitmap operations:– AND – AND operation between two

bitmaps– OR– NOT – MINUS (A MINUS B is A AND NOT B)– Bitmap range scans are supported (for

example: WHERE bathroom <= 2)

Bitmap AND

CREATE TABLE t1 (c1 NUMBER, c2 NUMBER);

CREATE BITMAP INDEX i1 ON t1 (c1);


SELECT c1,c2

FROM t1

WHERE c1 = 0 AND c2 = 0;

0 SELECT STATEMENT Optimizer=CHOOSE1 0 BITMAP CONVERSION (TO ROWIDS)2 1 BITMAP OR3 2 BITMAP INDEX (SINGLE VALUE) OF 'I1‘4 2 BITMAP INDEX (SINGLE VALUE) OF 'I2‘

Bitmap OR




SELECT c1,c2

FROM t1

WHERE c1 = 0 OR c2 = 0;

0 SELECT STATEMENT Optimizer=CHOOSE1 0 TABLE ACCESS (BY INDEX ROWID) OF 'T1‘2 1 BITMAP CONVERSION (TO ROWIDS)3 2 BITMAP OR4 3 BITMAP INDEX (SINGLE VALUE) OF 'I1‘5 3 BITMAP INDEX (SINGLE VALUE) OF 'I2'

Bitmap MINUS




SELECT c1,c2

FROM t1

WHERE c1 = 0 AND c2 != 0;

0 SELECT STATEMENT Optimizer=CHOOSE1 0 TABLE ACCESS (BY INDEX ROWID) OF 'T1‘2 1 BITMAP CONVERSION (TO ROWIDS)3 2 BITMAP MINUS4 3 BITMAP MINUS5 4 BITMAP INDEX (SINGLE VALUE) OF 'I1‘6 4 BITMAP INDEX (SINGLE VALUE) OF 'I2‘7 3 BITMAP INDEX (SINGLE VALUE) OF 'I2'

Bitmap MERGE




SELECT c1,c2

FROM t1

WHERE c1 > 0 AND c2 = 0;

0 SELECT STATEMENT Optimizer=CHOOSE1 0 TABLE ACCESS (BY INDEX ROWID) OF 'T1' 2 1 BITMAP CONVERSION (TO ROWIDS)3 2 BITMAP AND4 3 BITMAP INDEX (SINGLE VALUE) OF 'I2‘5 3 BITMAP MERGE6 5 BITMAP INDEX (RANGE SCAN) OF 'I1'

BTree bitmap execution plans


CREATE INDEX it2 ON t2 (c1);

CREATE INDEX it3 ON t2 (c2);

SELECT c1,c2

FROM t2

WHERE c1 = 0 AND c2 = 0;

0 SELECT STATEMENT Optimizer=CHOOSE (Cost=3 Card=1 Bytes=5)1 0 BITMAP CONVERSION (TO ROWIDS)2 1 BITMAP AND3 2 BITMAP CONVERSION (FROM ROWIDS)4 3 INDEX (RANGE SCAN) OF 'IT3' (NON-UNIQUE) 5 2 BITMAP CONVERSION (FROM ROWIDS)6 5 INDEX (RANGE SCAN) OF 'IT2' (NON-UNIQUE)

lecture 8 index organized tables clusters index compression bitmap indexes

Documents