Hinting the CBO

From: Niall Smart <regkey_at_yahoo.com>
Date: 30 Jan 2003 03:33:21 -0800
Message-ID: <a4dfa61e.0301300333.2e1c7e9c_at_posting.google.com>

Hi,

I've come across some interesting behaviour in the CBO which I don't fully understand -- maybe an expert can shed some light?

I was asked to optimise the following fairly simple query:

SELECT

 g.clntnum,
 g.grupname,
 g.grupnum,
 c.cltaddr01

FROM
 grpspf g,
 clntpf c
WHERE
 g.grupnum in (SELECT DISTINCT P.GRUPKEY FROM CHDRPF P WHERE P.AGNTNUM
= '02898') AND

 c.clntnum = g.clntnum

The plan for this is:

SELECT STATEMENT
  NESTED LOOPS
    MERGE JOIN

      SORT JOIN
        TABLE ACCESS FULL [GRPSPF]
      SORT JOIN
        VIEW [VW_NSO_1]
          SORT UNIQUE
            TABLE ACCESS BY INDEX ROWID [CHDRPF]
              INDEX RANGE SCAN [NDX_CHDRPF_AGNTNUM]
    TABLE ACCESS BY INDEX ROWID [CLNTPF]
      INDEX UNIQUE SCAN	PK_CLNTPF

It seems the problem is that the CBO thinks a full table scan of GRPSPF will be quicker than a full index scan of OLE_IDX_GRPS (which is on GRPSPF.GRUPNUM) I presume this is because it doesn't know how many rows will be returned by the subquery? So, I thought I would simplify the query a bit just for performance analysis, so I removed the the join on clntpf which I presumed would not affect the execution plan:

SELECT

 g.clntnum,
 g.grupname,
 g.grupnum

FROM
 grpspf g
WHERE
 g.grupnum in (SELECT DISTINCT P.GRUPKEY FROM CHDRPF P WHERE P.AGNTNUM
= '02898')

However it did:

SELECT STATEMENT
  MERGE JOIN
    TABLE ACCESS BY INDEX ROWID [GRPSPF]       INDEX FULL SCAN [OLE_IDX_GRPS]
    SORT JOIN

      VIEW	VW_NSO_1
        SORT UNIQUE
          TABLE ACCESS BY INDEX ROWID [CHDRPF]
            INDEX RANGE SCAN [NDX_CHDRPF_AGNTNUM]

This seems a lot better -- it now uses the OLE_IDX_GRPS index, but I don't understand why it changed at all? So anyway, I went back to the original query and changed the subselect to a fixed list of constants:

SELECT

 g.clntnum,
 g.grupname,
 g.grupnum,
 c.cltaddr01

FROM
 grpspf g,
 clntpf c
WHERE
 g.grupnum in ('101', '102', '209') AND
 c.clntnum = g.clntnum

This produced much better results because an INLIST ITERATOR and INDEX RANGE SCAN was used:

SELECT STATEMENT
  NESTED LOOPS
    INLIST ITERATOR

      TABLE ACCESS BY INDEX ROWID [GRPSPF]
        INDEX RANGE SCAN [OLE_IDX_GRPS]
    TABLE ACCESS BY INDEX ROWID [CLNTPF]
      INDEX UNIQUE SCAN	[PK_CLNTPF]

My main question then is this: is there any way to force the original query to use an INLIST ITERATOR and/or INDEX FULL SCAN? I did a bit more searching and found that rewriting the query like this would also fix things:

SELECT

 g.clntnum
 g.grupnum
 g.grupname
 c.cltaddr01

FROM
 (SELECT DISTINCT p.grupkey FROM chdrpf p WHERE p.agntnum = '02898') gp,
 grpspf g,
 clntpf c
WHERE
 g.grupnum = gp.grupkey AND
 c.clntnum = g.clntnum;

The plan for this is fine, but I'm still curious about optimising the first form of the query:

SELECT STATEMENT
  NESTED LOOPS
    NESTED LOOPS

      VIEW
        SORT UNIQUE
          TABLE ACCESS BY INDEX ROWID [CHDRPF]
            INDEX RANGE SCAN [NDX_CHDRPF_AGNTNUM]
      TABLE ACCESS BY INDEX ROWID [GRPSPF]
        INDEX UNIQUE SCAN [OLE_IDX_GRPS]
    TABLE ACCESS BY INDEX ROWID [CLNTPF]
      INDEX UNIQUE SCAN	[PK_CLNTPF]

Thanks in advance for any help

Niall Received on Thu Jan 30 2003 - 12:33:21 CET