about compression
hi all: I have a field in a table which needs to be 300 long. However, only 5% of all records (half a million of them) has this field filled with actual text. My question is, does informix automatically compress the empty space or do I have to do something to make it compress the wasted space? I don't want to use 300 chars for each field while I don't use most of them. thanks yan
I realize that you're question is about data compression, but an alternate
solution to using VARCHAR instead of CHAR is change the design of the table
in question. For example:
Before:
create table employee
(
emp_id serial
lname char(25),
fname char(25),
...
...
primary (emp_id)
)
After:
create table employee
(
emp_id serial
lname char(25),
fname char(25),
...
...
primary (emp_id)
)
Yan Zhu wrote:
> hi all:
> I have a field in a table which needs to be 300 long. However, only
> 5% of all records (half a million of them) has this field filled with
> actual text. My question is, does informix automatically compress the
> empty space or do I have to do something to make it compress the wasted
> space? I don't want to use 300 chars for each field while I don't use
> most of them.
>
> thanks
> yan
I realize that you're question is about data compression, but an alternate
solution to using VARCHAR instead of CHAR is change the design of the table
in question. For example:
Before:
create table emp
(
emp_id serial
lname char(25),
fname char(25),
comment char(300),
...
...
primary key (emp_id)
)
After:
create table emp
(
emp_id serial
lname char(25),
fname char(25),
...
...
primary key (emp_id)
)
create table emp_comment
(
emp_id integer,
comment char(300),
foreign key (emp_id) references emp
)
In this case, data only *exists* when it is needed.
Yan Zhu wrote:
> hi all:
> I have a field in a table which needs to be 300 long. However, only
> 5% of all records (half a million of them) has this field filled with
> actual text. My question is, does informix automatically compress the
> empty space or do I have to do something to make it compress the wasted
> space? I don't want to use 300 chars for each field while I don't use
> most of them.
>
> thanks
> yan