Position of NOT NULL cols in table ...

Is still valid to say they should be at start of table definition? My developer has scripts with NOT NULLS at start and end of large tables and columns of 1000 and 500 chars in between.
What are the storage implications?

Also - precision with NUMBER columns. What's the implications of using NUMBER over NUMBER(8)?

Quote
---------
Also - precision with NUMBER columns. What's the implications of using NUMBER over NUMBER(8)?
----------

there is no difference between number and number(8) as far as storage is concerned .

by mentioning number(8), u are just restricting the the maximum number of digits for this column. adding a constraint .
oracle will shrink the storage usage based on the value itself .

u can check the number of bytes used using the following command .

SQL> select vsize ( 9999) from dual ;

VSIZE(9999)
-----------
3

SQL> select vsize ( -99 ) from dual ;

VSIZE(-99)
----------
3

SQL> select vsize ( 9.9 ) from dual ;

VSIZE(9.9)
----------
3

SQL> select vsize ( 99 ) from dual ;

VSIZE(99)
----------
2

for every two digits it takes 1 byte .
and extra byte for sign and extra byte for precision .

pk first
fk second
not null third
null, large (..) last


F.

quote from Mnemonical

pk first
fk second
not null third
null, large (..) last


shouldn't it be


pk first
fk second
not null third
large (..)
null last << since null takes 1 byte if precedes non-null column

Quote:

---

Originally posted by Mnemonical

pk first
fk second
not null third
null, large (..) last

---

Hm, what does position of FK have to do with space saving? What if FK column is NULLable? Would you still position it immediately after the PK and in front of other NOT NULL columns?

So -I assume it's good practice to save nullable columns til the end of the table definitions, but WHY - explicitly? In words that a non-Oracle person might understand (the project manager, for instance).

It's quite simple. If row contains null value in a particular column, this column consumes 0 or 1 byte of space on disk. If a null-value column is the last one in a table or if all columns that follow it contain null values, then it consumes no space at all (0 bytes). But if any following columns contain non-null value, then oracle uses 1 byte to store that null value.

So -I assume it's good practice to save nullable columns til the end of the table definitions, but WHY - explicitly? In words that a non-Oracle person might understand (the project manager, for instance).

It's quite simple. If row contains null value in a particular column, this column consumes 0 or 1 byte of space on disk. If a null-value column is the last one in a table or if all columns that follow it contain null values, then it consumes no space at all (0 bytes). But if any following columns contain non-null value, then oracle uses 1 byte to store that null value.

To follow on to jmodic:

If you follow the NOT_NULL->NULLABLE pattern (PK's are not nullable by definition, a unique can be null but a PK can't and a foreign key only has to be unique in the parent table), then a given row in Oracle will take less space because Oracle doesn't store anything for null fields at the end of a row. This is because Oracle stores a 'map' of how many columns are stored for each row in a block. If a null is followed by a field with data, then the map is updated to show the columns.

This means less disk usage. It can also mean better performance because more rows are stored in a given block, meaning more data is returned in a given read (or written in a given write) and given rows can be found faster in memory than on disk.