index 和 index_desc hints的一點有意思的區別

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@]