is not null和<>如何走索引

chicheng_cn421 · 发表于 2011-11-1 13:01

likgui 发表于 2011-10-31 21:46
create index ind on t(col1 ,0)就可以了。

likgui 说的方案可以。

下面是我的实验过程。

建两个表，test表在CNT列上做了一个NULL的索引，testX表则CNT列上没有定义索引。
当同是执行 “where cnt is not null”条件时，test走了索引，而textX走的是全表扫描。

select * from testX where cnt is not null
select * from test where cnt is not null

【建表】
-- Create table
create table TEST
(
  ID  NUMBER,
  CNT NUMBER,
  C1  VARCHAR2(10),
  C2  VARCHAR2(10)
)
;
-- Create/Recreate indexes
create index IDX_TEST on TEST (CNT, 0)
;

-- Create table
create table TESTX
(
  ID  NUMBER,
  CNT NUMBER,
  C1  VARCHAR2(10),
  C2  VARCHAR2(10)
)
;

【表中数据】
SQL> select * from testx;

      ID       CNT C1       C2
---------- ---------- ---------- ----------
      1       3 aa       aaa
      2          bb       bbb
      3       4 bb       bbb
      4          bb       bbb
      5          bb       bbb
      6       5 bb       bbb
      7          bb       bbb
      8       6 bb       bbb
      9       7 bb       bbb

9 rows selected

SQL>

SQL> select * from test;

      ID       CNT C1       C2
---------- ---------- ---------- ----------
      1       3 aa       aaa
      2          bb       bbb
      3       4 bb       bbb
      4          bb       bbb
      5          bb       bbb
      6       5 bb       bbb
      7          bb       bbb
      8       6 bb       bbb
      9       7 bb       bbb

9 rows selected

SQL>

【执行计划 testX表】
select * from testX where cnt is not null

SELECT STATEMENT, GOAL = ALL_ROWS Cost=3 Cardinality=5 Bytes=200
TABLE ACCESS FULL Object owner=SCOTT Object name=TESTX Cost=3 Cardinality=5 Bytes=200

【执行计划 test表】
select * from test where cnt is not null

SELECT STATEMENT, GOAL = ALL_ROWS Cost=2 Cardinality=5 Bytes=200
TABLE ACCESS BY INDEX ROWID Object owner=SCOTT Object name=TEST Cost=2 Cardinality=5 Bytes=200
  INDEX FULL SCAN Object owner=SCOTT Object name=IDX_TEST Cost=1 Cardinality=1

lfree · 发表于 2011-11-1 14:32

create table t as select rownum id,'test' name from dual connect by level <=1;
insert into t select rownum+1 id,NULL name from dual connect by level <=9999;
commit;
create index i_t_name on t(name);
exec DBMS_STATS.GATHER_TABLE_STATS (user,'T', method_opt=>'FOR ALL columns size 1 ')

lfree · 发表于 2011-11-1 14:32

14:34:51 SQL> set autot traceonly
14:35:11 SQL> select * from t where name is not null;

Execution Plan
----------------------------------------------------------
Plan hash value: 3502950021

----------------------------------------------------------------------------------------
| Id  | Operation                | Name    | Rows  | Bytes | Cost (%CPU)| Time    |
----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT          |       |    1 |    5 |    2 (0)| 00:00:01 |
| 1 |  TABLE ACCESS BY INDEX ROWID| T       |    1 |    5 |    2 (0)| 00:00:01 |
|*  2 | INDEX FULL SCAN          | I_T_NAME |    1 |    |    1 (0)| 00:00:01 |
----------------------------------------------------------------------------------------

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

2 - filter("NAME" IS NOT NULL)

Statistics
----------------------------------------------------------
      0  recursive calls
      0  db block gets
      2  consistent gets
      0  physical reads
      0  redo size
      595  bytes sent via SQL*Net to client
      519  bytes received via SQL*Net from client
      2  SQL*Net roundtrips to/from client
      0  sorts (memory)
      0  sorts (disk)
      1  rows processed

lfree · 发表于 2011-11-1 14:35

SQL> set autot off
SQL> select * from t where name is not null;

SQL> select * from table(dbms_xplan.display_cursor(NULL,NULL,'ALLSTATS LAST'));

PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------------------------
SQL_ID  964sufn52x6ua, child number 0
-------------------------------------
select * from t where name is not null

