Re: Dreaming About Redesigning SQL

Bob Badour wrote:

>"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?
>
Just curious...

>>Is this feature really used any more?
>>
>>
>
>If one has a hard performance requirement that only clustering can meet, one
>will use it.
>

OK

>>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.
>
OK, right...

Lauri