Re: Hinting the CBO

On Wed, 21 Nov 2001 19:27:02 -0500, Robert Massey <rmassey_at_mindspring.com> wrote:

>On Wed, 21 Nov 2001 21:25:42 +0100, Sybrand Bakker, wrote:
>
>} As you don't show your hints no one will be capable to tell why they are
>} ignored
>} Please also note that when you use a table alias in the from clause you
>} _must_ use this alias in your hint.
>
>Thanks, I know to use aliases in the hints. The simplified SQL
>statement with hints is:
>
>Select --+ USE_NL(p) ORDERED
> p.pfield1,
> p.pfield2,
> p.pfield3,
> c.sum_cfld3,
> c.sum_cfld4,
> c.sum_cfld5,
> c.sum_cfld6,
> c.sum_cfld7
>From parent p, (
> Select
> cfield1,
> cfield2,
> sum(cfield3) sum_cfld3,
> sum(cfield4) sum_cfld4,
> sum(cfield5) sum_cfld5,
> sum(cfield6) sum_cfld6,
> sum(cfield7) sum_cfld7
> From child
> Group by cfield1, cfield2
> ) c
>Where c.cfield1 = p.pfield1
> and c.cfield2 = p.pfield2
>;
>
>Once again, all suggestions welcome!

AFAIK:
- You should use /* */ instead of --
- You should use the hint use_nl on the _child_ table as opposed to the parent table

Hth

Sybrand Bakker, Senior Oracle DBA

To reply remove -verwijderdit from my e-mail address Received on Wed Nov 21 2001 - 23:20:31 CST