Does oracle limit the number of columns we can have in a table?
DBAsupport.com Forums - Powered by vBulletin
Page 1 of 2 12 LastLast
Results 1 to 10 of 12

Thread: Does oracle limit the number of columns we can have in a table?

  1. #1
    Join Date
    Jan 2001
    Posts
    59



    Does oracle limit the number of columns we can have in a table? What about the number of rows?

    I have a table that will need more than 120 columns. Will it cause any trouble when I inserting new data to the table. I am worry about the 'INSERT..." statement's size...

  2. #2
    Join Date
    Mar 2001
    Posts
    188
    The maximun of the co0lums lays at 255 columns
    Best Regards
    Thomas Schmidt

    Thomas_Schmidt@eplus-online.de
    If you have no aims, you will never reach a goal !!!

  3. #3
    Join Date
    Sep 2000
    Posts
    384
    I think from 8 they have increased the number of columns to 1000.
    Radhakrishnan.M

  4. #4
    Join Date
    Jan 2001
    Posts
    59

    The reason I am asking this question is because of performance issues. Does anyone know the maximum pagesize of oracle database? If I have lots of columns and the size for each row is more than the max pagesize, then each row will uses several pages to store the data. Will this cause any performance problem?


    Thanks.

  5. #5
    Join Date
    Sep 2000
    Posts
    384
    It all depends on how many rows will there for this table and the average row lenght .It is always good to have a your average row lenght less than your db_block_size.
    If your average rowlenght is more than the db_block_size but very few number of rows Then that's not a problem.But if the number of rows is more then ou will have performance issues.Even if you have 140 rows and if the average rowlenght is less than your db_block_size you can stick to your specifiactions..
    Radhakrishnan.M

  6. #6
    Join Date
    Jan 2001
    Posts
    59

    can you tell me how I can find out the size of my current DB_BLOCK_SIZE?

  7. #7
    Join Date
    Jan 2001
    Posts
    59
    by the way, a null INT column will consume the same amount of space as a non-null INT column, only varchar can have variable length, right?


  8. #8
    Join Date
    Oct 2000
    Location
    Saskatoon, SK, Canada
    Posts
    3,925
    What is your env? NT/Unix, DB version? Check v$parameter and that would show your DB size or check your $ORACLE_BASE/admin/SID/pfile/initSID.ora file and there you would be able to see the db_block_size.

    Sam
    Thanx
    Sam



    Life is a journey, not a destination!


  9. #9
    Join Date
    Sep 2000
    Posts
    384
    login into the database as sys or system
    select substr(name,1,40),substr(value,1,40) from v$parameter where name='db_block_size';

    Null columns do not occupy any bytes irrespective of the datatypes??
    Radhakrishnan.M

  10. #10
    Join Date
    Mar 2001
    Posts
    188

    Unhappy

    can you tell me how I can find out the size of my current DB_BLOCK_SIZE

    it is in the init.ora but don't touch this value. You can set this value only at the installation after that it is a not change value.
    Best Regards
    Thomas Schmidt

    Thomas_Schmidt@eplus-online.de
    If you have no aims, you will never reach a goal !!!

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