Re: create a view with parallel hint

Many of our own Crystal report developers have a fixation about stored procedures for similar reasons.

Are there particular challenges about 9.2.0.8 and PIN that you can share?

• Original Message ---- From: Tony Adolph <tony.adolph.dba_at_gmail.com> To: David Aldridge <david_at_david-aldridge.com> Cc: oracle-l <oracle-l_at_freelists.org> Sent: Wednesday, April 2, 2008 6:45:52 PM Subject: Re: create a view with parallel hint

Hi David,

We've found a work around: I've written a procedure that returns a hard cursor. The cursor definition and the record it uses are defined in a separate package.

I tried to put the procedure in the same package as the record and cursor definintion, but it seems (pls correct me if I'm wrong) that Crystal does not "see" the packages in its browser (looking over the developer's shoulder). It only lists available procedures.

Anyway, apart from not being very elegant, it seems to work. My original query runs like a train (perhaps an old one), and we now have a way of hinting queries for Crystal. A result :-) If you'd like further details let me know.

Re upgrade to 10g. We currently struggling to get patched up to 9.2.0.8 (from 6). 10g's a long way off. So yes to both your questions,... yes we've thought about it, and yes we're stuck in the can't upgrade trap!

Cheers
Tony

On Thu, Apr 3, 2008 at 2:21 AM, David Aldridge <david_at_david-aldridge.com> wrote:

Hi Tony,  

Just a note to say that it's good to find another Oracle person working on PIN and Crystal Reports. We also couldn't find a way of embedding a hint in Crystal :(  

I see you're also 9.2.0.6 -- any thoughts of upgrading to 10g, or are you also stuck in the "can't upgrade my application" trap?

• Original Message ---- From: Tony Adolph <tony.adolph.dba_at_gmail.com> To: oracle-l <oracle-l_at_freelists.org> Sent: Wednesday, March 26, 2008 9:41:25 PM Subject: create a view with parallel hint

Hi folks,

After some tuning I went back to the developer and recommended a new super version, slightly changed and including a full and a parallel hint. The new query runs in 5 mins vs 68mins for the old, so a result.

The problem I have is that query is generated by Crystal Reports. Currently we don't know how we can include the hints in the report (the developer is looking into this as I type).

To work around this problem I created a view that includes the hints, and then changed the main query to use the query instead of the original table.

Something like this....

create or replace view EVENT_T_parallel_historic as select * /*+ full(eph) parallel(eph,8) */ from
EVENT_T partition (PARTITION_HISTORIC) eph;

then reference EVENT_T_parallel_historic in the main query.

But,... when I run the query, the hints are ignored.

Can hints be used within views in this manner, and/or am I doing something wrong here?

Any pointers appreciated.

Cheers
Tony
PS: Oracle 9.2.0.6
--

http://www.freelists.org/webpage/oracle-l
--

http://www.freelists.org/webpage/oracle-l Received on Wed Apr 02 2008 - 20:28:17 CDT