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...

06-25-2001, 10:38 AM

tomate

The maximun of the co0lums lays at 255 columns

06-25-2001, 10:57 AM

omegamark

I think from 8 they have increased the number of columns to 1000.

06-25-2001, 11:56 AM

thg

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.

06-25-2001, 12:17 PM

omegamark

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..

06-25-2001, 12:24 PM

thg

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

06-25-2001, 12:29 PM

thg

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?

06-25-2001, 12:39 PM

sambavan

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

06-25-2001, 12:45 PM

omegamark

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??

06-26-2001, 01:43 AM

tomate

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.