Demantra Performance Clustering Factor Out of Order Ratio TABLE_REORG CHECK_REORG (Doc ID 1594372.1)
- by user702295
Hello! There is a new document available:
Demantra Performance Clustering Factor Out of Order Ratio TABLE_REORG CHECK_REORG (Doc ID 1594372.1)
Demantra Performance Clustering Factor Out of Order Ratio TABLE_REORG CHECK_REORG
The table reorganization can be setup to automatically run in version 7.3.1.5.
In version 12.2.2 we run the TABLE_REORG.CHECK_REORG function at every appserver restart.
If the function recommends a reorg then we strongly encourage to reorg the database object.
This is documented in the official docs.
In versions 7.3.1.3 and 7.3.1.4, the TABLE_REORG module exists and can be used.
It has two main functions that are documented in the Implementation Guide Supplement,
Release 7.3, Part No. E26760-03, chapter 4.
In short, if you are using version 7.3.1.3 or higher, you can check for the need to run a reorg by doing the following 2 steps:
1. Run TABLE_REORG.CHECK_REORG('T');
2. Check the table LOG_TABLE_REORG for recommendations
If you are on a version before 7.3.1.3, you will need to follow the instructions below to determine if you need to do a manual reorg.
How to determine if a table reorg is needed
1. It is strongly encouraged by DEV that You gather statistics on the required table. The prefered percentage for the gather is 100%.
2. Run the following SQL to evaluate how table reorg might affect Primary Key (PK) based access:
SELECT ui.index_name,trunc((ut.num_rows/ui.clustering_factor)/(ut.num_rows/ut.blocks),2)
FROM user_indexes ui, user_tables ut, user_constraints uc
WHERE ui.table_name=ut.table_name
AND ut.table_name=uc.table_name
AND ui.index_name=uc.index_name
AND UC.CONSTRAINT_TYPE='P'
AND ut.table_name=upper('&enter_table_name');
3. Based on the result:
VALUE ABOVE 0.75 - DOES NOT REQUIRE REORG
VALUE BETWEEN 0.5 AND 0.75 - REORG IS RECOMMENDED
VALUE LOWER THAN 0.5 - IT IS HIGHLY RECOMMENDED TO REORG
