If you put compression on a column, then the system will always compress nulls. So if you compress 'Fred' on your column, the collumn will compress Fred or nulls.
If you do not compress the column, nulls are not compressed.
From the Database Design Manual:
IF COMPRESS is defined with no argument all nulls for the column are compressed to zero space.
IF COMPRESS is defined with one or more constant arguments then each occurrence of a specified constant is compressed to
zero space and all nulls for the column are compressed to zero space.
In addition to jimm's comment:
Compress is only usefull for large table, but you're compressing a table with 71 rows on a system with approx. 80 AMPs.
There's a table header of 1024 bytes on each AMP + a maximum of single 512 byte datablock.
Run your script on an empty table, to see if the table header size changes due to the compress.
And try it with a larger number of rows using
insert into tavoukar.nullcomptest values
select day_of_calendar ,NULL from sys_calendar.calendar;
instead of those single row insert. Now it's still a small table, but approx. 1000 rows per AMP.
Dieter
>If you do not compress the column, nulls are not compressed.
This appears to be the case from what I am trying however this goes against documentation per Database Design (Sept 2007) p67.
I have now tried with a larger table per dnoeth's suggestion:
A table was created with no explicit compression. It reduced in size after applying null compression via alter statements. However it kept the same size after removing the compression (thus returning back to the orignal table definition). Hence we have a table size difference despite the same definition and data. It appears that compression was not truly removed despite it being removed from the table definition.
I also created the same table but with null compression in the create table statement. I then altered it to remove compression. The resulting size was approximately the same as creating a table without null compression which is expected. So it seems removing compression only sometimes really removes compresssion.
The queries are as below:
--Test A
CREATE MULTISET TABLE TAVOUKAR.NullCompTest ,no FALLBACK ,
NO BEFORE JOURNAL,
NO AFTER JOURNAL,
CHECKSUM = DEFAULT
(
calendar_day int,
compressme CHAR(30) CHARACTER SET LATIN NOT CASESPECIFIC)
PRIMARY INDEX (calendar_day);
insert into TAVOUKAR.NullCompTest
select day_of_calendar ,NULL from sys_calendar.calendar;
select sum(currentperm)
from dbc.tablesize
where databasename = 'tavoukar'
and tablename = 'NullCompTest'
group by tablename;--3806720.00
ALTER TABLE TAVOUKAR.NullCompTest
ADD compressme COMPRESS NULL;
select sum(currentperm)
from dbc.tablesize
where databasename = 'tavoukar'
and tablename = 'NullCompTest'
group by tablename;--1539072.00
ALTER TABLE TAVOUKAR.NullCompTest
ADD compressme No compress;
select sum(currentperm)
from dbc.tablesize
where databasename = 'tavoukar'
and tablename = 'NullCompTest'
group by tablename;--1539072.00
--Test B
drop table tavoukar.NullCompTest;
CREATE MULTISET TABLE TAVOUKAR.NullCompTest ,no FALLBACK ,
NO BEFORE JOURNAL,
NO AFTER JOURNAL,
CHECKSUM = DEFAULT
(
calendar_day int,
compressme CHAR(30) CHARACTER SET LATIN NOT CASESPECIFIC compress null)
PRIMARY INDEX (calendar_day);
insert into TAVOUKAR.NullCompTest
select day_of_calendar ,NULL from sys_calendar.calendar;
select sum(currentperm)
from dbc.tablesize
where databasename = 'tavoukar'
and tablename = 'NullCompTest'
group by tablename;--1456128.00
ALTER TABLE TAVOUKAR.NullCompTest
ADD compressme No compress;
select sum(currentperm)
from dbc.tablesize
where databasename = 'tavoukar'
and tablename = 'NullCompTest'
group by tablename;--3889664.00
The documentation might be a bit misleading indeed.
But your test result is strange. It's ok when i run it on my system.
Looks like the internal housekeeping of the system table is lagging behind.
Which release is your system running?
DIeter
After reading that "The system always compresses nulls whether you specify null compression or not." in the Database Design (Sept 2007) documentation I decided to test this.
I created a table with a int column as primary index and a char column filled with only nulls, with no specified compression. I then compressed null on the char column via alter table. This increased the size of the table. However there should be no change as per documentation "The system always compresses nulls whether you specify null compression or not". I then removed compression via an alter table statement and the table size remained the same. This left us strangely with a table that uses more space than the original version despite having the same definition and data.
Would anyone have an explanation here as to why then:
1)The system does not act as though "it always compresses nulls whether you specify null compression or not" per documentation
2)There is a table size difference despite the same definition and data
The queries used are as below:
select *
from dbc.dbcinfo
--Result
--LANGUAGE SUPPORT MODE Standard
--RELEASE 12.00.03.18
--VERSION 12.00.03.14f
CREATE MULTISET TABLE TAVOUKAR.NullCompTest ,no FALLBACK ,
NO BEFORE JOURNAL,
NO AFTER JOURNAL,
CHECKSUM = DEFAULT
(
number INTEGER,
compressme CHAR(30) CHARACTER SET LATIN NOT CASESPECIFIC)
PRIMARY INDEX ( number );
insert into tavoukar.nullcomptest values (0,NULL);
insert into tavoukar.nullcomptest values (1,NULL);
...
insert into tavoukar.nullcomptest values (69,NULL);
insert into tavoukar.nullcomptest values (70,NULL);
select sum(currentperm)
from dbc.tablesize
where databasename = 'tavoukar'
and tablename = 'NullCompTest'
group by tablename;-- Result is 114176.00
ALTER TABLE TAVOUKAR.NullCompTest
ADD compressme COMPRESS NULL;
select sum(currentperm)
from dbc.tablesize
where databasename = 'tavoukar'
and tablename = 'NullCompTest'
group by tablename;-- Result is 197120.00
ALTER TABLE TAVOUKAR.NullCompTest
ADD compressme No compress;
select sum(currentperm)
from dbc.tablesize
where databasename = 'tavoukar'
and tablename = 'NullCompTest'
group by tablename;-- Result is 197120.00