Oracle Learning----Cluster factor (clustering Factor)

1. Clustering factor algorithm:Locate the block number that corresponds to the record by Dbms_rowid.rowid_block_number (ROWID). rowID is recorded in the index, so Oracle can determine whether the record is in the same block based on the ROWID in the index. For example, for instance, there are a,b,c,d,e five records in the index, first compare whether a, B is in the same block, if not the same block so clustering Factor +1, and then continue to compare b,c same, if B,c is not the same block, So clustering factor+1, so keep going until you compare all the records. According to the algorithm, we can know that the value of clustering factor is between block number and table row number. If the clustering factor is close to the block number, the table storage and index storage ordering is close, that is, the table records are very orderly, so when the index range scan can be, read a small number of data block can get what we want, the cost is relatively small. If the clustering factor is close to the table record count, the storage and index sorting of the table is very different, and when you do the index range scan, additional blocks are read because the table records are fragmented and expensive. 2. What is ROWID?

Sql> select rowID from T;

ROWID
------------------
Aaasez AAE Aaaaff AAA

Sql> Select Length (ROWID) from T;

LENGTH (ROWID)
-------------
18

rowID altogether 18 people

The first 6 bits represent the data object number

After which the next 3 digits represent datafile number

After which the next 6 digits represent datablock number

The last 3 bits represent the row number

3. How to check out the corresponding block number according to the ROWID information?

Sql> Select
2 rowID,
3 Dbms_rowid.rowid_relative_fno (ROWID) rel_fno,--return rowid corresponding file number
4 Dbms_rowid.rowid_block_number (ROWID) Blockno,--return the block number where the ROWID is located
5 Dbms_rowid.rowid_row_number (ROWID) rowno--returns the relative position of the row data in the block
6 from T where Rownum=1;

ROWID rel_fno Blockno ROWNO
------------------------  ------------- ------------- -------
AAASEZAAEAAAAFFAAA 4 325 0

4. Can I change the cluster factor? Does rebuilding an index change the cluster factor?

No, the index is created based on the value of the created column, only create table. ORDER by index column to change the cluster factor.

5. How does cluster factor affect performance?

Suppose a table has 10 million rows and only needs to return 1w rows of data and go through the index.

SELECT * FROM t where rowid<=10000;

Go to index to return 10,000 rowID first, back to the table to 10,000 times

SELECT * FROM Test where rowid=xxxx Assuming that 10,000 rowid are in 10 blocks, then back to the table, the physical back table is only 10 timesTime consuming 10*10msAssuming that 10,000 rowid are in 10,000 blocks, then back to the table, the physical back table is only 10,000 timesTime consuming 100000*10ms6. Under what circumstances does cluster factor not affect performance? 1. Do not return to the table2. The table is in buffer cache3. Return less data, primary key scan or go unique index is not related to cluster factor7. Cluster factor affects that index scan? Index Range ScanIndex Full ScanIndex Skip Scanindex fast full scanis not related to ROWID, so cluster factor does not affect8. Reversing the build index can result in an increase in cluster factors.

Sql> CREATE TABLE Test as SELECT * from Dba_objects;

The table is created.

Sql> CREATE index ObjID on test (object_id);

The index has been created.

Sql> Select Index_name,clustering_factor from user_indexes where table_name= ' TEST ';

Index_name Clustering_factor
------------------------------------------------------------ -----------------
OBJID 1085

Sql> Select COUNT (Distinct dbms_rowid.rowid_block_number (ROWID)) from test;

COUNT (Distinctdbms_rowid. Rowid_block_number (ROWID))
---------------------------------------------------
1027

Sql> Select COUNT (*) from test;

COUNT (*)
----------
72063

Invert to build an index

sql> ALTER index OBJID rebuild reverse;

The index has changed.

Sql> Select Index_name,clustering_factor from user_indexes where table_name= ' TEST ';

Index_name Clustering_factor
------------------------------------------------------------ -----------------
OBJID 72061

Sql> Select COUNT (*) from test;

COUNT (*)
----------
72063

Sql> Select COUNT (Distinct dbms_rowid.rowid_block_number (ROWID)) from test;

COUNT (Distinctdbms_rowid. Rowid_block_number (ROWID))
---------------------------------------------------
1027

9.sql algorithm to realize cluster factor

With T as
(SELECT OWNER column_name,
Lead (owner, 1, owner) over (ORDER by OWNER) Next_column_name,
ROWID Rowid_num,
Dbms_rowid. Rowid_block_number (ROWID) block_id,
Lead (ROWID)-Over (ORDER by OWNER) Next_rowid_num,
Lead (Dbms_rowid. Rowid_block_number (ROWID),
1,
Dbms_rowid. Rowid_block_number (ROWID)) over (ORDER by OWNER) next_block_id
From TEST A
WHERE OWNER is not NULL
ORDER by OWNER)
SELECT COUNT (*) Number of records, COUNT (DISTINCT block_id) Block_id_sum,
SUM (case
When t.block_id = t.next_block_id Then
0
ELSE
1
END) + 1 cluster factor,
SUM (case
When t.column_name = T.next_column_name and
t.block_id <> t.next_block_id Then
1
ELSE
0
END) value is different than _ block
From T;

Oracle Learning----cluster factor (clustering Factor)