Database Reference
In-Depth Information
read from the disks will increase performance. Actually, the performance should increase almost proportionally to the
compression factor. The following example, based on the
data_compression.sql
script, illustrates this:
SQL> CREATE TABLE t
NOCOMPRESS
AS
2 WITH
3 t AS (SELECT /*+ materialize */ rownum AS n
4 FROM dual
5 CONNECT BY level <= 1000)
6 SELECT rownum AS n, rpad(' ',500,mod(rownum,15)) AS pad
7 FROM t, t, t
8 WHERE rownum <= 1E7;
SQL> execute dbms_stats.gather_table_stats(ownname=>user, tabname=>'t')
SQL> SELECT table_name, blocks FROM user_tables WHERE table_name = 'T';
TABLE_NAME BLOCKS
---------- ------
T
715474
SQL> SELECT count(n) FROM t;
COUNT(N)
----------
10000000
Elapsed: 00:00:
27.91
SQL> ALTER TABLE t MOVE
COMPRESS
;
SQL> execute dbms_stats.gather_table_stats(ownname=>user, tabname=>'t')
SQL> SELECT table_name, blocks FROM user_tables WHERE table_name = 'T';
TABLE_NAME BLOCKS
---------- ------
T
140367
SQL> SELECT count(n) FROM t;
COUNT(N)
----------
10000000
Elapsed: 00:00:
05.38
SQL> SELECT 715474/140367, 27.91/05.38 FROM dual;
715474/140367 27.91/05.38
------------- -----------
5.09716671 5.18773234
