Row Chaining Questions
12663Mar 31 2013 — edited Apr 8 2013I am trying understand the mechanics of row chaining and how table design affects it.
Oracle Database ver 11.2.0.3
block_size = 32K for all tablespaces
I have made a copy of a large table from a datawarehouse and I am trying to understand the reason for row chaining in this copy. I created this copy by using
CREATE TABLE COPIED_TABLE AS SELECT * FROM BASE TABLE.
PCT_FREE = 10
I then ran the following command immediately after the table was created
ANALYZE TABLE COPIED_TABLE LIST CHAINED ROWS;
The CHAINED_ROWS table has approximately 20,000 entries for this table.
So I picked one specific row and tried to look at its data to see why the row chain. I couldn't really figure out from the data what the issue was.
So I used vsize to figure out an approximate length of that specific row. The row was 1029 Bytes as per vsize. I understand that vsize doesn't consider the column and row overhead.
As per gather stats, the average row length is 850 Bytes. So one block could have (32768 - (0.1 * 32768))/850 = 34.7 rows. 10% of the space in the block would be saved for updates since PCT_FREE is 10.
So my first question is -
The row that chained, why did it chain? Did it chain because Oracle put can only fit 34 complete rows and for the 35th row it put 0.7 of the row in one block and then 0.3 of that row in another block?
Also, this table I am working with has 279 columns. If a table has more than 255 columns there is intra-block chaining. (http://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:358341515662).
I do see opportunity to remove some columns in this case and move it to a different table. However, before I take that step I am looking to figure what difference will it make - will the logical IO be less or is difference not significant.
So my second question is - Can someone point me to a blog or content on this forum that can give some direction on how I can go about figuring out the difference.
My third question is - If I can help it, will it be better to keep all table to 255 columns or less?
Thank you