Tables in Oracle

When designing a database the end results is the DDLs for creating the database elements. For instance the tables. In Oracle there are two kinds of tables: relational and object tables. There are different kinds of relational tables: heap-organized table, index-organized table and external table. A table can be either permanent or temporary.

The regular table is a heap-table where the data is saved in a heap without no particular order. In Oracle Database 8.0 the index-organized table was introduced. An index-organized table is an index structure ordered by the primary key: each leaf block in the index structure stores both the key and non-key columns (the actual data). Since Oracle Database 9i there been a table type called external table. An external table is stored outside the database.

An object table is one of the object-relational features that were added to Oracle Database to support object-oriented functionalities introduced in object-oriented databases in 1990’s. In an object table each row represents an object or implementation of it. Object table can be defined using user-defined types, possibly supertypes and subtypes. Later we will talk about how to do this using Data Modeler.

All these table types described earlier are permanent tables where the data stays saved on a disc until somebody deletes it permanently. There is also another kind of table type: a temporary table. The data in those tables are saved only temporarily and the data is deleted automatically either after the transaction or a session. The definition of a temporary table is not dropped automatically; it remains until somebody deliberately drops it. A temporary table is meant to be used when you need to temporarily store a set of rows to be processed against other tables or temporary tables. Temporary tables are very useful if you need the result set in many queries, for instance when you need to update several tables using the data in a temporary table. Do not use it to split a query to smaller queries, it will be less efficient than the original query since Oracle database is specialized in performing queries in a database and will do them as efficiently as possible.

Cheers,

Heli

Advertisements

Share this:

Like this:

Post navigation

2 thoughts on “Tables in Oracle”

Good summary. One niggling issue: temporary tables should not be dropped by users – the data is automatically lost after a transaction or session (as you correctly point out) but the definition of the table remains.

Unless, of course, when you say “user” you actually meant “the designer of the schema”. 🙂

Thank you. Yes, that is exactly what I was trying to say. The data is deleted automatically and the object only when it is dropped by a person, it does not happen automatically. I’ll try to rephrase it.