Thumbs Up on Compression
- From: "MacGregor, Ian A." <ian@xxxxxxxxxxxxxxxxx>
- To: <oracle-l@xxxxxxxxxxxxx>
- Date: Tue, 25 Jan 2005 09:10:29 -0800
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@xxxxxxxxxxxxxxxxx
--
//www.freelists.org/webpage/oracle-l
Other related posts:
