Thanks for the question, shivam.
Asked: April 22, 2017 - 10:07 am UTC
Last updated: April 22, 2017 - 12:21 pm UTC
Version: Oracle10G
Viewed 1000+ times
You Asked
I am having one table (Table A) that contain 300 Million Records, want to do a data retention activity on basis of some criteria.
Concerning the performance, I planned to create a new table(Table B) with the oldest 10M records from Table-A. Then I can select records from Table-B which matches the criteria and will delete it in Table A.
Extracting 10M records from Table-A and loading into Table-B using SQL Loader and its taking ~5 hours.
Already created Indexes and using parallel 32 wherever applicable.
What I wanted to know is,
- Is there any better way to extract from Table-A and to load it in Table-B.
- Is there any better approach other than creating a temp table(Table-B).
Thanks.
and Connor said...
Why do you need SQL Loader ? If you want to copy 10m rows from A to B, you would just to:
create table B parallel as
select /*+ parallel */ from A
where [criteria]
and if that takes 5 hours...(even running serially) then I'd suspect something is very wrong, but 300million rows can be scanned on almost any hardware much much quicker than that.
When it comes deletion, you need to decide on which is more efficient for you
- deleting 10m rows, versus
- creating a new table with the rows you want to keep
Is this answer out of date? If it is, please let us know via a Comment