index 和 index_desc hints的一点有意思的区别

最新推荐文章于 2021-10-27 14:50:54 发布

转载最新推荐文章于 2021-10-27 14:50:54 发布 · 273 阅读

本文介绍在Oracle数据库中如何使用索引优化DELETE语句的执行计划，通过两种不同方式指定索引，实现对特定范围记录的有效删除，并展示了相关执行计划。

2 - access("T1"."OBJECT_ID">=6361 AND "T1"."OBJECT_ID"<=6400)

和

2 - access("T1"."OBJECT_ID">=6361 AND "T1"."OBJECT_ID"<=6400)
filter("T1"."OBJECT_ID"<=6400 AND "T1"."OBJECT_ID">=6361)

SQL>create table t1 as select * from user_objects;

SQL>create index t1_object_id_ind on t1(object_id) tablespace users;

SQL>explain plan for
2 delete /*+ index(t1 t1_object_id_ind)*/from t1 where object_id>=6361 and object_id<=6400;

Explained.

SQL>@?/rdbms/admin/utlxplp

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------

--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost |
--------------------------------------------------------------------------
| 0 | DELETE STATEMENT | | 4 | 52 | 2 |
| 1 | DELETE | T1 | | | |
|* 2 | INDEX RANGE SCAN | T1_OBJECT_ID_IND | 4 | 52 | 2 |
--------------------------------------------------------------------------

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

2 - access("T1"."OBJECT_ID">=6361 AND "T1"."OBJECT_ID"<=6400)
Note: cpu costing is off

15 rows selected.

SQL>explain plan for
2 delete /*+ index_desc(t1 t1_object_id_ind)*/from t1 where object_id>=6361 and object_id<=6400;

Explained.

SQL>@?/rdbms/admin/utlxplp

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------

----------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost |
----------------------------------------------------------------------------------
| 0 | DELETE STATEMENT | | 4 | 52 | 2 |
| 1 | DELETE | T1 | | | |
|* 2 | INDEX RANGE SCAN DESCENDING| T1_OBJECT_ID_IND | 4 | 52 | 2 |
----------------------------------------------------------------------------------

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

2 - access("T1"."OBJECT_ID">=6361 AND "T1"."OBJECT_ID"<=6400)
filter("T1"."OBJECT_ID"<=6400 AND "T1"."OBJECT_ID">=6361)

Note: cpu costing is off

16 rows selected.

SQL>[@more@]

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

转载于:http://blog.itpub.net/7312700/viewspace-1004727/