snowflakes and ORACLE partitions

We have a slice and dice tool driven by MDX. We can't control the SQL it generates that closely - it's bases mainly on data relationships although there are points where you can sneak in your own SQL.

Our users query the database typically by product and/or date. Usually date is a query predicate but sometimes it's absent altogether.

We'd like to use ORACLE partitions and partition by date - but I'm concerned that this might have a deleterious effect on product only searches.

I thought about redifining the primary key for product to include the first date that sales occurred. That way, even if the user queries only the product, we can link to the partitions by the 'birthday' of the product - and select from all partitios greater than the birthday.

However this isn't going to work : the MDX schema modeller generates joins based on primary key. It will join on the fact table "where product = 'x' and date = 'birthday'". What we want is "where product = 'x' and date >= 'birthday'". The "greater than" won't get generated.

We can use snowflakes though, and it occured to me that I could snowflake the product dimension to use a products-partitions intersection entity. That way we keep the primary key on the product dimension, but can access the partitions indirectly through the date component of the intersection entity.

Does anybody have any experience of this in ORACLE ? Are the main objections to useing snowflakes manageability or performance ? For us I think this would be a performance improvement.

J

Leave it alone. Yeah, a query unbounded by date will run longer than one that isn't. Such is the nature of things. Partition by transaction date and forget about it. Don't shoot yourself in the foot trying to be cleaver. Also, snowflakes really mess up the star schema optimizer.

Many thanks for that.

i thought as much. So just retain a single global index on the product id ?

J

If you are partitioning, use local indexes, particularly if you are talking about the bitmap index on the fact table FK.

You normally do not partition dimension tables unless you are dealing with very, very large dimensions.

Sorry - yes, the product id would be on the fact table. The product dimension is not big enough to partition.

If the number of rows returned per product id is relatively low, is there a case for a global bitmap index , or is this not possible ?