Clever Geek Handbook

Storing NULLABLE columns at the end of table-Oracle

This link indicates that all columns with a null value should be added to the end of the database table to save space.

My question is, how can I ensure that the column is always added to the end of the database table. I am assuming that when I do CREATE TABLE ..

, I have to add all NULLABLE columns at the end. But how does it work when I use it ALTER TABLE X ADD COLUMN ..

?

+3


source to share


3 answers


Oracle, unlike some DBMSs, does not suggest adding a column to a specific position in a table; new columns are always added at the end. So there is nothing special you can do about it. All you have is:



  • Usually, if your table has nullable columns, you won't need to add new non-null columns (especially since you will need to update all existing records to indicate some sort of filler value).
  • It is not very dangerous to have columns with a null value in the middle; it all means that length (0) must be preserved, if not otherwise. If you really have a huge number of rows that have very little non-zero data, but a lot of trailing zeros, this really won't make a significant difference.
+6


source


The documentation doesn't say that all nullable trailing columns don't take up space. It says, "For trailing null columns , Oracle Database doesn't even store the column length." - my accent. This means that for every column without any data in it, Oracle does not store the length of the column.

In the previous sentence, it says, "To save space, zero in a column only stores the length of the column (zero)," meaning Oracle only stores one byte per row for each zero value.



This way you will not save any space as soon as you add any data to the column. Also, even if your table is about 1m long, you only save 1MB, hardly anything. It is not worth worrying about.

+4


source


It doesn't work if you are using ALTER TABLE. If you need to rebuild the table after changing it, you can use create table as select (CTAS) to copy the data to another table (having the columns in the correct order), delete the original table, and rename the new table with the name of the original table afterwards. Of course, deleting the original table will invalidate all other objects depending on your table, so this probably isn't a viable option.

+1


source






More articles:


All Articles