where a0与索引

最新推荐文章于 2024-03-15 15:55:41 发布

cqcj2023

最新推荐文章于 2024-03-15 15:55:41 发布

阅读量198

点赞数

有说法：不等于操作符是永远不会用到索引的，因此对它的处理只会产生全表扫描。

　　推荐方案:用其它相同功能的操作运算代替，如

　　a<>0 改为 a>0 or a<0

通过实验可以看出：

看来a<>0是否走索引。
但把a<>0改为a>0或a<0走不走索引，与a的非0值为正负有关，以及正负的比例有关。

实验：
SQL> select value,count(*) from t
2 group by value;

VALUE COUNT(*)
---------- ----------
2 29688
0 139971

SQL> set autotrace traceonly

SQL> select * from t
2 where value<>0;

29688 rows selected.

Execution Plan
----------------------------------------------------------
Plan hash value: 1601196873

--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 41938 | 1761K| 197 (3)| 00:00:03 |
|* 1 | TABLE ACCESS FULL| T | 41938 | 1761K| 197 (3)| 00:00:03 |
--------------------------------------------------------------------------

SQL> select * from t
2 where value>0;

29688 rows selected.

Execution Plan
----------------------------------------------------------
Plan hash value: 1601196873

SQL> select * from t
2 where value<0;

no rows selected

Execution Plan
----------------------------------------------------------
Plan hash value: 1376202287

-------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 43 | 1 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| T | 1 | 43 | 1 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | T_IND | 1 | | 1 (0)| 00:00:01 |
-------------------------------------------------------------------------------------
where条件为value<>0或value>0时，都是走前表扫描。
而value<0时走索引。

在实验把value的2值全部改为负值。

SQL> update t set value=-2
2 where value=2;

29688 rows updated.

发现：
1 select * from t
2* where value>0
SQL> /

no rows selected

Execution Plan
----------------------------------------------------------
Plan hash value: 1376202287

where条件为value<>0或value<0时，都是走前表扫描。
而value>0时走索引。

SQL> select count(*),value from t
2 group by value
3 /

COUNT(*) VALUE
---------- ----------
169660 2
29688 -2
598440 0

SQL> select * from t
2 where value<>0;

199348 rows selected.

Execution Plan
----------------------------------------------------------
Plan hash value: 1601196873

--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 211K| 8883K| 637 (3)| 00:00:08 |
|* 1 | TABLE ACCESS FULL| T | 211K| 8883K| 637 (3)| 00:00:08 |
--------------------------------------------------------------------------

SQL> select * from t
2 where value>0;

169660 rows selected.

Execution Plan
----------------------------------------------------------
Plan hash value: 1601196873

--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 193K| 8144K| 637 (3)| 00:00:08 |
|* 1 | TABLE ACCESS FULL| T | 193K| 8144K| 637 (3)| 00:00:08 |
--------------------------------------------------------------------------

SQL> ed
Wrote file afiedt.buf

1 select * from t
2* where value<0
SQL> /

29688 rows selected.

Execution Plan
----------------------------------------------------------
Plan hash value: 1376202287

-------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 17584 | 738K| 122 (1)| 00:00:02 |
| 1 | TABLE ACCESS BY INDEX ROWID| T | 17584 | 738K| 122 (1)| 00:00:02 |
|* 2 | INDEX RANGE SCAN | T_IND | 17584 | | 67 (0)| 00:00:01 |
-------------------------------------------------------------------------------------

SQL> set autotrace off

看来a<>0十步走索引。
但把a<>0改为a>0或a<0走不走索引，与a的非0值为正负有关，以及正负的比例有关。

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/9599/viewspace-472934/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/9599/viewspace-472934/