In Vs Like

From: <meguesswho_at_googlemail.com>
Date: 6 Feb 2006 06:33:43 -0800
Message-ID: <1139236423.706346.313420@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 Received on Mon Feb 06 2006 - 08:33:43 CST