RE: create a view with parallel hint
Date: Thu, 27 Mar 2008 16:07:34 -0400
Message-ID: <21469B88E0EA11498818517F2103353101C6610F@EPRI17P32001A.csfb.cs-group.com>
Ah, yes, I misunderstood...sorry about that.
I knew that global hints are intended to replace hints in view definitions; that's probably why I got it wrong.
The docs say "Oracle recommends using global hints instead of embedding the hint in the view"; presumably hints in a view definition are legal. Are the view hints ignored no matter what query you run against the view?
Unless you can get the hints in the view working, I guess the solution will depend on getting Crystal to do what you want it to do.
Paul Baumgartel
CREDIT SUISSE
Information Technology
Prime Services Databases Americas
One Madison Avenue
New York, NY 10010
USA
Phone 212.538.1143
paul.baumgartel_at_credit-suisse.com
www.credit-suisse.com
-----Original Message-----
From: Tony Adolph [mailto:tony.adolph.dba_at_gmail.com]
Sent: Thursday, March 27, 2008 3:55 PM
To: Baumgartel, Paul
Cc: oracle-l
Subject: Re: create a view with parallel hint
Hi Paul,
Unless I've misunderstood, I wont be able to do this as the hint is added when the view is used as apposed to being *in* the view. E.g.
Create view v1 as
select blar blar from my_table t;
Then the global hint...
select /*+ full(v1.t) parallel(v1.t) */ * from tab1, tab2, v1;
But the problem I posted is that I can't add hints to my *top level*
query (in Crystal reports).
If I could, I would just add the hints to there.
Tony
On Fri, Mar 28, 2008 at 6:05 AM, Baumgartel, Paul
<paul.baumgartel_at_credit-suisse.com> wrote:
> Tony,
>
> You need to use global hints. Check the Performance Tuning manual
for
> details; 10g version is at
>
http://download.oracle.com/docs/cd/B19306_01/server.102/b14211/hintsref.
> htm#PFGRF50104.
>
> Regards,
>
>
> Paul Baumgartel
> CREDIT SUISSE
> Information Technology
> Prime Services Databases Americas
> One Madison Avenue
> New York, NY 10010
> USA
> Phone 212.538.1143
> paul.baumgartel_at_credit-suisse.com
> www.credit-suisse.com
>
>
>
>
> -----Original Message-----
> From: oracle-l-bounce_at_freelists.org
> [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Tony Adolph
> Sent: Wednesday, March 26, 2008 9:41 PM
> To: oracle-l
> 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
>
>
>
>
>
> Please access the attached hyperlink for an important electronic
communications disclaimer:
>
> http://www.credit-suisse.com/legal/en/disclaimer_email_ib.html
>
>
>
Please access the attached hyperlink for an important electronic communications disclaimer:
http://www.credit-suisse.com/legal/en/disclaimer_email_ib.html
-- http://www.freelists.org/webpage/oracle-lReceived on Thu Mar 27 2008 - 15:07:34 CDT