Thumbs Up on Compression

From: MacGregor, Ian A. <ian_at_slac.stanford.edu>
Date: Tue, 25 Jan 2005 09:10:29 -0800
Message-ID: <7F24308CD176594B8F14969D10C02C6C3B7D51@exch-mail2.win.slac.stanford.edu>

I built a non-partitioned, uncompressed table with 709,652,582 rows and = indexed it with a non-compressed index on (timestamp, pv_id) with the = most restrictive column first. A sample query

SELECT value, timestamp, nanosecs, stat, sevr, ostat from chanarch_pepii.new_archive_data_f
WHERE pv_id =3D :CUR_PV_ID AND
timestamp BETWEEN :START_TIME_ORACLE_DATE AND :END_TIME_ORACLE_DATE AND
ostat <> 1
ORDER BY timestamp, nanosecs

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          0     =
      0
Fetch      105    104.51     229.63     154464     154464          0     =

   1556
------- ------ -------- ---------- ---------- ---------- ---------- =

---

total 107 104.51 229.63 154464 154464 0 =

   1556

Misses in library cache during parse: 0
Optimizer goal: CHOOSE
Parsing user id: 27 (ORACLE)

Rows Row Source Operation

-------  ---------------------------------------------------

   1556 SORT ORDER BY
   1556 TABLE ACCESS BY INDEX ROWID NEW_ARCHIVE_DATA_F    1556 INDEX RANGE SCAN NEW_ARCHIVE_DATA_F_INDEX (object id 911430)
-------------------------------------------------------------------------=

---

Elapsed times include waiting on following events:

  Event waited on                             Times   Max. Wait  Total =

Waited

• Waited ---------- =

  ---

  SQL*Net message to client 105 0.00 = 0.00 db file sequential read 154464 0.18 = 124.96 SQL*Net message from client 105 0.00 = 0.29 *************************************************************************=

  ---

I then built the another table with the same data, but compressed it. I = also reversed the index key order, (pv_id, timestamp) and compressed the = first column of that index. I ran the same query against the new table.

SELECT value, timestamp, nanosecs, stat, sevr, ostat from chanarch_pepii.comp_archive_data_f
WHERE pv_id =3D :CUR_PV_ID AND
timestamp BETWEEN :START_TIME_ORACLE_DATE AND :END_TIME_ORACLE_DATE AND
ostat <> 1
ORDER BY timestamp, nanosecs

call count cpu elapsed disk query current =

   rows
------- ------ -------- ---------- ---------- ---------- ---------- =

---

Parse        1      0.01       0.00          0          0          0     =
      0
Execute      1      0.00       0.00          0          0          0     =
      0
Fetch      105      0.42       6.03        937        938          0     =

   1556
------- ------ -------- ---------- ---------- ---------- ---------- =

---

total 107 0.43 6.04 937 938 0 =

   1556

Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 27

Rows Row Source Operation

-------  ---------------------------------------------------

   1556 SORT ORDER BY
   1556 TABLE ACCESS BY INDEX ROWID COMP_ARCHIVE_DATA_F    1556 INDEX RANGE SCAN COMP_ARCIVE_DATA_F_INDEX (object id 911520)

Elapsed times include waiting on following events:

  Event waited on                             Times   Max. Wait  Total =

Waited

• Waited ---------- =

  ---

  SQL*Net message to client 105 0.00 = 0.00 db file sequential read 937 0.17 = 5.83 SQL*Net message from client 105 0.00 = 0.31 *************************************************************************=

  ---

  =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D= =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D= =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D= =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D

The size of the table was 28.5 GB uncompressed and 17.3125 GB = compressed. The index sizes were
17.4375 GB uncompressed and order by (timestamp, pv_id) and 14.4375 GB = for the indexed ordered by (pv_id, timestamp) with the pv_id column = compressed.

The performace increase wad better than I expected. Results were very = similar for the handful of pv_ids I tried. =20

Ian MacGregor
Stanford Linear Accelerator Center
ian_at_SLAC.Stanford.edu

--
http://www.freelists.org/webpage/oracle-l

Received on Tue Jan 25 2005 - 12:13:36 CST