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.

DROP TABLE … PURGE

The aim to use “drop table” command is to remove a table and all its data from the database. For example, let’s create a table named table_name :

SQL> create table table_name(id varchar(5));
Table created

If we decide to move the table:

SQL> drop table table_name;
Table dropped

But this execution moves the table, that we want to drop, to the recycle bin .We can see the contents of user’s recycle bin:

select * from recyclebin;

SQL> select object_name from recyclebin;
OBJECT_NAME
————————————————-
BIN$uCbv7uf/R/WSU6L3mWI1uQ==$0

After you drop the table Oracle will return an error:

SQL> select * from table_name;
select * from table_name
ORA-00942: table or view does not exist

If you accidentally drop the table, you can retrieve the oldest version of the table from the recycle bin:

SQL> flashback table table_name to before drop;
Done

SQL> select * from table_name;
ID
—–

But if you want to drop the table entirely and release the space, you should use “purge” clause. While , the table will be not moved to the recycle bin . You cannot recover the dropped table anymore.

SQL> drop table table_name purge;
Table dropped

On the other hand, you can use the following steps:

SQL> drop table table_name;
Table dropped

SQL> purge table table_name;
Done

Another way to purge table is:

SQL> drop table table_name;
Table dropped

SQL> select object_name from recyclebin;
OBJECT_NAME
————————————————–
BIN$KIkRl/BNTzmEdg9BW4Z3Pg==$0

SQL> purge table “BIN$KIkRl/BNTzmEdg9BW4Z3Pg==$0″;
Done