Re: Partitioning question
- From: tboss@xxxxxxxxxxxxxxxxxx
- To: brian.peasey@xxxxxxxxx
- Date: Wed, 12 Oct 2005 11:26:06 -0400 (EDT)
Licensing issues aside, implementing partitioning requires some careful
planning and thought, else you can actually harm yourself and your
database performance more than you can help (some of this is more generic
Data Warehousing, but all relates to the use of partitioning in a DW)
Some key decisions you'll have to make:
- Your partition key. Critical to the implementation, you need to choose
a partitioning field that makes sense with your business functions. How are
your queries accessing your data? Do you do a lot of queries in the DW
that are time-period specific? If so, partition on a month or time-period
based field. By area/region? Partition by state or some geographic locale.
If you're constantly scanning through dozens of partitions for a specific
query, no specific partitioning method may help.
If you don't choose a partitioning key wisely, then your database may
end up scanning every partition for every query, to find the information
it needs, before using just that partition to resolve the query (unless
you're using global indexes, see below)
- the type of partitioning you use: range, list, hash, or a composite
method using subpartitions. Consider your disk arrays and the I/O makeup
to make sure you're implementing properly. If you use a hash, Oracle
balances your data evenly across partitions and a lot of management is
removed. If you use a range or list, you'll have to load balance among
file systems/tablespaces so you don't have skewed data sets.
- the use of parallel server and parallel options on your tables/indexes.
- Indexing strategy: if you depend on bitmap indexes (and since you're
a DW, read up on star transformations with partitioning and bitmap indexes)
then they need to be local ... but local indexes can't help you find
data that crosses partitions. Global indexes span partitions, but preclude
you from doing partition exchanges for data loading.
- Data loading into your DW: the oracle designed way is to make use of
a separate staging server, then move the data to the warehouse server via
transportable tablespaces, prebuild indexes and gather stats, then
alter table exchange partition to load the data. Designing and developing
this etl process can be tricky. If you decide to load data directly into
the warehouse table from an ETL tool, consider how long a maintenance window
you have to rebuild indexes, re-gather stats, and rebuild aggregate tables/MVs
off that new data.
my 2 cents from working in a DW world,
Todd
>
> Hi again,
>
> I've been reading the "Database Data Warehousing Guide" and just
> wonder if there's anything else needed (other than licensing ;-) to
> implement partitioning. Is it just done by adding partitioning clauses
> to the create table statements?
>
> Thx Much,
> Brian Peasey
> --
> //www.freelists.org/webpage/oracle-l
>
--
//www.freelists.org/webpage/oracle-l
Other related posts:
