Re: With Clause

On Nov 6, 12:51 am, raja <dextersu..._at_gmail.com> wrote:
> On Nov 6, 3:01 am, Palooka <nob..._at_nowhere.com> wrote:
>
>
>
>
>
> > Palooka wrote:
> > > joel garry wrote:
>
> > >> A few SECONDS of google:
>
> > >>http://www.psoug.org/reference/with.html
> > >>http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:5...
>
> > >>http://www.oracle.com/technology/products/oracle9i/daily/oct10.html
> > >>http://www.singlequery.com/2006/12/using-the-oracle-with-clause/
>
> > >> et cetera.
>
> > > Why should he waste precious seconds on Google, when people here will do
> > > his work for nothing?
>
> > Oh, damn. I sound like Sybrand now.

metoo. Funny how that happens. (And seriously, that's why I don't castigate him when he's grouchy, even if at times it seems always).

>
> > Palooka- Hide quoted text -
>
> > - Show quoted text -
>
> Hi,
>
> Thanks for ur responses.
> I will check these links and then if i have doubts, i will get back to
> you.
>
> Should we use /*+ MATERIALIZE */ ( materialize hint ) even in 10g ?
>
> With Regards,
> Raja.

See rule 1:
http://jonathanlewis.wordpress.com/2008/05/02/rules-for-hinting/

Response to Jan:

Agree, but then again, if things are changing because of bugs, they're changing more because of bug fixes plus new functionality plus behavioral changes plus all the things that can change plans dynamically, including changes that affect explicit plan stability. So using hints is making more work in the long run, unless they are absolutely necessary.

All that said, one just has to look to see the Oracle built-in stuff uses hints. I'm not convinced the argument "that's ok because they know what is going on" is a good argument. Like gather_stats_job:

select /*+ no_parallel(t) no_parallel_index(t) dbms_stats cursor_sharing_exact use_weak_name_resl dynamic_sampling(0) no_monitoring */ count(*) from "<obscured by joel>"."<obscured by joel>" sample block ( .1756888761,1) t

The hints may be ok, I wouldn't know - google for the arguments about whether one should collect system stats, too. But the default sampling turns plan evaluation into a guess, which may affect any access to that user table (which happens to be a biggee, affecting end of month processing windows).

But maybe it's a really _good_ guess.

jg

--
@home.com is bogus.
http://ask.slashdot.org/article.pl?no_d2=1&sid=08/11/05/2027234