oracle - Delete records in an efficient way -

i've 2 tables stock , item. have query delete records item table,

delete item item_id not in(select itemid stock) 

and i've more 15,00,000 records delete, query taking time operation.

when searched, found efficient ways action.

1. one way:

   create table item_temp select * item item_id in(select itemid stock) ;

   truncate table item;

   insert /+ append +/ item select * item_temp; drop table item_temp;

2. secondly instead of truncating drop item , rename item_temp item. in case i've re create indexes.

can please suggest 1 of above more efficient, not check in production.

i think correct approach depends on environment, here.

if have privileges on table must not affected, or @ least must restored if drop table, insert /*+ append */ may more reliable. triggers, similarly, or foreign keys, or objects automatically dropped when base table dropped (foreign keys complicate truncate, of course).

i go truncate , insert method based on that. don't worry presence on indexes on table -- direct path insert efficient @ building them.

however, if have simple table without dependent objects there's nothing wrong drop-and-rename approach.

i not rule out running multiple deletes of limited number of rows, if in production environment.