James,

>As soon as I added two separate tags, one for category and one for type and ran the same sql the results appeared in 1/4 the time (10 seconds instead of 40), and the sys(3054,11) returned full optimization.

>Now I know the tags need to be individualized to the where clause.

You did mention having a DELETED() tag on your table. You can try to enhance performance even more by omiting this tag, if there are no or only a small percentage of deleted records in the table.

Would you mind giving me some statistics of the table.
- How manu percent of the records is deleted
- For how many percent of the records is the category = "F"
- For how many percent of the records is the expression INLIST(Type,"S","W","Y")
(Tip you can determine this by using COUNT FOR INLIST(Type,"S","W","Y"))

The point is that 'full optimization' is not giving you any guarantee that this will be the fastest way to retrieve your data. We've seen cases where Partial Optizmization is far faster than full optimization (I won't bother you with the theory). The key to optimimum speed is selectivity. Keyrule of thumb is only to use indexes that are highly selective (IOW indexes in which a particular value represents only a small portion of the table).

The deleted() index tag is low selective and often only burdens performance.

So, it may be wise to delete your index on DELETED() and see if your perfromance has improved.

Walter,