Not sure if the following scheme works for your case or not:
use the attribute column as range-key to create a range partitioned table, and then create a local spatial index.
Thanks for the suggestion yhu!
I know almost nothing about partitions, so I have some reading to do
Partitioning was definitely the answer to our performance issues!
We created a partitioned table where the records are partitioned by attribute. We had to use range partitioning as list partitioning is not supported for local spatial indexes (as suggested). Next we created a local spatial index.
This speeds up the problem query roughly 50 times! Processing time has now dropped from 5 seconds to <0.1 second. Amazing
Thanks again yhu