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

From: Serge Rielau <srielau_at_ca.ibm.com>
Date: Sat, 09 Feb 2008 16:28:02 -0500
Message-ID: <616k7fF1u1hg0U1@mid.individual.net>

DA Morgan wrote:
> 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 I fail to see how your test relates to my hypothesis (or Marks). I did not state that data values are random overall. I stated that there presence in particular blocks is random: I.e. it is not clustered according to its physical location (block). To debunk my hypothesis you would need to show that the data data in the 8 blocks has statistically different distribution between blocks. That would support benefits of localized compression dictionaries to accommodate for it.
Of course 8 blocks and a few thousand rows are insufficient for any meaningful measurement on the topic.

Cheers
Serge

-- 
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

Received on Sat Feb 09 2008 - 15:28:02 CST