Re: Backup and partitions

Howard,

I have to disagree with the strength of your disapproval. It's only a million rows, and it's in a batch overnight. Hardware RAID-5 is often perfectly adequate for large data loads, and only on small fussy systems with lots of little writes is RAID-5 likely to be a real threat.

The best reason for not using RAID-5 these days is that it isn't really much cheaper than a suitable RAID-10 configuration.

Alex,
As far as the partitioning goes, there are three main points to consider. First, partition the data according to the most frequent access pattern - if user wants a week (Mon-Sun) of data, partition into weeks, if they want the odd day, partition by day. 500 is getting a little near the borderline for partitioning, but is still okay if you don't have too many indexes.

Second, make sure that all your indexes are local indexes, otherwise you lose the administrative benefits of partitioning.

Third, the primary key of a partitioned table has to contain the partitioning columns if the corresponding index is to be locally partitioned.

For backups - if you have partitioned by date, then build tablespaces per week or month. Then most of your old tablespaces can be read-only, and you need only back them up once after they have switched. From that point on, you can probably fit a hot backup of the rest of the database into the overnight batch, though, as Howard indicates, a cold backup would be nicer if you can do it - especially as your notes suggest that you could simply copy back the previous night's backup and reload the data in the event of a crash.

--
Jonathan Lewis
Yet another Oracle-related web site:  http://www.jlcomp.demon.co.uk

Practical Oracle 8i:  Building Efficient Databases
Publishers:  Addison-Wesley

Reviews at: http://www.jlcomp.demon.co.uk/book_rev.html



Howard J. Rogers wrote in message
<1NEi6.203$305.67479_at_inet16.us.oracle.com>...


>

>You are proposing to write 1,000,000 rows per night to a database using

>RADI5?   You *have* read all the RAID 5 arguments that gone through this

>group over the past few months, have you?

>

>If you haven't, the short version goes like this: RAID 5 cripples systems

>that need to write.  Oracle warns you off using them.  They are getting

>better all the time, but nevertheless, there remains a significant write

>penalty.

>






><alex.berindei_at_elsag.it> wrote in message

>news:96dqjd$pd2$1_at_news.netmar.com...

 


>> I'm thinking to partition the table OPERATIONS by DATE, as rows inserted

 each


>> night have all previous day. Problems here:

>> - how big to make the partitions (week: 1Gb, month: 4Gb)?

>> - is a good idea to partition also BOXES and, eventually, BIGBOXES

 tables?


>> - there is a numerical column OPERATIONS.DIPENDENCY, range 1 to 1000,

 used
 in


>> most selects from OPERAZIONE. An option could be to partition the table

 using


>> two colums (DATE, DIPENDENCY), with larger ranges for DATE: (DATE 3

 mounts,


>> DIPENDENCY: 10 ranges 1-100,101-200,...).

>> This way I cand create local prefixed indexes in partitions, using colums

>> (DATE, DIPENDENCY)

>>