Table and Index in the same tablespace. Performance issue?
Posted on 2011-03-24
I have a column in a table which is being populated by a sequence. And I have an index on this column. Both the table and the index are located in the same tablespace. I am noticing multiple blocking sessions which exist for a very breif period. The wait event is TX : row lock contention. Is is correct to assume that the index is sequntial as the colum is being populated by a sequence? Because of this reason, would both table and the index element be sharing the same block? As I am doing multiple DMLs on this table, is this resulting in the TX : row lock contention?
Would it help creating the index in a different tablespace for columns populated with a sequence? or, would it be better if I created a reverse key index on this column?
Any input regarding this is greatly appreciated. My database is on 10.2.0.4 , (SunOS wust4019 5.10 Generic_142900-13 sun4u sparc SUNW,Sun-Fire)