Good idea Gentlemen.
Jim
"Jim Stern" <jdstern_at_k2services.com> wrote in message
news:anvqm2$4qb$1_at_news.utelfla.com...
> 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 …
> > > > - 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 - 20:32:52 CDT
