Types Of Tables In Oracle Database

Types of Tables in Oracle Database

Creating a Table:

To create a table, you need to define three things:

• Name of the table.
• Columns in the table.
• Datatypes of those columns

The basic syntax for creating table is :

Create table <table_name> (

<column_name 1> <data_type>,

<column_name 2>  <data_type>,

<column_name 3> <data_type>,

….

)

In Oracle, there are different types of tables. Each type of table have it's own characteristics. 

Type of Tables:

           1.      Heap Organized Table.

           2.      Index Organized Table.

           3.      External Table.

           4.      Cluster Table.

           5.      Temporary Tables.

Heap Organized Table:

        The default type of table in Oracle is heap table. If you use ‘Create table’ clause normally, you create a standard database table. A heap Organized Table is a table which does not stores rows in a particular order. This means the database free to store rows wherever there is space. You can add “Organization heap” if you want to be explicit:

Example: Create table student (id int, name char (20), sub char (20), marks int);

(Or)

Create table student (id number(10), name varchar2(20), sub char (20), marks number(10)) Organization heap;

Advantages of Heap Organized table:
                 ·        Heap tables are Very fasted to insert data.
                  ·      The Heap Table does not have any non clustered index. Then the entire table will examined to find any row. This is applicable when the table is tiny. 

Disadvantage of Heap organized table:

                ·        The heap table will not applicable for large tables.
                 ·        When you want to access the data form large tables then it is very slow to access the data.

Index Organized Table:

         A row in an Index Organized Table (IOT) does not have a stable physical location. It keeps data in sorted order. Index Organized Table (IOT) have their primary key data and non-key columns data stored with in the same B-Tree structure. The data will stored in the primary key index.

         To Create Index Organized Table you need:

                

             ·        Specify the primary key using a column or table constrain.

             ·        Use the ORGANIZATION INDEX.

Example:

Create table student (id number(10), name varchar2(20), marks number(10), constrain pk_student primary key(id))

Advantages of Index Organized Table:

          ·        Index Organized Tables make accessing data via the primary key quicker as the key and the data reside in the same structure.
           ·        Since there is no need to read an index, the table data is read in a separate structure.
            ·        Less duplication of the key columns in an index and table mean low storage requirements

·        Increased performance: There is no need to access a row in the database from an index structure, so you can reduce the total number of operations needed to retrieve data.

·        Reduced table space: Because you do not need to link to a row in a table, there is no need to store the ROW ID in the index. The overall space required for the table is reduced.

·        Presorted data: The data in the leaf nodes is already sorted by the value of the primary key.

Disadvantages of Index Organized Table:

       ·        You must have a primary key on the table with a unique value.

       ·        You cannot have any other indexes on the data.

       ·        You cannot partition an index-organized table.

       ·        An index-organized table cannot be a part of a cluster.

       ·        The data in the row must be fairly small. Large amounts of data in the leaf nodes will require more to retrieve it.

External Table:

            External tables allow Oracle to query data that is stored outside the database in flat files. The ORACLE_LOADER driver can be used to access any data stored in any format that can be loaded by SQL*Loader. No DML can be performed on external tables but they can be used for query, join and sort operations. Views and synonyms can be created against external tables. They are useful in the ETL process of data warehouses since the data doesn't need to be staged and can be queried in parallel. They should not be used for frequently queried tables.

            You use external tables to read non-database files on the database server. For example, comma-separated values (CSV) files. To do this, you need to:

• Create a directory pointing to the location of the file on the server
• Use the organization external clause
• State the directory and name of the file you want to read

Creation of External Table:

            Create the external table using the CREATE TABLE..ORGANIZATION EXTERNAL syntax. This defines the metadata for the table describing how it should appear and how the data is loaded.

Example:

create or replace directory student_dir as '/myfile';

create table Student  (

Student_name varchar2(100)

) organization external (

default directory tmp

location ('student.csv')

);

Note: Live SQL doesn't support external tables. So these statements will fail!

When you query this table, it will read from the file:

/myfile/student.csv

This file must be accessible to the database server. You cannot use external tables to read files on your machine.

Advantages of External Tables:

            ·        External tables can be selected, sorted, filtered, joined, intersected, misused, unioned and so on using SQL, the language most familiar database to Oracle developers.
              ·         Loading a table from an external table is faster than SQL*Loader due to parallel query and DML.
                ·        External tables do not require any database space; only the space consumed by the flat-files on the file system.

Cluster Table:

            Use the CREATE CLUSTER statement to create a cluster. A cluster is a schema object that contains data from one or more tables, all of which have one or more columns in common. Oracle Database stores together all the rows from all the tables that share the same cluster key.

            After you create a cluster, you  can add tables to it. A cluster can contain a maximum of 32 tables. After you create a cluster and add tables to it, the cluster is transparent. You can access clustered tables with SQL statements just as you can access non clustered tables.

Creation of Cluster Table:

create cluster emp_cluster (

  emp_name varchar2(100)

);

Then place your tables in it using the cluster clause of create table:

create table emp_table (

  emp_name varchar2(100)

) cluster emp_cluster ( emp_name );

create table dept_table (

  dept    varchar2(20),

  emp_name varchar2(100)

) cluster emp_cluster ( emp_name );

Rows that have the same value for emp_name in emp_table and dept_table will be in the same place. This can make it faster to get a row for a given emp_name from both tables.

Temporary Tables:

           

           Temporary tables store session specific data. Only the session that adds the rows can see them. This can be handy to store working data.

     Oracle support two types of temporary tables:

·        Global Temporary Tables:

To create a global temporary table add the clause "global temporary" between create and table. For example:

Create global temporary table emp_table (emp_name varchar2(100))

The definition of the temporary table is permanent. All users of the database

can access it.But only your session can view rows you insert.

·        Private Temporary Tables:

These tables are only visible in your session. Other sessions can't see the table. To create one use "private temporary" between create and table. 

                   create private temporary table emp (emp_name varchar2(100));

    For both temporary table types, by default the rows disappear when you end your transaction. You can change this to when your session ends with the "on commit" clause.

   

    But either way, no one else can view the rows. Ensure you copy data you need to permanent tables before your session ends.