Shulou(Shulou.com)06/01 Report--

How to understand Oracle clustering Cluster, many novices are not very clear about this, in order to help you solve this problem, the following editor will explain for you in detail, people with this need can come to learn, I hope you can gain something.

There are three basic types of Oracle data tables: heap table Heap Table, index organization table IOT and clustering table Cluster. In our daily life, the most commonly used and most adaptable data table is the heap table Heap Table. Generally, in the absence of special performance defects and feature requirements, stacking tables is our first option.

IOT is a type of data table that merges data into an index structure. In the previous article, the author introduced the structure, characteristics and adaptation scenarios of IOT in detail, as well as the concepts of segment overflow Segment Overflow, logical Rowid and Secondary Index.

Let's introduce clustering.

1. Overview of clustering Cluster

It should be said that of the three types of data tables, the least commonly used structure should be clustering. Clustering is also a type of structure that is most different from the other two data tables, and the biggest difference is that clustering can exist alone.

In the Oracle storage structure, we must follow two concepts, namely, object and segment Segment. We can create a lot of objects, such as data tables, indexes, and views, but not every object takes up storage space. Oracle space allocation is based on logical tablespaces, segment objects, partitions, and blocks. Only objects such as data tables and indexes can really use space and allocate Segment.

Although there are differences between heap tables and index organization tables, they are similar in nature. In the heap table, the index and the data table are separate segment structures, and the index segment and the data table segment are consistent. IOT implements the integration of index and data table segments. All the contents of the data table are saved on the leaf node of the IOT index tree according to the primary key order. Because of the particularity of the content of the data table, such as when the field is too large, it is realized through the overflow section.

Cluster is completely different. Cluster is a separate segment structure, or the author understands it as a separate segment space container. In the absence of data tables and indexes, Cluster segments can exist alone. According to certain rules, such as the join key (Join Key), multiple data tables can be saved in the same segment. And quickly retrieve the connection according to a certain scene.

Why do we use Cluster data sheets? The most common explanation is to reduce the number of IO used in association retrieval. In the traditional data table structure, two tables are connected, and the data blocks should be retrieved at least twice. Cluster procedures, because they are all stored together (note: same Segment).

When Cluster is used, there are two types to choose, which are B-tree Cluster and hash Hash Cluster. Both of them have the same structure and slight differences.

2. Introduction of experimental environment.

We chose Oracle 11gR2 to carry out the experiment.

SQL > select * from v$version

BANNER

Oracle Database 11g Enterprise Edition Release 11.2.0.3.0-Production

PL/SQL Release 11.2.0.3.0-Production

CORE 11.2.0.3.0 Production

TNS for Linux: Version 11.2.0.3.0-Production

NLSRTL Version 11.2.0.3.0-Production

Create special users to carry out experiments.

SQL > create user test identified by test default tablespace users

User created

SQL > grant resource, connect to test

Grant succeeded

SQL > grant create cluster to test

Grant succeeded

SQL > grant select any table to test

Grant succeeded

SQL > grant select any dictionary to test

Grant succeeded

3. B-tree Cluster experiment

Through a series of experiments, we explore the characteristics and use of discovering Cluster data tables. Oracle Cluster is not created with any datasheet object, but can be created through the SQL statement create cluster.

SQL > create cluster emp_dept (deptno number) size 600

Cluster created

SQL > select cluster_name, tablespace_name, cluster_type, key_size from user_clusters

CLUSTER_NAME TABLESPACE_NAME CLUSTER_TYPE KEY_SIZE

-

EMP_DEPT USERS INDEX 600

Note two issues, one is the size 600th that we specified during the creation of the cluster. This is used to specify the approximate size of the cluster key, after which the space reservation can be achieved. If the setting of this value is unreasonable, it is easy to cause confusion in the Cluster structure.

Another problem is that since tablespace_name,Cluster objects include tablespace information, they must take up space and appear in the form of segment.

SQL > select SEGMENT_TYPE, extents, HEADER_FILE, HEADER_BLOCK, BYTES, BLOCKS from dba_segments where wner='TEST' and segment_name='EMP_DEPT'

SEGMENT_TYPE EXTENTS HEADER_FILE HEADER_BLOCK BYTES BLOCKS

--

CLUSTER 1 4 522 65536 8

Note: we did not create a data table or index at this time, but cluster segment already exists. Let's create a data table based on cluster emp_dept.

SQL > create table emp (empno number, empname varchar2 (10), deptno number) cluster emp_dept (deptno)

Table created

SQL > select SEGMENT_TYPE, extents, HEADER_FILE, HEADER_BLOCK, BYTES, BLOCKS from dba_segments where wner='TEST' and segment_name='EMP_DEPT'

SEGMENT_TYPE EXTENTS HEADER_FILE HEADER_BLOCK BYTES BLOCKS

--

CLUSTER 1 4 522 65536 8

SQL > select SEGMENT_TYPE, extents, HEADER_FILE, HEADER_BLOCK, BYTES, BLOCKS from dba_segments where wner='TEST' and segment_name='EMP'

SEGMENT_TYPE EXTENTS HEADER_FILE HEADER_BLOCK BYTES BLOCKS

--

When you create a data table based on cluster, you need to specify which field is the key key of cluster. From the segment structure data dictionary, we can not see the segment information of the data table, only the segment information of cluster. From dba_tables, we do see the successful creation of the data table.

