Re: Partitioning best practices
- From: "jaromir nemec" <jaromir@xxxxxxxxxxxx>
- To: <tboss@xxxxxxxxxxxxxxxxxx>, <anuragdba@xxxxxxxxx>, <oracle-l@xxxxxxxxxxxxx>
- Date: Sun, 3 Sep 2006 21:41:52 +0200
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@xxxxxxxxxxxxxxxxxx>
To: <anuragdba@xxxxxxxxx>
Cc: <oracle-l@xxxxxxxxxxxxx>
Sent: Friday, September 01, 2006 9:08 PM
Subject: Re: Partitioning best practices
--
//www.freelists.org/webpage/oracle-l
Other related posts:
