Maximum number of columns in table
DBAsupport.com Forums - Powered by vBulletin
Page 1 of 2 12 LastLast
Results 1 to 10 of 15

Thread: Maximum number of columns in table

  1. #1
    Join Date
    Dec 2005
    Posts
    195

    Maximum number of columns in table

    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

  2. #2
    Join Date
    Mar 2004
    Location
    DC,USA
    Posts
    650
    Quote Originally Posted by pranavgovind
    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.
    "What is past is PROLOGUE"

  3. #3
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    Anything over 255 is chained.
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

  4. #4
    Join Date
    Jan 2001
    Posts
    2,828
    David is right its is 255.Over 255 the rows are chained

  5. #5
    Join Date
    Mar 2002
    Posts
    534
    But in this case chained doesn't mean that the data are not all stored in the same block

  6. #6
    Join Date
    May 2000
    Location
    ATLANTA, GA, USA
    Posts
    3,136
    Quote Originally Posted by slimdave
    Anything over 255 is chained.

    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

  7. #7
    Join Date
    Mar 2004
    Location
    DC,USA
    Posts
    650
    Quote Originally Posted by tamilselvan
    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.

    Tamil
    Will the block size 2k ~ 32k has more ~ less impact with the number of I/O's for columns > 255?
    "What is past is PROLOGUE"

  8. #8
    Join Date
    May 2000
    Location
    ATLANTA, GA, USA
    Posts
    3,136
    Quote Originally Posted by dbasan
    Will the block size 2k ~ 32k has more ~ less impact with the number of I/O's for columns > 255?
    The block size does not matter.

    Tamil

  9. #9
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    It's a real learning experience this, ain't it?
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

  10. #10
    Join Date
    Nov 2005
    Posts
    32
    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width