Need Suggestion

[jt]

I have a table containing 12.8 Millions of rows and the total bytes=3.5 Gig. I want to move maybe 10% of the most recent data (there is a date column) to a table in another database. This table is not partitioned. What is the fastest way to do this?

[jlakhani]

1. put db in restrict session
2. exp with direct=y
3. then import into new schema

[jt]

Thanks but I don't want to do a regular full exp/imp. No space and no need for it.

[gopi]

Many ways to do this here's one way:

Go to the 'other' (new) database.
Create database link back to the first (old) database - for this example let's call the link 'ORIGINAL_DB_link' and we are moving data that's 30 days old.

1. first copy the data to new location in new database:

CREATE TABLE owner.new_table
SELECT * from owner.old_table@ORIGINAL DB_link
WHERE date_col > sysdate - 30
NOLOGGING


An index on the date would be very helpful.

2. Then you would have to run a delete on the original table to get rid of the 10% created in the new table.

[jt]

But this way it would take FOR EVER! I have 13 million rows. I wouldn't do a create table... as select... on something this huge. At least if I do a regular exp/imp then deleting unneeded rows in the new table would probably be faster, but it will still take a long time to delete 90% of 13 million rows. Is there any better ways to make this faster?

[sanju_dba]

lemme refine Gopi's suggestion for high performance...

-- Create index on the source table.
In the new DB
-- create dblink
-- create rollback segment with enough preallocated space
-- create table with enough preallocated space with Parallel degree ( if applicable )
-- hook the above RBS to this transaction
-- fire a insert statement with APPEND hint and select statement with PARALLEL hint .

Thanx
sanjay

[marist89]

Use the QUERY option to exp to only get the rows you are interested in.

[jt]

Thank you Jeff! This is what I really needed! I figured there must be something like this with export but I checked my Oracle 8 book and there was no such parameter. I guess QUERY only comes with Oracle 8i and above, correct? Luckily my database is 8.1.6 Thanks much!

[marist89]

Yes, this is an 8i feature. Check out http://oradoc.photo.net/ora817/DOC/s...ch01.htm#37997 for details.

[jmodic]

If you are realy concerned with speed and efficiency, then the following is certanly much quicker than any exp/imp variation:

- write a query that selects the needed rows
- spool the resultset of that query to a flat file
- create a simple SQL*Loader controlfile
- load the rows into destination table using DIRECT=Y option.