Re: Hinting the CBO

regkey_at_yahoo.com (Niall Smart) wrote in message news:<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

Start with removing the distinct from your subquery. It is fully redundant. Behind the () is a *SET* and a set can't have duplicate elements by definition.
Oracle automatically takes care of this, but it isn't smart enough to know, you code a redundant distinct.

Regards

Sybrand Bakker
Senior Oracle DBA Received on Thu Jan 30 2003 - 15:50:22 CET