Re: webmail user mailbox schema design?

From: Michael Austin <>
Date: Fri, 01 May 2009 22:31:44 -0500
Message-ID: <O8PKl.28355$>

Tegiri Nenashi wrote:
> On May 1, 12:01 pm, Michael Austin
>> You, my friend have obviously only ever worked on very small databases.

> Define "small"...
> "Terradata" -- a spectacular name 10 years ago sounds pretty ordinary
> today.

Terradata is a HARDWARE partitioning of data - at least the last time I looked... My Oracle DB bigger than a Terradata in the same company - well, due "economic conditions and poor WS performance" I am no longer there.. oh well..

Exadata (New Oracle beast) is also a hardware partitioning+db partitioning and maybe some indexing...

As for the definition of "small"
Medium is < 20TB
Small is < 1TB.
Tiny is < 500G.
IttyBitty is < 50G.

Then there is large and HUGE.

>> In a DW enviroment, you would be insane to not use partitions.

> Ah, the one that stubbornly resist indexing. Why indexing by the
> record time is such a bad idea? For example, why table Sales
> partitioned by the TransactionTime column can't be index organized?

Who's stubborn? Not I. Not only do I use partitioning on things of this size I also index.


>> When you need to query with this much data, being able to restrict your
>> search to a single or even a couple of partitions - you WILL become a
>> believer in the use of partitions.

> Again, from query access path perspective partitioning looks like
> rudimentary indexing. Because everything that partition pruning does

One could argue that, but they would be wrong.

> index range scan does better. Yet, implementation of this rather
> unsophisticated idea of chopping one table into many is not stellar: I
> have seen optimizer failing to do partition pruning about as often as
> it failing to find an indexed access path.

While in some cases this is true, you have to learn to finesse the optimizer. Some of the hints work some of the time, but not always. Query "building" is not just throwing a bunch of tables together in JOINS just because that is what is needed, in order to finesse the optimizer successfully, one must give it something to work with. And to do that, you have to figure out what it is looking for.

I have written about this before in this forum, but bears repeating. Have you ever had a query that no matter what index you had or what hint you had for a given column in the WHERE clause, the CBO just would not pick that index - (you need to remember that "hints" are just that... and the CBO can and in some cases DOES ignore them).

While at DEC and working with Rdb Regional Field Support, we had one such case. We initially "fixed" the problem by duplicating that statement... Again, this may not work in all cases, but I have solved similar problems in Oracle version 8/9 and 10 using this method...

Example (terse pseudo-code version):

select a,b,c from a,b,c
where a.d = 'something'
and a.b = 'somethingelse'
and b.a=a.a
and a.a=a.a

Index is on a.d but it just will not use it...

Example (terse "improved" pseudo-code version):

select a,b,c from a,b,c

     a.d = 'something'
and a.d = 'something'
and a.b = 'somethingelse'
and b.a=a.a
and a.a=a.a

Then the CBO gives a higher ranking to a.d and says "Oh - you really wanted to use that index". The last time I used this, we had a query that went from ~20-30 minutes to < 3.

BTW, the world lost a real genius at the passing of Gennady Anteshenkov - the author of the CBO - although there have been lots of improvements over the years, without his insight, it would not be where it is today. Received on Fri May 01 2009 - 22:31:44 CDT

Original text of this message