Thanks Ronald! I can't agree more!
Possible reason for the slowness here is that
the table itself is too big (with millions of records).
It will eat up cache space on the database server which
was allocated for DML operation for all databases on this
server.
So, my recommended solution is two steps:
1. do a batch deletion instead of whole. e.g. delete 5,000
rows one time, then loop through the delete function.
2. ask your DBA to allocate more cache space. e.g. from 100M
to 150M
Yibin
Ronald J Kimball wrote:
>
> On Thu, Oct 18, 2001 at 11:41:54AM -0400, Yibin Dong wrote:
> > 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.
>
> I don't think that's accurate. The index is useless if a function is used
> on the value of the column. In Javier's query:
>
> > > >$sql = "DELETE FROM ft_t_ispc WHERE LAST_CHG_TMS <
> > > >TO_DATE('$final_date','MM/DD/YYYY')";
>
> the TO_DATE function is not being used on the column; it's being used on
> the value the column is being compared to.
>
> I tried a query like this:
>
> SELECT count(*)
> FROM mytable
> WHERE mydate < TO_DATE('2000-01-01', 'YYYY-MM-DD')
>
> and Oracle did a fast full scan of the index on mydate.
>
> If I had done a TO_CHAR on the mydate column instead, then the index would
> have been useless.
>
> Ronald
