Everyone in the USA (300+ million) lives somewhere. Imagine if you want to call everyone in Portland Oregon. If all you have are the phone numbers of everyone, you would have to call all 300+ million people to see if they live in Portland. But if you indexed all the phone numbers on the city, then you would only call those in Portland (600,000). Which method will take longer?
create table t1(
create index t1_idx1 on t1(id);
select * from t1 where id = 100;
If the index (t1_idx1) did not exist, DB2 would have to scan the entire table (t1) to find the one record with value id = 100. If the index exist, DB2 will look for the value id = 100 in the index and than using the RID (Record Identifier) which is assigned to the value will jump directly to the valid row in the table.
Index scan is much faster than table scan because of some conditions:
- size - in most cases the index is (should be) smaller than the table
- structure - special structure of an index makes the scanning very fast
Of course the final decision if the index is used or not is made by DB2 Optimizer