Deletion Query taking lot of time ....

Hello Friends, I have got a query for deleting records from tables. the query is taking lot of time DELETE from table abc where abc_column IN ( Select tab1.col1 from tab1, tab2 where tab1.col1 = tab2.col2 and tab1.date1 is not null and (tab1.date1< tab2.date or tab1.date2 > tab2.date2) the query is right now taking 7 hours. Any way to optimize query there is no index on the tables and we cannot create index..thats the problem because it has lot of insertion. Hoping For a positive response. --------------------------------- Yahoo! Mail Use Photomail to share photos without annoying attachments.

prateek jain wrote: > Hello Friends, > I have got a query for deleting records from tables. > the query is taking lot of time > DELETE from table abc where abc_column IN ( > Select tab1.col1 from tab1, tab2 > where tab1.col1 = tab2.col2 > and tab1.date1 is not null > and (tab1.date1< tab2.date or tab1.date2 > tab2.date2)> > the query is right now taking 7 hours. > Any way to optimize query > > there is no index on the tables and we cannot create index..thats the > problem because it has lot of insertion. > > > Hoping For a positive response. You can try plugging the WHERE clause into the -s option of my dbdelete utility. It deletes large numbers of rows with minimal server impact very quickly. You may be running into several problems amoung which: - Indexes quickly become inefficient once the deletes begin. o Dbdelete avoids this somewhat by performing the actual delete by ROWID rather than by key (ROWIDs are fetched based on the key and a large IN clause is built with the ROWIDs for the actual DELETE). - You may be filling the logical logs and rather than the deletes taking 7 hours its taking 4 hours to delete nad 4 hours to rollback once the logical logs fill up. o Dbdelete only deletes up to 8192 rows in a single DELETE statement and commits after each DELETE so that resources are not held and far fewer are required. Dbdelete is in the package utils2_ak in the IIUG Software Repository. Art S. Kagel

prateek jain wrote: > Hello Friends, > I have got a query for deleting records from tables. > the query is taking lot of time > DELETE from table abc where abc_column IN ( > Select tab1.col1 from tab1, tab2 > where tab1.col1 = tab2.col2 > and tab1.date1 is not null > and (tab1.date1< tab2.date or tab1.date2 > tab2.date2)> > the query is right now taking 7 hours. > Any way to optimize query > > there is no index on the tables and we cannot create index..thats the > problem because it has lot of insertion. > > > Hoping For a positive response. You can try plugging the WHERE clause into the -s option of my dbdelete utility. It deletes large numbers of rows with minimal server impact very quickly. You may be running into several problems amoung which: - Indexes quickly become inefficient once the deletes begin. o Dbdelete avoids this somewhat by performing the actual delete by ROWID rather than by key (ROWIDs are fetched based on the key and a large IN clause is built with the ROWIDs for the actual DELETE). - You may be filling the logical logs and rather than the deletes taking 7 hours its taking 4 hours to delete nad 4 hours to rollback once the logical logs fill up. o Dbdelete only deletes up to 8192 rows in a single DELETE statement and commits after each DELETE so that resources are not held and far fewer are required. Dbdelete is in the package utils2_ak in the IIUG Software Repository. Art S. Kagel

Art S. Kagel wrote: > prateek jain wrote: >> Hello Friends, >> I have got a query for deleting records from tables. >> the query is taking lot of time >> DELETE from table abc where abc_column IN ( >> Select tab1.col1 from tab1, tab2 where>> tab1.col1 = tab2.col2 >> and tab1.date1 is not null >> and (tab1.date1< tab2.date or tab1.date2 > tab2.date2) >> >> the query is right now taking 7 hours. >> Any way to optimize query >> >> there is no index on the tables and we cannot create index..thats the >> problem because it has lot of insertion. >> >> >> Hoping For a positive response. > > You can try plugging the WHERE clause into the -s option of my dbdelete > utility. It deletes large numbers of rows with minimal server impact > very quickly. You may be running into several problems amoung which: > - Indexes quickly become inefficient once the deletes begin. > o Dbdelete avoids this somewhat by performing the actual delete by > ROWID rather than by key (ROWIDs are fetched based on the key and a > large IN clause is built with the ROWIDs for the actual DELETE). > > - You may be filling the logical logs and rather than the deletes > taking 7 hours its taking 4 hours to delete nad 4 hours to rollback once > the logical logs fill up. > o Dbdelete only deletes up to 8192 rows in a single DELETE statement > and commits after each DELETE so that resources are not held and far > fewer are required. > > Dbdelete is in the package utils2_ak in the IIUG Software Repository. > > Art S. Kagel Humm, >> there is no index on the tables and we cannot create index What version are you running? Could you post the sqexplain.out from set explain on; Select tab1.col1 from tab1, tab2 wheretab1.col1 = tab2.col2 and tab1.date1 is not null and (tab1.date1< tab2.date or tab1.date2 > tab2.date2) and also the output from : oncheck -pt db:tab1 oncheck -pt db:tab2 Do you have any statistics on these tables? If so post the output from dbschema -d db -hd tab1and dbschema -d db1 -hd tab2