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

Performance impact of secondary extents, tables and indexes

Compared to other performance issues, what is the performance impact of secondary extents, tables indexes? Is the impression correct that Load/Reorg leaves the free space (PCTFREE and FREE PAGES) unused and leave unused space at the end of the Primary Space (PQTY) free?

When inserts have filled near free space, they insert to far free space then to the end of PQTY. Then, they have to allocate SQTY and insert to it.

Is the impression correct that inserts to near and far free space are roughly the same for tables and indexes? But inserts to the end of Index PQTY and SQTY degrade performance more than inserts to PQTY and SQTY of tables? (Indexes are on a random dumb key, many millions of rows.)
Well, let me provide some general guidance. Secondary extents are probably at the bottom in terms of performance impact. There are many more issues that have greater importance such as proper indexing, efficient SQL, memory usage (buffer pools), database organization, workload balancing and so on.

That being said, extents will negatively impact performance. The chain from the primary to the extent must be read to get to the extent -- and that is additional work that does not need to be done when extents do not exist. But don't go overboard worrying about extents. The impact is small and I wouldn't worry about them until there were dozens (as opposed to just a few).

In terms of INSERTs, when a secondary extent must be created for the INSERT, performance will suffer.

Editor's note: Do you agree with this expert's response? If you have more to share, post it in one of our .VO7aaqqaAFk.0@/search390>discussion forums.

Dig Deeper on IBM DB2 management

Have a question for an expert?

Please add a title for your question

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.

Please create a username to comment.