Re: Daily partitioning
- From: japplewhite@xxxxxxxxxxxxx
- To: Oracle L <oracle-l@xxxxxxxxxxxxx>
- Date: Fri, 14 May 2010 09:39:46 -0500
Stefano,
Why do you think 31 partitions are too many to manage? We have several
tables with hundreds of partitions - keyed on date or some other ID. We
usually go ahead and pre-create all the partitions we think we'll need for
a good while (just one extent per empty partition segment) so the
maintenance is minimal. We have scripts that we do simple edits on and
run when we need to add a new set of partitions or drop a no-longer-used
set. We also do the same for all the partitioned local indexes (only kind
we have) on the partitioned tables. It's pretty easy.
Script everything and don't worry too much about maintenance.
Jack C. Applewhite - Database Administrator
Austin I.S.D. - MIS Department
512.414.9250 (wk) / 512.935.5929 (pager)
From: Stefano Cislaghi <s.cislaghi@xxxxxxxxx>
To: Oracle L <oracle-l@xxxxxxxxxxxxx>
Date: 05/14/2010 05:06 AM
Subject: Daily partitioning
Sent by: oracle-l-bounce@xxxxxxxxxxxxx
Hi all,
just a question to discuss. I've a table want to be partitioned. Partition
key should be a date field.
This table will retain data for last 40 days from sysdate, i.e. delete *
from mytable where date < sysdate-40
Managing a partition for each day is too big ... 31 partitions.
There's a way with virtual column reference the day of week and creating 7
partition...
Hash partition maybe useful ... but
... but how optimizer will use efficiently a partition based on virtual
column for day of week when the query reference only the full date field?
and also..how will use using hash partitions?
Any suggestion will be appreciated
Stefano
--
http://www.stefanocislaghi.eu
Other related posts:
