Creating Indexes to optimize query

Question

I have this query and I want to increase its performance by adding appropriate Indexes.

DELETE 
  FROM MYTAB1 
  WHERE MYID1 IN 
        ( SELECT MYID2 FROM MYTAB2 );

I am not familiar with the syntax for indices and the type of settings that they require. Please provide the same.

The main issue here is that MYTAB1 has millions of records and thus the query takes a lot of time.

What indices do I need?

Answer 1

Perhaps this will help. If your data looks something like this:

create table mytab1 as (select level myid1 from dual connect by level <=2000000);
create table mytab2 as (select level+20000 myid2 from dual connect by level <=100);

No indexes:

Cost: 881, Consistent Gets: 3140, CPU Used: 174

Index on MyTab1 Table:

create index myid1_1 on mytab1 (myid1);

Cost: 104, Consistent Gets: 103, CPU Used: 14

Index on both MyTab1 and MyTab2:

create index myid2_1 on mytab2 (myid2);

Cost: 53, Consistent Gets: 102, CPU Used: 12

Your mileage will vary depending on quite a few factors, but in general an index on myid1 would probably be beneficial.

If you haven't already you should read through the Oracle Concepts Guide (pdf) particularly the section on indexing.