1

One possible approach to optimize search queries is (a) to store the records that retain data that corresponds to different relations/tables in (b) the same file → on the same pages. That way, a join can be performed much faster.

I've googled "co-clustering" and surprisingly few results showed up. I found nothing on MySQL for example. There is some indication that Oracle offered it 10 years ago. Is co-clustering still a valid option for optimization?

For example, you have two relations/tables:

  • Employee (id, name, age, did)
  • Department (did, location)

A typical query for which you optimize maybe looks something like this:

SELECT E.name, 
       E.age 
  FROM Employee E, 
       Department D 
 WHERE E.age = 25 
   AND E.did = D.did;

If you had 1,000,000 employees and they are all between 25 and 27, the best join method is probably sort-merge join or hash join - both require multiple scans.

Now, if you store tuples/rows of multiple relations/tables on the same page, you can use a physical structure that stores a department with a certain did together with employees with the same did. Notice that such join requires much less IOs.

  • "One possible approach to optimize search queries is to store records of different tables in the same file - on the same pages. That way, a join can be performed much faster." is an over-generalized and unsubstantiated claim. can you explain why you think it is so? – SQLRaptor Sep 5 at 18:50
  • Of course. I've added an example. – sanjihan Sep 5 at 19:29
  • I only know of Oracle that implemented this, and IIRC it's not for searching. And it's not really relevant any more. It has a lot of limitations, and inherently makes scans of either table more expensive. You can materialize a join result on most platforms if you want to using a Materialized View or similar. See docs.oracle.com/cd/E11882_01/server.112/e41573/… – David Browne - Microsoft Sep 5 at 19:37
2

Is co-clustering still a valid option for optimization?

Sure, it is a valid option for optimization, if your DBMS offers it. As David Browne has mentioned in a comment, only Oracle does (which, in a way, tells you how practical this feature is).

As you have noted, it is useful in a very limited set of scenarios, while being detrimental to a wider range of queries. In the cases that might benefit from table co-clustering you can employ alternative optimization techniques, such as materialized (indexed) views or column-organized tables, which offer similar performance benefits while being more widely available.

Consider also that today common use of SSD storage, abundance of cheap RAM on database servers, in combination with better query optimizers, decrease the value of marginal reduction in physical I/O at the cost of possible negative side effects and additional database maintenance overhead.

TLDR: don't bother.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.