Re: Daily partitioning

1. How does data arrive? In a batch load or OLTP?
2. How much data are you talking about?
3. what do typical queries look like?
4. What's the DB version?

You sound as if you *might* be describing a situation suited for 11g's interval partitioning, but it's rather difficult to tell. It's also extremely likely that your maintenance should not be DELETE but ALTER TABLE .. DROP PARTITION
Niall

On Fri, May 14, 2010 at 11:05 AM, Stefano Cislaghi <s.cislaghi_at_gmail.com>wrote:

> 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
>
>

-- 
Niall Litchfield
Oracle DBA
http://www.orawin.info

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