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:
https://www.amazon.com/exec/obidos/ASIN/0672318288/mullinassoci-20/002-578067
```

#### Start the conversation

Send me notifications when other members comment.

• ### Tableau 2020.1 highlighted by Viz Animations, Dynamic Parameters

Dynamic Parameters and Viz Animations highlight Tableau's first 2020 platform update and comes nearly four months after ...

• ### Embedded BI software creates common ground for diverse analytics

Learn how embedding separate business intelligence capabilities into one application empowers users to drill down, access and ...

• ### Embedded BI and analytics apps speed workflows, insight access

Embedded BI is fast becoming a focal point for analytics uses as data analysts, developers and business users enjoy faster ...

## SearchAWS

• ### Amazon grocery strategy could shake up food retail industry

Amazon is a small player in the grocery store industry but one with increasing influence. It already has considerable technical ...

• ### Amazon's environmental impact delivers climate change concerns

Consumers are accustomed to free and fast delivery from Amazon and other e-tailers, but the convenience of online shopping comes ...

• ### New themed Amazon pop-up stores built on consumer data

In its never-ending quest to establish unique physical locations, Amazon has plans to launch a chain of themed pop-up stores that...

## SearchContentManagement

With new Google Drive integration, Adobe embeds free features for commenting and annotating PDFs inside Google Docs. Acrobat ...

• ### 4 benefits of business process automation

Companies are automating business processes to improve workflows and use technology effectively. Some benefits of BPA include ...

• ### Test yourself on the differences between SharePoint and OneDrive

Businesses use Microsoft SharePoint and OneDrive capabilities for a number of reasons. Test your knowledge on the differences ...

## SearchOracle

• ### Oracle Autonomous Database shifts IT focus to strategic planning

This handbook looks at what Oracle Autonomous Database offers to Oracle users and issues that organizations should consider ...

• ### Oracle Autonomous Database features free DBAs from routine tasks

Oracle Autonomous Database can automate routine administrative and operational tasks for DBAs and improve productivity, but ...

• ### Oracle co-CEO Mark Hurd dead at 62, succession plan looms

Oracle co-CEO Mark Hurd's abrupt death at 62 has put the software giant in the position of naming his replacement, and the ...

## SearchSAP

• ### Understand SAP ECC vs. HANA vs. S/4HANA vs. R/3

SAP ECC, HANA, S/4HANA and R/3 are all valuable tools in the IT world, but it's easy to get them confused. Learn about their ...

• ### 5 tips for a successful S/4HANA Cloud implementation

Moving to S/4HANA public cloud can help you save on IT maintenance and simplify real-time analysis. Here's a look at what you can...

• ### Take care of data before SAP S/4HANA migration

In this Q&A, Rajesh Rengarethinam of ERP security vendor Appsian discusses why reviewing data security and business processes are...

## SearchSQLServer

• ### SQL Server database design best practices and tips for DBAs

Good database design is a must to meet processing needs in SQL Server systems. In a webinar, consultant Koen Verbeeck offered ...

• ### SQL Server in Azure database choices and what they offer users

SQL Server databases can be moved to the Azure cloud in several different ways. Here's what you'll get from each of the options ...

• ### Using a LEFT OUTER JOIN vs. RIGHT OUTER JOIN in SQL

In this book excerpt, you'll learn LEFT OUTER JOIN vs. RIGHT OUTER JOIN techniques and find various examples for creating SQL ...

Close