oracle - Different queries or One? Which one is feasible? -

i have following queries want delete 3 employees in admin department.

delete employee_tbl emp_id = 123 , emp_dep = 'admin'; delete employee_tbl emp_id = 456 , emp_dep = 'admin'; delete employee_tbl emp_id = 789 , emp_dep = 'admin'; 

i thinking write single query like-

delete employee_tbl emp_id in (123, 456, 789) , emp_dep = 'admin'; 

is correct way write query in case of performance , all? confused.

any explanation appreciated.

both queries have pros , cons

1) multiple selects.

i rather rewrite query using bind variables.

delete employee_tbl emp_id = :l_emp_id , emp_dep = :l_emp_dep; 

and run multiple times.

this way, oracle don't hard parse query every time, , oracle re-uses explain plan reducing latch activity in shared global area(sga) minimizing cpu usage.

more details bind variables - key application performance

but 1 glitch here is, every execution of query actually, needs switch client sql engine process.(context switching, again costly process)

to override this, use bulk binding. not clients support this.

2) single query using in or temp tables/cte.

this query depends on db intelligence itself. no brainy developers. recent optimizers, handle them smartly, internally, using in-list iterators or temp tables if needed.

other advantage be, run query once, , operation needs 1 round trip database.

let database decide load.