sql – 在Oracle中存储NULL

表格1

col1 col2 col3 col4 col5 col6 col7 col8       col9 col10 col11
1    NULL 2    3    4    5    NULL NULL       19   21    22
1    NULL 2    3    4    5    NULL 1 Jan 2009 19   21    22
1    NULL 2    3    4    5    NULL NULL       19   21    22
1    9    2    3    4    5    A    NULL       19   21    22
1    NULL 2    3    4    5    B    NULL       19   21    22

表desc是：

Name                 Null Type          
-------------------- ---- ------------- 
COL1                      NUMBER        
COL2                      NUMBER        
COL3                      NUMBER        
COL4                      NUMBER       
COL5                      NUMBER        
COL6                      NUMBER        
COL7                      VARCHAR2(255) 
COL8                      DATE          
COL9                      DATE  
COL10                     DATE        
COL11                     VARCHAR2(255)

我需要找出表消耗的存储空间的百分比是NULL？

示例：消耗的table1存储空间为1 GB,内部的NULL消耗100MB,因此NULL占用存储空间的10％.

此外,ORACLE中是否有NULL的替代表示？

解决方法

但是这个数字是没有意义的,尽管它基于下面可重现的测试用例.更重要的是要理解NULL是微小的(只有一个字节).如此微小的“真实”大小应该是无关紧要的,除非在极端情况下.这么小,几乎总是浪费时间来担心替代表示.

最好的案例测试用例(实际使用空间)

我们使用您的表定义创建1GB的数据.首先,我们来创建表.

create table test1(
COL1  NUMBER,COL2  NUMBER,COL3  NUMBER,COL4  NUMBER,COL5  NUMBER,COL6  NUMBER,COL7  VARCHAR2(255),COL8  DATE,COL9  DATE,COL10 DATE,COL11 VARCHAR2(255)
) pctfree 0 /* Let's assume no updates or deletes,and pack the data tightly */;

现在创建一千兆字节的数据.每个值使用该数据类型的最大可能值.

begin
    for i in 1 .. 15 loop  --Magic number to generate exactly 1GB.
        insert into test1
        select
            .0123456789012345678901234567890123456789,.0123456789012345678901234567890123456789,lpad('A',255,'A'),sysdate,'A')
        from dual
        connect by level <= 95000;    --Magic number to generate exactly 1GB.
        commit;
    end loop;
end;
/

这些查询显示它对于1,425,000行使用1GB的空间.

select count(*) from test1;
select bytes/1024/1024/1024 gb from user_segments where segment_name = 'TEST1';

现在创建一个第二个表,其行数相同,但在每列中都为空.

create table test1_null as
select col1+null c1,col2+null c2,col3+null c3,col4+null c4,col5+null c5,col6+null c6,cast(null as varchar2(255)) c7,col8+null c8,col9+null c9,col10+null c10,cast(null as varchar2(255)) c11
from test1;

新的细分市场规模只有0.0175GB,即1.75％.

select bytes/1024/1024/1024 gb from user_segments where segment_name = 'TEST1_NULL';

为什么这个测试用例是误导的

虽然这可能听起来像是一个简单的问题,但是要完全回答,它需要一本整本书或一个水晶球.获得真正的存储大小是非常复杂的.您至少需要考虑以下问题：

>可变宽度数据.大多数Oracle数据类型只能使用存储数据所需的空间.因此,用于该NULL字节的存储百分比会精确地取决于其他列中的内容.无论数据如CHAR,NCHAR,DATE,TIMESTAMP等,数据类型都使用静态数据量.
>追踪空值.行末尾的所有连续NULL都存储在一个字节中.除非启用基本压缩,否则每个NULL再次使用一个字节.
>行开销.每行都具有取决于列和配置的开销.表中的皮肤越大,行的顶部占用了空间,所以使用的百分比将会波动.
>块开销.这取决于行数,如PCTFREE的设置,如果先前的行被删除,最后重新组织表,块大小等.
>段开销空间被分配为多个区块.范围管理可以使用默认算法(我认为分配大小为1MB到64MB),也可以是任何自定义值.取决于数据量,这种开销变得不太重要.有可能将一个表空间设置为巨大的统一扩展大小,例如10GB,无论列值如何,都可能浪费大量空间.
>其他I / O开销.空间可能也被ASM,操作系统,SAN等浪费.

排列格式(理论上的空间使用)

下图为Logical Storage Structures chapter of the Concepts Guide：

列数据由一系列列长度和列值组成.如果值为NULL,则列长度设置为0,并且列值不使用任何空格.这就是为什么一个NULL总是只使用1个字节,数字0.

大多数数据类型是可变的,因此长度将至少使用1个字节,如果非空,则该值至少使用1个字节.静态数据类型,如DATE,仍然使用1个字节的长度,然后7个字节的值.再次,除非日期为NULL,否则长度设置为0,值为空.

这个图像也可以解释“尾随空”的存储技巧.当有尾随空值时,Oracle可能将列数降低,将最后一列列长度设置为0,并推断剩余列也为空.

替代代表？

现在我越来越怀疑询问NULL的替代表示可以想到四种人：

>无理的理论人士抱怨违反关系模型,并提出使用晦涩的工具,而不是几十年来一直工作良好的工具.
>数据架构师谁认为一个巨大的Entity-Attribute-Value表始终是答案. “嘿,我的PDF看起来不错,谁在乎无法查询？”
>那些对SQL有点新鲜,对于NULL的工作方式好受挫折的人.
> Stackoverflow用户谁读了太多的问题. (如果我要离开,请随时添加背后的背景信息！)

是的,NULL有点奇怪.但是很快就会有意义的.不要太担心空间,或者完全避免NULL的方法.您为NULL支付的价格与您为避免完全避免的反模式所支付的价格无关.

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!