Re: Convert SAP Oracle Database to IBM DB2 Database??
Date: Sat, 09 Feb 2008 17:24:26 -0800
Message-ID: <1202606635.186907@bubbleator.drizzle.com>
Serge Rielau wrote:
> 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).
Depends on the data. Have you looked at bank data? phone company data? online reseller data? It isn't as random as you appear to think. Consider, for example, the clustering factor on an index. That same table that has address information to which you refer also has cities, state/provinces, first names, and lots of other very predictably repeating values. And, yes, at the block level.
What I am surprised you have not yet given voice to is the implication, in Oracle, of using small blocks versus large blocks.
> 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.
Are you familiar with Benford's Law? If not: http://users.skynet.be/albert.frank/benfordslaw2.htm
> Of course 8 blocks and a few thousand rows are insufficient for any > meaningful measurement on the topic. > > Cheers > Serge
If I wasn't building a Customer Service application this month for one of Oracle's new customers ... a publicly traded public utility ... F5 load balancers, clustered app servers, RAC clusters, etc., and preparing to speak at the Rocky Mountain OUG and Northern California OUG in the next two weeks I'd further accommodate you. <g>
I'll be in Redwood Shores on the 18th of the month. Perhaps you could fly in and join us.
-- 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.orgReceived on Sat Feb 09 2008 - 19:24:26 CST