This discussion is archived
2 Replies Latest reply: Jul 27, 2013 2:53 AM by pawii RSS

indexed column updation

pawii Newbie
Currently Being Moderated

i had a table name emp which is holding at least 30000 rows and i had created an index on emp_city

what will happen if i update any emp_city value? what effect it will create on index or that table?

 

the index will be updated automatically?

the index become invalid?

the leaf row in the index will be deleted and inserted?

or update in the leaf row will take place?? 

  • 1. Re: indexed column updation
    MRERP2 Explorer
    Currently Being Moderated

    According to field name I understand that the distribution is quite sparse for normal index on emp_city.

    Will be better to create a bitmap index in case that you apply to this table with OLAP queries or no create any index on this field.

     

    Any way,

    If you have an index on emp_city or emp_city is a part of some normal index and you'll upadate any emp_city value:

     

     

    the index will be updated automatically. (Yes)

    the index become invalid?  (No, but I suggest you run statistics on index or on wole table)

     

    the leaf row in the index will be deleted and inserted?or update in the leaf row will take place??

     

    This is leaf block (not leaf row). Entire block will be replaced.

     

    Best,

    Ark

  • 2. Re: indexed column updation
    nagarw31 Newbie
    Currently Being Moderated

    Hi,

     

    Please refer the below site to get answers of all your questions

     

    Update Indexes | Richard Foote's Oracle Blog

     

    Thanks!!

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points