Tell me more ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.
up vote 0 down vote favorite
1

I am using oracle 11g. I noticed some performance problems that causes hanging on some search operations.

Can I still search based on an index while that index is being updated in the database?

share|improve this question
1  
Are you asking about performance during an online index rebuild? – Derek Downey May 16 '12 at 14:09
I guess he refers to concurrent inserts and selects. The short answer is - yes. Slow performance may be due to queries not using the index... – Ohadi May 16 '12 at 14:17
@DTest yeah that is what I suspect. – Floradu88 May 16 '12 at 14:19
As @Phil mentions in a comment to one answer, it's difficult to tell what your issue is without more information. Can you provide more info such as what is 'some search operations'? Are the rebuilds happening from blocking offline structure changes, etc? – Derek Downey May 16 '12 at 15:57

1 Answer

up vote 4 down vote accepted

Here is what the 11.2 Concepts Guide says about doing selects while dml is changing an index (ephasis mine):

The database automatically maintains and uses indexes after they are created. The database also automatically reflects changes to data, such as adding, updating, and deleting rows, in all relevant indexes with no additional actions required by users. Retrieval performance of indexed data remains almost constant, even as rows are inserted. However, the presence of many indexes on a table degrades DML performance because the database must also update the indexes.

So, you might see sightly degraded performance if you have a lot of indexes and a lot of DML taking place, but I suspect this is insufficient to cause the appearance of hanging. I suggest you trace a session to see what is slowing the search down.

share|improve this answer
2  
I'd assumed he meant DDL & a non-online ALTER/CREATE INDEX was blocking sessions. Difficult to tell from the question wording. – Phil May 16 '12 at 15:51
@Phil - Indeed he may have. Care to create that answer? – Leigh Riffel May 16 '12 at 15:55

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.