This week's book giveaway is in the OCAJP 8 forum. We're giving away four copies of OCA Java SE 8 Programmer I Study Guide and have Edward Finegan & Robert Liguori on-line! See this thread for details.
I am using Prepared Statement to delete records from a table in Oracle 8i. My total number of records are 269818. The table have 15 columns in it.
I am using like below ********************************* PreparedStatement psmtDel = null; delSql="DELETE from CUSTOMER where id like 'ap%'"; psmtDel = con.prepareStatement(delSql); boolean deleted = psmtDel.execute(); psmtDel.close(); **********************************
The above operation is taking 30-35 minutes. I want to reduce the timing . I tried even using Statment object and as well as Prepare Statement Batch execute command.But the result is almost same.
The speed of that delete will depend more on your db then the Statement you are using to execute it, i.e. if you run the same delete as close as possible to the actual db (like a SQL editor tool most db vendors provide), you'll probably find the performance just as bad.
Here are a couple of factors that can influence the speed: 1. Number of records being affected. You mentioned 269,000 records but I'm assuing that is total records in the table, not total records to be deleted. But obviously it wil take the db considerably longer to delete 10,000 records vs. 10. How many records are you deleting?
2. Is you WHERE clause hitting on an index? If not, then the db is forced to do a full-table scan so in your case it would be scanning through 269,000 records. Most db vendors also provide a tool to show the "query plan" which can usually show you if it is hitting on an index.
3. Triggers. Remember that if there are any delete triggers on the table, then the db may be performing extra work for each record you delete. For example, if you have a delete trigger that performs some logging into another table, then for each record you delete, the db is actually executing 2 statements.
4. And of course there is always the hardware capabilities of the db server to consider.
Blake Minghelli<br />SCWCD<br /> <br />"I'd put a quote here but I'm a non-conformist"
Originally posted by Jamie Robertson: using the like operator in your where clause is inherently slow as it does a full table scan... and that's a lot of tables to scan!
="DELETE from CUSTOMER where substr( id , 1, 2) = 'ap'";
not sure if this will work, but it may be able to still utilize the index on the id field.
Although I haven't tested it, I would say your way would be much slower than using LIKE. Think of the mumber of temporary objects being created.
I have a table with 43,000 records and a full scan of it takes only a few hundreds milliseconds.
Clearly we have different environments and activities, but I am supprise that the operation takes more than a minute.
Do you know how many records match the parameter?
SCJP, SCWCD, SCBCD, IBM CSD WebSphere v5, <br />A+, MCP 2000 and 2000 server, CST, and few incompleted certification tracks.<br /> <br />Ivory Coast<br /> <br />Analyze your web Request/Response @ <a href="http://webtools.servehttp.com" target="_blank" rel="nofollow">http://webtools.servehttp.com</a> down for a while...
In the special case where the wildcard for the LIKE is at the end, the Oracle optimizer will consider using an index to find the matching records (if a suitable index exists, and the index is selective enough to be useful).
In any case, it should be able to scan 300,000 records in something less than half an hour, so that's probably not the problem. (Though it wouldn't hurt to check the query plan, time a "select count(*)..." using the same where condition, etc.
One big question that hasn't been asked is whether the database has other traffic or not. Contention for locks or some other resource is at least one possible culprit.