HEAP ORGANIZED TABLES – OBJECT TABLES

In Oracle, tables are the basic storage units. There are several types of database tables. While designing an application in the database, you should use the appropriate table type to store data for necessities. Each type of table has different characteristics to be used in different applications.

Heap Organized Table:

The default table type in Oracle is heap table. If you use “create table” clause normally, you create a standard database table. Exp:

SQL> create table test (id integer);
Table created

If you want to create another type of table, it should be denoted in create table statement.
“Heap” name comes from its heap-like management. When data is added to the table, the first free space that data can fit is used. As data is removed from table, insert and update statements can use this freed spaces.

Object Table:

This type of table is created based on an object type, not collection of columns. It is generally used in special cases. The logic depends on object-oriented programming. “create table ” statement for object tables is:

create table test of x_type;

An example from Thomas Kyte-Expert one-on-one book :

SQL> create or replace type address_type
2 as object
3 ( city varchar2(30),
4 street varchar2(30),
5 add_state varchar2(2),
6 zip number
7 )
8 /
Type created

SQL> create or replace type person_type
2 as object
3 ( name varchar2(30),
4 dob date,
5 home_address address_type,
6 work_address address_type
7 )
8 /
Type created

SQL> create table people of person_type
2 /
Table created

SQL> desc people;

Name Type Nullable Default Comments
———— ———— ——– ——- ——–
NAME VARCHAR2(30) Y
DOB DATE Y
HOME_ADDRESS ADDRESS_TYPE Y
WORK_ADDRESS ADDRESS_TYPE Y

As you can see from the example, two objects are created. One of them is address_type and the other one is person_type. After that, a table named people is created based on these objects. This table has type person_type, also pay attention to the person_type object. It includes two attributes which has types address_type. The table people has four columns , two of them are object types.

Like in C structure types, C++ or Java class types , Object types are good structures for representing objects to use in the tables, instead of specifying the columns of the object again.
But according to heap tables, object tables allocates more spaces.

Remember that, there is no one truth. Performance depends on the application.