Yes, the deleted () index makes it full but may actually slow the query down in real life so you need to test it on realistic data to see if you should keep it or not.
On 3 November 2017 21:14:55 GMT-04:00, mbsoftwaresolutions@mbsoftwaresolutions.com wrote:
On 2017-11-03 20:21, Frank Cazabon wrote:
Individual indexes on each field should work, not an index on the compound expression.
Yep. That's what I found. Here's a subset of my indexes:
index on city tag city index on state tag state index on deleted() tag mydel BINARY
And then here's my query results from SYS(3054,12,"memvar"):
SELECT o1.invoice, o1.vendor, o1.vendor_id, o1.del_date, o1.equipment, o1.gross, o1.cost, o1.pu_city, o1.pu_st, o1.dest_city, o1.dest_st, o1.qty, o1.product, o1.ven_miles, o1.weight, o1.entered, o1.ven_fha, o1.dot_no, o1.ven_city, o1.ven_state, o1.ven_zip, o1.ven_poc, o1.ven_phone, o1.ven_fax, o1.ven_email, 0 as stop_flag, m.tlAlternate as Alt_flag FROM tmpDetail o1 WHERE pu_city = m.tcPu_City AND pu_st = m.tcPu_St INTO CURSOR Query1 Using index tag Pu_city to rushmore optimize intermediate result Using index tag Pu_st to rushmore optimize intermediate result Using index tag Mydel to rushmore optimize intermediate result Rushmore optimization level for intermediate result: full
Note that without the DELETED() index tag, it would have said Rushmore was PARTIAL.
[excessive quoting removed by server]