Re: Index - Partition design confusion..

From: Bobak, Mark <Mark.Bobak_at_proquest.com>
Date: Sun, 13 Mar 2011 14:13:40 -0400
Message-ID: <4D5C2062-7079-4DE7-AD43-F1E746CC480D_at_proquest.com>

Ok, 7 days. How many records per partition (day)? If there's only 7 days/partitions, is that really too many to scan when you have a query that doesn't specify partition key? What percent of queries will not specify partition key? Is this a warehouse or DSS? If so, can you use parallel query to get the performance you need? (Consider concurrent users and CPU count.)

Lots of questions, perhaps some of them will get you thinking.

Sent from my iPhone

On Mar 13, 2011, at 12:53 PM, "Gokul Kumar Gopal" <gokulkumar.gopal_at_gmail.com> 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 Sun Mar 13 2011 - 13:13:40 CDT