Re: [HACKERS] Dreaming About Redesigning SQL

[Lauri Pietarinen]

Bob Badour wrote:

"Lauri Pietarinen" <[EMAIL PROTECTED]> wrote in message
news:[EMAIL PROTECTED]
 

Bob Badour wrote:

   

"Lauri Pietarinen" <[EMAIL PROTECTED]> wrote in message
news:[EMAIL PROTECTED]
     

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

---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend