Re: In Vs Like

<meguesswho_at_googlemail.com> wrote in message news:1139236423.706346.313420_at_g14g2000cwa.googlegroups.com...
> Dear All,
>
> Env: Oracle 9.2.0.6 (Linux 3AS)
> TAB1 = 2.5 M rows
> TAB2 = 200 rows
>
> The two SQLs are as follows
>
> =========
> SQL 1
> =========
> SELECT * FROM TAB1
> WHERE TAB1_col1 IN (SELECT TAB2_col1
> FROM TAB2
> WHERE TAB2_col1 IN ('YY'))
>
> Operation Object Name Rows Bytes Cost
>
> SELECT STATEMENT Optimizer Mode=CHOOSE 14 K 488
> NESTED LOOPS 14 K 499 K 488
> INDEX UNIQUE SCAN PK_TAB2 1 4
> TABLE ACCESS FULL TAB1 14 K 442 K 487
>
>
> ======
> SQL 2
> ======
> SELECT * FROM TAB1
> WHERE TAB1_col1 IN (SELECT TAB2_col1
> FROM TAB2
> WHERE TAB2_col1 LIKE ('YY'))
>
>
> Operation Object Name Rows Bytes Cost
> SELECT STATEMENT Optimizer Mode=CHOOSE 102 31
> NESTED LOOPS 102 3 K 31
> INDEX UNIQUE SCAN PK_TAB2 1 4
> TABLE ACCESS BY INDEX ROWID TAB1 102 3 K 30
> INDEX RANGE SCAN FK_TAB1 102 21
>
> Schema stats are gathered everyday once. Can anyone help me understand
> the reason for why would first execution plan do a full table scan and
> second sql wont?
>
> Many Thanks
> Regards
> MS
>

Check the execution plan - and especially the filter_predicates and access_predicates - that you get by using dbms_xplan.

I think you are seeing a side effect of how Oracle has implemented transitive closure:

In example 1
> SELECT * FROM TAB1
> WHERE TAB1_col1 IN (SELECT TAB2_col1
> FROM TAB2
> WHERE TAB2_col1 IN ('YY'))

The

    TAB2_col1 IN ('YY')
 is transformed to

    TAB2_col1 = 'YY',

The subquery is then transformed into a join, which introduces a predicate

    TAB1_col1 = TAB2_col1

By transitive closure, this is converted to:

    TAB1_col1 = 'YY'
and the join predicate is discarded.

So you have TWO predicate:

    TAB1_col1 = 'YY'
    TAB2_col1 = 'YY',

In the second example
> SELECT * FROM TAB1
> WHERE TAB1_col1 IN (SELECT TAB2_col1
> FROM TAB2
> WHERE TAB2_col1 LIKE ('YY'))

The

    TAB2_col1 LIKE ('YY')
is not converted to an equality, but the subquery transformation to a join still
takes place, so you still get

    TAB1_col1 = TAB2_col1

At this point, transitive closure generates

    TAB1_col1 LIKE ('YY')

but because the generated predicate is not an equality, the join predicate is not discarded, so you have three predicates

    TAB2_col1 LIKE ('YY')
    TAB1_col1 = TAB2_col1
    TAB1_col1 LIKE ('YY')

As a consequence of the 'extra' predicate, the CBO arithmetic produces a lower join cardinality - which happens, in your case, to result in a different execution being appropriate.

-- 
Regards

Jonathan Lewis

http://www.jlcomp.demon.co.uk/faq/ind_faq.html
The Co-operative Oracle Users' FAQ

http://www.jlcomp.demon.co.uk/cbo_book/ind_book.html
Cost Based Oracle: Fundamentals

http://www.jlcomp.demon.co.uk/appearances.html
Public Appearances - schedule updated 2nd Feb 2006