We've all had it drilled into our heads that Rushmore optimization does not support filtered indexes... but now it does... at least it does on a filter of NOT DELETED().

Run the program below in all versions of VFP that you have. It creates a million-record table in a database and creates an index that is filtered on NOT DELETED(). When SET DELETED is ON, and you're running VFP9, then FULL optimization occurs on a SQL SELECT (which you will see via SYS(3054) and the number of seconds to perform the query).

close all
set safety off
create database junk
create table junktable name junktable (junkfield c(10))
index on junkfield tag junkfield for not deleted()
for xx=1 to 1000000
  if mod(xx,10000)=0  &&Create special key every 10,000 records
    insert into junktable values (replicate("X",10))
    if xx=100000  &&Delete one of them just for the heckuvit
      delete
    endif
  else  &&Otherwise create random value and delete 2% of them
    insert into junktable values (sys(2015))
    if rand()<=0.02
      delete
    endif
  endif
endfor
clear
sys(3054,1)
? "Filtered Index with Set Deleted Off:"
set deleted off
lnSec=seconds()
select * from junktable where junkfield=replicate("X",10) into array myarr &&No optimization... 0.485 seconds
? seconds()-lnSec,"seconds"
? "Filtered Index with Set Deleted On:"
set deleted on
lnSec=seconds()
select * from junktable where junkfield=replicate("X",10) into array myarr &&Fully optimizable... 0.000 seconds
? seconds()-lnSec,"seconds"

I discovered this by mistake. I was answering a question regarding key uniqueness at www.foxite.com and mentioned that a filter of NOT DELETED() could be done on their candidate key to help solve their problem. I didn't go the extra mile, though, and neglected to mention the non-optimizability of that option. Andy Kramek jumped in and mentioned that, and so, just for the heck of it, I wrote a little program to test that assumption, since I, like Andy and all other developers out there, have just had that notion in my head for more than a decade and never questioned it. I wrote and ran the program in VFP9 and SYS(3054) said it WAS fully optimizable. Upon running the same program in VFP6, it was NOT. Andy ran the program in VFP7 and VFP8 (which I don't have) and it was also not optimizable. So this is a behavior that was added in VFP9.

This came as a surprise to both Andy and me and to others I've mentioned it to. It seems like a "stealth" enhancement, even slipping past Tamar, because I don't see this mentioned in her "What's New in Nine" book.

Just wanted to let you guys know this little interesting tidbit...

--Brad