Re: webmail user mailbox schema design?

From: <cimode_at_hotmail.com>
Date: Fri, 8 May 2009 06:45:08 -0700 (PDT)
Message-ID: <9ccf29ab-da1f-47b0-a698-956d410fd42f_at_m24g2000vbp.googlegroups.com>



On 2 mai, 05:31, Michael Austin <maus..._at_firstdbasource.com> wrote:
> 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
> 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.
While I frequently use partitionning to alleviate the unpractical consequences of administering direct image systems, I would hardly call partitionning an improvement on a fundamental perspective.

Aside from the ego battle on *who knows best* or *who has dealt with the biggest db*, I do not understand the fundamental interest in opposing the subject of *logical* access optimization and the subject of partitionning, a matter exclusively related to direct image system physical layer, on the same standpoint.

This SQL-DBMS only debate simply ignores the principle of independence between the two layers dictated by RM. In other words, partitionning is simply a limitation imposed by direct image systems and so are current indexing schemes.

 I would find it more interesting to discuss how building a TRDBMS would make the need of partitionning data physical representations an unneeded feature.

My two cents...

Regards... Received on Fri May 08 2009 - 08:45:08 CDT

Original text of this message