Re: Convert SAP Oracle Database to IBM DB2 Database??

Serge Rielau wrote:

> Data in a page is typically a random collection.
> Lets take an orders table. Addresses and names of customers will be random.

To some extent this may be true but a decent statistician will tell you otherwise.

Let's take, for example a simple table belonging to an Oracle Users Group.

CREATE TABLE test AS
SELECT sys_op_map_nonnull(per_address1)
FROM person
WHERE per_address1 IS NOT NULL;

SQL> SELECT COUNT(*) FROM test;

   COUNT(*)

       1559

SQL> SELECT COUNT(*)
   2 FROM test
   3 WHERE INSTR(testcol, '000', 1, 1) <> 0;

   COUNT(*)

        252

SQL> SELECT COUNT(*)
   2 FROM test
   3 WHERE INSTR(testcol, '4E4', 1, 1) <> 0;

   COUNT(*)

        325

SQL> exec dbms_stats.gather_table_stats(USER, 'TEST');

PL/SQL procedure successfully completed.

SQL> exec dbms_stats.gather_table_stats(USER, 'TESTCOMP');

PL/SQL procedure successfully completed.

SQL> SELECT table_name, blocks

   2 FROM user_tables
   3 WHERE table_name IN ('TEST', 'TESTCOMP');

TABLE_NAME                         BLOCKS
------------------------------ ----------
TEST                                    9
TESTCOMP                                8

It isn't 3:1 compression but it isn't insignificant either.

-- 
Daniel A. Morgan
Oracle Ace Director & Instructor
University of Washington
damorgan_at_x.washington.edu (replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org