RE: Partition pruning problem

From: Michael Thomas <mhthomas_at_yahoo.com>
Date: Thu, 19 Aug 2004 21:11:27 -0700 (PDT)
Message-ID: <20040820041127.28530.qmail@web50609.mail.yahoo.com>

Hi,

• Leslie Tierstein <leslie.tierstein_at_visionchain.com> wrote:
  > If you figure this one out, please let me know. This
  > is probably not
  > what you want to hear:
  >
  > Sad story with similar scenario, except using Oracle
  > 8i (8.1.7):
  >
  > Couldn't for the life of us get Oracle (in queries
  > built by either
  > Business Objects or MicroStrategy) to use partition
  > pruning on a date
  > selected from a lookup table. (And in those tools
  > you can't rewrite the
  > generated SQL or embed hints in the SQL.) This
  > affected all
  > queries/reports that used filters that need date
  > predicates based on the
  > current date, e.g., Last 4 weeks, current week, etc.
  >
  ...

I remember generating native dynamic sql (NDS) in a PL/SQL procedure for Business objects. The last parameter is OUT and type REF CURSOR. There is a catch.

When the report is first created, behind the scenes, it parses the NDS statement and builds into the BO report a hidden data structure based on the items in the SELECT statement. As long as those SELECT items don't change the report can use NDS for the rest of the query. You might need BO Supervisor privileges to get the initial report created and REF CURSOR parsed into the report. Good luck.

Might that help?

Regards,

Mike Thomas                 

---

Do you Yahoo!?
Win 1 of 4,000 free domain names from Yahoo! Enter now. http://promotions.yahoo.com/goldrush

---

Please see the official ORACLE-L FAQ: http://www.orafaq.com

---

To unsubscribe send email to: oracle-l-request_at_freelists.org put 'unsubscribe' in the subject line.

--
Archives are at http://www.freelists.org/archives/oracle-l/
FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------

Received on Thu Aug 19 2004 - 23:07:05 CDT