Re: webmail user mailbox schema design?

Tegiri Nenashi wrote:
> On May 1, 12:01 pm, Michael Austin

>> You, my friend have obviously only ever worked on very small databases.

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.

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.

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
where

     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 Sat May 02 2009 - 05:31:44 CEST