Plan hash value: 3502950021

---------------------------------------------------------
| Id  | Operation                | Name    | E-Rows |
---------------------------------------------------------
| 0 | SELECT STATEMENT          |       |       |
| 1 |  TABLE ACCESS BY INDEX ROWID| T       |    1 |
|*  2 | INDEX FULL SCAN          | I_T_NAME |  10000 |
---------------------------------------------------------

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

2 - filter("NAME" IS NOT NULL)

Note
-----
- dynamic sampling used for this statement (level=2)
- Warning: basic plan statistics not available. These are only collected when:
   * hint 'gather_plan_statistics' is used for the statement or
   * parameter 'statistics_level' is set to 'ALL', at session or system level

26 rows selected

走索引全扫描。

lfree · 发表于 2011-11-1 14:36

只要name大部分是NULL，name is not null可以使用索引的。

lfree · 发表于 2011-11-1 14:38

SQL> select * from t where name <>'TEST';

> @dpc

PLAN_TABLE_OUTPUT
-----------------------------------------------------------------
SQL_ID  4jykzkbfysqv4, child number 0
-------------------------------------
select * from t where name <>'TEST'

Plan hash value: 1601196873

-------------------------------------------
| Id  | Operation       | Name | E-Rows |
-------------------------------------------
| 0 | SELECT STATEMENT  |    |       |
|*  1 |  TABLE ACCESS FULL| T |    1 |
-------------------------------------------

Outline Data
-------------

  /*+
   BEGIN_OUTLINE_DATA
   IGNORE_OPTIM_EMBEDDED_HINTS
   OPTIMIZER_FEATURES_ENABLE('11.2.0.1')
   DB_VERSION('11.2.0.1')
   ALL_ROWS
   OUTLINE_LEAF(@"SEL$1")
   FULL(@"SEL$1" "T"@"SEL$1")
   END_OUTLINE_DATA
  */

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

1 - filter("NAME"<>'TEST')

Note
-----
- Warning: basic plan statistics not available. These are only collected when:
   * hint 'gather_plan_statistics' is used for the statement or
   * parameter 'statistics_level' is set to 'ALL', at session or system level

38 rows selected.

这个不行。
看看提示是否可以。

伊思 · 发表于 2011-11-1 14:39

lfree 发表于 2011-11-1 14:36
只要name大部分是NULL，name is not null可以使用索引的。

斑竹真好，谢谢啦，我记得上次我测试的情况如下：大部分数据都是not null，但是只有少数数据时null，条件中是is null.这种情况好像不走索引呢

lfree · 发表于 2011-11-1 14:40

14:40:31 SQL> select /*+ index(t,i_t_name) */* from t where name <>'TEST';

      ID NAME
---------- ----
      1 test

14:42:52 SQL> @dpc

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  5pggmjyx22yxc, child number 0
-------------------------------------
select /*+ index(t,i_t_name) */* from t where name <>'TEST'

Plan hash value: 3502950021

---------------------------------------------------------
| Id  | Operation                | Name    | E-Rows |
---------------------------------------------------------
| 0 | SELECT STATEMENT          |       |       |
| 1 |  TABLE ACCESS BY INDEX ROWID| T       |    1 |
|*  2 | INDEX FULL SCAN          | I_T_NAME |    1 |
---------------------------------------------------------

Outline Data
-------------

  /*+
   BEGIN_OUTLINE_DATA
   IGNORE_OPTIM_EMBEDDED_HINTS
   OPTIMIZER_FEATURES_ENABLE('11.2.0.1')
   DB_VERSION('11.2.0.1')
   ALL_ROWS
   OUTLINE_LEAF(@"SEL$1")
   INDEX(@"SEL$1" "T"@"SEL$1" ("T"."NAME"))
   END_OUTLINE_DATA
  */

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

2 - filter("NAME"<>'TEST')

Note
-----
- Warning: basic plan statistics not available. These are only collected when:
   * hint 'gather_plan_statistics' is used for the statement or
   * parameter 'statistics_level' is set to 'ALL', at session or system level

39 rows selected.

14:42:54 SQL>

lfree · 发表于 2011-11-1 14:42

使用提示也是可以发现是可以的。

dingjun123 · 发表于 2011-11-1 14:45

改索引改SQL是最好的方法

[讨论] is not null和<>如何走索引