oracle10g不走索引,not in 和 <> 不走索引-优快云博客

在Oracle 10g中，当在WHERE子句中使用不等于（<>）或NOT IN操作符时，数据库倾向于忽略索引。这是因为Oracle认为这些操作可能导致大量数据的检索，全表扫描可能更快。文章通过创建大表、插入数据、建立索引并进行10053 trace分析，证实了这一行为。虽然可以通过调整SQL语句（如使用OR操作符）来利用索引，但默认情况下，Oracle在处理这些情况时会选择全表扫描。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

首先我们要知道的一点就是CBO的代码oracle是不会对我们公开的，起码现在是。所以本文中的结论不一定适用所有的版本。在应用本文的结论之前最好先试一下。

ok 下面就是本文的结论，当你在where语句中使用不等于或者not in时候，oracle 倾向于忽略索引。比如：

SQL> Select * from test where text<>‘star‘;

IDTEXT

---------- ------------

4939426 sun

这条语句即使在test上有索引，oracle也仍然会忽略。

接下来我们分析证明一下这是为什么。其实，我认为oracle这么做是有道理的。一般我们在写SQL的时候，如果用了

<>，也就是不等于，通常都是说选取结果集中的很大一部分。我们可以感受一下平时我们的思维方式和和习惯确实是这样的。比如我们说要"找出这些人中不是姓李的"，"找出这些车中不是大众的"。这一般来说是要返回结果集中很大一部分的。Oracle认为如果是这样，那么用索引不如全表扫描迅速，所以这种情况根本就不考虑索引，直接采用全表扫描。

而且oracle认为，如果你知道你的<>会返回少量的结果，那么你应该会调整你的SQL 用 (< or >)来代替。

下面我们验证一下。

首先创建表。一个很大的表。

SQL> select * fromv$version;

BANNER--------------------------------------------------------------------------------

Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 -64bi

PL/SQL Release 10.2.0.5.0 -Production

CORE10.2.0.5.0Production

TNSfor Solaris: Version 10.2.0.5.0 -Production

NLSRTL Version10.2.0.5.0 -Production

SQL> create table test as select rownum id, ‘star‘ text fromdba_objects,v$session;Table created.

验证一下这个表是不是很大，然后插入一条数据。

SQL> select count(*) fromtest;COUNT(*)----------

4939425SQL> insert into test values (4939426,‘sun‘);1row created.

SQL> commit;Commit complete.

创建索引，并收集统计信息。

SQL> create index test_i on test(text);Indexcreated.

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‘;

IDTEXT

---------- ------------

4939426sun

SQL> ALTER SESSION SET EVENTS ‘10053 trace name context off‘;

Session altered.

现在我们看一下10053的结果。

***************************************BASE STATISTICAL INFORMATION***********************

TableStats::Table: TEST Alias: TEST

#Rows:4944655 #Blks: 10780 AvgRowLen: 10.00

IndexStats::Index: TEST_I Col#: 2LVLS:2 #LB: 10468 #DK: 1 LB/K: 10468.00 DB/K: 19790.00 CLUF: 19790.00

***************************************

SINGLE TABLEACCESS PATH-----------------------------------------

BEGIN Single TableCardinality 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 TableCardinality Estimation-----------------------------------------

Access Path: TableScan

Cost:3098.02 Resp: 3098.02 Degree: 0Cost_io:2360.00 Cost_cpu: 2153524223Resp_io:2360.00 Resp_cpu: 2153524223Best:: AccessPath: TableScan

Cost:3098.02 Degree: 1 Resp: 3098.02 Card: 1.00 Bytes: 0

***************************************OPTIMIZERSTATISTICS ANDCOMPUTATIONS***************************************GENERAL PLANS***************************************Considering cardinality-based initial join order.

Permutationsfor 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: 1Cost:3098.0217 Degree: 1 Card: 1.0000 Bytes: 10Resc:3098.0217 Resc_io: 2360.0000 Resc_cpu: 2153524223Resp:3098.0217 Resp_io: 2360.0000 Resc_cpu: 2153524223kkoipt: 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 forthis session:Select * from test where text<>‘star‘

============

Plan Table

============

-------------------------------------+-----------------------------------+

-------------------------------------+-----------------------------------+

| 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: 0Cost_io:2360.00 Cost_cpu: 2153524223Resp_io:2360.00 Resp_cpu: 2153524223Best:: AccessPath: TableScan

Cost:3098.02 Degree: 1 Resp: 3098.02 Card: 1.00 Bytes: 0

根本就没有去calculate index的开销。所以执行计划就是全表扫描。

之前也怀疑过<>这种方式会不走索引，但是不知道为什么没有当回事，这次一定要记住非常有用。

原文：http://www.cnblogs.com/kramer/p/3571585.html