0

Let's assume that we have a table (cars) with a indexed column (category) contains values like 'A' , 'B' , 'C'.

If I want to tune the update on this column knowing that I use it in where condition like:

 update cars set category = 'Class A' where category = 'A';

so what is the best solution?

  • Alter Index Index_Name Disable
  • Update DML
  • Alter Index Index_Name rebuild

or

  • drop Index Index_Name
  • Update DML
  • Create Index Index_Name on (cars)
Mhd H_Nahhas
  • 27
  • 1
  • 4

1 Answers1

0

let me do some explanation, the update will use an index depending on data distribution

70% Status='A'
20%'B'
10% 'C'

then Status='A' will be better with full table scan because its getting most of the data in the table however if you want to get 'C' then index is better as you are accessing specific data.

however as for your update , and you have an index, the optimizer will (most of the time) find a better execution and query plan. so why you are worried? is it taking time more then usual ?

If you have huge data and you want to update them , the first approach will be an index as you are doing, if this table being hit a lot ( many dml opparations) and it contains several indexes then update statistics is required, this query will give you info when last time statistics are updated ( select last_analyzed from user_tables where table_name ='urtable';)

Alter Index Index_Name Disable--> this statement will disable the index only if its Function based index. you can use unusable or better approach and recommended make it invisible.

Alter Index Index_Name rebuild its like you are creating index again, it reclaim the space. when you have Lot of data changing you can consider it. note you need space the same as the index has.

Moudiz
  • 7,211
  • 22
  • 78
  • 156