Re: Hinting the CBO

From: Robert Massey <rmassey_at_mindspring.com>
Date: Thu, 22 Nov 2001 10:24:36 -0500
Message-ID: <0c1qvtchevarint62pburn5t0gs43sho53@4ax.com>

On Thu, 22 Nov 2001 06:20:31 +0100, Sybrand Bakker, wrote:

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

From the Oracle documentation:

  A statement block can have only one comment containing hints, and that   comment must follow the SELECT, UPDATE, INSERT, or DELETE keyword. The   syntax below shows hints contained in both styles of comments that   Oracle supports within a statement block.

  {DELETE|INSERT|SELECT|UPDATE} /*+ hint [text] [hint[text]]... */

  or

  {DELETE|INSERT|SELECT|UPDATE} --+ hint [text] [hint[text]]...

However, you are correct about the usage of the USE_NL hint. The inner table (child, in this case) should be specified. So, the query becomes:

Select --+ USE_NL(c) 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
;

This does, in fact, result in a nested loops join. Unfortunately, Oracle executes a full table scan on the child table for each record in the parent table, despite the presence of an index on cfield1, cfield2 in the child table.

What I would like is for pfield1 and pfield2 to be pushed into the subquery for each parent row. Oracle will do this if I provide additional criteria for the parent table, however this is impractical.

What I'm trying to achieve is something with the speed of the following query:

Select

  p.pfield1,
  p.pfield2,
  p.pfield3,

(Select sum(cfield3) from child c

    Where c.field1 = p.field1
      and c.field2 = p.field2
    ) sum_cfld3,
(Select sum(cfield4) from child c

    Where c.field1 = p.field1
      and c.field2 = p.field2
    ) sum_cfld4,
(Select sum(cfield5) from child c

    Where c.field1 = p.field1
      and c.field2 = p.field2
    ) sum_cfld5,
(Select sum(cfield6) from child c

    Where c.field1 = p.field1
      and c.field2 = p.field2
    ) sum_cfld6,
(Select sum(cfield7) from child c

    Where c.field1 = p.field1
      and c.field2 = p.field2
    ) sum_cfld7
From parent p
;

This works fairly quickly, but it is not very efficient since the same portion of the child table is searched 5 times for each parent record.

-- 
Robert

"While I'm still confused and uncertain, it's on a much higher plane,
d'you see, and at least I know I'm bewildered about the really
fundamental and important facts of the universe."
Treatle nodded. "I hadn't looked at it like that," he said, "But you're
absolutely right. He's really pushed back the boundaries of ignorance."
        -- Discworld scientists at work
           (Terry Pratchett, Equal Rites)

Received on Thu Nov 22 2001 - 09:24:36 CST