Oracle 10g: Optimizing an update query that uses a subquery

oraclesubqueryupdate

I have the following query within a stored procedure, and I've been trying to improve its performance to no avail.

  UPDATE TBL_REPORTING REP
  SET    (REP.ISSUER_ID, REP.ISSUER_NAME
         ) = ( SELECT DISTINCT TMP.ISSUER_ID
                       ,TMP.ISSUER_NAME
                FROM   TMP_REPORTING TMP
                WHERE  TMP.CMD_KEY = REP.CMD_KEY
                AND    TMP.REPORTING_KEY = REP.REPORTING_KEY
            )
  WHERE  REP.CMD_KEY = v_cmd_key;

TBL_REPORTING has 332124 rows in total.
TMP_REPORTING has 55589 rows in total.

I have tried rewriting the subquery using the WITH clause, but I can't get it working when there's 2 columns in the SET statement. I was reading that perhaps I could use the MERGE statement, but I haven't been able to get a working query yet. Ideas?

Update:

I managed to rewrite the query using the MERGE statement, but I still need to check if the results are the same:

MERGE INTO TBL_REPORTING REP
USING (SELECT DISTINCT TMP.CMD_KEY, TMP.REPORTING_KEY, TMP.ISSUER_ID, TMP.ISSUER_NAME
            FROM TMP_REPORTING TMP
            WHERE TMP.CMD_KEY = v_cmd_key) comp
            ON (REP.CMD_KEY = comp.CMD_KEY AND REP.REPORTING_KEY = comp.REPORTING_KEY)
            WHEN MATCHED THEN UPDATE SET ISSUER_ID = comp.ISSUER_ID, ISSUER_NAME = comp.ISSUER_NAME;

Update 2: Results are not the same. The UPDATE statement updates 40 more rows than the MERGE statement.

Execution plan for update:

Plan
UPDATE STATEMENT  ALL_ROWSCost: 644,432  Bytes: 322,065  Cardinality: 7,157                 
    6 UPDATE TBL_REPORTING          
        2 TABLE ACCESS BY INDEX ROWID TABLE TBL_REPORTING Cost: 302  Bytes: 322,065  Cardinality: 7,157         
            1 INDEX RANGE SCAN INDEX IDX_TBL_REPORTING_R4 Cost: 8  Cardinality: 7,157   
        5 SORT UNIQUE  Cost: 44  Bytes: 45  Cardinality: 1          
            4 TABLE ACCESS BY INDEX ROWID TABLE TMP_REPORTING Cost: 43  Bytes: 45  Cardinality: 1   
                3 INDEX RANGE SCAN INDEX TMP_REPORTING_13R2 Cost: 10  Cardinality: 6,949

Table TBL_REPORTING has indices:
PK_TBL_REPORTING on REPORTING_KEY and
IDX_TBL_REPORTING_R4 on CMD_KEY.

Table TMP_REPORTING has:
an index on CMD_KEY,
but not on REPORTING_KEY.

Best Answer

Try the below to update unique issuer id and issuer name instead of using Distinct by using Group by together with the index on Issue Id and Issuer Name in Temporary reporting table

Kindly refer the below link Fast Query To Get TOP-N Distinct Rows From A Huge Data Table

UPDATE TBL_REPORTING REP
SET(REP.ISSUER_ID, REP.ISSUER_NAME
) = ( SELECT TMP.ISSUER_ID
                   ,TMP.ISSUER_NAME
            FROM   TMP_REPORTING TMP
            WHERE  TMP.CMD_KEY = REP.CMD_KEY
            AND    TMP.REPORTING_KEY = REP.REPORTING_KEY
            GROUP BY TMP.ISSUER_ID
                   ,TMP.ISSUER_NAME
        )
WHERE  REP.CMD_KEY = v_cmd_key;

Related Solutions

Oracle 11g INDEX_SS Hint

In general, the optimizer is much better at figuring out whether a range scan would be more efficient than a skip scan (or vice versa). If the optimizer is making the wrong choice, the problem almost always comes down to the human not providing the optimizer with the proper information in the form of accurate statistics on the table and on the indexes (including things like histograms on columns that contain skewed data).

For any given query, the only way to know with certainty which of two plans is more efficient is to try it out with your data on your hardware with your query. You can try adding the hint to force the plan to change and you can then measure which approach is actually more efficient. If the plan the optimizer is choosing isn't the most efficient, I would tend to suspect that the root cause is that some aspect of the statistics are not correct but that is something that you'd need to look at a 10053 trace to get to the bottom of in most cases. Rarely, there are cases where the optimizer is wrong because the optimizer isn't able to take into account some aspect of the data (for example, until recently, it was unable to deal with situations where the data in multiple columns was not independent such as a car table with make, model, and year columns where knowing one value constrained the set of valid values in the other columns).

Oracle 12c – Query Against History Table Not Using Index

Index usage is obviously possible, but optional.

CREATE TABLE hist (
  HIST_ID INTEGER,
  HIST_TIMESTAMP TIMESTAMP,
  ID INTEGER -- this is the id of the table that is being tracked
  --OTHER COLS
);

CREATE INDEX hist_ix ON hist (ID, HIST_TIMESTAMP);

explain plan for SELECT ID, MAX(HIST_TIMESTAMP) FROM hist WHERE HIST_TIMESTAMP <= :B1 GROUP BY ID;
select * from table(dbms_xplan.display);


Plan hash value: 1027924405

--------------------------------------------------------------------------------
| Id  | Operation            | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |         |     1 |    26 |     1   (0)| 00:00:01 |
|   1 |  SORT GROUP BY NOSORT|         |     1 |    26 |     1   (0)| 00:00:01 |
|*  2 |   INDEX FULL SCAN    | HIST_IX |     1 |    26 |     1   (0)| 00:00:01 |
--------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("HIST_TIMESTAMP"<=TO_TIMESTAMP(:B1))
       filter("HIST_TIMESTAMP"<=TO_TIMESTAMP(:B1))

Note
-----
   - dynamic statistics used: dynamic sampling (level=2)

It is not true that an index can be used only for the leading columns:

explain plan for SELECT /*+ INDEX_SS(hist hist_ix) */ ID, MAX(HIST_TIMESTAMP) FROM hist WHERE HIST_TIMESTAMP <= :B1 GROUP BY ID;
select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 2669193891

--------------------------------------------------------------------------------
| Id  | Operation            | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |         |     1 |    26 |     1   (0)| 00:00:01 |
|   1 |  SORT GROUP BY NOSORT|         |     1 |    26 |     1   (0)| 00:00:01 |
|*  2 |   INDEX SKIP SCAN    | HIST_IX |     1 |    26 |     1   (0)| 00:00:01 |
--------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("HIST_TIMESTAMP"<=TO_TIMESTAMP(:B1))
       filter("HIST_TIMESTAMP"<=TO_TIMESTAMP(:B1))

Note
-----
   - dynamic statistics used: dynamic sampling (level=2)

Another method would be an index fast full scan (INDEX_FFS hint).

If you force the usage of your index with hints, then compare the cost of the plan with full table scan and the plan with index access path. It is simply a cost based decision with a simple example like this.

If you can not even force the usage of your index, I would search the problem somewhere else. For example your index is in UNUSABLE state (check USER_INDEXES.STATUS) or it was made INVISIBLE (USER_INDEXES.VISIBILITY).

Best Answer

Related Solutions

Oracle 11g INDEX_SS Hint

Oracle 12c – Query Against History Table Not Using Index

Related Question