How indexes work ?
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 8 of 8

Thread: How indexes work ?

Hybrid View

  1. #1
    Join Date
    Jan 2001
    Posts
    318

    Question

    Can someone tell me how indexes work ?
    Does index stores some kind of number which select statement then accesses to make query faster ?
    Is it stored in memory ? If so what happens when some one does DML on that table. Now you try to do select, how does index knows the new data ?

    I read many documents on this but this is not explained any where. It is just said that good indexes improves performance, which I think everyone knows. How ??

    Thanks
    Sonali
    Sonali

  2. #2
    Join Date
    Oct 2000
    Posts
    123
    Try this:

    [url]http://www.precise.com/products/tip5.html[/url]

    hope this will give you a hint

    Thanks

  3. #3
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447
    in short

    index is another segment, seperated from table segment. in each "block" it stores the rowid and the value of the column
    in b*tree index, the most common one:

    if you have empno from 1 to 16
    ythe structure would be somthing like

    1~16
    1~8 9~16
    1~4 4~8 9~12 13~16
    .....
    ....


    for example empno column in table emp

    EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
    --------- ---------- --------- --------- -------- --------- --------- ---------
    16 SMITH CLERK 7902 17/12/80 800 20

    ROWID
    ------------------
    AAAHwJAAFAAAVaSAAA



    empno is indexed so index empno will store AAAHwJAAFAAAVaSAAA and 16

    rowid is the physical address of data in disk so itīs the fastest way to retrieve a data if we know rowid

    HTH

  4. #4
    Join Date
    Jan 2001
    Posts
    318
    thanks, I still doesn't understand why inserts and deletes make the query slower if you have index. If it locates it by Rowid-column value combination ? Does Rowid change with insert / deletes ?

    thanks again
    Sonali

  5. #5
    Join Date
    Jan 2001
    Posts
    126
    Hi,

    When you insert records into a table, all the indexes related to this table have to be updated with the rowid information for the newly inserted record. If you have too many indexes on a table then this synchronization operation takes time.

    Baliga

  6. #6
    Join Date
    Aug 2000
    Posts
    194
    When u insert or delete, the index also has to be inserted with the new rowid of the newly inserted record or has to delete the rowid from the index leaf page for the deleted records.

    Thus, if u have many indexes on a particular table, the insert/delete on the base table causes i/o on the indexes.

  7. #7
    Join Date
    Jan 2001
    Posts
    230
    Hi:

    Since Sonali brought index issue, I have a question
    which index is better bitmap or b-Tree? when do you use b-Tree and Bitmap index?

    Let me know.

    Thanks.

  8. #8
    Join Date
    Jul 2000
    Location
    delhi,india
    Posts
    10
    Hi,
    Whether to create a bit map index or b-tree index depends on the requirement.If the column has low cardinality,then creating a bitmap index is better.If the column has a high cardinality,then creating a b-tree index is better.Hope this helps.

    Rajesh.

Posting Permissions

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



Click Here to Expand Forum to Full Width