Classification of Oracle Database object tables Learn notes

1, the Heap organization table (heap organized table)
is the "normal" standard database table. Data is managed in a heap manner. The records in the heap organization table are unordered and are not placed in a particular order.

2, Index Organization table (indexed organized table)
The data for the Index organization table is stored in the index associated with it, adding, updating, and deleting the table will only cause an update to the index. Queries must query the data based on the index.

The Index organization table must have a primary key set.

CREATE TABLE Indextable (
ID VARCHAR2 (10),
Name VARCHAR2 (20),
Constraint Pk_idx primary key (ID)
)
Organization Index;

INSERT into indextable values (' 1 ', ' 1 ');
INSERT into indextable values (' 2 ', ' 2 ');

To organize table queries on indexes:

SELECT * from indextable where id = ' 2 ';
SELECT * from indextable where name = ' 2 ';

The first query is the primary key, and the execution plan goes with the index UNIQUE SCAN. The index FAST full SCAN is the execution plan for the second query, which is not an indexed field. Indicates that the data for the Index organization table is followed by the index, and the execution plan is automatically queried based on the index.

3. Index Clustered table
A cluster (cluster) that stores data from multiple tables on the same block. Similar to data sharing.

1) Create a cluster

Create cluster Emp_dep_cluster (depno number (2)) size 2048;

The size option tells Oracle to associate approximately 2048 bytes of data with each clustered key value, and Oracle uses this parameter to calculate the maximum number of clustered keys that can be dropped per block.

2) Create a clustered key index

CREATE index emp_dep_cluster_idx on cluster emp_dep_cluster;

3) Create a table

CREATE TABLE Department (
Depno Number (2) primary key,
Depname VARCHAR2 (20)
)
Cluster Emp_dep_cluster (DEPNO);

CREATE TABLE Employee (
Empno Number primary Key,
EmpName varchar2 (20),
Depno Number (2) References Department (DEPNO)
)
Cluster Emp_dep_cluster (DEPNO);

You need to use the cluster keyword to specify which column of the base table maps to the cluster key of the cluster itself. The deptno of the two tables is the same as the location on the disk.

4, hash cluster table (hash clustered tables)
These tables are similar to clustered tables, but do not use the b* Tree index clustering key to locate the data, and the clustered key index is replaced by a hash function.

1) to create a hash cluster

Create cluster Emp_dep_cluster (depno number (2)) Hashkeys size 2048 hash is depno;

2) Create a table

CREATE TABLE Department (
Depno Number (2) primary key,
Depname VARCHAR2 (20)
)
Cluster Emp_dep_cluster (DEPNO);

CREATE TABLE Employee (
Empno Number primary Key,
EmpName varchar2 (20),
Depno Number (2) References Department (DEPNO)
)
Cluster Emp_dep_cluster (DEPNO);

5, Ordered hash cluster table (sorted hash clustered tables)
If you frequently use:
Select *
From T
Where key=:x
ORDER BY Sorted_column
That is, you want to press a key to get the data, but require that the data be sorted by another 1 columns/columns. By using an ordered hash cluster, Oracle can return data without performing a sort. This is done by physically storing the data by pressing the key during insertion.

I feel like this kind of table is used in a very special place, usually encounter a relatively small chance. So I'm not going to elaborate.

6. Nested table
Skip it, wait till you use it.

7. Temporary table
A temporary table (temporary table) is used to hold intermediate result sets during a transaction or session. Data that is saved in a temporary table is visible only to the current session, and the data for other sessions is invisible to all sessions, even if the current session has committed (commits) the data, and no other session can see its data.

For temporary tables, there is no multiuser concurrency problem because one session will not block another session because of the use of a temporary table. Even if we "lock" a temporary table, it does not prevent other sessions from using their own temporary tables.

Temporary tables are divided into session-based temporary tables and transaction-based temporary tables.

0 The default temp table

Create Global temporary Table Temp
As
SELECT * FROM EMP where 1=0
/

The default temporary table is transaction-level based (on commit delete rows).

1 session-based temporary table

Create Global temporary Table Temp
On commit Preserve rows
As
SELECT * FROM EMP where 1=0
/

On commit Preserve rows display Description This is a session-based temporary table. The data in the temporary table persists when you exit the session or use DELETE, Truncate statement deletion.

You can only query the records of temporary tables in the current session.

2 transaction-based temporary tables

Create Global temporary Table Temp
On commit Delete rows
As
SELECT * FROM EMP where 1=0
/

The description of the on commit delete rows display this is a transaction based temporary table.

If a commit is executed in a session, the record in the temporary table is emptied.

Temporary tables are available for every session, and records are emptied after the session is finished, but the table structure is saved in the database and no temporary tables are deleted.

8. Object Table
The object table resembles a struct variable in C. First create an object, and then create the table with this object.

Create or replace type Address_type
As Object
(City Varchar2 (30),
Street VARCHAR2 (30),
State VARCHAR2 (2),
Zip number
)
/

Create or replace type Person_type
As Object
(Name VARCHAR2 (30),
Dob date,
Home_address Address_type,
Work_address Address_type
)
/

Create table people of Person_type
/

Insert into people values (' Tom ', ' 15-mar-1965 ',
Address_type (' Reston ', ' 123 Main Street ', ' Va ', ' 45678 '),
Address_type (' Redwood ', ' 1 Oracle Way ', ' Ca ', ' 23456 '));

SELECT * from people;

Select name, p.home_address.city from people p;

9. Summary
The most commonly used table is the heap organization table, index organization table, temporary table of these three kinds.