SQL IS NOT NULL条件走索引 试验
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
http://www.itpub.net/thread-1506807-2-1.html
