Deleting/inserting large number of records into a table

I have a table XYZ_TBL. The properties of the table are as follows.

No of Columns: 27

No of Rows: Approx 3.4 Milliion Rows

No of new rows inserted when the script runs: approx 250K

No of rows deleted/purged (older than 12 months): 250K approx

There are indexes on following 6 columns with discount of each column approx

dstid                    3,
acctid                  204346
acct_period           16 --- this is basically year/month in YYYYMM format
 svc_id                    338957
 emi_cd                  5
 premiseid               202690
 run_date_tag       14 -- year/month YYYYMM format
 
the part of the script in the stored procedure to delete the records is as follows:

 DELETE FROM XYZ_TBL
WHERE run_date_tag = v_date (eg '201010')
              OR run_date_tag < vpurgethreshold; (eg '200909')

Deleting the records in the table takes a very long time. May be the operation has to generate large redo files. Also drop all the indexes and rebuild the indexes. I think having too many indexes is also a problem there.
Are there any better alternatives to delete the records from the table.

May be deleting the records in a loop with deleting around 10000 records at a time. I am thinking this will also take pretty much the same time as it has to commit sereral times and do operations multiple times to re-construct the indexes.

May be other alternative is to partition the table based on acct_period (YYYYMM) and delete the partition itself which is not required. I have read some where that this is a good method to delete large no of records in a big table.

Please let me know what your thoughts are? Incase partitioning the table and deleting a partition is the better method, I need to incorporate script in my stored procedure to create partition and delete the partitions when ever the procedure executes. Please provide me with a script to do this. I have not done this before

Also comment on the use of indexes. Are so many indexes required. This application was written by someone and i am tweaking. This is a stand alone table which is used for some reporting purposes and pretty much do not have relation with other tables..

Please advice..

Thanks
             
gs79Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Naveen KumarProduction Manager / Application Support ManagerCommented:
try this approach --> "May be deleting the records in a loop with deleting around 10000 records at a time. I am thinking this will also take pretty much the same time as it has to commit sereral times and do operations multiple times to re-construct the indexes."  ---> Test it out and give your feedback. Do not think it may not give benefit without testing it.

Also 10000 is just a sample size you had mentioned and so you can test it with 25000 or 20000 and compare which is the best here.

What all indexes do you have for this table ?

Thanks,
Naveen KumarProduction Manager / Application Support ManagerCommented:
yes, this is another approach which will give good benefits and worth trying/testing. --> "May be other alternative is to partition the table based on acct_period (YYYYMM) and delete the partition itself which is not required. I have read some where that this is a good method to delete large no of records in a big table."

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
sventhanCommented:
I will not take the partition approach for the 3 mil rows table. Partition is good but it takes some maintenance time and you would require a different game plan.

If you want to make the delete faster, create a temp table and swap it to original

== > CTAS (create temptable as select cols from your table where condition)
== > create all the indexes/ constrains/ STATS etc
== > rename the temptable to your original

This is the fastest method i've ever seen for 3 mil.
Exploring ASP.NET Core: Fundamentals

Learn to build web apps and services, IoT apps, and mobile backends by covering the fundamentals of ASP.NET Core and  exploring the core foundations for app libraries.

Naveen KumarProduction Manager / Application Support ManagerCommented:
the benefits you get for each method/approach depend on how big the objects are ( tables , indexes ) and into which tablespace they fall into , database space availability, how much load does your database have while you do this testing...etc

While you do this deletion/insertion, does your table still need to be accessible for end users or this activity happens over a lean period everyday ( say in the mid night ) etc
gs79Author Commented:
Nav_Kum_v

The table is 3.5 million rows. And there are indexes on the six columns of the table as described above with distinct count of each column in my quesion. Normally procedure is run in the night to do insertion and deletion and the table is not accessed during that time. I have to try the table SWAP method as well as suggested by sventhan. BTW can you please provide me with a script to delete partition and insert rows to new partition and recreate the indexes..

Thanks
Naveen KumarProduction Manager / Application Support ManagerCommented:
in simple terms,  we can drop the partition by using the below...

alter table xyz drop partition <<partition_name>>;

you can query the DBA_TAB_PARTITIONS to understand how the data looks and then use a loop if you want to drop more than one partition for the same day.

select * from dba_tab_partitions
where table_name ='XYZ'
order by partition_position

Number of columns = 27 and count is 3.4 M is not enough for one to understand clearly because i am not sure whether your 27 columns have all data fully populated for all 3.4 M records or mostly they have null values etc. Can you give me the table size ?

select sum(bytes)
from dba_segments
where segment_name ='XYZ';

You need to have enough space in your database to hold the temp table and its indexes otherwise you will end up in problems. So query DBA_FREE_SPACE and understand how much free space is there and which tablespace can be used.

I am fine even if SWAP method works for you but note that the packages/views which use this table which become invalid and you may need to compile them to make them valid.  I believe you need the below additional steps as well.

==> you need to drop your original table and only then the 3rd step will work right ?
==> compile all the invalid packages/procedures/functions/views etc though it may not be required otherwise you might end up in performance issues when they try to get compiled by oracle automatically on the fly when you try to access them.

Thanks
aaaaaaCommented:
i suspect this happen when your script run some time.

So the table have a lot of un structure or empty block.

Try run below in weekly basic:

ALTER TABLE XYZ_TBL SHRINK SPACE;
aaaaaaCommented:
alternative way,

Not delete the data, in your select statement, use below:
select * FROM XYZ_TBL
WHERE run_date_tag >vpurgethreshold; (eg '200909')
--............

Schedule a job at night to delete.

sventhanCommented:
here is the simple way of doing it.

http://www.experts-exchange.com/Database/Oracle/Q_24861244.html?sfQueryTermInfo=1+10+30+partit+sventhan

CREATE OR REPLACE PROCEDURE add_partitions (in_owner IN sys.dba_tab_partitions.table_owner%TYPE,
in_table IN all_tab_partitions.table_name%TYPE)
IS
v_cr_part VARCHAR2 (3000);
BEGIN
v_cr_part :=
'alter table '
|| in_owner
|| '.'
|| in_table
|| ' add partition p'
|| LTRIM (TO_CHAR (SYSDATE, 'YYYYMMDD'))
|| ' values less than (to_date('''
|| LTRIM (TO_CHAR (SYSDATE + 1, 'YYYYMMDD'))
|| ''',''YYYYMMDD'')) tablespace ta_dm_'
|| LTRIM (TO_CHAR (SYSDATE, 'YYYYMM'));
EXECUTE IMMEDIATE v_split_par_cmd;
DBMS_OUTPUT.put_line ('Partition has been created successfully ');
EXCEPTION
WHEN OTHERS
THEN
DBMS_OUTPUT.put_line ('Error in creating partitions');
END;

This link might also useful

http://ocpdba.wordpress.com/2009/10/12/automatic-partition-management-for-oracle-10g/
slightwv (䄆 Netminder) Commented:
I just wanted to point out that Partitioning isn't free.  You need to license it separately.  Before you consider this you need to make sure you are licensed for it.
gs79Author Commented:
Folks, Its gonna be a while until I get back to you on this as I would like to try various approach and I am caught up in multiple assignments..Thank you for your responses..I will keep you posted..
Naveen KumarProduction Manager / Application Support ManagerCommented:
most of the updates given here should have heped the asker for sure but unfortunately no latest updates here.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.