Two components of Oracle index clustering table data loading

The following content mainly introduces two main components of Data Loading for Oracle index clustering tables, including the working principle of index clustering tables, the description of the data loading and creation process of the Oracle Index Cluster table is as follows.

I. First, I would like to introduce how index clustering tables work.

Clustering means that if a group of tables have some common columns, such a group of tables will be stored in the same database block; clustering also stores related data on the same block. Using clustering, a block may contain data from multiple tables. In concept, if two or more tables are often linked, you can store the required data together in advance. Clustering can also be used for a single table. data can be stored in groups by a column.

To put it more simply, for example, EMP and DEPT tables are stored in different segments and may even be stored in different tablespaces. Therefore, their data will not be in the same BLOCK. We often perform join queries on these two tables, such as select * from emp and dept where emp. deptno = dept. deptno. if you think about it, the query mainly involves BLOCK operations. The more blocks you query, the larger the system IO consumption.

If I aggregate the data in these two tables into a small number of blocks, the query efficiency will certainly increase a lot. For example, if I extract all employees with the value of deptno = 10 and store the Department Information in this BLOCK, the original block can be connected to another block ). This is how index clustering tables work.

Ii. creation process

The index clustering table is created based on an index clustering index cluster. Each cluster key is recorded. The clustering key is different from the index key we use to do more. The index key points to a row of data and the clustering key points to an Oracle BLOCK. Run the following command to create an Oracle Index Cluster.

 
 

  
  
SQL> conn scott/tiger 
 
 

Connected.

 
 

  
  
SQL> desc dept 
 
 

Is the name empty? Type

 
 

  
  
DEPTNO NOT NULL NUMBER(2)  
  
  
DNAME VARCHAR2(14)  
  
  
LOC VARCHAR2(13)  
  
  
SQL> create cluster emp_dept_cluster  
  
  
2 ( deptno number(2) )  
  
  
3 size 1024  
  
  
4 /  
 
 

The cluster has been created.

This name can be defined by the user, not necessarily deptno. The data type must be the same NUMBER (2) as the data type to use this cluster ). The most critical parameter here is size. This option originally tells Oracle that we want to associate about 1024 bytes of data with each cluster key value 1024 is no problem for a common table ), oracle uses this database block to calculate the maximum number of clustering keys that can be put down on each block.

Assuming that the block size is 8 KB, Oracle will put a maximum of 7 cluster keys on each database block, that is, data of the corresponding departments 10, 20, 30, 40, 50, 60, and 70 will be placed in one block. Once the Department 80 is inserted, a new block will be used. The stored data is related to the insertion sequence.

The SIZE test controls the maximum number of clustering keys on each cluster. This is the biggest factor affecting cluster space utilization. If this SIZE is set too high, then the keys on each BLOCK will be less than the clustering keys that can be stored in a unit BLOCK ), we do not need to use more space. If it is set too low, it will lead to excessive data chain, a clustering key is not enough to store a piece of data), which is inconsistent with the original purpose of clustering, because clustering was originally designed to store all relevant data on one block.

Before placing data in a cluster, you must create an Oracle index for the cluster. You can create a table in the cluster now. However, because we want to create and fill the table at the same time, we must have a clustered index before we have data, so we should first create a clustered index.

The cluster index task obtains a clustering key value and returns the block address of the block containing the key. In fact, this is a primary key, and each clustering key value points to a block in the clustering itself. Therefore, when we request Department 10 Data, Oracle will read the cluster key, determine the corresponding block address, and then read the data. The cluster key index is created as follows:

 
 

  
  
SQL> create index emp_dept_cluster_idx  
  
  
2 on cluster emp_dept_cluster  
  
  
3 /  
 
 

The index has been created.

Now you can create a table:

 
 

  
  
SQL> conn segment_study/liugao 
 
 

Connected.

 
 

  
  
SQL> create table dept  
  
  
2 ( deptno number(2) primary key, 3 dname varchar2(14),  
  
  
4 loc varchar2(13)  
  
  
5 )  
  
  
6 cluster emp_dept_cluster(deptno)  
  
  
7 /  
 
 

The table has been created.

 
 

  
  
SQL> create table emp  
  
  
2 ( empno number primary key, 3 ename varchar2(10),
 4 job varchar2(9), 5 mgr number, 6 hiredate date,
 7 sal number, 8 comm number,  
  
  
9 deptno number(2) constraint emp_fk references
 dept(deptno)  
  
  
10 )  
  
  
11 cluster emp_dept_cluster(deptno)  
  
  
12 /  
 
 

The table has been created.

We can use the following SQL statement to view the creation:

 
 

  
  
SQL> select cluster_name, table_name  
  
  
2 from user_tables  
  
  
3 where cluster_name is not null  
  
  
4 order by 1;  
  
  
CLUSTER_NAME TABLE_NAME  
  
  
EMP_DEPT_CLUSTER DEPT  
  
  
EMP_DEPT_CLUSTER EMP  
 
 

Now, clustering, clustering index, and clustering Oracle index tables have been created.