This content has been marked as final. Show 2 replies
934825 wrote:ALTER SYSTEM SET MAKEUPDATE_FASTER = TRUE;
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?
HOW To Make TUNING request
SQL and PL/SQL FAQ
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.