On Tue, 17 Jul 2001 09:27:38 +1200, you wrote:
>> So how long does an insert take if done on a blank/new table with the same
>> structure??
>
>Quite lengthy. When table was only a couple of million records, this could take
>5-8+ hours plus if the blank table had indices. However, SQL*LDR could shove
>into a new blank table in minutes.
>
>> 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.
>
>Hmm, a union query - this would simpler than partitioning and retain the fast
>select times for most usage. Now this is something well worth trying.
Create a view which is a union of several (many) batch tables.
Bulk load each batch of data into a smaller (empty?) table & then
enable the indices. Add the new table to the view. You may want to
trade off ease of deleting a day's or week's data against load speed
by making each constituent table for a time period, so it is quick to
delete (not quite so quick to load).
Applications use the view (only), to give a single.
This technique is also very performant when it comes time to do bulk
deletes (where these match the original batches) - simply modify the
view & drop the table. I have seen this used for removing monthly
data in a hurry - very much faster than doing any form of row delete.
>
>
>----------------------------------------------------------
>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/
---------------------------------------------------------------------------
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/
