SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Need to delete rows fast

Uday_NDec 27 2021

Hi All,
I need to delete a million records fastly . I have tried a few options but all taking time . I have provided the example
Table :
CREATE TABLE SOURCE_TABLE ( ID NUMBER, CREATE_DATE DATE) ;
CREATE TABLE TARGET_TABLE ( TG_ID NUMBER , ID NUMBER , CREATE_DATE DATE);
My scenario:
I need to delete target_table records based on the id and today's date ;
Volume of data in target table to be deleted is 108444 . It takes a lot of time more than 2-3 hours . Some time at backend data has been deleted but session out is coming . Total records in the target table is 228997
Query I tried :
delete from target_table where trunc(create_date ) = trunc(sysdate) and id in (select id from source_table) ;
Query 2 I tried :
delete from target_table where trunc(create_date ) = trunc(sysdate) and id in (select id from source_table) ;
Query 3 I tried:
set serveroutput on
declare
cursor c1 is
select * from source_table;
begin
for i in c1 loop
delete from target_table where trunc(create_date) = trunc(sysdate) and id = i.id
end loop ;
end ;

In 1st and second query it is taking more than 2 hours time while in 3rd query it took more than 5 hours and session was timed out but deleted at the back end .

Is there any way to quickly delete the records ? I didnt try forall delete . should i try that as well ? Please kind your advice ?

Regards,
Uday

This post has been answered by Uday_N on Dec 27 2021

Jump to Answer

Ruben Rodriguez

Hello,

I have never done this in the model, but you could try to do the same but in the DBResourceBundle.java class call AM method getResourceBundle like this:

getResourceBundle(String locale){

String amDef = "your.fully.qualified.class.name.of.the.application.module"; String config = "the_configuration_name_to_use"; ApplicationModule am = Configuration.createRootApplicationModule(amDef, config); Map map = am.getResourceBundle(locale); Configuration.releaseApplicationModule(am, false);

return map;

}

Then, in your entity.xml you should have this reference:

</ResourceBundle>

and in the attribute something like this:

<Attribute

Name="AttrName"

...>

</SchemaBasedProperties>

</Properties>

</Attribute>

regards,

Ruben

1 person found this helpful

Ravi4-Oracle

Hi Ruben,

Thanks for the code. Will give it a try.

1 - 2

Added on Dec 27 2021

#delete

18 comments

966 views

3 mentions

SQL & PL/SQL

Need to delete rows fast

Comments

Post Details