Indexes and queries

[smsinger3]

Hello all. I have a query in Oracle that I'm not sure exactly how to create the index. Here is the query:

Select a.field1
from table1 a, table2 b
where a.field2 = b.field2 and
b.field3 = 37

I assume I should place an index on a.field2. But, what would the indexes be on tableb? I figure it's either
1) 2 separate indexes: b.field2 and b.field3 or
2) 1 index: b.field2, bfield3

Also, is there are more efficient way to write this simple query? I know in SQL Server 7.0, I would use the join statement. What do you think?

Thanks for your help.

Steve

 

[Dagon]

I don't think that basing this decision on one query is very sensible. You have got to look at all the possible queries which could be executed against the data.

In this example, you are querying using a value for b.field3 but in other circumstances, you may use b.field2. If you have a concatenated index on field3 || field2, the query with query with the field2 value will not use the index because a concatenated index can only be used if the leading portion is present. In that case, separate indexes would be better.

It also depends on the selectivity of the columns. Is b.field2 selective on its own or is it only ever selective in combination with b.field3 ? If it is very unselective, then the concatenated index may be the better option.