Performance issue -index

[kt_ananth]

Hai ,

I'm having a table with 30000 rows with an index which was created by a primary key constraint.
any updations on that table tooks 1 minute .

How can i reduce the timing.

If i create a index to fasten the search condition of the update whether it leads to any negative impact ?

Help me with a suggestion .

Ananth K.T
kt_ananth@yahoo.com

[ggnanaraj]

Check this out for guidelines to creating indexes...

http://technet.oracle.com/docs/produ..._acce.htm#2913

HTH.

[DaPi]

Originally posted by kt_ananth
How can i reduce the timing.

If i create a index to fasten the search condition of the update whether it leads to any negative impact

You seem to have some idea already how to speed it up . . . .
Run ANALYZE TABLE xxxx COMPUTE STATISTICS; after creating the index.

Negative impact is small: indexes take disk space - but it's worth it (some of my tables use almost as much index space as data space!), updating the extra indexes takes time when inserting or updating - but usually only a fraction of a second.

[mike9]

hi Ananth,

I got some questions:

- Is it correct that you update the entire table? If not how many rows?

- What are you updating? Do you replace nulls by a large amount of data? (have a look at row chaining before and after update)

- How fast is the update when you drop the primary key constraint/index ?

Regards
Mike