Use the new is_deleted and last_updated columns from xx_LOGICAL_RESOURCES to eliminate joins

[punktilious]

Is your feature request related to a problem? Please describe.
Searches always need to join to the _RESOURCES table to filter on the value for IS_DELETED. This flag represents the current state of the logical resource and so should be defined at the LOGICAL_RESOURCE level, not the RESOURCE (which represents a version of LOGICAL_RESOURCE.

Describe the solution you'd like
Investigate if it should be added to the global table rather than the resource-specific table.
Add the column to the logical resources table
Migrate data
Remove column from the resources tables
reorg
Update the search query generation as required, removing joins to the resources table unless they are required. The only reason to join with resources should be when the lastUpdated value is needed, and the final join to fetch the data blob.

Team discussion - added a comment that we have to consider VERSION specific status which is also on the RESOURCES table.

Describe alternatives you've considered
Status quo, and accept more expensive joins.

Additional context
N/A

Implementation Note
Trace specifications. To see search query timings:

com.ibm.fhir.persistence.jdbc.dao.impl.FHIRDbDAOImpl.level=FINE

To see search query SQL statements:

com.ibm.fhir.database.utils.query.QueryUtil.level=FINE

[lmsurpre]

Team discussion - added a comment that we have to consider VERSION specific status which is also on the RESOURCES table.

I think what that means is that we would need IS_DELETED on both the RESOURCES and LOGICAL_RESOURCES tables.
The one on RESOURCES will continue to mean that this particular version of the resource is deleted.
The one on LOGICAL_RESOURCES will indicate whether the "latest" version is deleted

[lmsurpre]

This flag was added under #1958, but work remains to actually use it.

[lmsurpre]

Now that we have #2014 I'm not sure if we still need this one. If so, how do they relate?

[d0roppe]

Ran the performance bucket against this code and all searches were processed. No odd behavior. Closing issue as the new code seems to be working.