How Oracle indexed clustered tables work

Richard-lui

One: first introduce the working principle of Index Cluster table: ( create cluster first , then CREATE index in cluster, specify column's cluster type when table is created )

Clustering refers to the fact that if a group of tables has some common columns, such a set of tables is stored in the same database block; Clustering also means that the relevant data is stored on the same block. With clustering, a block may contain data from multiple tables. Conceptually, if two or more tables are frequently linked, you can store the required data together in advance. Clustering can also be used for a single table, which can be grouped by a column to store data.

More simply, for example, the EMP table and the Dept table, these two tables are stored in different segment and may even be stored in different tablespace, so their data must not be in the same block. And we will often do a correlation query on these two tables, for example: SELECT * from Emp,dept Whereemp.deptno = Dept.deptno. Think carefully, the query is mainly to the operation of the block, the more block query, the system IO is more expensive. If I aggregate the data of these two tables in a small number of blocks, the query efficiency will certainly improve a lot.

For example, I now extract the value of all the employees deptno=10, and the corresponding department information is stored in the block (if not, you can concatenate the original block of additional blocks). This is how the indexed clustered table works.

Two: the creation process.

The indexed clustered table is created based on an indexed cluster (index cluster). Each cluster key is recorded inside. The cluster key is different from the index key we used to do, and the index key points to a row of data, and the cluster key points to an Oracle BLOCK. We can create an index cluster with the following command first.

Sql> Conn Scott/tiger

is connected.

Sql> DESC Dept

is the name empty? Type

----------------------------------------- ------------------------------------

Deptnonot NULL Number (2)

DNAMEVARCHAR2 (14)

LOCVARCHAR2 (13)

sql> Create cluster Emp_dept_cluster

2 (Deptno number (2))

3 size1024

4/

The cluster has been created.

The name can be user-defined, not necessarily called deptno, and the data type must be the same as the data type that needs to use this cluster number (2). One of the most critical parameters here is size. This option originally tells Oracle that we want to associate approximately 1024 bytes of data with each cluster key value (1024 is not a problem with the normal table data), and Oracle will use this database block setting to calculate the maximum number of clustered keys that each block can put down. Assuming that the block size is 8kb,oracle, a maximum of 7 clustered keys are placed on each database block, that is, the data for the corresponding department 10, 20, 30, 40, 50, 60, and 70 is placed on a block, and once the department 80 is inserted, a new block is used. The data stored is related to the insertion order.

Therefore, the size test controls the maximum number of clustered keys on each block. This is the most influential factor in the utilization of cluster space. If this size is set too high, then the keys on each block will be very small (the number of cluster keys that can be stored in block), we will unnecessarily use more space. If set too low, it will result in too much data chain (a cluster key is not enough to hold a single piece of data), which is inconsistent with the purpose of clustering, because the cluster is originally meant to store all the relevant data on a block.

Before you can put data into a cluster, you need to index the cluster first. You can create a table in a cluster now, but since we want to create and populate the table at the same time, we have to have a clustered index before we have the data, so let's set up the clustered index first.

The task of a clustered index is to get a clustered key value, and then return the block address of the block that contains the key. This is actually a primary key, where each cluster key value points to a block in the cluster itself. Therefore, when we request department 10 data, Oracle reads the cluster key, determines the corresponding block address, and then reads the data. The cluster key index is created as follows:

Sql> CREATE INDEX Emp_dept_cluster_idx

2 Oncluster Emp_dept_cluster

3/

The index has been created.

Now you can create a table:

Sql> Conn Segment_study/liugao

is connected.

Sql> CREATE TABLE Dept

2 (Deptno number (2) Primary key,3 dname VARCHAR2 (14),

4 LOCVARCHAR2 (13)

5)

6 Clusteremp_dept_cluster (DEPTNO)

7/

The table is created.

Sql> CREATE TABLE EMP

2 (empno numberprimary key,3 ename varchar2), 4 job varchar2 (9), 5 Mgr number, 6 hiredate date,7 Sal number, 8 comm number,

9 Deptnonumber (2) Constraint EMP_FK references dept (DEPTNO)

10)

11cluster Emp_dept_cluster (DEPTNO)

12/

The table is created.

We can view the creation through the SQL statement:

Sql> Select Cluster_Name, table_name

2 Fromuser_tables

3 Wherecluster_name is not NULL

4 by 1;

CLUSTER_NAME table_name

-----------------------------------------------------------

Emp_dept_cluster DEPT

Emp_dept_cluster EMP

Now, clustered, clustered indexes, clustered index tables have been established.

Three: Load data.

Loading data into the clustered index table is a very important thing, the processing method is not correct, will make the function of clustering is not complete, reduce query performance.

Method 1:

First, I added a very large column char (1000), adding this column to make the EMP line much larger than the current size. Makes a 1024 cluster unable to store a single row of records. Cannot add varchar2 (1000), because Oracle's principle of VARCHAR2 storage is to save, if data data is less than 1000, 1000 of the space will not be allocated. Char is how much it is used. Oh.

Sql> begin

2 for x in (SELECT * from Scott.dept)

3loop

4 Insertinto Dept

5 VALUES (X.deptno, X.dname, X.loc);

6 Insertinto EMP

7 select*

8 from SCOTT.EMP9 where deptno = X.deptno;

Ten endloop;

11end;

12/

Begin

*

An error occurred on line 1th:

ORA-02032: Cluster table cannot be used before cluster index is established

ORA-06512: On line 4

Sql> CREATE INDEX Emp_dept_cluster_idx

2 Oncluster Emp_dept_cluster

3;

The index has been created.

sql> ALTER TABLE EMP disable constraint EMP_FK;

The table has changed.

sql> truncate cluster emp_dept_cluster;

The cluster has been truncated.

Sql> ALTER TABLE EMP enable constraint EMP_FK;

The table has changed.

Sql> ALTER TABLE EMP Add data char (1000);

The table has changed.

The above execution error indicates that the clustered table cannot be used before the cluster index is established.

First we load the EMP table first, then the Dept table is loaded by the way.

sql> INSERT INTO Dept

2 Select * from Scott.dept; 4 rows created.

sql> INSERT INTO EMP

2 Select emp.*, ' * ' from scott.emp; 14 rows created.

Then make a query, through Dbms_rowid.rowid_block_number can see the blockid of this data, if the dept and EMP stored row data is not a block ID, then mark a ' * '. The query results are as follows:

Sql> Select Dept_blk, emp_blk,2 case if dept_blk <> emp_blk Then ' * ' End flag,

3deptno

4 from (

5 Selectdbms_rowid.rowid_block_number (Dept.rowid) dept_blk,6 dbms_rowid.rowid_block_number (Emp.rowid) emp_blk,7 Dept.deptno 8 from EMP, dept 9 Whereemp.deptno = Dept.deptno

10)

One by one Deptno

12/

DEPT_BLKEMP_BLK F DEPTNO

---------- ---------- - ----------

85 86 *10

85 86 *10

85 87 *10

85 8520

85 87 *20

85 86 *20

85 8520

85 86 *20

85 8530

85 86 *30

85 8530

DEPT_BLKEMP_BLK F DEPTNO

---------- ---------- - ----------

85 86 *30

85 8530

85 8530

14 rows have been selected.

We found that by inserting the EMP data before inserting the Dept data, most of the EMP and dept data are not on a block, which is not the purpose of our clustered index.

How Oracle indexed clustered tables work