Re: Index - Partition design confusion..

From: Yechiel Adar <adar666_at_inter.net.il>
Date: Mon, 14 Mar 2011 08:49:47 +0200
Message-id: <4D7DBA8B.5030306_at_inter.net.il>

You can make it partitioned by date (for retention) sub partition by network id (for reporting).

This way you can drop partition each day while scanning only the requested network ids in the sub partition.

Yechiel Adar
Israel

On 13/03/2011 19:49, Gokul Kumar Gopal wrote:
> Hello List,
>
> I have to design a table for reporting purposes with no down time at all.
>
> The table must have retention period of about 7 days.
>
> Consider:
>
> Table : Network_Elements
> Columns: networkid number, action id number, action1
> varchar2(100)..... upto 20 columns
>
> Now, I can add a date column to this and make this a daily partitioned
> table to solve the retention requirement.
>
> To make this highly available, I create partitioned indexes on
> networkid (for reporting purposes). The problem with this that
> for any querying to be efficient it has to do a parallel scan of all
> partitions (or serial scan of all partitions which can take time).
>
> Creating global indexes can lead to index rebuilds after partition
> maintenance. This is not desirable as the table must be available at
> all times.
> (or no downtime on this table).
>
> Is there anyway to design this table to solve both the retention and
> performance requirements ?
>
> Rgds,
> Gokul
> --
> http://www.freelists.org/webpage/oracle-l
>
>
>

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

Received on Mon Mar 14 2011 - 01:49:47 CDT