design question, use of partitioned tables?

OS: SuSe Enterprise Server SLES-8
RDBMS: Oracle Standard Edition 9.2.0.3.0 DB Size: 1 TB
Tablespaces: Locally managed, uniform size 8 MB Datafiles: all 32 GB

Dear all,
our application is based on Oracle Text. Each month we insert about 3 million new records (including html files as BLOBs) via sqlldr in our main table, after Oracle Text index creation
(each month new indexes are build) duplicate old entries are deleted
(about 2 million records).

Currently the main table consists of 17 million records, temporarily about 20 millions.
After 5 months we observed very slow loading rates. In order to investigate table fragmentation as a potential source I set up a new tablespace, an empty table and started the load with the same data. Load performance was high, so I supposed we have a problem with table fragmentation.
Currently I perform a load of 17 Mio records, which will take about 5 days. This is far beyond my time frame.

Our current application design which I inherited isn't scalable at this dimension.

I read about partioned tables, but lack practical experience. May use of this technique be advantageous? Any caveats to watch out or other ideas how to deal with this bottleneck?
Any suggestions are welcome, TIA and have a nice day.

M.Mueller Received on Tue Aug 05 2003 - 06:39:47 CDT