We will define each type of table before getting into the details. There are nine major types of tables in Oracle, as follows:

•\ Heap-organized tables: These are normal, standard database tables. Data is managed in a heap-like fashion. As data is added, the first free space found in the segment that can fit the data will be used. As data is removed from the table, it allows space to become available for reuse by subsequent INSERTs and UPDATEs. This is the origin of the name “heap” as it refers to this type of table. A heap is a bunch of space, and it is used in a somewhat random fashion.

•\ Index-organized tables: These tables are stored in an index structure. This imposes physical order on the rows themselves. Whereas in a heap the data is stuffed wherever it might fit, in index-organized tables (IOTs) the data is stored in sorted order, according to the primary key.

•\ Index clustered tables: Clusters are groups of one or more tables, physically stored on the same database blocks, with all rows that share a common cluster key value being stored physically near each other. Two goals are achieved in this structure. First, many tables may be stored physically joined together. Normally, you would expect data from only one table to be found on a database block, but with clustered tables, data from many tables may be stored on the same block. Second, all data that contains the same cluster key value, such as DEPTNO = 10, will be physically stored together. The data is clustered around the cluster key value. A cluster key is built using a B*Tree index. The advantage to index clustered tables is that disk I/O is reduced and query performance is improved when accessing tables that are frequently joined on the cluster key.

•\ Hash clustered tables: These tables are similar to index clustered tables, but instead of using a B*Tree index to locate the data by cluster key, the hash cluster hashes the key to the cluster to arrive at the database block the data should be on. In a hash cluster, the data is the index (metaphorically speaking). These tables are appropriate for data that is read frequently via an equality comparison on the key.

•\ Sorted hash clustered tables: This table type combines some aspects of a hash clustered table with those of an IOT. The concept is as follows: you have some key value that rows will be hashed by (say, CUSTOMER_ID) and then a series of records related to that key that arrive in sorted order (timestamp-based records) and are processed in that sorted order. For example, a customer places orders in your order entry system, and these orders are retrieved and processed in a first in, first out (FIFO) manner. In such a system, a sorted hash cluster may be the right data structure for you.

•\ Nested tables: These are part of the object-relational extensions to Oracle. They are simply system-generated and maintained child tables in a parent/child relationship. They work much in the same way as EMP and DEPT in the SCOTT schema with the EMP table being the nested table. EMP is considered to be a child of the DEPT table, since the EMP table has a foreign key—DEPTNO—that points to DEPT. The main difference is that they are not stand-alone heap-organized tables.

•\ Temporary tables: These tables store scratch data for the life of a transaction or the life of a session. These tables allocate temporary extents, as needed, from the current user’s temporary tablespace. Each session will see only the extents that session allocates; it will never see any of the data created in any other session. Temporary tables allow you to temporarily persist data with the benefit of generating much less redo (and less undo as of Oracle 12c) than a regular heap-organized table (see Chapter 9 for a complete discussion on the redo and undo behavior of temporary tables).

•\ Object tables: These tables are created based on an object type. They have special attributes not associated with nonobject tables, such as a system-generated REF (object identifier) for each row. Object tables are really special cases of heap, index-organized, and temporary tables, and they may include nested tables as part of their structure as well.

•\ Blockchain tables: Blockchain tables are immutable (insert-only) tables. As rows are inserted, they are organized into a number of chains. Each row (except the first row in the chain of rows) is chained to the previous row. The rows are tamper-proof, meaning they cannot be removed or updated. Each row is signed with a cryptographic hash value which is based on the hash of the new data being inserted and the hash of the prior row in the chain. This allows companies to maintain a tamper-resistant audit trail of the current row and any historical transactions.

•\ External tables: The data in these tables are not stored in the database itself; rather, they reside outside of the database in ordinary operating system files (with the columns of data in the file usually demarcated by a delimiter or position). External tables give you the ability to query a file residing outside the database as if it were a normal heap-organized table inside the database. They are most useful as a means of getting data into the database (they are a very powerful data loading tool). Furthermore, external tables have an unload capability; this provides an easy way to move data between Oracle databases without using database links. We will look at external tables in some detail in Chapter 15.