All,
I remember, if total number columns reach to N in a table, oracle would not store the record in the same data block. I don't remember the value N. Can any one answer for this?.
Regards
GT
Printable View
All,
I remember, if total number columns reach to N in a table, oracle would not store the record in the same data block. I don't remember the value N. Can any one answer for this?.
Regards
GT
1000 max.Quote:
Originally Posted by pranavgovind
Anything over 255 is chained.
David is right its is 255.Over 255 the rows are chained
But in this case chained doesn't mean that the data are not all stored in the same block
Quote:
Originally Posted by slimdave
It is not chained. The data of the columns whose number is > 255 is still stored in the same block provided the block has space.
The real problem is "table fetch continued row " event appears in the statspack report.
Oracle has to do 2 IOs (logical) for the data appears after the 255th column even when all the columns data are stored in one block and the row is fetched via index look up.
However, full table scan will not suffer from 2 IOs.
You can have 1000 columns in a table. In my opinion, restrict the number of columns less than 255 and the performance will be good.
Tamil
Tamil
Will the block size 2k ~ 32k has more ~ less impact with the number of I/O's for columns > 255?Quote:
Originally Posted by tamilselvan
The block size does not matter.Quote:
Originally Posted by dbasan
Tamil
It's a real learning experience this, ain't it? :D
As long as an object has proper storage attributes defined and its row fits within a block, there shouldn't be any issues. Row migration and Row chaining caused either by a row update or inability to accomodate a row within a block can cause waits. You can find details on row migration, row chaining and the specific conditions that cause the "table fetch continued row" wait event, at these links:
http://www.akadia.com/services/ora_chained_rows.html
or
http://www.oracle.com/technology/ora...o62asktom.html
http://www.dbaxchange.com
Here, we are not talking about row migration or row chaining, but the effect of having more than 255 columns in a table.Quote:
As long as an object has proper storage attributes defined and its row fits within a block, there shouldn't be any issues.
What I am saying is Oracle cannot read more than 255 columns in a single IO even when all 1000 columns of a row stored in a single block.
Tamil
Yes, I do agree with you there........
"table fetch continued row" wait events do start appearing due to mutiple fetches (physical or logical IO) when a table has more than 255 columns and 256 columns within the table column list has atleast 1 byte of data in them.....
http://www.dbaxchange.com
Thank you so much for everybody's answers. TamilSelvan, thanks for your exact answer for my question...
Tamil,
In you answer, you are saying, "row is fetched via index look up".
What does it mean?.
Quote:
Originally Posted by tamilselvan
It means fetching a row data from the table's block using an index lookup.Quote:
In you answer, you are saying, "row is fetched via index look up".
What does it mean?.
OR
Oracle reads the index leaf block based on the index key submitted to get the rowid, and then using the rowid it goes and gets the row data from the table's block.
Tamil