oracle 无法删除用于强制唯一/主键的索引,10-强制索引失效以及更有效的使用索引...

强制索引失效以及更有效的使用索引

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

1、强制索引失效

如果两个或以上索引具有相同的登记，你可以强制命令oracle优化器使用其中一个

例：

SQL> select * from an1 where id=732;

ID NAME

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

732 anbaisheng

执行计划

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

Plan hash value: 1252233083

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

| Id  | Operation                   | Name         |

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

|   0 | SELECT STATEMENT            |              |

|   1 |  TABLE ACCESS BY INDEX ROWID| AN1          |

|*  2 |   INDEX RANGE SCAN          | INDEX_AN1_ID |

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

SQL> select * from an1 where id+0=732;

ID NAME

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

732 anbaisheng

执行计划

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

Plan hash value: 2246187579

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

| Id  | Operation         | Name |

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

|   0 | SELECT STATEMENT  |      |

|*  1 |  TABLE ACCESS FULL| AN1  |

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

SQL> select count(*) from an1 where name='anbaisheng';

COUNT(*)

----------

10000

执行计划

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

Plan hash value: 3907877743

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

| Id  | Operation         | Name           |

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

|   0 | SELECT STATEMENT  |                |

|   1 |  SORT AGGREGATE   |                |

|*  2 |   INDEX RANGE SCAN| INDEX_AN1_NAME |

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

SQL> select count(*) from an1 where name||''='anbaisheng';

COUNT(*)

----------

10000

执行计划

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

Plan hash value: 2832067379

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

| Id  | Operation          | Name |

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

|   0 | SELECT STATEMENT   |      |

|   1 |  SORT AGGREGATE    |      |

|*  2 |   TABLE ACCESS FULL| AN1  |

这种方法可以很有效的控制sql语句的执行计划，但使用的时候一定要谨慎

当id列有唯一索引，name列有普通索引的时候，查询时为了使用name字段的索引可以使用上面的方法限制

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

2、避免在索引列上使用计算

where子句中，如果索引列是函数的一部分，优化器将不使用索引而使用全表扫描

应该把计算放在等式的右边

select name from an1

where id+1=222;

改写成

select name from an1

where id=222-1;

效率回提高

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

3、避免在索引列上使用not

通常，我们要避免在索引列上使用not

not会产生在和索引列上使用函数相同的影响

当oracle遇到not，也会停止使用索引而使用全表扫描

SQL> select count(*) from an1 where id is not null;

COUNT(*)

----------

20001

执行计划

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

Plan hash value: 2832067379

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

| Id  | Operation          | Name |

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

|   0 | SELECT STATEMENT   |      |

|   1 |  SORT AGGREGATE    |      |

|*  2 |   TABLE ACCESS FULL| AN1  |

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

而使用>就会引用索引

SQL> select count(*) from an1 where id>0;

COUNT(*)

----------

20001

执行计划

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

Plan hash value: 4005259610

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

| Id  | Operation         | Name         |

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

|   0 | SELECT STATEMENT  |              |

|   1 |  SORT AGGREGATE   |              |

|*  2 |   INDEX RANGE SCAN| INDEX_AN1_ID |

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

所以在索引列尽量不要使用not

NOT > to <=

NOT >= to <

NOT < to >=

NOT <= to >

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

4、用>=替代>

select * from an1 where id>=4;

要比

select * from an1 where id>3;

效率高，原因是>=4直接跳到等于4的记录，

而>3首先定位等于3的，再向前扫描到第一个大于3的记录