Partition Table - Cluster Table - IOT Table(Index Organized Table)

Contents:- Partition Table, Cluster Table, IOT Table(Index Organized Table)

We can use following concepts to do performance tuning at database level. To enhance the performance in datawarehouse environment following technology are best to use to enhance the performance of the system.

Partition Table

Partition table divides table into several partition at more granulity level to enhance the performance of the table.

Concept

Partition Table is used to do Performance Tuning in an Oracle Database. Suppose There are Crores of records in table in data warehouse system . We can implement partition table there. We can divide whole table into different partition to do performance tuning while fetching the records from table.

Partition Table Types

Range Partition

CREATE TABLE EMP_RECORD(EID NUMBER,ENAME VARCHAR2(100),ESALARY NUMBER)

PARTITION BY RANGE(ESALARY)

(PARTITION E1 VALUES LESS THAN (50000) tablespace users,

PARTITION E2 VALUES LESS THAN (100000) tablespace example,

PARTITION E3 VALUES LESS THAN (150000),

PARTITION E4 VALUES LESS THAN (MAXVALUE));

Hash Partition

CREATE TABLE DEP_RECORD

(DEPARTMENT_ID NUMBER,DEPARTMENT_NAME VARCHAR2(100))

PARTITION BY HASH(DEPARTMENT_ID)

PARTITIONS 4

STORE IN (USERS,EXAMPLE);

List Partition

CREATE TABLE DEPT_RECORD

(DEPARTMENT_ID NUMBER,

DEPARTMENT_NAME VARCHAR2(100))

PARTITION BY LIST(DEPARTMENT_ID)

(PARTITION UPTO_20 VALUES(10,20) TABLESPACE USERS,

PARTITION UPTO_50 VALUES(30,40,50));

Composite Partition

Composite Partition is a combination of more than one partition in a table.

CREATE TABLE EMP_DEPT(EMPID NUMBER,EMPNAME VARCHAR2(10),DEPT_ID NUMBER)

PARTITION BY RANGE(EMPID)

SUBPARTITION BY HASH(DEPT_ID)

SUBPARTITIONS 8 STORE IN (USERS,EXAMPLE)

(PARTITION PART1 VALUES LESS THAN (110) TABLESPACE EXAMPLE,

PARTITION PART2 VALUES LESS THAN (MAXVALUE));

Partition Administration

Insert Records into Partition Table

SQL> Insert into dept_record values(10,’Administration’);

Select Data from Partition

SQL> select * from dept_record partition(upto_20);

Truncate Particular Partition

Sql> alter table dept_record truncate partition upto_20;

Add Partition into Partition by List

Sql> alter table dept_record add partition other values(default);

Add Partition into Partition by Range

SQL> alter table emp_Record add partition part6 values less than (500);

Add Partition into Hash

Alter table emp_record add partition;

Drop Partition of a table.

Sql> alter table emp_Record drop partition part6;

Dynamic Performance Views Related to Partition

Query to know how many partition tables in your Schema

Sql> select * from user_part_Tables;

Query to know partition level partitioning information

Sql> select * from user_tab_partitions;

Cluster Table

A Cluster is a schema object that contains data from one or more tables, all table of cluster have one or more common columns. Oracle Database stores together all the rows from all the tables that shares the same cluster key. So, that common column stores at the same disk space. So at the time of fetching data server process did not require to go different different block, it will fetch data from the same block.

1.Create Cluster Definitions

Sql> create cluster dept_id_c(department_id number) size 500 tablespace users;

2.Create Index on Cluster

Sql> create index dept_id_x_idx on cluster dept_id_c tablespace example;

3.Create First Table and use cluster

Sql> create table emp_record(empid number,ename varchar2(100),department_id number) cluster dept_id_c(department_id);

Create Second Table and use cluster

Sql> create table dept_record(department_id number,department_name varchar2(100)) cluster dept_id_c(department_id);

Index Organized Table(IOT)

An Index-Organized-Table has a storage organization that is variant of a primary B-Tree. Unlike an Ordinary (heap-organized) table whose data is stored as an unordered collection (heap).

An index organized table keeps its data sorted according to the primary key column values for the table. An Index Organized table stores its data as if the entire table was stored in an index.

An index organized table allows you to store its entire data in an index. A Normal index only stores the indexed column; an index organized table stores all its columns in the index.

Organization index Clause is used to create an index-organized table.

Typical Table                    -         Head Organized

Index Organized Table(IOT)      -         Structured in an Index

IOT Used for

è Small tables

è Exact Match

è Reducing Storage Requirements.

Example

SQL> create table country_state(state_id number,name varchar2(20 byte),constraint country_state_pk primary key(state_id)) organized index tablespace users;