Re: Loading data (MORE INFO)

Pete_Thomas_at_inmarsat.org schrieb:
>
> Here is some more information as resquested
>
> - Oracle Version ? 8.0
>
> - What is the size of your records?
>
> The data to be loaded will be passed as binary records, which our loader
> (probably Pro*C++) will need to translate to ascii, and insert into 3
> related tables (avg row sizes 38, 48, 20 ).
>
> - How long do you want to store this records?
>
> This data will be stored for 1 mth then archived
>
> - Do you want to store it in one large table
> or do you want to store it in diffreent tables.
>
> Will be stored in 3 related tables where
> parent table has most rows - approx 120,000,000 rows at any point in
> time.
>
> regards
> Pete

Hallo Pete
it seems the best solution to fransfer the data as flat files whith ftp, so you have minimal network overhead. I assume you use a C/C++ programm to read the data into your database so I ignore SQL*Load.

I think you are running in a performance problem. You have abaout 4.848.000 records a day.

If you want to use your data you must create at least one index. This doubles your datavolume. Then ORACLE writes redologs this increases your I/O volume too.

Later you must delete data this double your I/O again. I recogniced that doing insert and delete at the same time against the same table decreases performance dramaticaly.

If you realy need your database 24hours 7 Days a week, you must use archive log modus this increases your I/O again. (Maybe NT is not the best solution for 7*24)

So be very carefull with performance.

Instead using 3 big tables A, B and C I recomend using smaler tables tASitename, tBSitename and tCSitename. You can then create views
create view A AS
select * from tASitename
UNION
select * ...

Maybe you can use partition tables in Version 8.

Best regards
Jens Loebling Received on Fri Jul 02 1999 - 05:22:26 CDT