Skip to Main Content
  • Questions
  • How to tell if a table has compression enabled?

Breadcrumb

Question and Answer

Tom Kyte

Thanks for the question, Steven.

Asked: September 08, 2002 - 11:38 am UTC

Last updated: April 06, 2006 - 9:12 am UTC

Version: 9.2.0

Viewed 10K+ times! This question is

You Asked

With the new table (data segment) compression feature of 9iR2, I'm
not seeing anything in DBA/ALL/USER_TABLES that indicates if
compression is enabled or not. There is a COMPRESSION column present
in DBA/ALL/USER_TAB_PARTITIONS that indicates if a partition has
compression enabled. When I do an export, the compression
attribute shows up when I do SHOW=Y, but it would be better if I
could pick that attribute out of a dictionary table. Any ideas?

and Tom said...

Appears to be an oversight -- we can workaround like this.

I used my getaview script as SYS to suck out the definition of user_tab_partitions and user_tables.

------------ getaview -------------------------
set heading off
set long 99999999
set feedback off
set linesize 1000
set trimspool on
set verify off
set termout off
set embedded on

column column_name format a1000
column text format a1000

spool &1..sql
prompt create or replace view &1 (
select decode(column_id,1,'',',') || column_name column_name
from user_tab_columns
where table_name = upper('&1')
order by column_id
/
prompt ) as
select text
from user_views
where view_name = upper('&1')
/
prompt /
spool off

set termout on
set heading on
set feedback on
set verify on
-------------------------------------------------

then, looking at user_tab_partitions -- I found that the compressed column was really:

decode(bitand(s.spare1, 2048), 2048, 'ENABLED', 'DISABLED'),

and that s was a correlation name for sys.seg$. I modified the user_tables create view statment in the following way:

1) changed the view being created to my_user_tables
2) added a column name compressed to the end of the list of columns
3) added the above decode -- unchanged since this query also aliased sys.seg$ as "S"
4) created the view (as sys or sysdba -- I was actually sysdba which is sys)
5) created a public synonym
6) granted select on it

you can use this view until they correct this oversite.

do NOT just add this to user_tables, that could have serious unintended side effects elsewhere!


Rating

  (5 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

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

Tom Kyte
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?

Tom Kyte
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,


Tom Kyte
April 06, 2006 - 9:12 am UTC

give details of table please (eg: select dbms_metadata.get_ddl( 'TABLE', 'TAB-name' ) from dual)

More to Explore

PL/SQL demos

Check out more PL/SQL tutorials on our LiveSQL tool.

PL/SQL docs

PL/SQL reference manual from the Oracle documentation library