not in 和 <> 不走索引
首先我们要知道的一点就是CBO的代码oracle是不会对我们公开的，起码现在是。所以本文中的结论不一定适用所有的版本。在应用本文的结论之前最好先试一下。
ok 下面就是本文的结论，当你在where语句中使用不等于或者not in时候，oracle 倾向于忽略索引。 比如：
SQL> Select * from test where text<>'star'; ID TEXT ---------- ------------ 4939426 sun
这条语句即使在test上有索引，oracle也仍然会忽略。
接下来我们分析证明一下这是为什么。 其实，我认为oracle这么做是有道理的。一般我们在写SQL的时候，如果用了 <>，也就是不等于，通常都是说选取结果集中的很大一部分。我们可以感受一下平时我们的思维方式和和习惯确实是这样的。比如我们说要"找出这些人中不是姓李的"，"找出这些车中不是大众的"。这一般来说是要返回结果集中很大一部分的。Oracle认为如果是这样，那么用索引不如全表扫描迅速，所以这种情况根本就不考虑索引，直接采用全表扫描。 而且oracle认为，如果你知道你的<>会返回少量的结果，那么你应该会调整你的SQL 用 （< or >）来代替。
下面我们验证一下。
首先创建表。一个很大的表。
SQL> select * from v$version; BANNER -------------------------------------------------------------------------------- Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bi PL/SQL Release 10.2.0.5.0 - Production CORE 10.2.0.5.0 Production TNS for Solaris: Version 10.2.0.5.0 - Production NLSRTL Version 10.2.0.5.0 - Production SQL> create table test as select rownum id, 'star' text from dba_objects,v$session; Table created.
验证一下这个表是不是很大，然后插入一条数据。
SQL> select count(*) from test; COUNT(*) ---------- 4939425 SQL> insert into test values (4939426,'sun'); 1 row created. SQL> commit; Commit complete.
创建索引，并收集统计信息。
SQL> create index test_i on test(text); Index created. SQL> exec dbms_stats.gather_table_stats(ownname=> 'SYS', tabname=> 'TEST' ,cascade=> true); PL/SQL procedure successfully completed.
进行10053trace
SQL> alter session set tracefile_identifier='haha'; Session altered. SQL> ALTER SESSION SET EVENTS='10053 trace name context forever, level 1'; Session altered. SQL> Select * from test where text<>'star'; ID TEXT ---------- ------------ 4939426 sun SQL> ALTER SESSION SET EVENTS '10053 trace name context off'; Session altered.
现在我们看一下10053的结果。
*************************************** BASE STATISTICAL INFORMATION *********************** Table Stats:: Table: TEST Alias: TEST #Rows: 4944655 #Blks: 10780 AvgRowLen: 10.00 Index Stats:: Index: TEST_I Col#: 2 LVLS: 2 #LB: 10468 #DK: 1 LB/K: 10468.00 DB/K: 19790.00 CLUF: 19790.00 *************************************** SINGLE TABLE ACCESS PATH ----------------------------------------- BEGIN Single Table Cardinality Estimation ----------------------------------------- Column (#2): TEXT(CHARACTER) AvgLen: 5.00 NDV: 1 Nulls: 0 Density: 1 Table: TEST Alias: TEST Card: Original: 4944655 Rounded: 1 Computed: 1.00 Non Adjusted: 1.00 ----------------------------------------- END Single Table Cardinality Estimation ----------------------------------------- Access Path: TableScan Cost: 3098.02 Resp: 3098.02 Degree: 0 Cost_io: 2360.00 Cost_cpu: 2153524223 Resp_io: 2360.00 Resp_cpu: 2153524223 Best:: AccessPath: TableScan Cost: 3098.02 Degree: 1 Resp: 3098.02 Card: 1.00 Bytes: 0 *************************************** OPTIMIZER STATISTICS AND COMPUTATIONS *************************************** GENERAL PLANS *************************************** Considering cardinality-based initial join order. Permutations for Starting Table :0 *********************** Join order[1]: TEST[TEST]#0 *********************** Best so far: Table#: 0 cost: 3098.0217 card: 1.0000 bytes: 10 (newjo-stop-1) k:0, spcnt:0, perm:1, maxperm:80000 ********************************* Number of join permutations tried: 1 ********************************* Final - All Rows Plan: Best join order: 1 Cost: 3098.0217 Degree: 1 Card: 1.0000 Bytes: 10 Resc: 3098.0217 Resc_io: 2360.0000 Resc_cpu: 2153524223 Resp: 3098.0217 Resp_io: 2360.0000 Resc_cpu: 2153524223 kkoipt: Query block SEL$1 (#0) ******* UNPARSED QUERY IS ******* SELECT "TEST"."ID" "ID","TEST"."TEXT" "TEXT" FROM "SYS"."TEST" "TEST" WHERE "TEST"."TEXT"<>'star' kkoqbc-subheap (delete addr=ffffffff7b11c008, in-use=11712, alloc=26392) kkoqbc-end : call(in-use=15256, alloc=49184), compile(in-use=37792, alloc=40520) apadrv-end: call(in-use=15256, alloc=49184), compile(in-use=38608, alloc=40520) sql_id=192f8vs3fqvpc. Current SQL statement for this session: Select * from test where text<>'star' ============ Plan Table ============ -------------------------------------+-----------------------------------+ | Id | Operation | Name | Rows | Bytes | Cost | Time | -------------------------------------+-----------------------------------+ | 0 | SELECT STATEMENT | | | | 3098 | | | 1 | TABLE ACCESS FULL | TEST | 1 | 10 | 3098 | 00:00:38 | -------------------------------------+-----------------------------------+ Predicate Information: ---------------------- 1 - filter("TEXT"<>'star')
注意access pass 这里
Access Path: TableScan Cost: 3098.02 Resp: 3098.02 Degree: 0 Cost_io: 2360.00 Cost_cpu: 2153524223 Resp_io: 2360.00 Resp_cpu: 2153524223 Best:: AccessPath: TableScan Cost: 3098.02 Degree: 1 Resp: 3098.02 Card: 1.00 Bytes: 0
根本就没有去calculate index的开销。 所以执行计划就是全表扫描。
之前也怀疑过<>这种方式会不走索引，但是不知道为什么没有当回事，这次一定要记住 非常有用。
