Compressed Tables
Oracle9i 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.
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
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
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
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
********************************************************************************
No comments:
Post a Comment