and this table is LIST partitioned on the key value PTL_ID. (There are many reasons for this need which I won't go into).
My question is, queries that use this table in Interactive Reports I have always have the PTL_ID in them, but it's never a displayed column, it's always hidden (as it's a meaningless surrogate key, not to display to the user). However the default IR behavior, when filtering, won't use it, it will use PTL_NAME (if filtering on a particular PROTOCOL).
What can I do to make the IR use the list partition key PTL_ID instead of PTL_NAME, even though PTL_ID is hidden and PTL_NAME is displayed? I'd get MUCH better performance if I could get it to query via the list partition key.
If I was querying the database directly, I'd find out the PTL_ID for the protocol I was interested in, and then use it in my where clauses.
I meant it's just meaningless to the end user (no reason to show it), and they certainly wouldn't filter it. The same thing is done in other (non-APEX) apps we have, and basically the java code behind the scenes fetches the PTL_ID value from the Protocol name they select, and then uses it for all the joins and such, to make use of the partition elimination, etc.
Users can change the name of the protocol at will, so that is not an option. Switching the partitioning is not an option either, as the PTL_ID's/partitions get pre-created during scheduled maintenance, and there is no tolerance to downtime to create a new partition for every new protocol as they are needed.
Each partition is put in a separate tablespace as there is a need to archive off protocols once they are done.
So I'm back to the original question still. My guess is the answer involves a bunch coding work arounds, not something native in the IRs. I figured I'd ask.
If you hash partition (not list) by protocol, all of those issues go away. Your current partitioning strategy won't play well with any 3rd party tools as it requires custom code to generate the queries.
If users can change the name of the protocol at will, how do you figure out which partition it's in?
If I hash partition, I can no longer easily archive off the data per protocol.
The list partition key is the surrogate PTL_ID value, not the name. So changing the name is no issue. The PTL_ID does not change.
I appreciate the help, thank you. Unfortunately changing the partitioning scheme is not an option. So I need to figure out a solution to make the 3rd party tool (APEX) work with it as I need to -- or at least, come up with a way to make the IR queries faster as they go across all partitions.