Re: 90GB table on Windows 2000

I agree with Jeff, partition the tables and indexes by an archival method you will be using to purge data (ie, weekly or monthly ), you might also look into using replication to move a copy of the data to a DSS server... (one way propagation).

Jim

"Jeff Kommers" <kommers_at_ll.mit.edu> wrote in message news:76Eo9.93$I7.6228_at_llslave.llan.ll.mit.edu...
> The index creation is an interesting problem that I've dealt with
> before in a very similar situation. Basically we had point-of-sale
> records and we had indexed on the date/time column and a
> transaction key column. It is a huge pain to have to re-index
> 3 billion rows, and the operation tends to fail owing to out-of-temp
> space problems.
>
> Here's a suggestion that works if your data updates are incremental
> and don't affect any existing data (e.g. loading a new day of sales
> records in). Partition your table and indexes. You can use
> built-in features for this or "do-it-yourself" in the following way.
> Create a set of tables with identical schemas for each time period,
> e.g. "create table week1 (...); create table week2 (...);" etc.
> Then index each table separately. "create index idxweek1 on
> week1(...); create index idxweek2 on week2(...);" etc. Access your
> data through a view that is the union of your tables: "create
> view mydata as select * from week1 union all week2 union all ...." etc.
> It's pretty quick for the optimizer to find which table has the data
> relevant to a given query as long as it can use the index lookups. Now
> you can drop/create/index/whatever your data in weekly chunks that
> are much easier to manage. In particular, if you bulk-load a new day's
> worth
> of data, then re-indexing the current week (maybe
> 60 million rows) of your 3 billion rows of data is much faster than
> re-indexing
> *everything* just because you loaded one more day's data into your
> database.
>
> Good luck
>
> Jeff
>
>
> "Jim Kennedy" <kennedy-family_at_attbi.com> wrote in message
> news:fQBo9.11290$ST4.26543_at_rwcrnsc53...
> > 1. Post the query and the explain plan and the tkprof.
> > 2. More disks would probably speed it up.
> > 3. Try to compress the index. alter index blah compress; (it will
rebuild
> > it, but redundant entries will be compressed and faster for use for
> queries,
> > inserts might slow down a little.)
> > Jim
> >
> > "Andras Kovacs" <andkovacs_at_yahoo.com> wrote in message
> > news:412ebb69.0210080408.1777a9f2_at_posting.google.com...
> > > I am having problems to maintain a very large database. In fact the
> > > product has just been installed and we are trying to fine tune the
> > > application. All the transactions are recorded into a single table
> > > that can't be split (but can be partitioned).
> > >
> > > Here are the facts :
> > > - This table has 6 attributes and 1 index on 2 of the attributes,
> > > structure is very simple.
> > > - No more than 10 concurrent users on the system to query data
> > > - Table records 6000 rows /min and it grows 246 MB/day
> > > - Its size by the end of the year is expected to be 90 GB and
> > > it will hold 3 153 600 000 rows &#8230;
> > > - At the end of the year all data older than 1 year will be archived
> > > in another table
> > > - So this table will not grow beyond 90GB
> > > - We are going to upgrade Oracle to the Enterprise edition
> > > for partitioning and bitmap indexes
> > > - The system runs on Window 2000 advanced server
> > > - We have a Compaq hardware with two 1266 MHZ CPUs
> > > - 2,4 GB RAM
> > > - Controller N1: (Disk C 17 GB Raid 1: hold OS),
> > > (Disk D 17 GB Raid 0: holds temp space)
> > > (Disk E 17 GB Raid 1: holds Oracle )
> > > - Controller N2: (Disk F 34 GB Raid 5: holds indexes)
> > > (Disk H 101GB Raid 0+1: holds data)
> > >
> > >
> > > My questions are :
> > >
> > > 1. What type of backup should we use ? (We are thinking about
> > > replication
> > > and incremental backups or maybe a third machine)
> > >
> > > 2. Our write performance is very good. However we have some problems
> > > with
> > > reads (at present we have 15GB of data and 320 000 000 rows). The
> > > first
> > > read for a given query takes 52 seconds. Then the second time the
> > > query
> > > runs in 12 seconds with a 100% cache hit ratio. What type of
> > > hardware
> > > (controllers and disks) should we use to improve performance (52
> > > seconds)?
> > > Is there any thing to do to reduce these 12 seconds cache reads ?
> > >
> > > 3. I have tried to rebuild the index on the table after having dropped
> > > it.
> > > It is still running ... I had to configure a 15GB temporary table
> > > space.
> > > Any advise to speed up the index reconstruction ?
> > >
> > > 4. What would be the benefit of switching from NT to Unix ?
> > >
> > > 5. If somebody has a similar sized system, could you indicate us what
> > > type
> > > of hardware you have ?
> > >
> > > Thanks for your time, Andras
> >
> >
>
>
Received on Tue Oct 08 2002 - 18:45:02 CDT