A reader, September 09, 2002 - 6:12 am UTC
Irreplaceable service
Miroslav Dzakovic, September 15, 2003 - 3:44 pm UTC
Exporting compressed table
A reader, January 12, 2005 - 11:33 am UTC
Is the ORDER BY used when the compressed table was originally created stored anywhere in the dictionary?
If not, suppose I create a compressed table, export it, drop it and re-import it. It would NOT occupy the same "compressed" size it used to, right? "exp" does not export the raw data blocks, it just exports the data and imports back using SQL statements, right?
Shouldnt there be a *_compressed_tab_columns dictionary view that stores this information so that subsequent bulk operations can benefit?
Thanks
January 12, 2005 - 1:24 pm UTC
nope, the query itself is not stored anywhere, it is not relevant after the load.
if you import, not a thing will be compressed for IMP uses conventional path SQL to load. It'll be totally "not compressed at all"
the order by on a CTAS -- I don't see how it could be relevant for subsequent bulk operations?
if you are inserting /*+ append */ -- you have to be using a SELECT in there, so it would have its own order by.
the other bulk operations don't "sort" at all.
David Aldridge, January 12, 2005 - 2:25 pm UTC
Now, if only there was a way of finding out the proportion of blocks in the segment for which the data is actually compressed -- that'd be extremely useful.
The only way I can think of at the moment is to full scan the table and count the number of rows per block when you _know_ the data is compressed, and maybe do the same again when you _know_ that it isn't. This ought to give some kind of benchmark (for that table and data set) for the number of rows that the compression process manages to fit into each compressed block.
Then you might say "if the block has greater than 120 rows in it then it's compressed, otherwise it is (probably) not".
Or is there a better way?
January 12, 2005 - 2:36 pm UTC
that the data is compressed or not is totally transparent by design, it is not exposed.
NULL entry for COMPRESSION in user_tables
Ik, April 05, 2006 - 12:40 pm UTC
Tom,
This is 10g R2.
I have a null entry in user_tables for a particular table (non compressed).
I did an ALTER TABLE <tablename> NOCOMPRESS;
Still the value is NULL.
Any reason why that is?
Thanks,
April 06, 2006 - 9:12 am UTC
give details of table please (eg: select dbms_metadata.get_ddl( 'TABLE', 'TAB-name' ) from dual)