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 -- //www.freelists.org/webpage/oracle-l
-- //www.freelists.org/webpage/oracle-l
