Hinting the CBO

From: Robert Massey <rmassey_at_mindspring.com>
Date: Tue, 20 Nov 2001 19:29:59 -0500
Message-ID: <0prlvt4mqqnt91q7shm5o10b0k6pl8pofn@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."

Received on Tue Nov 20 2001 - 18:29:59 CST