关于oracle的<>

看到一则oracle优化的例子,关于<>的,但不理解为何
平常用<>都是直接用 “字段<>值”的方式的，但看到奇怪的用法确是：“字段<值 or 字段>值”这样的方式
而且前者能用不到索引，而后者确用到了。这个是<>的特殊用法还是什么呢？

--测试数据
--创建大表
create table big_table
as
select rownum id, a.*
  from all_objects a
where 1=0
/
alter table big_table nologging;

declare
    l_cnt number;
    l_rows number := &1;
begin
    insert /*+ append */
    into big_table
    select rownum, a.*
      from all_objects a
   where rownum <= &1;

    l_cnt := sql%rowcount;

    commit;

    while (l_cnt < l_rows)
    loop
        insert /*+ APPEND */ into big_table
        select rownum+l_cnt,
               OWNER, OBJECT_NAME, SUBOBJECT_NAME,
               OBJECT_ID, DATA_OBJECT_ID,
               OBJECT_TYPE, CREATED, LAST_DDL_TIME,
               TIMESTAMP, STATUS, TEMPORARY,
               GENERATED, SECONDARY
          from big_table
         where rownum <= l_rows-l_cnt;
        l_cnt := l_cnt + sql%rowcount;
        commit;
    end loop;
end;
/

alter table big_table add constraint
big_table_pk primary key(id)
/

select count(*) from big_table;
/*
Connected to Oracle9i Enterprise Edition Release 9.2.0.1.0
Connected as scott

SQL>

Table created

Table altered

PL/SQL procedure successfully completed

Table altered

  COUNT(*)
----------
    100000

SQL>
*/


select * from big_table where id<>100000;
--Explain Plan(全表扫描)
/*
SELECT STATEMENT, GOAL = CHOOSE        0                                                               
TABLE ACCESS FULL        1        FULL        SCOTT        BIG_TABLE                                       
*/

select * from big_table where id<100000 or id>100000;
--Explain Plan(使用索引)
/*
SELECT STATEMENT, GOAL = CHOOSE        0                                                               
CONCATENATION        1                                                               
  TABLE ACCESS BY INDEX ROWID        2        BY INDEX ROWID        SCOTT        BIG_TABLE                                       
   INDEX RANGE SCAN        3        RANGE SCAN        SCOTT        BIG_TABLE_PK                                       
  TABLE ACCESS BY INDEX ROWID        4        BY INDEX ROWID        SCOTT        BIG_TABLE                                       
   INDEX RANGE SCAN        5        RANGE SCAN        SCOTT        BIG_TABLE_PK                                       
*/

dingjun123

本来就不是同一个东西啊

是不是只要<>作用在where子句的索引字段上就不会使用该索引了呢？

〇〇

以前记得哪里说or 也不能用index...看来不是的

dingjun123

<>可能用到index的，但是不一定你想要的index,比如full index

jimn1982

原帖由 dingjun123 于 2010-7-15 11:08 发表
本来就不是同一个东西啊

的确不是同一个东西，一个不不等于10000的，一个走区间的，虽然结果一样，可是不一样的表达，索引不知道什么东西不存在，却知道给你要的哪个范围的数据。

killkill_shadow

原帖由 dingjun123 于 2010-7-15 12:19 发表
可能用到index的，但是不一定你想要的index,比如full index

不是很理解，能否举个例子呢？

dingjun123

你可以造个单列的表试试看，然后建个索引，select语句中我测试了<>是走full index scan的，其他语句没有试验

yellowlee

SQL> create table t_test_notequ1
  2  as select * from dba_objects ;

表已创建。

SQL>
SQL> create index idx_test_notequ1 on t_test_notequ1 (object_id);

索引已创建。

SQL> set autot on;
SQL> select count(*) from t_test_notequ1 a where a.object_id <> 1;

  COUNT(*)
----------
     59622


执行计划
----------------------------------------------------------
Plan hash value: 2434651730

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

----------

| Id  | Operation             | Name             | Rows  | Bytes | Cost (%CPU)|
Time     |

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

----------

|   0 | SELECT STATEMENT      |                  |     1 |    13 |    33   (7)|
00:00:01 |

|   1 |  SORT AGGREGATE       |                  |     1 |    13 |            |
         |

|*  2 |   INDEX FAST FULL SCAN| IDX_TEST_NOTEQU1 | 60125 |   763K|    33   (7)|
00:00:01 |

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

----------


Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter("A"."OBJECT_ID"<>1)

Note
-----
   - dynamic sampling used for this statement


统计信息
----------------------------------------------------------
        199  recursive calls
          0  db block gets
        235  consistent gets
        320  physical reads
          0  redo size
        410  bytes sent via SQL*Net to client
        385  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          3  sorts (memory)
          0  sorts (disk)
          1  rows processed

SQL>

yellowlee

SQL> update t_test_notequ1 a set a.object_id = 2 where a.object_id <> 1;

已更新59622行。


执行计划
----------------------------------------------------------
Plan hash value: 2339249418

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

-----

| Id  | Operation          | Name           | Rows  | Bytes | Cost (%CPU)| Time
    |

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

-----

|   0 | UPDATE STATEMENT   |                | 60125 |   763K|   184   (3)| 00:00

:03 |

|   1 |  UPDATE            | T_TEST_NOTEQU1 |       |       |            |
    |

|*  2 |   TABLE ACCESS FULL| T_TEST_NOTEQU1 | 60125 |   763K|   184   (3)| 00:00

:03 |

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

-----


Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter("A"."OBJECT_ID"<>1)

Note
-----
   - dynamic sampling used for this statement


统计信息
----------------------------------------------------------
        410  recursive calls
      67022  db block gets
       1083  consistent gets
        301  physical reads
   21311844  redo size
        673  bytes sent via SQL*Net to client
        605  bytes received via SQL*Net from client
          4  SQL*Net roundtrips to/from client
          6  sorts (memory)
          0  sorts (disk)
      59622  rows processed

SQL>