This discussion is archived
6 Replies Latest reply: Nov 24, 2010 8:03 AM by 37951 RSS

How to make use of a list partition key in an IR?

37951 Newbie
Currently Being Moderated
Hi all -- wondering if anyone has any thoughts on the following:

I have a table like this:

CREATE TABLE protocol (
ptl_id INTEGER,
ptl_name VARCHAR2(100),
...
...

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.

Any thoughts??

Thanks!
David
  • 1. Re: How to make use of a list partition key in an IR?
    Tyler Expert
    Currently Being Moderated
    If it's meaningless, how would you use it in a query from sqlplus?

    Tyler Muth
    http://tylermuth.wordpress.com
    "Applied Oracle Security: Developing Secure Database and Middleware Environments": http://sn.im/aos.book
  • 2. Re: How to make use of a list partition key in an IR?
    37951 Newbie
    Currently Being Moderated
    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.
  • 3. Re: How to make use of a list partition key in an IR?
    Tyler Expert
    Currently Being Moderated
    It sounds like you'd be better off partitioning on "protocol". You have built a model that you can't directly query without some code to dynamically change the query.

    Tyler Muth
    http://tylermuth.wordpress.com
    "Applied Oracle Security: Developing Secure Database and Middleware Environments": http://sn.im/aos.book
  • 4. Re: How to make use of a list partition key in an IR?
    37951 Newbie
    Currently Being Moderated
    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.

    Thanks
    David
  • 5. Re: How to make use of a list partition key in an IR?
    Tyler Expert
    Currently Being Moderated
    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?

    Tyler Muth
    http://tylermuth.wordpress.com
    "Applied Oracle Security: Developing Secure Database and Middleware Environments": http://sn.im/aos.book
  • 6. Re: How to make use of a list partition key in an IR?
    37951 Newbie
    Currently Being Moderated
    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.

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points