Q
Problem solve Get help with specific problems with your technologies, process and projects.

# When do we need to reorganize a table? Part II

When do we need to reorganize a table? We are currently under version 5 and next month we are going for version 7.

For the first part of this answer, please see part I of this question

```NEAROFFPOSF and FAROFFPOSF are measures to gauge the organization of the
data in the underlying table. It assumes that the index in question is the
clustering index. The values indicate how many of the rows in the table are
ill-placed given that assumption. If the index is not the clustering index,
FAROFFPOSF and NEAROFFPOSF are not useful as indicators of data
organization.

The NEARINDREF and FARINDREF columns for a tablespace indicate the number of
rows that have been relocated either near (2 to 15 pages) or far away (16 or
more pages) from their original location. This relocation (also called
indirection) can occur as the result of updates to variable length rows
(that is, rows with VARCHAR columns, tables with EDITPROCs, or compressed
rows).

LEAFDIST helps determine the relative efficiency of each index. LEAFDIST
indicates the average number of pages between successive index leaf pages.
The more intervening pages, the less efficient the index will be.

Finally, you can use CLUSTERRATIOF to determine the overall condition of the
index as it corresponds to the physical order of the tablespace data. The
more clustered an index is, the greater its conformance to the order of the
rows as they are physically aligned in the tablespace. A cluster ratio of
100 percent indicates that the index and the tablespace ordering matches
exactly. As the cluster ratio diminishes, access that uses the index becomes
less efficient.

Also, please note that CLUSTERRATIOF for partitioned indexes can be found in
SYSIBM.SYSINDEXSTATS. This CLUSTERRATIOF is at the partition level and can
help to determine if only a subset of the partitions needs to be
reorganized.

The following table can be used as guide to determine how frequently
tablespaces and indexes should be reorganized. A + indicates that you should
REORG more frequently as the value in that column gets larger. A - (subtract
character) indicates that you should REORG more frequently as the value gets
smaller. As the number of + or - characters increases, the need to REORG
becomes more urgent. For example, as PERCDROP gets larger, the need to REORG
is very urgent, as indicated by five plus signs. For CLUSTERRATIOF, as the
value gets smaller, the need to REORG increases.

Table Reorganization indicators.

Column	Object	 Impact
----------- -----------  ----------
PERCDROP      Tablespace + + + + +
NEAROFFPOSF   Tablespace +
FAROFFPOSF    Tablespace + + + +
NEARINDREF    Index      +
FARINDREF     Index      + + + +
LEAFDIST      Index      + + +
CLUSTERRATIOF Index      - - - - -

(Note: the above information is culled from my book, DB2 Developer's Guide.
It can be ordered online at the following link:
http://www.amazon.com/exec/obidos/ASIN/0672318288/mullinassoci-20/002-578067
```
This was last published in November 2002

## Content

Find more PRO+ content and other member only offers, here.

#### Have a question for an expert?

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

#### Start the conversation

Send me notifications when other members comment.

• ### Tableau vs. Qlik Sense: Pros and cons of the rival analytics tools

Products from the two top data visualization vendors are starting to resemble each other as the need for strong visualizations ...

• ### AI functionality limited today but could be a game-changer

Limited AI capabilities could soon give way to technology that is truly transformative for enterprises, surpassing the overhyped ...

## SearchAWS

• ### HIPAA-eligible services nudge enterprises toward compliance

While certain services on AWS can help you ensure HIPAA compliance, don't view them as a complete replacement for proper security...

• ### Azure and AWS continue their race for public cloud dominance

In the battle between public cloud vendors, Microsoft hopes its large enterprise customer base and a focus on hybrid cloud can ...

• ### Direct Connect gateway grants your network global access

Large global enterprises will welcome AWS Direct Connect gateways, which enable multi-region connections to VPCs. However, some ...

## SearchContentManagement

• ### Content management in the cloud a main theme in 2018

The future of content management resides in the cloud and with AI, as several 2018 conferences will assure you.

• ### Six things to know about today's SharePoint implementations

As companies migrate their on-premises Microsoft SharePoint sites to the cloud, here are some things they should know about the ...

• ### Upgrades for the SharePoint Online portal

As more organizations migrate SharePoint sites to the cloud, Microsoft has increased at-a-glance dashboard data and analytics to ...

## SearchOracle

• ### How to use Oracle DBSAT to do a database security assessment

The Oracle Database Security Assessment Tool is free software that checks the security of databases. Here are the steps involved ...

• ### Oracle Blockchain Cloud Service: What it is and what it does

Oracle's cloud-based blockchain service uses Hyperledger Fabric to support distributed transaction ledgers for corporate users on...

• ### Using Oracle 12c Unified Auditing to set database audit policies

Oracle Database 12c's built-in Unified Auditing feature streamlines the database auditing process, including creation and ...

## SearchSAP

SAP paid \$2.4 billion to acquire lead-to-money vendor CallidusCloud, and analysts agree that the significant price may be worth ...

• ### SAP's Barry Padgett on future of SAP Ariba Network

In this Q&A, new SAP Ariba President Barry Padgett discusses the future of procurement and the experience he will bring to Ariba ...

• ### Avoiding SAP indirect access woes requires good faith

Some customers are concerned that SAP will hit them for indirect access licensing fees, but they can avoid trouble if they act in...