snowflakes and ORACLE partitions

View previous topic View next topic Go down

snowflakes and ORACLE partitions

Post  jackd1000 on Thu May 31, 2012 5:37 am

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

jackd1000

Posts : 3
Join date : 2012-05-31

View user profile

Back to top Go down

Re: snowflakes and ORACLE partitions

Post  ngalemmo on Thu May 31, 2012 11:40 am

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.

avatar
ngalemmo

Posts : 3000
Join date : 2009-05-15
Location : Los Angeles

View user profile http://aginity.com

Back to top Go down

Re: snowflakes and ORACLE partitions

Post  jackd1000 on Thu May 31, 2012 11:52 am

Many thanks for that.

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

J

jackd1000

Posts : 3
Join date : 2012-05-31

View user profile

Back to top Go down

Re: snowflakes and ORACLE partitions

Post  ngalemmo on Thu May 31, 2012 1:11 pm

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.
avatar
ngalemmo

Posts : 3000
Join date : 2009-05-15
Location : Los Angeles

View user profile http://aginity.com

Back to top Go down

Re: snowflakes and ORACLE partitions

Post  jackd1000 on Wed Jun 06, 2012 4:50 am

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 ?

jackd1000

Posts : 3
Join date : 2012-05-31

View user profile

Back to top Go down

Re: snowflakes and ORACLE partitions

Post  Sponsored content


Sponsored content


Back to top Go down

View previous topic View next topic Back to top

- Similar topics

 
Permissions in this forum:
You cannot reply to topics in this forum