Re: Dreaming About Redesigning SQL

"Lauri Pietarinen" <lauri(dot)pietarinen(at)atbusiness(dot)com> wrote in message
news:bn3tve$qln$1(at)nyytiset(dot)pp(dot)htv(dot)fi(dot)(dot)(dot)
> Bob Badour wrote:
>
> >"Lauri Pietarinen" <lauri(dot)pietarinen(at)atbusiness(dot)com> wrote in message
> >news:3F94BCBB(dot)7030001(at)atbusiness(dot)com(dot)(dot)(dot)
> >
> >>I could now denormalise OrderDetail so that it contains cust_id also
> >>and cluster by cust_id
> >>(might cause you trouble down the road, if you can change the customer
> >>of an order), in which case, with 3 I/O's I would get
> >>- 8 customer rows
> >>- 16 order rows
> >>- 24 order detail rows (which would all apply to one customer)
> >
> >Depending on block size, by clustering the three tables together, one
might
> >get all of those rows for a single read potentially improving on Wol's
> >numbers by a factor of eight or more for this one query. Of course, doing
so
> >would increase the cost of a table scan on the customer table.
> >
> Which DBMS'es support clustering of mutiple tables except for Oracle?

I don't know. Why would it matter?

> Is this feature really used any more?

If one has a hard performance requirement that only clustering can meet, one
will use it.

> I thought it was more trouble than worth.

All physical structures will bias performance for some operations and
against others. In general, increasing the cost of customer scans will be
sufficiently unpleasant to make clustering customers with orders
undesirable. However, if one chooses to consider only one physical
arrangement and one operations, as Wol is wont to do, I observe we can
outperform his product by a factor of eight.