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


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?

  • 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

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.

  • Also partitioning may help. – Iľja Apr 30 '12 at 4:56

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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