0

I am a novice in tuning oracle queries thus need help.

If I have a sql query like:

select a.ID,a.name.....
   from a,b,c
   where a.id=b.id
   and ....
   and b.flag='Y';

then will adding index to the FLAG column of table b help to tune the query by any means? The FLAG column has only 2 values Y and N

APC
  • 144,005
  • 19
  • 170
  • 281
user6157109
  • 63
  • 1
  • 1
  • 5
  • The thing is, the only sensible answer to this question is, it depends. @Williamrobertson has done a good job of explaining how indexes work, and so why it's unlikely that indexing FLAG will improve things. It might if the data in table B fits a certain profile. But there are all sorts of other things which might improve performance. If this is a genuine issue you're trying to solve you need to post details about the data in your tables, the actual query and its explain plan. – APC Jan 23 '18 at 11:12

2 Answers2

2

With a standard btree index, the SQL engine can find the row or rows in the index for the specified value quickly due to its binary structure, then use the physical address (the rowid) stored in the index to access the desired row in a second hop. It's like looking in the index of a book to find the page number. So that is:

  1. Go to index with the key value you want to look up.
  2. The index tells you the physical address in the table.
  3. Go straight to that physical address.

That is nice and quick for something like a unique customer ID. It's still OK for something nonunique, like a customer ID in a table of orders, although the database has to go through the index entries and for each one go to the indicated address. That can still be faster than slogging through the entire table from top to bottom.

But for a column with only two distinct values, you can see that it is going to be more work going through all of the index entries for 'Y' for example, and for each one going to the indicated location in the table, than it would be to just forget the index and scan the whole table in one shot.

That's unless the values are unevenly distributed. If there are a million Y rows and ten N rows then an index will help you find those N rows fast but be no use for Y.

William Robertson
  • 15,273
  • 4
  • 38
  • 44
  • 1
    If the data does have such a skew it might be better to build a function based index on `decode(flag, 'N', 'N', null)` instead of pointlessly indexing ~1000000 `'Y'` values. Alternatively, adding FLAG to a compound index could help, depending on the queries which they run. – APC Jan 23 '18 at 11:06
0

Adding an index to a column with only 2 values normally isn't very useful, because Oracle might just as well do a full table scan.

From your query it looks like it would be more useful to have an index on id, because that would help with the join a.id=b.id.

If you really want to get into tuning then learn to use "explain plan", as that will give you some indication of how much work Oracle needs to do for a query. Add (or remove) an index, then rerun the explain plan.

Brian Cryer
  • 2,126
  • 18
  • 18