It depends on which database you are using. In Oracle,
when TO_DATE() is employed within where clause, all indexes
will be useless. Oracle will do a range scan instead of
index row scan.
Javier Dussaillant wrote:
>
> Hi,
>
> I would suggest the following (any or all of the options that your
> DB supports):
>
> - Drop any indexes that you have on the table BEFORE deleting and rebuild
> them AFTER you delete the rows.
>
> - I you can, set AutoCommit to 0.
>
> - this one is a long shot, since I'm not too familiar with the DB
> internals. I don't known if the DB evaluates the TO_DATE function for each
> row. Since you are giving it a fixed value and not a variable one I guess
> that the DB evaluates the function only once, but perhaps it doesn't. If
> this is the case, then you could make the conversion yourself and then
> compare against your result like in "WHERE LAST_CHG_TMS < $my_result".
>
> At 09:48 AM 10/18/2001 -0400, Rozengurtel, Daniel wrote:
> >Hello all
> >
> >I am trying to delete from a large table (about 500,000 recs). This
> >procedure has to take place twice a month, on a specific day. So for that
> >purpose I have this sql:
> >
> >$sql = "DELETE FROM ft_t_ispc WHERE LAST_CHG_TMS <
> >TO_DATE('$final_date','MM/DD/YYYY')";
> > my $rc = $dbh->do("$sql");
> >
> >However, it seems to run forever. Am I doing something wron? Should I check
> >my indexes on thst tsble? Is there anything like deleteing data in chunks?
> >Since what I have is per-transaction commit (AutoCommit=>1)
> >
> >Thanx all
> >
> >
> >
> >_____________________________________________________________________
> >IMPORTANT NOTICES:
> > This message is intended only for the addressee. Please notify the
> >sender by e-mail if you are not the intended recipient. If you are not the
> >intended recipient, you may not copy, disclose, or distribute this message
> >or its contents to any other person and any such actions may be unlawful.
> >
> > Banc of America Securities LLC("BAS") does not accept time
> >sensitive, action-oriented messages or transaction orders, including orders
> >to purchase or sell securities, via e-mail.
> >
> > BAS reserves the right to monitor and review the content of all
> >messages sent to or from this e-mail address. Messages sent to or from this
> >e-mail address may be stored on the BAS e-mail system.
>
> Javier Dussaillant G.
> [EMAIL PROTECTED]
> Departamento de Desarrollo VRWEB ltda.
> http://www.vrweb.cl
