CODEWITHSUNDEEP
sqloracle-database
Milind
Milind

Reputation: 38

Oracle PL/SQL query regarding handling data

I have a live production table which has more than 1 million records. Now i don't need to tamper anything on this table and would like to create another table which fetches all records from this live production table. I would schedule a job which can take entries from my main table and inserts them to my new table. But i don't want all the records daily; i just need the records added on a daily basis in the production table to get added in my new table. Please suggest a faster and efficient approach.

Upvotes: 0

Views: 25

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269953

Copying one million records from an existing table to a new table should not take very long -- and might even be faster than figuring out what records to copy. You could do something like:

truncate table copytable;

insert into copytable
     select *
     from productiontable;

Note that you should explicitly list the columns when doing the insert.

You can also readily add new records -- assuming you have some form of id on the production table, such as an id assigned by a sequence. Then you can do:

insert into copytable
    select *
    from productiontable p
    where p.id > (select max(id) from copytable);

Upvotes: 1

Jeff Watkins
Jeff Watkins

Reputation: 6359

You could do this with an INSERT/UPDATE/DELETE trigger to send the INSERTED/UPDATED/DELETED row to the new table, however this feels like reinventing the wheel on the most basic level.

You could just use asynchronous replication rather than hand-rolling it all yourself, this is probably safer, more sustainable and scalable. You could add as many tables as you like to the replicated source.

Upvotes: 1

Related Questions