ALTER TABLE Sales.SalesOrderDetail_inmem ALTER INDEX imPK_SalesOrderDetail_SalesOrderID_SalesOrderDetailID REBUILD WITH (BUCKET_COUNT=67108864); GO The following example adds a column with a NOT NULL constraint and with a DEFAULT definition, and uses WITH VALUES to provide values for each existing row in the table. Check the table… example : alter index <> rebuild parallel nologging tablespace <>> 2. enable OLTP compression at table level: alter table table_name move compress for OLTP or alter table table_name move compress for all operations; - This will … 2. ALTER TABLE … COMPRESS FOR OLTP This approach will enable OLTP Table Compression for all future DML -- however, the existing data in the table will remain uncompressed. As you can see, the table has reduced to about 50 percent of the original size. Oracle Database compresses data during all DML operations on the table.” The question then is what triggers compression on updates (or deletes). In addition to saving storage space, compression can result in increased I/O performance and reduced memory use in the buffer … To speed up, use parallel and nologging option while rebuilding them and after set index back to logging and noparallel. Cause Using DBMS_REDEFINITION keeps the table online for both read/write activity … Check the size one more time by typing < select segment_name, bytes from user_segments where segment_name = 'EMP';> You see something like this: SEGMENT_NAME BYTES ----- ----- EMP 9,437,184. In 11g it is now considered a mainstream feature that is acceptable for OLTP databases. If a table to be compressed is partitioned, you may want to apply different degrees of compression to each partition. It is worked for Direct path insert operation not with insert,update, delete with OLTP systems. Now, move the table to compress the data SQL> alter table loc_normal move; SQL> select bytes, extents from dba_segments where segment_name = 'LOC_NORMAL'; BYTES EXTENTS ----- ----- 167,772,160 91 -- The table is now compressed . Partitions. Table Compression Enhancements in Oracle Database 11g Release 1. The SQL Reference manual (E10592-04 p16-34), under “Create Table” tells us: “When you enable table compression by specifying COMPRESS FOR OLTP, you enable OLTP table compression. Online Redefinition (DBMS_REDEFINITION) This approach will enable OLTP Table Compression for future DML and also compress existing data. It is used to save the hard disk space but it will increase the overhead during retrieve and store data. Compress and nocompress table in Oracle Compress allow to reduce the disk space utilization for a table. Compress the rows by typing You see this: Table altered. Table compression was introduced in Oracle 9i as a space saving feature for data warehousing projects. The workaround given in that note works in 11.2.0.4 but does not work in 12C. create table jba (c1 number not null, c2 number not null) compress for oltp; alter table jba nocompress; alter table jba add c3 number default 99 not null; Issue is discussed in length in MOS note: (Doc ID 1288918.1). If WITH VALUES is not …