Oracle 8i | Oracle 9i | Oracle 10g | Oracle 11g | Oracle 12c | Miscellaneous | PL/SQL | SQL | Oracle RAC | Oracle Apps | Linux

Oracle Compressed Tables

Oracle 9i allows whole tables or individual table partitions to be compressed to reduce disk space requirements. The compression is not suitable for volatile tables as it decreases insert and update performance but it can have value for data that is predominantly read-only.

Related articles.

Create Table

First we create a partitioned table to hold the data. The compressed TEST_TAB_Q1 partition will hold rows for the first quarter of the year. The uncompressed TEST_TAB_Q2 table will hold rows for the current quarter.

DROP TABLE test_tab;

CREATE TABLE test_tab (
  id            NUMBER(10)    NOT NULL,
  description   VARCHAR2(50)  NOT NULL,
  created_date  DATE          NOT NULL,
  created_by    VARCHAR2(50)  NOT NULL,
  updated_date  DATE,
  updated_by    VARCHAR2(50)
)
PARTITION BY RANGE (created_date) (
  PARTITION test_tab_q1 VALUES LESS THAN (TO_DATE('01/04/2003', 'DD/MM/YYYY')) COMPRESS,
  PARTITION test_tab_q2 VALUES LESS THAN (MAXVALUE)
)
/

ALTER TABLE test_tab ADD (
  CONSTRAINT test_tab_pk PRIMARY KEY (id)
)
/

Insert Performance

Next we test insert performance.

-- TEST_TAB Inserts into non-compressed partition.
DECLARE
  v_date  test_tab.created_date%TYPE := SYSDATE;
  v_user  test_tab.created_by%TYPE   := USER;
BEGIN
  FOR i IN 1 .. 100000 LOOP
    INSERT INTO test_tab (id, description, created_date, created_by)
    VALUES (i, 'description: ' || i, v_date, v_user);
  END LOOP;
  COMMIT;
END;
/

********************************************************************************
INSERT INTO test_tab (id, description, created_date, created_by)
    VALUES (:b3, 'description: ' || :b3, :b2, :b1)

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          3          0           0
Execute 100000     16.98      19.64          0     101236     513333      100000
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total   100001     16.98      19.65          0     101239     513333      100000
********************************************************************************

-- TEST_TAB Inserts into compressed partition.
DECLARE
  v_date  test_tab.created_date%TYPE := TO_DATE('31/03/2003', 'DD/MM/YYYY');
  v_user  test_tab.created_by%TYPE   := USER;
BEGIN
  FOR i IN 100001 .. 200000 LOOP
    INSERT INTO test_tab (id, description, created_date, created_by)
    VALUES (i, 'description: ' || i, v_date, v_user);
  END LOOP;
  COMMIT;
END;
/

********************************************************************************
INSERT INTO test_tab (id, description, created_date, created_by)
    VALUES (:b3, 'description: ' || :b3, :b2, :b1)

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute 100000     16.46      20.56          2     101208     513447      100000
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total   100001     16.46      20.56          2     101208     513447      100000
********************************************************************************

From these results you can see that although 100,000 inserts into the compressed partition took slightly less CPU time but it did take approximately one second longer to complete compared to an uncompressed partition.

Check Compression

Once the statistics are gathered you can see that in this example the compressed partition uses approximately 10% fewer blocks for storage.

EXEC DBMS_STATS.gather_schema_stats('DEV', cascade => TRUE);

SELECT table_name,
       partition_name,
       compression,
       num_rows,
       blocks,
       empty_blocks
FROM   user_tab_partitions;

TABLE_NAME   PARTITION_NAME   COMPRESSION   NUM_ROWS     BLOCKS EMPTY_BLOCKS
------------ ---------------- ----------- ---------- ---------- ------------
TEST_TAB     TEST_TAB_Q1      ENABLED         100000        558            0
TEST_TAB     TEST_TAB_Q2      DISABLED        100000        622            0

2 rows selected.

Update Performance

Next we test update performance.

-- TEST_TAB Updates of non-compressed partition.
BEGIN
  FOR i IN 1 .. 100000 LOOP
    UPDATE test_tab
    SET    description  = 'description: update ' || i,
           updated_date = SYSDATE,
           updated_by   = USER
    WHERE  id = i;
  END LOOP;
  COMMIT;
END;
/

********************************************************************************
UPDATE test_tab
    SET    description  = 'description: update ' || :b1,
           updated_date = SYSDATE,
           updated_by   = USER
    WHERE  id = :b1

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute 100000     18.33      28.67        583     260158     316675      100000
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total   100001     18.33      28.67        583     260158     316675      100000
********************************************************************************

-- TEST_TAB Updates of compressed partition.
BEGIN
  FOR i IN 100001 .. 200000 LOOP
    UPDATE test_tab
    SET    description  = 'description: update ' || i,
           updated_date = SYSDATE,
           updated_by   = USER
    WHERE  id = i;
  END LOOP;
  COMMIT;
END;
/

********************************************************************************
UPDATE test_tab
    SET    description  = 'description: update ' || :b1,
           updated_date = SYSDATE,
           updated_by   = USER
    WHERE  id = :b1

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute 100000     19.53      34.52        522     275606     373908      100000
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total   100001     19.53      34.52        522     275606     373908      100000
********************************************************************************

From these results you can see that 100,000 updates of rows in the compressed partition take over 1 second more CPU time and approximately 6 seconds longer to complete compared to an uncompressed partition.

Query Performance

Finally we test query performance.

-- TEST_TAB Selects from non-compressed partition.
DECLARE
  v_desc  test_tab.description%TYPE;
BEGIN
  FOR i IN 1 .. 100000 LOOP
    SELECT description
    INTO   v_desc
    FROM   test_tab
    WHERE  id = i;
  END LOOP;
END;
/

********************************************************************************
SELECT description
    FROM   test_tab
    WHERE  id = :b1

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute 100000      3.83       3.70          0          0          0           0
Fetch   100000      3.94       6.12        744     330793          0      100000
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total   200001      7.78       9.82        744     330793          0      100000
********************************************************************************

-- TEST_TAB Selects from compressed partition.
DECLARE
  v_desc  test_tab.description%TYPE;
BEGIN
  FOR i IN 100001 .. 200000 LOOP
    SELECT description
    INTO   v_desc
    FROM   test_tab
    WHERE  id = i;
  END LOOP;
END;
/

********************************************************************************
SELECT description
    FROM   test_tab
    WHERE  id = :b1

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute 100000      3.98       3.77          0          0          0           0
Fetch   100000      3.96       6.15        701     338932          0      100000
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total   200001      7.95       9.92        701     338932          0      100000
********************************************************************************

From these results you can see queries against rows in compressed and uncompressed partitions are reasonably comparable in terms of CPU and elapsed time. Oracle claim that compressed tables and partitions may give improved query performance due to the reduced number of disk reads required.

For more information see:

Hope this helps. Regards Tim...

Back to the Top.