Thanks for the pointers. I looked at both.
The Oracle feature seems to be a key structure optimization only.
The MySQL readonly compressed table would be OK for situations that are,
well, readonly.
You could probably get away with setting up a Microsoft SQL Server database
that was "readonly" on a compressed volume, too. I do note that MS SQL
Server does NOT support databases on compressed volumes but, according to
the KB article I read, it has do do with sector-aligned writes on the log
device and a "readonly" database wouldn't get into trouble that way, so I
imagine it would work.
I couldn't find the prerelease documentation for the MySQL COMPRESS() and
DECOMPRESS() functions but I'm guessing they would be used this way:
insert mytable (mykey, mycomprsdcol) values (1,compress('data to store in
compressed form')
and then
select * from mytable where DECOMPRESS(mycomprsdcol) like '%store%'
.... in other words, lots of CPU overhead, a tendency to force scans and
programmer awareness of the compressed structure? You'd have to save a LOT
of space and only do comparisons on the compressed columns very rarely to
make this pay off.
I suppose one could write one's own COMPRESS() and DECOMPRESS() functions in
MS SQL Server and have functionality equivalent to MySQL, along with the
equivalent tradeoffs.
I also looked at the DB2 site... Compression there appears to take advantage
of special hardware available on the IBM mainframe systems. I'm impressed
with that. However, if you do compression on a system that does not have
the special hardware, your CPU will take the hit. I suppose that would
include all Intel-based servers (and maybe this feature is only supported at
all on MVS DB2s).
Although it's available in some DBMS systems, it's different in each one and
seems to be of limited utility in some and completely untransparent in
others; I'd hesitate to call it a "basic staple" at this point.
If I were to request this feature from MS, I think I'd ask for it in terms
of "support database devices on compressed volumes." That way, you could
establish the highly compressible part of your database on a filegroup
that's compressed and the parts of your database where you don't think
compression will work well for you could be on uncompressed volumes. Since
NTFS compression already exists, I'd also think this was the fastest way to
get the feature to market. In fact, explicitly limited support, e.g., "no
log files on compressed devices" would be reasonable.
Ideally, I guess, I'd want to be able to declare particular columns to be
compressed, specifying compression as an attribute:
create table foo
(fookey int not null,
footext varchar(100) compressed)
and have handling of the compressed data be transparent to the programmer.
It would still be an nteresting performance tradeoff problem for the DBA, of
course.
"No Junkmail" <no********@anywhere.com> wrote in message
news:yXEXa.67772$o%2.32105@sccrnsc02...
Just a little more detail if anyone cares:
On Oracle you can look at the following white paper on indexed
tables (the feature is call "key compression".)
http://otn.oracle.com/products/oracl...ts/iot_ds.html
On MySQL http://www.mysql.com/doc/en/index.html an look for
the key word compress. You will have to look into the prerelease
documentation for the new UDF functions to come.
"No Junkmail" <no********@anywhere.com> wrote in message
news:ONEXa.67708$o%2.32778@sccrnsc02... MySQL has compressed tables (read only) and is adding COMPRESS()
and DECOMPRESS() UDF in version 5.0.
MS does compression on it's native OLAP cubes.
Oracle 9i can do data compression on Enterprise Edition relational
tables and
OLAP cubes.
Data compression seems to be such a basic staple when working with
large data of any kind I was surprised that MS did not have it as a
standard feature in the current release
"DHatheway" <dl********@mmm.com.nospam> wrote in message
news:bg**********@tuvok3.mmm.com... Just out of curiosity, which other DBMS's that you know of support
compression?
