عندي جدول به عدد سجلات كثيرة  نصف مليون ويأخذ وقت طويل اثناء تنفيذ أمر delete 

 

هل هناك فرق في سرعة تنفيذ هذا المثال .... ترتيب القيم داخل الاقواس 

delete from table where col in (100,20,50,40 ) ;

delete from table where col in ( 20 , 40,50,100 ) ;

وهل في اقتراح لتحسين سرعة حذف السجلات مع العلم انه يوجد فهرسهة على العمود وهذا العمود ليس مفتاح للجدول 

create table CHECK_INDEXES(
  Ind_Col       NUMBER PRIMARY KEY
);
/

begin
  for i in 1..10000 loop
    insert into check_indexes values(i);
  end loop;
end;
/

select * from check_indexes;
/

set autotrace on explain;

delete from check_indexes where ind_col in (100,20,50,40 ) ;
ROLLBACK;
delete from check_indexes where ind_col in ( 20 , 40,50,100 ) ;
ROLLBACK;

النتيجة 

delete from check_indexes where ind_col in (100,20,50,40 ) ;//

 
-------------------------------------------------------------------------------------
| Id  | Operation           | Name          | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------
|   0 | DELETE STATEMENT    |               |     1 |    13 |     1   (0)| 00:00:01 |
|   1 |  DELETE             | CHECK_INDEXES |       |       |            |          |
|   2 |   INLIST ITERATOR   |               |       |       |            |          |
|*  3 |    INDEX UNIQUE SCAN| SYS_C0011534  |     1 |    13 |     1   (0)| 00:00:01 |
-------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   3 - access("IND_COL"=20 OR "IND_COL"=40 OR "IND_COL"=50 OR "IND_COL"=100)

//delete from check_indexes where ind_col in ( 20 , 40,50,100 ) ;
-------------------------------------------------------------------------------------
| Id  | Operation           | Name          | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------
|   0 | DELETE STATEMENT    |               |     1 |    13 |     1   (0)| 00:00:01 |
|   1 |  DELETE             | CHECK_INDEXES |       |       |            |          |
|   2 |   INLIST ITERATOR   |               |       |       |            |          |
|*  3 |    INDEX UNIQUE SCAN| SYS_C0011534  |     1 |    13 |     1   (0)| 00:00:01 |
-------------------------------------------------------------------------------------
 

حسب النتائج متشابهة