RE: create a view with parallel hint

From: Pratap Singh (c) <psingh_at_vmware.com>
Date: Thu, 27 Mar 2008 16:13:42 -0700
Message-ID: <C388F0160F8F6A4DA25614D019619C8F1F8411@PA-EXCH21.vmware.com>

Hi Tony

Your other option is to put parallel option on underlying tables. Of course it will apply on all access to this table.

ALTER TABLE <tablename> PARALLEL ( DEGREE x);

Thanks,
PB Singh

---

PB Singh
DW Architect and Sr Data Modeler
VMware  

-----Original Message-----
From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Tony Adolph
Sent: Thursday, March 27, 2008 12: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
>

---

=
>
>

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


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

Received on Thu Mar 27 2008 - 18:13:42 CDT