I've tried playing with some different indexes.  I even did a clustered unique covering index.  I was able to avoid the table scans, but not nearly as much as when...

Yes, it does complie.  I've added the IMPORT_DATAINVENTORY table to the FROM clause and the results do not change.