Re: Partitioning best practices

Hi,

> Also ensure that any OLAP tools properly join the dimension
> that controls the partiting field and properly presents that dimension as
> a filter option to the user. If these two key items are not followed,
> you'll
> end up hash scanning every partition in your table for every query
> and performance will be a nightmare.

I'd formulate it even more straightforward: "Don't use surrogate keys for dimensions used as a partition key".
The surrogate key, though the preferred DW modelling methodology can be very problematic as a partition key.
 (Surrogate key - the fact table doesn't contain the natural dimension key, instead an artificial surrogate key is defining the association to the dimension table; the natural key is stored in the dimension table only).

The possible pitfalls on range partitioned tables using surrogate keys range from:
a) an equal predicate on dimension key with a literal value leads to runtime partition pruning KEY-KEY instead of parse time pruning (i.e. the partition level statistics are not used) b) a range predicate on dimension key leads (using hash join) to no pruning in the fact table at all (as the surrogate keys don't necessary preserve the order of the natural keys)

Regards,

Jaromir

• Original Message ----- From: <tboss_at_bossconsulting.com> To: <anuragdba_at_gmail.com> Cc: <oracle-l_at_freelists.org> Sent: Friday, September 01, 2006 9:08 PM Subject: Re: Partitioning best practices

--
http://www.freelists.org/webpage/oracle-l