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!
Try:
="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...