0

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?

Improve this question
4
  • 1
    Are you asking about performance during an online index rebuild? Commented May 16, 2012 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... Commented May 16, 2012 at 14:17
  • @DTest yeah that is what I suspect. Commented May 16, 2012 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? Commented May 16, 2012 at 15:57

1 Answer 1

Reset to default
4

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.

Improve this answer
2
  • 2
    I'd assumed he meant DDL & a non-online ALTER/CREATE INDEX was blocking sessions. Difficult to tell from the question wording. Commented May 16, 2012 at 15:51
  • @Phil - Indeed he may have. Care to create that answer? Commented May 16, 2012 at 15:55

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.