Skip to Main Content

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

Comments

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>

         <JavaResourceBundle MsgBundleClass="path.to.DBResourceBundle"/>

      </ResourceBundle>

and in the attribute something like this:

     <Attribute

         Name="AttrName"

         ...>

         <Properties>

           <SchemaBasedProperties>

             <LABEL ResId="LABEL_KEY_IN_DB"/>

           </SchemaBasedProperties>

         </Properties>

      </Attribute>

regards,

Ruben

Ravi4-Oracle

Hi Ruben,

Thanks for the code. Will give it a try.

1 - 2

Post Details

Added on Dec 27 2021
18 comments
966 views