OK, so while our bitmap scan allows multiple indexes to be joined to get to heap rows, a star joins allows multiple dimensions _tables_ to be joined to index into a larger main fact table --- interesting. Added to TODO: * Allow star join optimizations While our bitmap scan allows multiple indexes to be joined to get to heap rows, a star joins allows multiple dimension _tables_ to be joined to index into a larger main fact table. The join is usually performed by either creating a cartesian product of all the dimmension tables and doing a single join on that product or using subselects to create bitmaps of each dimmension table match and merge the bitmaps to perform the join on the fact table. --------------------------------------------------------------------------- Simon Riggs wrote: > On Fri, 2005-12-16 at 23:28 -0500, Bruce Momjian wrote: > > How are star joins different from what we do now? > > Various ways of doing them, but all use plans that you wouldn't have > come up with via normal join planning. > > Methods: > 1. join all N small tables together in a cartesian product, then join to > main Large table once (rather than N times) > 2. transform joins into subselects, then return subselect rows via an > index bitmap. Joins are performed via a bitmap addition process. > > You can fake (1) yourself with a temporary table, and the basics for (2) > are now in place also. > > The characteristics of these joins make them suitable for large Data > Warehouses with Fact-Dimension style designs. > > Many RDBMS have this, but we need to be careful of patent claims. I'm > sure there's a way through that, but I'm not looking for it yet. Anybody > else wishing to assist with a detailed analysis would be much > appreciated. > > Best Regards, Simon Riggs > -- Bruce Momjian | http://candle.pha.pa.us pgman@xxxxxxxxxxxxxxxx | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup. | Newtown Square, Pennsylvania 19073
