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

Niall, I am not a SQL tuning expert, but this is my take under the assumption that your statistics are current and valid. In the first form you told Oracle to join the tables and then filter since non-merged subqueries are normally done last, and the CBO had to drive on one of the two FROM clause tables. When you moved the subquery to the FROM cause the CBO looked at the tables to pick a driving table and saw that CLNTPF had a selectly indexed column value available for use and choose the smallest result set (table) as its driver resulting in a desirable plan.

There is a hint to push subquery consideration up, PUSH_SUBQ, but I have never used it.

HTH -- Mark D Powell -- Received on Thu Jan 30 2003 - 15:59:45 CET