Ed,

>I have to vouch for this. Having an index tag on DELETED() has greatly trimmed down our access time (from 1-15 secs to 3-4 secs, which the users now feel is acceptable).

Again, this would only be the case where you:
- USE filtered resultset from a SQL - SELECT query or;
- Have more than about 70% of deleted records in your table.

for the first there is an alternative by:
USE table
SET FILTER to Expression

For the second: Get rid of the deleted records by:
- packing the table
- recycling deleted records
- use another existing index to filter out the majority of deleted records.
(If there you deleted last years invoices, use the invoicedate index to filter out the deleted records)

This speeds up even more than by adding a deleted tag

Well, If in your particular case it is not possible to get rid of the deleted records, you might have good reasons to use the deleted() tag. but IMHO i think the majority of apps dont need an index on deleted().

>It seems some are strongly against using the deleted() tag, but I'm still looking into documentation to back this up. Also, if you do have SET DELETED ON (which we do), then SYS(3054) will only report FULL optimization if this tag exists.

I'm against the deleted() index because there was a general thinking that the deleted() tag was alway giving you a performanceblast. This is simply not true. And index on deleted must be regarded as all other indexes on normal columns. Therefore normally, you won't need the index on deleted() tag.

There are lots of people who add the deleted tag right after they've made the table. Then you could ask the question: "Why not index all columns ????". If your index policy is: "The optimizer might use the index to optimize" then go ahead and index all columns where you suspect that the optimizer could use it.

When your policy is: "I only index columns which i need" then don't use the index tag on deleted unless you have very good reasons for it.

Walter,