表格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;
/
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支付的价格与您为避免完全避免的反模式所支付的价格无关.
