2 Replies Latest reply: Dec 11, 2012 9:58 PM by Salman Qureshi RSS

    How to make update stmt to use indexes

    937828
      Hi,
      I do have an update stmt which has been running for 5 hrs and table has many indexes, but it's not using any. Can you please suggest?
        • 1. Re: How to make update stmt to use indexes
          sb92075
          934825 wrote:
          Hi,
          I do have an update stmt which has been running for 5 hrs and table has many indexes, but it's not using any. Can you please suggest?
          ALTER SYSTEM SET MAKEUPDATE_FASTER = TRUE;


          HOW To Make TUNING request
          SQL and PL/SQL FAQ
          • 2. Re: How to make update stmt to use indexes
            Salman Qureshi
            Hi,
            I do have an update stmt which has been running for 5 hrs and table has many indexes, but it's not using any. Can you please suggest?
            Having many indexes does not necessarily mean that your statements will use one of them. It depends on you WHERE clause whether any suitable index is present to be used in this situation. There are two posibilities, one is that your condition in WHERE clause is returning most of the rows from the table and hence optimizer thinks that having a full table scan is better than index scan and therefore not using any index.
            Second possibility is that no index is benificial for this prticular statement and therefore none of indexes can be used.

            5 hours is a very long time, are you sure your session is not being blocked by any other session? Query v$session.blocked_session to confirm that this session is not blocked.

            1. Tell us the size (in MB/GB and number of rows in your table.
            2. Show us your update statement.
            3. Show us your indexes. The columns (in correct order) on which each index is based on.

            Salman