Partitioning

There would probably be benefits if

- You use local indexes (not strictly necessary, but ideal from a performance standpoint)
- You load data into a staging table with the same structure as the partitioned table
- You build the indexes on the staging table
- You do a partition exchange with the partitioned table to load the staging table

Justin

Is your question if you can speed up the insert by partitioning the target table rather than inserting the same amount into an unpartitioned table?

Are you talking about a single insert or multiple inserts? I assume multiple inserts according to your "Every insert statement..." information.

* Does your target table already contain data?
* Has this data been manipulated by updates/deletes?
* Do you have existing indexes on the target table?

I don't think that you'll be able to save significant amount of time regarding the insert per se, but of course this amount of data can be maintained in a better way if you use a reasonable partitioning.

As already mentioned, you could load a partitioned table partition-wise using the exchange partition feature, but if the table is not in use yet, you can just insert directly into the target partition and create/rebuild indexes afterwards as required.

Apart from the partitioning question, you can always speed up inserts using the following options:

* Use direct-path inserts to avoid undo generation (/*+ APPEND */ hint, but consider the restrictions of direct-path inserts, e.g. no enabled triggers, foreign key constraints etc.) on target table. Note however that direct-path inserts always use blocks above the current high-water mark and therefore don't reuse existing blocks, i.e. if you have a lot of used but rather empty blocks in your table it will be larger after the direct-path insert as necessary, because the empty blocks have not been re-used.
* Use parallel query (mark source object as parallel or use /*+ PARALLEL (<table>) */ hint) on the source object
* Use parallel dml inserts (alter session enable parallel dml, mark target object as parallel or use /*+ PARALLEL (<table>) */ hint in insert) if you have suitable hardware and license, in combination with parallel query on source object
* Set the target object to NOLOGGING to avoid redo generation, but consider the implications regarding recovery or standby databases
* Set indexes to unusable prior to loading or drop and recreate/rebuild them accordingly

Note that some of these options mentioned are kind of "offline" options as you don't want to apply them to a table that is currently being used due to locking and/or index usage issues.

The "exchange partition" option together with "local" indexes is quite useful if you want to manipulate partitions that are currently being queried by other (read-only) processes, because it allows "seamless" transition from old data to new data from a reader's perspective (processes will continue to read the old data after exchanging the partition, new processes will read the new data).

Regards,
Randolf

Oracle related stuff blog:
http://oracle-randolf.blogspot.com/

SQLTools++ for Oracle (Open source Oracle GUI for Windows):
http://www.sqltools-plusplus.org:7676/
http://sourceforge.net/projects/sqlt-pp/

You would consider a direct path load into a staging table with indexes enabled before doing the partition exchange? Or doing a direct path load into the partitioned table itself without doing the partition exchange?

It sounds like you are suggesting the first (direct path load into a staging table with indexes enabled before doing a partition exchange). Have you done benchmarks on this sort of processing? I'd be hard-pressed to understand why a direct-path load would be better at building an index on a previously empty table than just building the index after the load. But I'd be very interested if you're aware of some mechanism I'm not...

Justin