ORACLE COMPRESSED TABLE

[chenany]

Hello,
I'd like to know what is the best way to load data into a Oracle Compressed Table.
if a Oracle table is compressed,then i can use "insert /*+append*/... " to compress data,but when i use it by used-defined sql in oracle enterprise stage,it's not effect,who can tell me the reason?TKS

[chulett]

"It's not effect"? Sorry, but can you explain that, expand on the problem you are having please?

[chenany]

When I was used ee job in oracle enterprise or se job in oci stage, tried to use the user-defined sql " insert / * + append * / into...", can not play the effect of compressed form, as shown below:
Table has been compressed
SQL>select table_name,compression from user_table where table_name='TEST_COMPRESS';
TEST_COMPRESS ENABLED
1.SQL
SQL>insert /*+append*/ into TEST_COMPRESS select NO_COMPRESS;
SQL> commit;
41044 rows inserted
SQL>analyze table TEST_COMPRESS compute statistics;
SQL>select table_name,blocks,empty_blocks from user_tables where table_name in ('TEST_COMPRESS');
TEST_COMPRESS 113 15
2.Datastage Oracle Enterprise
Used the same statement in user-defined sql
SQL>analyze table TEST_COMPRESS compute statistics;
SQL>select table_name,blocks,empty_blocks from user_tables where table_name in ('TEST_COMPRESS');
TEST_COMPRESS 313 7

[chenany]

who can help me for this case?

[chulett]

Your official support provider?

I've never bothered with a 'compressed' table in Oracle, never really saw the point, so I'm out from that regard unfortunately. However, there really shouldn't be any difference in the end result when the same sql is run from a stage versus sqlplus so not sure what's up with that either.

Was your DBA able to help at all?