Re: Extracting 2.5 million rows
Date: Fri, 8 Jan 2010 00:41:49 -0800 (PST)
Message-ID: <703c8462-8f34-4b4d-8fcb-30e4c7d4e2e0_at_u7g2000yqm.googlegroups.com>
I suggest creating indexes (online) in the production database and doing analysis right there (assuming there is space for indexes). Another option is to create summary table/materialized view and use it for analysis. If your production box is big enough then running one query at a time shouldn't affect performane for other queries. The only disadvantage is that you may not be able to create bitmap indexes on the audit table as they don't allow multiple simultaneous updates.
If this is not possible then transportable tablespaces are the best option for moving entire table.
Another alternative is Data Pump (assuming this is 10g or 11g). The
advantage is that you don't need to export entire table,
export a small subset first and play with it, once you are happy with
it then run it for entire table.
Data Pump can be used to select data from one database and
simultaneously insert into another,
without using any dump file.
The same approach can be used with Export - it has parameter QUERY that alows to select part of the table.
Finally SQL*Plus can also be used to select subset of data, either by
specifying WHERE clause or by
using SAMPLE clause of SELECT.
Many systems run on weekly cycle, large historic table may get 10 mil
records on Monday, 11 on Tuesday,
13 on Wednesday, 9 on Thursday and 10 on Friday, then 5 on Saturday
and 3 on Sunday. So start from analyzing one day, then on week.
Received on Fri Jan 08 2010 - 02:41:49 CST