How To Replace Leading Wildcards

So many of the queries in our database query by an account but an account is allowed to vary by prefix and so queries are written similar to account like '%suffix'

Our DBA has rejected the use of context indexes and friends to deal with this.

as an aside and I'm probably going to regret going into this much detail but our model is

A->>B->>C

typically the queries want to fetch C's for a customer defined by A.account

A, B, C are all partitioned with partition key created_date

however, only C is typically queried with created_date as a qualifier. A and B are related by joins from A->>B and B->>C

when queries are written to use '%' leading wildcards often it results in a full table scan across multiple partitions. on any given day all we care about are the most recent C's for the customer where customer is defined by A.account.

I recently had an idea whereby fields such as A.Account could also be kept(denormalized) in the C table

the idea is if the queries were written to use C.account like '%bla' instead of A.account like '%bla' then because C is always qualified with the partition key ie. C.created_date that would at worst result in a full partition scan only. this is considerably less expensive than doing a full table scan of A. The IO cost of doing that is huge.

populating the C.account is a simple before insert or update trigger. when you insert a C you have a foreign key to a B and B has a foreign key to A. A and B are always created before any C is seen for any given C. When a C is seen A and B already exist in the database.