RE: Partition pruning problem
- From: Michael Thomas <mhthomas@xxxxxxxxx>
- To: oracle-l@xxxxxxxxxxxxx
- Date: Thu, 19 Aug 2004 21:11:27 -0700 (PDT)
Hi,
--- Leslie Tierstein
<leslie.tierstein@xxxxxxxxxxxxxxx> 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@xxxxxxxxxxxxx
put 'unsubscribe' in the subject line.
--
Archives are at //www.freelists.org/archives/oracle-l/
FAQ is at //www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------
Other related posts:
