Compress enable novalidate

We knewhere that we can’t DDL optimise a BASIC compressed table. So I uncompressed my table in a 12.2.0.1.0 and DDL optimise it as in the following:

SQL> select 
        pct_free
	,compression
	,compress_for
    from 
	 user_tables
    where 
	   table_name = 'T1';

  PCT_FREE COMPRESS COMPRESS_FOR
---------- -------- -------------
         0 DISABLED

SQL> alter table t1 add c_ddl number default 42 not null;

Table altered.

But I changed my mind and finally decided to revert back and drop this newly added column:

SQL> alter table t1 drop column c_ddl;
alter table t1 drop column c_ddl
                           *
ERROR at line 1:
ORA-39726: unsupported add/drop column operation on compressed tables

Damned database!!!

What’s going on here? My table is not compressed. Is it?

In fact I didn’t show you how I have uncompressed the t1 table:

SQL> alter table t1 nocompress;

Table altered.

Compressing or un-compressing a table without moving
it will do nothing to its data. When the COMPRESSION column of a table is DISABLED this doesn’t necessarily mean that this table doesn’t contain compressed data . And this is probably the reason why the above bizarre error pops up out from nowhere.

In fact in order to have a significant meaning, the COMPRESSION column should normally always be considered with the PCT_FREE column (notice in passing that this is why I printed above the pct_free value):

SQL> select
         table_name
        ,compression
        ,pct_free
        ,compress_for
     from 
     user_tables
    where table_name = 'T1';

TABLE_NAME COMPRESS   PCT_FREE COMPRESS_FOR
---------- -------- ---------- --------------
T1         DISABLED          0

When PCT_FREE equals zero this is generally a hint that Oracle is considering BASIC compression since it thinks that the table is not considered for updates anymore. The definition given by Oracle to the COMPRESSION column goes in the same direction as well:

COMPRESSION	 	Indicates whether table compression 
                    is enabled (ENABLED) or not (DISABLED)

This is crystal clear: COMPRESSION referes to the status of the current
compression. It doesn’t tell anything about the compression history of the table if any.

Compressing/uncompressing a table without moving it is nothing else than implementing or desimplementing compression but only for newly inserted rows. This is why I think that Oracle could have avoided the confusion by using the same command as when dealing with table constraints. Instead of this:

SQL> alter table t1 nocompress;

We would have this :

SQL> alter table t1 nocompress enable novalidate;

And of course the COMPRESSION column should have had a status indicating whether it is validated or not.

In the 10046 file of a compressed table with a move of its data and without a move we can see this respectively:

alter table t1 move compress basic

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.01       0.02          0          0          1           0
Execute      1      1.43       1.44          3       1865       2548     1000000
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        2      1.45       1.46          3       1865       2549     1000000

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 106  
Number of plan statistics captured: 1

Rows (1st) Rows (avg) Rows (max)  Row Source Operation
---------- ---------- ----------  ---------------------------------------------------
         0          0          0  LOAD AS SELECT  T1 (cr=1905 pr=1 pw=1382 time=1436345 us
   1000000    1000000    1000000   TABLE ACCESS FULL T1 (cr=1797 pr=0 pw=0 time=104378 

Elapsed times include waiting on following events:
  Event waited on                             Times   Max. Wait  Total Waited
  ----------------------------------------   Waited  ----------  ------------
  PGA memory operation                         1140        0.00          0.01
  Disk file operations I/O                        1        0.00          0.00
  db file sequential read                         3        0.00          0.00
  direct path write                               1        0.00          0.00
  reliable message                                4        0.00          0.00
  enq: RO - fast object reuse                     2        0.00          0.00
  enq: CR - block range reuse ckpt                2        0.00          0.00
  SQL*Net message to client                       1        0.00          0.00
  SQL*Net message from client                     1        5.93          5.93
********************************************************************************
alter table t1 nocompress


call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      1      0.00       0.00          0          0          2           0
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        2      0.00       0.01          0          0          2           0

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 106  

Elapsed times include waiting on following events:
  Event waited on                             Times   Max. Wait  Total Waited
  ----------------------------------------   Waited  ----------  ------------
  Compression analysis                            8        0.00          0.00
  SQL*Net message to client                       1        0.00          0.00
  SQL*Net message from client                     1        6.65          6.65
********************************************************************************

Spot that whileOracle managed to rework a million rows of in the first case it didn’t touch any in the second case proving that nocompress/compress without a move doesn’t concern existing data.

Since it concerns only new data then an inserted row should be compressed/non-compressed. Let’s see:

SQL> create table t1 as select
      rownum n1
     ,mod(rownum,10) n2
     from dual
     connect by level  @sizeBysegNameMB
Enter value for segment_name: t1
Enter value for owner: c##mhouri


SEGMENT_TYPE  TABLESPACE_NAME  SEGMENT_NAME PARTITION_NAME          MB
------------- ---------------- ------------ --------------- ----------
TABLE         USERS            T1                                   15
                                                            ----------
Total Segment Size                                                  15

Let’s compress this table without moving its data and get its new size:

SQL> alter table t1 compress;

Table altered.

SQL> @sizeBysegNameMB
Enter value for segment_name: t1
Enter value for owner: c##mhouri


SEGMENT_TYPE  TABLESPACE_NAME  SEGMENT_NAME PARTITION_NAME          MB
------------- ---------------- ------------ --------------- ----------
TABLE         USERS            T1                                   15
                                                            ----------
Total Segment Size                                                  15

As you can see the size of the table remains intact indicating again that compressing without moving does nothing to the existing data.

Let’s now direct path load a single row, dump the corresponding block and analyse the dump to see if compression has been activated or not:

SQL> select rowid, n1,n2 from t1 where n1=1;

ROWID                      N1         N2
------------------ ---------- ----------
AAATYNAAHAAAdpTAAA          1          1

SQL> insert /*+ append */ into t1 select 1,42 from dual;

1 row created.

SQL> select rowid, n1,n2 from t1 where n1=1;
select rowid, n1,n2 from t1 where n1=1
                         *
ERROR at line 1:
ORA-12838: cannot read/modify an object after modifying it in parallel


SQL> commit;

Commit complete.

SQL> select rowid, n1,n2 from t1 where n1=1;

ROWID                      N1         N2
------------------ ---------- ----------
AAATYNAAHAAAdpTAAA          1          1
AAATYNAAHAADOgcAAA          1         42

SQL> select
  2    dbms_rowid.rowid_relative_fno('AAATYNAAHAADOgcAAA') file_no
  3  , dbms_rowid.rowid_block_number('AAATYNAAHAADOgcAAA') block_no
  4  from dual;

   FILE_NO   BLOCK_NO
---------- ----------
         7     845852


SQL> alter session set tracefile_identifier='DumpingDirectLoadRowInsert';

Session altered.

SQL> alter system dump datafile 7 block 845852;

System altered.

Unfortunately I couldn’t spot anything interesting in the dump file which indicates that the inserted row has been compressed. I will probably come back to this article in a near future.

责编内容来自:Mohamed Houri’s Oracle Notes (源链) | 更多关于

阅读提示:酷辣虫无法对本内容的真实性提供任何保证,请自行验证并承担相关的风险与后果!
本站遵循[CC BY-NC-SA 4.0]。如您有版权、意见投诉等问题,请通过eMail联系我们处理。
酷辣虫 » 后端存储 » Compress enable novalidate

喜欢 (0)or分享给?

专业 x 专注 x 聚合 x 分享 CC BY-NC-SA 4.0

使用声明 | 英豪名录