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.

What would be best index for this kind of query:

-- id is the PK
create table test3(id number,name varchar2(30) not null,lastname varchar2(30) not null);

select id from test3 where id=:1 and (name=:2 or lastname=:3);

Would that be depends on the number of distinct values of each column(in that case the index would be on the more distinct column)? What if we cant tell what is the nature of the data..?

share|improve this question
    
Doesn't the table have a primary key? –  ypercube Mar 6 at 9:26

2 Answers 2

up vote 1 down vote accepted

If id is the primary key, any query with where id=:1 and (..whatever..) can effectively use the primary key index and retrieve, lookup only one row (if there is one.) It will then have to do the additional checks, the (..whatever..) but that is not going to be slow, since it's only one row.

If id was not the primary key, the situation might be different, depending on the selectivity of the 3 columns involved. The best index, would probably be two indexes, on (name, id) and on (lastname, id)

Oracle's optimizer can then use both indexes, doing an "index join". See the SQL-Fiddle.

share|improve this answer
    
regards the seconds option, if we do create 2 indexes it will still use only one (the best one by stats & histograms), right? –  nirrbared Mar 6 at 10:28
    
No, it can use 2 indexes, if it finds that is better than using one. –  ypercube Mar 6 at 10:39

Too broad. Anyway, in this specific example, the best reasonable index is the PK itself, because its unique, and you get your 0-1 row (block) immediately that you can visit and evaluate the rest of the conditions. Of course, you can create an index as (id, name) which in some cases saves you the table visit because of the short-circuit evaluation, but in my opinion the performance gain with this would be marginal. Sure, you could also create an index as (id, name, lastname), so you would not have to visit the table at all, but there is no point doing that, since you dont have any other columns, so you would index the whole table. It would be better to just create the table as an index-organized table in that case.

share|improve this answer

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.