ITPub博客

首页 > Linux操作系统 > Linux操作系统 > concepts之null在block中的存储

concepts之null在block中的存储

原创 Linux操作系统 作者:lsq_008 时间:2008-08-28 16:55:43 0 删除 编辑

关于null,在concepts中有如下解释:
A null is the absence of a value in a column of a row. Nulls indicate missing,
unknown, or inapplicable data. A null should not be used to imply any other value,
such as zero. A column allows nulls unless a NOT NULL or PRIMARY KEY integrity
constraint has been defined for the column, in which case no row can be inserted
without a value for that column.
Nulls are stored in the database if they fall between columns with data values. In
these cases they require 1 byte to store the length of the column (zero).
Trailing nulls in a row require no storage because a new row header signals that the
remaining columns in the previous row are null.
For example, if the last three
columns of a table are null, no information is stored for those columns. In tables
with many columns, the columns more likely to contain nulls should be defined last
to conserve disk space.
Most comparisons between nulls and other values are by definition neither true nor
false, but unknown. To identify nulls in SQL, use the IS NULL predicate. Use the
SQL function NVL to convert nulls to non-null values.
Nulls are not indexed, except when the cluster key column value is null or the index
is a bitmap index.

红色字体可以看出,如果null列位于2个非null列之间,将占用一个字节的空间,而如果位于最后一列,则不占用存储空间,这样将节省一些存储空间

  为了进一步证明,测试如下:

SQL> create table t1(a varchar2(1000),b varchar2(1000),c varchar2(1000),d varchar2(1000));
 
Table created.
 
SQL> create table t2(a varchar2(1000),b varchar2(1000),c varchar2(1000),d varchar2(1000));
 
Table created.
 
SQL> insert into t1 values(null,null,null,'1234567890123456789012345678901234567890');
 
1 row created.
 
SQL> commit;
 
Commit complete.

SQL> insert into t2 values('1234567890123456789012345678901234567890',null,null,null);
 
1 row created.
 
SQL> commit;
 
Commit complete.

SQL> analyze table t1 compute statistics;
 
Table analyzed.
 
SQL> c/t1/t2
  1* analyze table t2 compute statistics
SQL> /
 
Table analyzed.


 SQL> select table_name,num_rows,avg_row_len from user_tables where table_name in('T1','T2');
 
TABLE_NAME                       NUM_ROWS AVG_ROW_LEN
------------------------------ ---------- -----------
T1                                      1          47
T2                                      1          44

可见,三个null列正好占用了三个字节的空间。^_^
 

来自 “ ITPUB博客 ” ,链接:https://blog.itpub.net/10972173/viewspace-438684/ ,如需转载,请注明出处,否则将追究法律责任。

上一篇: PGA tuning
请登录后发表评论 登录
全部评论
十余年大型金融及电信系统数据库管理经验,曾服务于中国建设银行、中国移动。对oracle,mysql数据库有深入了解。 擅长python开发,独立开发了开源数据库自动化监控运维平台Power Monitor。

注册时间:2008-02-29

  • 博文量
    335
  • 访问量
    1369116