Results 1 to 4 of 4
  1. #1
    Join Date
    Oct 2012
    Posts
    6

    Unanswered: Question on Indexes

    In what way Indexes built on table help? could you please exlpain with an example..

  2. #2
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    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?

    Andy

  3. #3
    Join Date
    Sep 2012
    Posts
    7
    Let's analyze the following example:

    create table t1(
    id int,
    firstname varchar(50),
    lastname varchar(50)
    );

    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

    Peter,

  4. #4
    Join Date
    Oct 2012
    Posts
    6
    Thanks Andy, Thanks Peter . The information really helped...

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •