So how long does an insert take if done on a blank/new table with the same
structure??
Where am I heading ...
2 Possible solutions - variations on a theme.
A. Insert into a logging table, then from this do a normal insert into the
main table without dropping indexes etc. Change all your queries to
incorporate the main and "logging/insert" table.
B. Variation on A.
Always insert into a new table (with unique name) that has the same
structure and then dynamically rewrite your queries to incorporate the new
table. eg Always call stored procedures as use these as your interface, but
behind the scenes rewrite the stored procedures as required (Suspect A is
easier and maybe faster).
The above solutions do assume that a insert on a small table (one that is
empty) is much faster than a already populated table.
Myles.
-----Original Message-----
From: Phil Scadden [mailto:[EMAIL PROTECTED]]
Sent: Monday, 16 July 2001 4:54 p.m.
To: Myles Penlington
Cc: [EMAIL PROTECTED]
Subject: RE: [DUG]: Single MASSIVE table. Recommendations?
> It's sounds rather specialised - can you do it with disabling the indexes?
With Sqlldr - I disable (rather than drop) index, load, than reenable. This
is
much faster than recreating the indices from scratch. (3 hours on fast
unix).
However, running into an Oracle bug with this at moment.
> Sounds like you really need a different design to beat the problem, it's
> either that or buy a very fast unix box ...
Pretty hard to see a different design within a relational database. However
we
are looking at a non-RDBMS solution built for this kind of data.
> What speed trade offs can you accept??
> eg Slow insert versus fast query.
Already have that! 3 hours for an insert is really below my tolerance level
though.
> So is 2 billion records in a table enough, how often are the bulk inserts
> done?
Maybe a couple of times a week on average. 4 in a day followed by maybe a
week
or two without any.
----------------------------------------------------------
Phil Scadden, Institute of Geological and Nuclear Sciences
41 Bell Rd South, PO Box 30368, Lower Hutt, New Zealand
Ph +64 4 5704821, fax +64 4 5704603
---------------------------------------------------------------------------
New Zealand Delphi Users group - Delphi List - [EMAIL PROTECTED]
Website: http://www.delphi.org.nz
To UnSub, send email to: [EMAIL PROTECTED]
with body of "unsubscribe delphi"
Web Archive at: http://www.mail-archive.com/delphi%40delphi.org.nz/
