Skip to Main Content
  • Questions
  • Is row chaining implicit on "wide" tables?

Breadcrumb

Question and Answer

Chris Saxon

Thanks for the question, rob.

Asked: April 14, 2021 - 10:04 am UTC

Last updated: April 19, 2021 - 1:09 pm UTC

Version: 19.8

Viewed 100+ times

You Asked

Hi Tom,

in our ETL tool we have a process by which a flat file is read and transformed. A table is created with columns to hold the flat file content and columns for the functions that are applied sequentially per row. the file content is loaded via bulk copy. After the bulk copy a series of update statements are applied into the function columns, again applied sequentially because updates are dependent on previous updates in many cases. All these tables have PCTFREE = 10.

The total count of columns is over 255 in all these examples and i was expecting all rows to be chained because of this. However in one case i have ~5% chaining, in others i have 99% chaining.

Notes
These are edge cases..normally files are not so wide. We are looking at separating the tables.
We cannot alter the order of the columns
We cannot drop any columns

Examples

Flat File Col Count Function Col Count % Chain
261 116 7.5%
252 73 99%

So some questions:
1. Can the diff in chaining be explained by which columns are being referred to in the update statements? The whole splitting of the table (in memory terms) from the right-hand side perhaps
2. Can PCTFREE settings have any positive impact on chaining with wide tables?
3. Are there any other tactics you can suggest?

and we said...

In later versions, Oracle Database has gotten better at chaining within a block if there's enough space. I've discussed this in detail at:

https://asktom.oracle.com/pls/apex/asktom.search?tag=table-255-columns-row-chaining

Chaining within a block often won't appear in row chaining metrics, which could go some way to explaining the big difference.

Can the diff in chaining be explained by which columns are being referred to in the update statements?

That may also play a part here - which columns you change, how often, and how much space is available in the block at the time all play a role.

Can PCTFREE settings have any positive impact on chaining with wide tables?

It could have a positive impact, it could have a negative impact...

Are there any other tactics you can suggest?

To do what specifically?

Unless you have a performance issue which you've found is primarily caused by row chaining, I suspect there are other optimizations you could make to the system which would have bigger benefits.

More to Explore

Administration

Need more information on Administration? Check out the Administrators guide for the Oracle Database