-
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 .
siva prakash
DBA
-
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
-
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?
Jurij Modic
ASCII a stupid question, get a stupid ANSI
24 hours in a day .... 24 beer in a case .... coincidence?
-
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.
Jurij Modic
ASCII a stupid question, get a stupid ANSI
24 hours in a day .... 24 beer in a case .... coincidence?
-
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.
Jurij Modic
ASCII a stupid question, get a stupid ANSI
24 hours in a day .... 24 beer in a case .... coincidence?
-
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.
Joseph R.P. Maloney, CSP,CDP,CCP
'The answer is 42'
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|