What are the types of tables in Oracle

There are 9 types of tables: 1. Index-organized table, a table stored according to an index structure; 2. Index clustered table, a table that stores multiple tables together; 3. Hash clustered table , hash the data to the database block by hashing the clustering key; 4. Ordered hashing clustering table, the rows are hashed according to a certain key value, and a series of records related to the key are stored in the order of insertion; 5. Nested tables and so on.

The operating environment of this tutorial: Windows 10 system, Oracle 11g version, Dell G3 computer.

What are the types of tables in Oracle

1) Heap organized table (heaporganized table)

This is a standard database table. Data is managed in a heap. When adding data, the first free space found in the segment that can accommodate the data is used. After deleting data from the table, allow future INSERTs and UPDATEs to reuse this space. A heap is a set of space that is used in a random manner.

2) Index organized table

The table is stored according to the index structure. This forces some physical ordering of the rows themselves. In a heap-organized table, data can be placed anywhere; in IOT, data must be stored in an orderly manner according to the primary key.

3) Index clustered table

Cluster refers to a group of one or more tables, which are physically stored All rows with the same clustering key value are physically stored adjacent to each other on the same database block.

First, multiple tables can be physically stored together. Generally speaking, you can think of one database block as storing data for one table, but for clustered tables, data from multiple tables may be stored on the same block.

Secondly, all data containing the same clustering key value (such as DEPTNO=10) will be physically stored together. The data is "clustered" together by the clustering key value. Clustering keys are created using B* tree indexes.

4) Hash clustered table

is similar to an index clustered table, but does not use B* tree index clustering keys to locate data. Instead, the data is hashed onto database blocks by hashing the clustering key. In hash clustering, the data is the index (metaphorically speaking). Hash clustered tables are suitable if data needs to be read frequently through key equality comparisons.

5) Sorted hash clustered table

Newly added in Oracle10g, it has the characteristics of a hash clustered table and is also There are some characteristics of IoT.

Rows are hashed by a key value (such as CUSTOMER_ID), and a series of records related to that key are stored in insertion order. (Thus these records may be timestamp based records).

For example, in an order entry system, orders are obtained and processed on a first-in, first-out (FIFO) basis. In such a system, ordered hash clustering is the appropriate data structure.

6) Nested table

Nested table is part of the Oracle object relational extension. They are actually child tables in a parent/child relationship generated and maintained by the system.

7) Temporary table (temporarytable)

The temporary table allocates temporary sections from the current user's temporary table space as needed. Each session sees only the extents allocated by this session; it never sees any data created in any other session.

8) Object table (objecttable)

The object table is created based on a certain object type. They have special properties that non-object tables do not have. For example, the system generates a REF (object identifier) ​​for each row of the object table.

Object tables are actually special cases of heap-organized tables, index-organized tables, and temporary tables, and can also contain nested tables as part of their structure.

9) External table (externaltable)

These tables are not stored in the database itself, but placed outside the database, that is, placed in the ordinary operating system in the file.

Using an external table, you can query a file outside the database, as if the file was also an ordinary table in the database. External tables are most useful for loading data into the database (external tables are very powerful data loading tools).

Oracle10g goes a step further and also introduces an external table unloading feature, which provides an easy way to move data between Oracle databases without using database links.

Recommended tutorial: "Oracle Video Tutorial"

The above is the detailed content of What are the types of tables in Oracle. For more information, please follow other related articles on the PHP Chinese website!