Oracle Clustering Factor

When we query the index state, we usually use the User_indexes table, which has a column (Clustering_factor clustering factor), here simply describes the meaning of the next cluster factor, you know that the data in the data table is a disordered existence in the library, When we are retrieving the data, looking up is very resource-intensive, so we need to create indexes for tables, the role of the index is to put the data in the table in a certain order to save, so there is a problem, some tables in the data and index want to arrange the order is very similar, In other tables, the data and index want to arrange the order is far apart, the role of the cluster factor is used to mark this, the smaller the clustering factor, the higher the similarity, the greater the clustering factor, the lower the similarity.

We know what the clustering factor is, but we still don't know what the similarity of the marker data is, and we continue to discuss:

Oracle stores data in a way that is not stored in the data in the order of the blocks, because the data stored in it is often DML or DDL, and after the data is deleted, the data block that originally held the data becomes empty, and Oracle, in order to save storage space, When the database has new data to insert again, will give priority to the use of those empty blocks, only when the space is not enough to use, will go to high water level above the new block, this situation will lead to, a table of data, is not stored in the adjacent data block, so the clustering factor becomes very large, When this is done logically, the number of IO is increased and the reading speed is affected.

Now that the clustering factor is related to the reading speed of the table, can we manually control the size of the cluster factor?

The answer is yes, but there are always pros and cons.

1 We can refactor the table (ALTER TABLE EMP move);

2) or rebuilding the table in the Order of the index (CREATE table EMP_BK as SELECT * from emp sequence by empno);

3 can open enough new block above the high water level, put all the data of a table in here, to achieve the goal of reducing the clustering factor, but the result is a waste of space, at the same time because the high watermark is the end of the whole table scan, artificially overstating the water level, easy to cause the whole table sweep rate of reduction, so need careful consideration.

4 Create partitioned tables to reduce access to data blocks

For example, in the EMP table, employees are divided into 3 departments (deptno:10,20,30), and we partition by department number:

CREATE TABLE EMP

(

Empno Number (4) is not NULL,

Ename VARCHAR2 (10),

Job VARCHAR2 (9),

Mgr Number (4),

HireDate DATE,

Sal Number (7,2),

Comm Number (7,2),

Deptno Number (2)

)

Partition by list (DEPTNO)

(Partition dept_10 values (10),

Partition dept_20 values (20),

Partition dept_30 values (30),

Partition Dept_other values (default));

When we execute the SELECT * from EMP where deptno=10; , Oracle will remove all other partitions that are not part of the 10 partition and access only 10 sections.

View 10 Sections: SELECT * from EMP partition (DEPT_10);