Re: design advice

curwen wrote:

> Hi all,
> I'd like to have you opinion about the following performance/design
> issue:
>
> a logging system is generating a lot of records every our,(about
> 500k/day)
> I need to store them in a huge Oracle RAC system
> a Java web application connects to the data and executes queries on
> them, mainly on last entered set of records
> queries on informations stored more then 3 months ago are quite rare
>
> as first guess I managed to store them in a table indexed by a
> primary key, let's say in the table 'LOGS'
> the problem is that table is going to grows very big,
>
> have you got 'best practice' ideas to handle the situation in a better
> way?
>
> I thought about moving data priodically in a 'LOGS_ARCHIVE' table ,
> for example using a Oracle job
> is it necessary?
> PS I can't use Oracle standard file archiving support, I need data to
> stay on tables
>
> thanks in advance
> jc

I recently consulted on a system that has many similarities to what you describe and it was a complete disaster. I don't know enough from your post to comment on your specific case but here are some things to keep in mind.

1. RAC requires a specific architecture to be successful. If what you have will require a lot of block sharing between nodes ... RAC may make things worse ... not better.
2. When you say "last entered set of records" does this mean, and I suspect it does, every query requires a full table scan? If so you are in for a world of pain.
3. Are your indexes reversed? Hashed?
4. Are you range partitioning? If not ... why not?

Contact me off-line if you wish.

-- 
Daniel Morgan
http://www.outreach.washington.edu/ext/certificates/oad/oad_crs.asp
http://www.outreach.washington.edu/ext/certificates/aoa/aoa_crs.asp
damorgan_at_x.washington.edu
(replace 'x' with a 'u' to reply)