SQL > select segment_created from dba_tables where wner='TEST' and table_name='EMP'

SEGMENT_CREATED

-

YES

At this point, we try to add data to the data table emp, which is prohibited.

SQL > insert into emp select empno, ename, deptno from scott.emp

Insert into emp select empno, ename, deptno from scott.emp

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

Here, we realize that using cluster also requires the creation of a special cluster index. To test the connection, create a second data table.

SQL > create table dept (deptno number primary key, deptname varchar2 (10)) cluster emp_dept (deptno)

Table created

SQL > select segment_created from dba_tables where wner='TEST' and table_name='DEPT'

SEGMENT_CREATED

-

YES

At the same time, the required index structure is created.

SQL > create index idx_emp_dept on cluster emp_dept

Index created

SQL > select SEGMENT_TYPE, extents, HEADER_FILE, HEADER_BLOCK, BYTES, BLOCKS from dba_segments where wner='TEST' and segment_name in ('EMP_DEPT','IDX_EMP_DEPT')

SEGMENT_TYPE EXTENTS HEADER_FILE HEADER_BLOCK BYTES BLOCKS

--

INDEX 1 4 538 65536 8

CLUSTER 1 4 522 65536 8

SQL > select SEGMENT_TYPE, extents, HEADER_FILE, HEADER_BLOCK, BYTES, BLOCKS from dba_segments where wner='TEST' and segment_name='EMP'

SEGMENT_TYPE EXTENTS HEADER_FILE HEADER_BLOCK BYTES BLOCKS

--

SQL > select SEGMENT_TYPE, extents, HEADER_FILE, HEADER_BLOCK, BYTES, BLOCKS from dba_segments where wner='TEST' and segment_name='DEPT'

SEGMENT_TYPE EXTENTS HEADER_FILE HEADER_BLOCK BYTES BLOCKS

--

-- Index metadata information

SQL > select index_type, table_name, table_type, UNIQUENESS from dba_indexes where wner='TEST' and index_name='IDX_EMP_DEPT'

INDEX_TYPE TABLE_NAME TABLE_TYPE UNIQUENESS

-

CLUSTER EMP_DEPT CLUSTER UNIQUE

We created two data tables and an index, and only cluster and the index became segment objects. It is obvious that both data tables are included in the cluster segment structure.

The process of creating an index is different from a normal index. Instead of indexing the data table, we indexed the cluster object. From the dba_indexes view, you can see the differences and differences.

Let's fill in the data.

SQL > insert into dept select deptno, dname from scott.dept

4 rows inserted

SQL > insert into emp select empno, ename, deptno from scott.emp

14 rows inserted

SQL > commit

Commit complete

At this point, the segment structure still maintains the shape of an cluster and an index.

SQL > select SEGMENT_TYPE, extents, HEADER_FILE, HEADER_BLOCK, BYTES, BLOCKS from dba_segments where wner='TEST' and segment_name='EMP'

SEGMENT_TYPE EXTENTS HEADER_FILE HEADER_BLOCK BYTES BLOCKS

--

SQL > select SEGMENT_TYPE, extents, HEADER_FILE, HEADER_BLOCK, BYTES, BLOCKS from dba_segments where wner='TEST' and segment_name='DEPT'

SEGMENT_TYPE EXTENTS HEADER_FILE HEADER_BLOCK BYTES BLOCKS

--

SQL > select SEGMENT_TYPE, extents, HEADER_FILE, HEADER_BLOCK, BYTES, BLOCKS from dba_segments where wner='TEST' and segment_name in ('EMP_DEPT','IDX_EMP_DEPT')

SEGMENT_TYPE EXTENTS HEADER_FILE HEADER_BLOCK BYTES BLOCKS

--

CLUSTER 1 4 522 65536 8

INDEX 1 4 538 65536 8

The biggest benefit of using cluster is the connection. Let's take a look at the execution plan in the case of the connection.

SQL > explain plan for select * from emp a, dept b where a.deptno=b.deptno

Explained

SQL > select * from table (dbms_xplan.display)

PLAN_TABLE_OUTPUT

Plan hash value: 1709228156

| | Id | Operation | Name | Rows | Bytes | Cost (% CPU) | Time |

| | 0 | SELECT STATEMENT | | 14 | 350 | 6 (0) | 00:0 |

| | 1 | NESTED LOOPS | | 14 | 350 | 6 (0) | 00:0 |

| | 2 | TABLE ACCESS FULL | DEPT | 4 | 48 | 3 (0) | 00:0 |

| | 3 | TABLE ACCESS CLUSTER | EMP | 4 | 52 | 1 (0) | 00:0 |

| | * 4 | INDEX UNIQUE SCAN | IDX_EMP_DEPT | 1 | | 0 (0) | 00:0 |

Predicate Information (identified by operation id):

4-access ("A". "DEPTNO" = "B". "DEPTNO")

16 rows selected

Cluster is essentially a container, and if we need to delete cluster, we need to delete all the data table objects before we can execute it. Or use the including tables clause.

SQL > drop cluster emp_dept

Drop cluster emp_dept

ORA-00951: cluster is not empty

SQL > drop cluster emp_dept including tables

Cluster dropped

This part introduces B-tree clustering, which is also a simple form of clustering.

Is it helpful for you to read the above content? If you want to know more about the relevant knowledge or read more related articles, please follow the industry information channel, thank you for your support.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.