Re: Hinting the CBO

"Robert Massey" <rmassey_at_mindspring.com> wrote in message news:0prlvt4mqqnt91q7shm5o10b0k6pl8pofn_at_4ax.com...
> Hi, all:
>
> I'm running Oracle 8i v8.1.5 on HP-UX B.11.00.
>
> I've got a fairly standard pair of parent/child tables. The parent
> table contains lots of records and the child table contains lots more.
> Both tables have been analyzed, and the statistics are kept up-to-date.
>
> I've got a query that, greatly simplified, looks remarkably like this:
>
> Select
> 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
> ;
>
> The CBO properly performs the child subquery first and then joins the
> results to the parent table.
>
> However, what I _really_ want is a nested loops arrangement where the
> subquery is executed for each parent record.
>
> I've tried using the USE_NL and ORDERED hints, but this didn't make any
> difference. Nor did the FIRST_ROWS hint.
>
> Now, the CBO is smart enough that if I add some additional conditions
> like so:
>
> Select
> 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
> --new conditions below!
> and p.pfield1 = some_value
> and p.pfield2 = some_other_value
> ;
>
> The CBO will push the extra clauses into the subquery so that a nested
> loops execution plan is achieved (the child table does have an index on
> cfield1, cfield2).
>
> I was hoping I could somehow hint the CBO into using that execution path
> for every record in the parent table thus returning the first record
> more quickly. As I mentioned previously, my hinting has not been
> successful.
>
> Does anyone have any suggestions, or is this just a limitation that I'll
> have to live with?
> --
> Robert
>
> "Would that reason were as contagious as emotion."

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.

Hth

--
Sybrand Bakker
Senior Oracle DBA

to reply remove '-verwijderdit' from my e-mail address