Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question
    
what indexes do you have on MYTAB1 and MYTAB2? This is difficult to answer without more information on the table structure –  kevinsky Apr 27 '12 at 12:43
    
there are indexes on other columns but not on these columns –  MozenRath Apr 27 '12 at 13:25

1 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.

share|improve this answer
    
Also partitioning may help. –  ipip Apr 30 '12 at 4:56

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.