Types of Table in Oracle

Table:-

Table is a basic data storage unit of oracle. Data get’s stored in a table logically. When we insert a row in table that time each row get’s a rowid assign to it.  Suppose I have a table called EMP_TEST below and if I want to see the rowid for a particuler row I can see that by using below mention queries.

CREATE TABLE EMP_TEST

(EMPNO NUMBER,

EMPTYPE VARCHAR2(10),

SALARY NUMBER,

JDATE DATE,

DEPTNO NUMBER

);

INSERT ALL

INTO EMP_TEST VALUES (1,'HR',99999,TO_DATE('04-NOV-2013','DD-MON-YYYY'),10);

INTO EMP_TEST VALUES (2,'ADMIN',1234,TO_DATE('02-MAY-2013','DD-MON-YYYY'),20);

INTO EMP_TEST VALUES (3,'IT',4321,TO_DATE('02-MAY-2013','DD-MON-YYYY'),30);

INTO EMP_TEST VALUES (4,'OPERATION',2314,TO_DATE('02-MAY-2013','DD-MON-YYYY'),40);

INTO EMP_TEST VALUES (10,'FINANCE',9876,TO_DATE(''02-MAY-2013','DD-MON-YYYY'),50);

INTO EMP_TEST VALUES (10,' FINANCE ',99999,TO_DATE(''02-MAY-2013','DD-MON-YYYY'),50);       

SELECT * FROM DUAL

COMMIT;

                                         

Rowid get’s assigned in the following format:-

Let’s talk about the rowid of EMPLOYEE whose EMPLOYEE NUMBER is 1.

Data object number              Relative file number                Block number                      Row number

AAAu61

AAJ

AAB8Ce

AAA

 

What are these four blocks for rowid AAAu61AAJAAB8CeAAA:-

·         AAAu61

The data object number identifies the segment AAAu61. A data object number is assigned to every database segment.

·         AAJ

The tablespace - relative data file number identifies the data file that contains the row AAJ.

·         AAB8Ce

The data block number identifies the block that contains the row AAB8Ce.Block numbers are relative to their data file, not their tablespace. Thus, two rows with identical block numbers could reside in different data files of the same tablespace. I.e. empno=10.

·         AAA

The row number identifies the row in the block.

There are different types of tables which are listed below:-

NO.	TABLE NAME
1	Heaped Organized Tables
2	Index Organized Tables
3	Clustered Tables
4	Partitioned tables
5	Global temporary tables
6	Dual table
7	External tables

Let’s discuss different types of table in detail:-

Ø  Ordinary or Heap organized tables:-

It’s a default type of table. When we create table ordinary/ heap organized table get’s created.

Syntax for creating heap organized table is:-

CREATE TABLE EMPLOYEE (EMPNO NUMBER, EMPNAME VARCHAR2 (10));

You can create and drop the table like shown below:-

Ø  Index organized tables (IOT):-

The data of this table is stored in an index like structure. Unlike head organized table whose data is stored as an unordered collection, data for index organized table is stored in B-Tree index structure in a primary key sorted manner.

This table is use to have direct access to the row. In short this can be use to increase the performance of the fetching query.

Syntax to create index organized table is:-

CREATE TABLE EMPLOYEE (EMPNO NUMBER, EMPNAME VARCHAR2 (10),

CONSTRAINT PK_EMPNO PRIMARY KEY (EMPNO));

OR

CREATE INDEX IND_EMPNO ON EMPLOYEE (EMPNO);

When you create primary key constraint or unique key constraint on a table, that time index with the same constraint name get’s created.  When you drop the constraint, index also get’s drop. Let’s have an example below:-

                                                 

When you create index on a table specifically and you need to drop that index, you need to drop it specifically like shown below:-

Ø  Clustered tables:-    

In simple words, “A cluster is a schema object that contains data from one or more tables, all of which have one or more columns in common”. Syntax to create cluster table is:-

CREATE CLUSTER EMPLOYEE (DEPTNO NUMBER (3));

CREATE TABLE DEPT (DEPTNO NUMBER (3) PRIMARY KEY) CLUSTER EMPLOYEE (DEPTNO);

CREATE TABLE EMPLOYEE (EMPNO NUMBER (5) PRIMARY KEY, ENAME VARCHAR2 (15), DEPTNO NUMBER (3) REFERENCES DEPT) CLUSTER EMPLOYEE (DEPTNO);

When you need to drop the cluster, that time you would need to drop it including its tables or you will have to first drop all the tables which are in the cluster and then drop the cluster like shown below:-

OR, drop all the tables first then drop cluster:-                     

Ø  Partitioned tables:-

Partitioning allows a table, index, or index-organized table to be subdivided into smaller pieces, where each piece of such a database object is called a partition. Each partition has its own name. We can divide a large amount of data into sub tables called partitions, according various criteria. We can partition tables when there are millions of records in a table. Partition tables are mostly used in data warehousing. 

Most commonly use partition types are:-

·         Range

·         Hash

·         List

·         Composite

We will be having example of Range partition only. Syntax to create partition table is:-

CREATE TABLE TABLE_NAME (COLUMN_LIST)

PARTITION BY RANGE

(

PARTITION PARTITION_NAME VALUES LESS THAN (VALUE),

PARTITION PARTITION_NAME VALUES LESS THAN (VALUE),

PARTITION PARTITION_NAME VALUES LESS THAN (VALUE),

PARTITION PARTITION_NAME VALUES LESS THAN (MAXVALUE),

);

Let’s look at the actual example:-

Above I have created an EMPLOYEE table with EMPNO, EMPNAME, SALARY and J_DATE (joining date) columns. I have partitioned that table on J_DATE column. While inserting or fetching values in the table it will insert or fetch the data from respective partition. For example, if I want to insert a record which is having J_DATE as 15 Feb 2013 then this record will be saved in PART_EMP_2013 sub table.

You can drop this table by simple drop command.

Ø  Global temporary table:-

The data in a global temporary table is private, such that data inserted by a session can only be accessed by that session. The session-specific rows in a global temporary table can be preserved for the whole session, or just for the current transaction. The ON COMMIT DELETE ROWS clause indicates that the data should be deleted at the end of the transaction.

Syntax to create global temporary table is:-

We can create transaction level temporary table and session level temporary table.

Let’s see what transaction level temporary table is:-

CREATE GLOBAL TEMPORARY TABLE TABLE_NAME

(

  COLUMN_LIST WITH_DATA_TYPE

)

 ON COMMIT DELETE ROWS;

            Let’s see what session level temporary table is:-

           

CREATE GLOBAL TEMPORARY TABLE TABLE_NAME

(

            COLUMN_LIST WITH_DATA_TYPE

)          

ON COMMIT PRESERVE ROWS;

Ø  Dual table:-

The DUAL table is a special one-column table present by default in all Oracle database installations. It is suitable for use in selecting a pseudo column such as SYSDATE or USER. The table has a single VARCHAR2 (1) column called DUMMY that has a value of 'X'.

Ø  External tables:-

External tables are used to fetch data from files like text file or excel file.

Read Also:-