2 Replies Latest reply: Jul 27, 2013 4:53 AM by pawii RSS

    indexed column updation

    pawii

      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

          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

            Hi,

             

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

             

            Update Indexes | Richard Foote's Oracle Blog

             

            Thanks!!