Skip to Main Content
  • Questions

  • Update the table by getting the output from same table or Joining Tables

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, amit.

Asked: April 05, 2017 - 12:26 pm UTC

Last updated: April 05, 2017 - 2:03 pm UTC

Version: 11.2.0.4.0

Viewed 1000+ times

You Asked

Hi,

I need to update a column of table by getting the selected data from the same table using grouping function. Can you advise some sample query for this.
For example: I've the data like below:

FOM_SAP_VKORG SDB_SAP_MATNR SDB_DATAB SDB_DATBI FOM_DATAB FOM_DATBI RESULT_TYPE D_RECON_DATE ROWNUMBER
M100 000000000002043171 20161224 99991231 20161224 20170220 VALID_TO_MISMATCH 05/04/2017 1
M100 000000000002043171 20170221 99991231 20170221 99991231 MATCHED 05/04/2017 2


Now, i want to overwrite SDB_DATBI of 1st record with value of SDB_DATAB from 2nd record. So, i've to overwrite values for all rows where result_type = 'VALID_TO_MISMATCH'.
Can you advise the best query for this. I've written below query but it's taking too long and it's not completing:
UPDATE sap_recon_sdb_ret_price_dtl2 x
   SET SDB_DATBI = (SELECT to_char(max(to_date(sdb_datab,'YYYYMMDD')) -1,'YYYYMMDD')
         FROM sap_recon_sdb_ret_price_dtl2 y
        WHERE --y.sdb_sap_matnr   = '000000000002043171'AND 
           y.sdb_sap_kotabnr   = x.sdb_sap_kotabnr
           AND y.sdb_sap_kschl   = x.sdb_sap_kschl
           AND y.sdb_sap_vtweg   = x.sdb_sap_vtweg
           AND y.sdb_sap_vkorg   = x.sdb_sap_vkorg
           AND NVL(y.sdb_sap_pltyp,'$') = NVL(x.sdb_sap_pltyp,'$')
           AND y.sdb_sap_matnr   = x.sdb_sap_matnr
           AND y.sdb_sap_vrkme   = x.sdb_sap_vrkme
           and y.ROWNUMBER    = x.rownumber + 1)
 WHERE x.result_type    = 'VALID_TO_MISMATCH'
   and exists (SELECT 'X'
      FROM sap_recon_sdb_ret_price_dtl2 y
      WHERE --y.sdb_sap_matnr   = '000000000002043171' AND
           y.sdb_sap_kotabnr   = x.sdb_sap_kotabnr
          AND y.sdb_sap_kschl   = x.sdb_sap_kschl
          AND y.sdb_sap_vtweg   = x.sdb_sap_vtweg
          AND y.sdb_sap_vkorg   = x.sdb_sap_vkorg
          AND NVL(y.sdb_sap_pltyp,'$') = NVL(x.sdb_sap_pltyp,'$')
          AND y.sdb_sap_matnr   = x.sdb_sap_matnr
          AND y.sdb_sap_vrkme   = x.sdb_sap_vrkme
          and y.rownumber    = x.rownumber + 1)


Please let me know if my details are clear enough. Thank You.
Regards,
Amit

with LiveSQL Test Case:
https://livesql.oracle.com/apex/livesql/s/es5nexkbqeecptzmahbndw1el

and Chris said...

What precisely does

"taking too long and it's not completing"

mean? Your example runs nice and quick for me:

SQL> set timing on
SQL> CREATE TABLE SAP_RECON_SDB_RET_PRICE_DTL2
  2  (
  3    SDB_SAP_KOTABNR    VARCHAR2(1 BYTE),
  4    SDB_SAP_KSCHL      VARCHAR2(4 BYTE),
  5    SDB_SAP_VKORG      VARCHAR2(4 BYTE),
  6    SDB_SAP_VTWEG      VARCHAR2(2 BYTE),
  7    SDB_SAP_WERKS      VARCHAR2(4 BYTE),
  8    SDB_SAP_PLTYP      VARCHAR2(2 BYTE),
  9    SDB_SAP_MATNR      VARCHAR2(18 BYTE),
 10    SDB_SAP_VRKME      VARCHAR2(3 BYTE),
 11    SDB_KBETR          NUMBER,
 12    SDB_KONWA          VARCHAR2(5 BYTE),
 13    SDB_KPEIN          NUMBER(5),
 14    SDB_SAP_KMEIN      VARCHAR2(3 BYTE),
 15    SDB_DATAB          VARCHAR2(8 BYTE),
 16    SDB_DATBI          VARCHAR2(8 BYTE),
 17    FOM_SAP_KOTABNR    VARCHAR2(1 BYTE),
 18    FOM_SAP_KSCHL      VARCHAR2(4 BYTE),
 19    FOM_SAP_VKORG      VARCHAR2(4 BYTE),
 20    FOM_SAP_VTWEG      VARCHAR2(2 BYTE),
 21    FOM_SAP_WERKS      VARCHAR2(4 BYTE),
 22    FOM_SAP_PLTYP      VARCHAR2(2 BYTE),
 23    FOM_COUNTRY_IDEN   VARCHAR2(30 BYTE),
 24    FOM_SAP_MATNR      VARCHAR2(18 BYTE),
 25    FOM_SAP_VRKME      VARCHAR2(3 BYTE),
 26    FOM_KBETR          VARCHAR2(22 BYTE),
 27    FOM_KONWA          VARCHAR2(5 BYTE),
 28    FOM_KPEIN          NUMBER(22),
 29    FOM_SAP_KMEIN      VARCHAR2(3 BYTE),
 30    FOM_DATAB          VARCHAR2(8 BYTE),
 31    FOM_DATBI          VARCHAR2(8 BYTE),
 32    RESULT_TYPE        VARCHAR2(2000 BYTE),
 33    D_RECON_DATE       DATE,
 34    V_REMARKS          VARCHAR2(2000 BYTE),
 35    UPLOADED_VALID_TO  VARCHAR2(11 BYTE),
 36    ROWNUMBER          NUMBER
 37  );

Table created.

Elapsed: 00:00:00.33
SQL>
SQL> Insert into SAP_RECON_SDB_RET_PRICE_DTL2
  2     (sdb_sap_kotabnr, sdb_sap_kschl, sdb_sap_vkorg, sdb_sap_vtweg, sdb_sap_matnr, sdb_sap_vrkme, sdb_kbetr, sdb_konwa, sdb_kpein, sdb_sap_kmein, sdb_datab,
sdb_datbi, fom_sap_kotabnr, fom_sap_kschl, fom_sap_vkorg, fom_sap_vtweg, fom_sap_matnr, fom_sap_vrkme, fom_kbetr, fom_konwa, fom_kpein, fom_sap_kmein, fom_datab
, fom_datbi, result_type, d_recon_date, rownumber)
  3   Values
  4     ('1', 'VKP0', 'B100', '01', '000000000002043171', 'KG', 1.69, 'BND', 1, 'KG', '20161221', '99991231', '1', 'VKP0', 'B100', '01', '000000000002043171', '
KG', '1.69', 'BND', 1, 'KG', '20161221', '20170328', 'VALID_TO_MISMATCH', TO_DATE('04/05/2017 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 1);

1 row created.

Elapsed: 00:00:00.31
SQL>
SQL> Insert into SAP_RECON_SDB_RET_PRICE_DTL2
  2     (sdb_sap_kotabnr, sdb_sap_kschl, sdb_sap_vkorg, sdb_sap_vtweg, sdb_sap_matnr, sdb_sap_vrkme, sdb_kbetr, sdb_konwa, sdb_kpein, sdb_sap_kmein, sdb_datab,
sdb_datbi, fom_sap_kotabnr, fom_sap_kschl, fom_sap_vkorg, fom_sap_vtweg, fom_sap_matnr, fom_sap_vrkme, fom_kbetr, fom_konwa, fom_kpein, fom_sap_kmein, fom_datab
, fom_datbi, result_type, d_recon_date, rownumber)
  3   Values
  4     ('1', 'VKP0', 'B100', '01', '000000000002043171', 'KG', 1.99, 'BND', 1, 'KG', '20170329', '99991231', '1', 'VKP0', 'B100', '01', '000000000002043171', '
KG', '1.99', 'BND', 1, 'KG', '20170329', '20170403', 'VALID_TO_MISMATCH', TO_DATE('04/05/2017 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 2);

1 row created.

Elapsed: 00:00:00.31
SQL>
SQL> Insert into SAP_RECON_SDB_RET_PRICE_DTL2
  2     (sdb_sap_kotabnr, sdb_sap_kschl, sdb_sap_vkorg, sdb_sap_vtweg, sdb_sap_matnr, sdb_sap_vrkme, sdb_kbetr, sdb_konwa, sdb_kpein, sdb_sap_kmein, sdb_datab,
sdb_datbi, fom_sap_kotabnr, fom_sap_kschl, fom_sap_vkorg, fom_sap_vtweg, fom_sap_matnr, fom_sap_vrkme, fom_kbetr, fom_konwa, fom_kpein, fom_sap_kmein, fom_datab
, fom_datbi, result_type, d_recon_date, rownumber)
  3   Values    ('1', 'VKP0', 'M100', '01', '000000000002043171', 'KG', 4.99, 'MYR', 1, 'KG', '20161224', '99991231', '1', 'VKP0', 'M100', '01', '00000000000204
3171', 'KG', '4.99', 'MYR', 1, 'KG', '20161224', '20170220', 'VALID_TO_MISMATCH', TO_DATE('04/05/2017 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 1);

1 row created.

Elapsed: 00:00:00.31
SQL>
SQL> Insert into SAP_RECON_SDB_RET_PRICE_DTL2
  2     (sdb_sap_kotabnr, sdb_sap_kschl, sdb_sap_vkorg, sdb_sap_vtweg, sdb_sap_pltyp, sdb_sap_matnr, sdb_sap_vrkme, sdb_kbetr, sdb_konwa, sdb_kpein, sdb_sap_kme
in, sdb_datab, sdb_datbi, fom_sap_kotabnr, fom_sap_kschl, fom_sap_vkorg, fom_sap_vtweg, fom_sap_pltyp, fom_sap_matnr, fom_sap_vrkme, fom_kbetr, fom_konwa, fom_k
pein, fom_sap_kmein, fom_datab, fom_datbi, result_type, d_recon_date, rownumber)
  3   Values    ('2', 'VKP0', 'M100', '01', 'P1', '000000000002043171', 'KG', 3.59, 'MYR', 1, 'KG', '20161201', '99991231', '2', 'VKP0', 'M100', '01', 'P1', '00
0000000002043171', 'KG', '3.59', 'MYR', 1, 'KG', '20161201', '20170331', 'VALID_TO_MISMATCH', TO_DATE('04/05/2017 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 1);

1 row created.

Elapsed: 00:00:00.32
SQL>
SQL> Insert into SAP_RECON_SDB_RET_PRICE_DTL2
  2     (sdb_sap_kotabnr, sdb_sap_kschl, sdb_sap_vkorg, sdb_sap_vtweg, sdb_sap_pltyp, sdb_sap_matnr, sdb_sap_vrkme, sdb_kbetr, sdb_konwa, sdb_kpein, sdb_sap_kme
in, sdb_datab, sdb_datbi, fom_sap_kotabnr, fom_sap_kschl, fom_sap_vkorg, fom_sap_vtweg, fom_sap_pltyp, fom_sap_matnr, fom_sap_vrkme, fom_kbetr, fom_konwa, fom_k
pein, fom_sap_kmein, fom_datab, fom_datbi, result_type, d_recon_date, rownumber)
  3   Values    ('2', 'VKP0', 'M100', '01', 'P2', '000000000002043171', 'KG', 3.59, 'MYR', 1, 'KG', '20161201', '99991231', '2', 'VKP0', 'M100', '01', 'P2', '00
0000000002043171', 'KG', '3.59', 'MYR', 1, 'KG', '20161201', '20170331', 'VALID_TO_MISMATCH', TO_DATE('04/05/2017 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 1);

1 row created.

Elapsed: 00:00:00.31
SQL>
SQL> Insert into SAP_RECON_SDB_RET_PRICE_DTL2
  2     (sdb_sap_kotabnr, sdb_sap_kschl, sdb_sap_vkorg, sdb_sap_vtweg, sdb_sap_pltyp, sdb_sap_matnr, sdb_sap_vrkme, sdb_kbetr, sdb_konwa, sdb_kpein, sdb_sap_kme
in, sdb_datab, sdb_datbi, fom_sap_kotabnr, fom_sap_kschl, fom_sap_vkorg, fom_sap_vtweg, fom_sap_pltyp, fom_sap_matnr, fom_sap_vrkme, fom_kbetr, fom_konwa, fom_k
pein, fom_sap_kmein, fom_datab, fom_datbi, result_type, d_recon_date, rownumber)
  3   Values    ('2', 'VKP0', 'M100', '01', 'P3', '000000000002043171', 'KG', 4.99, 'MYR', 1, 'KG', '20161224', '99991231', '2', 'VKP0', 'M100', '01', 'P3', '00
0000000002043171', 'KG', '4.99', 'MYR', 1, 'KG', '20161224', '20170220', 'VALID_TO_MISMATCH', TO_DATE('04/05/2017 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 1);

1 row created.

Elapsed: 00:00:00.31
SQL>
SQL> Insert into SAP_RECON_SDB_RET_PRICE_DTL2
  2     (sdb_sap_kotabnr, sdb_sap_kschl, sdb_sap_vkorg, sdb_sap_vtweg, sdb_sap_pltyp, sdb_sap_matnr, sdb_sap_vrkme, sdb_kbetr, sdb_konwa, sdb_kpein, sdb_sap_kme
in, sdb_datab, sdb_datbi, fom_sap_kotabnr, fom_sap_kschl, fom_sap_vkorg, fom_sap_vtweg, fom_sap_pltyp, fom_sap_matnr, fom_sap_vrkme, fom_kbetr, fom_konwa, fom_k
pein, fom_sap_kmein, fom_datab, fom_datbi, result_type, d_recon_date, rownumber)
  3   Values    ('2', 'VKP0', 'M100', '01', 'P5', '000000000002043171', 'KG', 6.99, 'MYR', 1, 'KG', '20161011', '99991231', '2', 'VKP0', 'M100', '01', 'P5', '00
0000000002043171', 'KG', '6.99', 'MYR', 1, 'KG', '20161011', '20170313', 'VALID_TO_MISMATCH', TO_DATE('04/05/2017 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 1);

1 row created.

Elapsed: 00:00:00.42
SQL> Insert into SAP_RECON_SDB_RET_PRICE_DTL2
  2     (sdb_sap_kotabnr, sdb_sap_kschl, sdb_sap_vkorg, sdb_sap_vtweg, sdb_sap_pltyp, sdb_sap_matnr, sdb_sap_vrkme, sdb_kbetr, sdb_konwa, sdb_kpein, sdb_sap_kme
in, sdb_datab, sdb_datbi, fom_sap_kotabnr, fom_sap_kschl, fom_sap_vkorg, fom_sap_vtweg, fom_sap_pltyp, fom_sap_matnr, fom_sap_vrkme, fom_kbetr, fom_konwa, fom_k
pein, fom_sap_kmein, fom_datab, fom_datbi, result_type, d_recon_date, rownumber)
  3   Values    ('2', 'VKP0', 'M100', '01', 'P6', '000000000002043171', 'KG', 6.99, 'MYR', 1, 'KG', '20161011', '99991231', '2', 'VKP0', 'M100', '01', 'P6', '00
0000000002043171', 'KG', '6.99', 'MYR', 1, 'KG', '20161011', '20170313', 'VALID_TO_MISMATCH', TO_DATE('04/05/2017 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 1);

1 row created.

Elapsed: 00:00:00.31
SQL> Insert into SAP_RECON_SDB_RET_PRICE_DTL2
  2     (sdb_sap_kotabnr, sdb_sap_kschl, sdb_sap_vkorg, sdb_sap_vtweg, sdb_sap_pltyp, sdb_sap_matnr, sdb_sap_vrkme, sdb_kbetr, sdb_konwa, sdb_kpein, sdb_sap_kme
in, sdb_datab, sdb_datbi, fom_sap_kotabnr, fom_sap_kschl, fom_sap_vkorg, fom_sap_vtweg, fom_sap_pltyp, fom_sap_matnr, fom_sap_vrkme, fom_kbetr, fom_konwa, fom_k
pein, fom_sap_kmein, fom_datab, fom_datbi, result_type, d_recon_date, rownumber)
  3   Values
  4     ('2', 'VKP0', 'M101', '01', 'P4', '000000000002043171', 'KG', 7.49, 'MYR', 1, 'KG', '20161011', '99991231', '2', 'VKP0', 'M101', '01', 'P4', '0000000000
02043171', 'KG', '7.49', 'MYR', 1, 'KG', '20161011', '20170313', 'VALID_TO_MISMATCH', TO_DATE('04/05/2017 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 1);

1 row created.

Elapsed: 00:00:00.31
SQL>
SQL> COMMIT;

Commit complete.

Elapsed: 00:00:00.32
SQL>
SQL> UPDATE sap_recon_sdb_ret_price_dtl2 x
  2     SET SDB_DATBI = (
  3        SELECT to_char(max(to_date(sdb_datab,'YYYYMMDD')) -1,'YYYYMMDD')
  4                FROM sap_recon_sdb_ret_price_dtl2 y
  5               WHERE --y.sdb_sap_matnr      =  '000000000002043171' AND
  6                 y.sdb_sap_kotabnr      =  x.sdb_sap_kotabnr
  7                 AND y.sdb_sap_kschl      =  x.sdb_sap_kschl
  8                 AND y.sdb_sap_vtweg      =  x.sdb_sap_vtweg
  9                 AND y.sdb_sap_vkorg      =  x.sdb_sap_vkorg
 10                 AND NVL(y.sdb_sap_pltyp,'$')  =  NVL(x.sdb_sap_pltyp,'$')
 11                 AND y.sdb_sap_matnr      =  x.sdb_sap_matnr
 12                 AND y.sdb_sap_vrkme      =  x.sdb_sap_vrkme
 13                 and y.ROWNUMBER        =  x.rownumber + 1
 14             group by y.sdb_sap_matnr,y.sdb_sap_kotabnr,y.sdb_sap_kschl,y.sdb_sap_vtweg,y.sdb_sap_vkorg,NVL(y.sdb_sap_pltyp,'$'),y.sdb_sap_vrkme
 15   )
 16   WHERE x.result_type        =  'VALID_TO_MISMATCH'
 17     and exists (SELECT 'X'
 18            FROM sap_recon_sdb_ret_price_dtl2 y
 19            WHERE y.sdb_sap_kotabnr      =  x.sdb_sap_kotabnr
 20               AND y.sdb_sap_kschl      =  x.sdb_sap_kschl
 21               AND y.sdb_sap_vtweg      =  x.sdb_sap_vtweg
 22               AND y.sdb_sap_vkorg      =  x.sdb_sap_vkorg
 23               AND NVL(y.sdb_sap_pltyp,'$')  =  NVL(x.sdb_sap_pltyp,'$')
 24               AND y.sdb_sap_matnr      =  x.sdb_sap_matnr
 25               AND y.sdb_sap_vrkme      =  x.sdb_sap_vrkme
 26               and y.rownumber        =  x.rownumber + 1) ;

1 row updated.

Elapsed: 00:00:00.33


I'm guessing this is because your real table has lots more data in it. If so you may get some mileage by:

- Selecting the next date in a scalar subquery (BTW: please use "date" not varchar2 to store dates!)
- Updating this query where the subquery is not null.

For example:

update (SELECT SDB_DATBI, 
       (select to_char(max(to_date(sdb_datab,'YYYYMMDD'))-1,'YYYYMMDD')
        from sap_recon_sdb_ret_price_dtl2 x
        where y.sdb_sap_kotabnr      =  x.sdb_sap_kotabnr 
        AND y.sdb_sap_kschl      =  x.sdb_sap_kschl 
        AND y.sdb_sap_vtweg      =  x.sdb_sap_vtweg 
        AND y.sdb_sap_vkorg      =  x.sdb_sap_vkorg 
        AND NVL(y.sdb_sap_pltyp,'$')  =  NVL(x.sdb_sap_pltyp,'$') 
        AND y.sdb_sap_matnr      =  x.sdb_sap_matnr 
        AND y.sdb_sap_vrkme      =  x.sdb_sap_vrkme 
        and y.rownumber        =  x.rownumber + 1) mx
FROM   sap_recon_sdb_ret_price_dtl2 y )
set    SDB_DATBI = mx
where  mx is not null;


Note: this assumes that you can only have 2 rownumbers per group. If this isn't the case, you'll need a different approach.

If this doesn't help, then please post the execution plan for your update. You can generate this by running:

set serveroutput off
alter session set statistics_level = all;

... your update statement ...

select * from table(dbms_xplan.display_cursor(null, null, 'ALLSTATS LAST'));


Then send us the output of the "select * from table..." query.

Rating

  (2 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Reply

amit chaudhary, April 05, 2017 - 3:22 pm UTC

Hi,

Thank you for your quick feedback..
Yes, in actual my table contains approximately 1 million record. So performance is too bad. Secondly, for your note point : "this assumes that you can only have 2 rownumbers per group" - No, In actual I can have many records per group, not necessary 2 only.

Below Explain plan based on the update statement you provided:

Plan hash value: 2723158068

-----------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------------------
| 0 | UPDATE STATEMENT | | 1 | 53 | 4088M (2)|999:59:59 |
| 1 | UPDATE | SAP_RECON_SDB_RET_PRICE_DTL2 | | | | |
|* 2 | FILTER | | | | | |
| 3 | TABLE ACCESS FULL | SAP_RECON_SDB_RET_PRICE_DTL2 | 931K| 47M| 4395 (2)| 00:00:53 |
| 4 | SORT AGGREGATE | | 1 | 53 | | |
|* 5 | TABLE ACCESS FULL| SAP_RECON_SDB_RET_PRICE_DTL2 | 1 | 53 | 4397 (2)| 00:00:53 |
-----------------------------------------------------------------------------------------------------

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

2 - filter( (SELECT TO_CHAR(MAX(TO_DATE("SDB_DATAB",'YYYYMMDD'))-1,'YYYYMMDD') FROM
"SAP_RECON_SDB_RET_PRICE_DTL2" "X" WHERE "X"."SDB_SAP_MATNR"=:B1 AND "X"."SDB_SAP_VRKME"=:B2
AND "X"."SDB_SAP_VKORG"=:B3 AND "X"."SDB_SAP_KOTABNR"=:B4 AND "X"."ROWNUMBER"+1=:B5 AND
NVL("X"."SDB_SAP_PLTYP",'$')=NVL(:B6,'$') AND "X"."SDB_SAP_KSCHL"=:B7 AND
"X"."SDB_SAP_VTWEG"=:B8) IS NOT NULL)
5 - filter("X"."SDB_SAP_MATNR"=:B1 AND "X"."SDB_SAP_VRKME"=:B2 AND
"X"."SDB_SAP_VKORG"=:B3 AND "X"."SDB_SAP_KOTABNR"=:B4 AND "X"."ROWNUMBER"+1=:B5 AND
NVL("X"."SDB_SAP_PLTYP",'$')=NVL(:B6,'$') AND "X"."SDB_SAP_KSCHL"=:B7 AND
"X"."SDB_SAP_VTWEG"=:B8)

to Amit on the explain plan provided

Rajeshwaran, April 06, 2017 - 12:13 pm UTC

I hope the provided explain plan reflects the correct cardinality.
----------------------------------------------------------------------------------------------------- 
| Id | Operation   | Name        | Rows | Bytes | Cost (%CPU)| Time   | 
----------------------------------------------------------------------------------------------------- 
| 0 | UPDATE STATEMENT  |         | 1  | 53  | 4088M (2) |999:59:59  | 
| 1 | UPDATE    | SAP_RECON_SDB_RET_PRICE_DTL2  |   |   |    |    | 
|* 2 | FILTER    |         |   |  |    |    | 
| 3 | TABLE ACCESS FULL | SAP_RECON_SDB_RET_PRICE_DTL2  | 931K | 47M | 4395 (2) | 00:00:53  | 
| 4 | SORT AGGREGATE  |         | 1  | 53  |    |    | 
|* 5 | TABLE ACCESS FULL| SAP_RECON_SDB_RET_PRICE_DTL2 | 1   | 53 | 4397 (2) | 00:00:53  | 
----------------------------------------------------------------------------------------------------- 

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

2 - filter( (SELECT TO_CHAR(MAX(TO_DATE("SDB_DATAB",'YYYYMMDD'))-1,'YYYYMMDD') FROM 
"SAP_RECON_SDB_RET_PRICE_DTL2" "X" WHERE "X"."SDB_SAP_MATNR"=:B1 AND "X"."SDB_SAP_VRKME"=:B2 
AND "X"."SDB_SAP_VKORG"=:B3 AND "X"."SDB_SAP_KOTABNR"=:B4 AND "X"."ROWNUMBER"+1=:B5 AND 
NVL("X"."SDB_SAP_PLTYP",'$')=NVL(:B6,'$') AND "X"."SDB_SAP_KSCHL"=:B7 AND 
"X"."SDB_SAP_VTWEG"=:B8) IS NOT NULL) 
5 - filter("X"."SDB_SAP_MATNR"=:B1 AND "X"."SDB_SAP_VRKME"=:B2 AND 
"X"."SDB_SAP_VKORG"=:B3 AND "X"."SDB_SAP_KOTABNR"=:B4 AND "X"."ROWNUMBER"+1=:B5 AND 
NVL("X"."SDB_SAP_PLTYP",'$')=NVL(:B6,'$') AND "X"."SDB_SAP_KSCHL"=:B7 AND 
"X"."SDB_SAP_VTWEG"=:B8)

(i) a full scan on the table "SAP_RECON_SDB_RET_PRICE_DTL2" returns 931K records. (step id=3 from the plan)
(ii) for each row returned in step id=3, go to the step id=5 and do full scan on table "SAP_RECON_SDB_RET_PRICE_DTL2" and return one row and aggregate them.
(iii) validate the filter at step id=2 and then update them back to the table.

so looking at this in total, we got 931K times the full scan is performed at step id=5 to return just one row per execution of this step.

enter into analytics, where we could things in bulk using full scans and hashjoins. 

demo@ORA12C> select sdb_sap_kotabnr , sdb_sap_kschl ,
  2         sdb_sap_vtweg  , sdb_sap_vkorg  ,
  3         sdb_sap_pltyp , sdb_sap_matnr ,
  4         sdb_sap_vrkme , rownumber, sdb_datab,sdb_datbi
  5  from sap_recon_sdb_ret_price_dtl2;

S SDB_ SD SDB_ SD SDB_SAP_MATNR      SDB  ROWNUMBER SDB_DATA SDB_DATB
- ---- -- ---- -- ------------------ --- ---------- -------- --------
1 VKP0 01 B100    000000000002043171 KG           1 20161221 99991231
1 VKP0 01 B100    000000000002043171 KG           2 20170329 99991231
1 VKP0 01 M100    000000000002043171 KG           1 20161224 99991231
2 VKP0 01 M100 P1 000000000002043171 KG           1 20161201 99991231
2 VKP0 01 M100 P2 000000000002043171 KG           1 20161201 99991231
2 VKP0 01 M100 P3 000000000002043171 KG           1 20161224 99991231
2 VKP0 01 M100 P5 000000000002043171 KG           1 20161011 99991231
2 VKP0 01 M100 P6 000000000002043171 KG           1 20161011 99991231
2 VKP0 01 M101 P4 000000000002043171 KG           1 20161011 99991231

9 rows selected.

demo@ORA12C> select *
  2  from (
  3  select sdb_sap_kotabnr , sdb_sap_kschl ,
  4         sdb_sap_vtweg  , sdb_sap_vkorg  ,
  5         sdb_sap_pltyp , sdb_sap_matnr ,
  6         sdb_sap_vrkme , rownumber, sdb_datab,sdb_datbi,
  7         lag(rownumber) over( partition by sdb_sap_kotabnr , sdb_sap_kschl ,
  8         sdb_sap_vtweg  , sdb_sap_vkorg  ,
  9         sdb_sap_pltyp , sdb_sap_matnr ,
 10         sdb_sap_vrkme order by rownumber desc) rn,
 11        max( to_date(sdb_datab,'yyyymmdd') ) over( partition by sdb_sap_kotabnr , sdb_sap_kschl ,
 12         sdb_sap_vtweg  , sdb_sap_vkorg  ,
 13         sdb_sap_pltyp , sdb_sap_matnr ,
 14         sdb_sap_vrkme order by rownumber desc) max_dt
 15  from sap_recon_sdb_ret_price_dtl2
 16      )
 17  where rn = rownumber+1 ;

S SDB_ SD SDB_ SD SDB_SAP_MATNR      SDB  ROWNUMBER SDB_DATA SDB_DATB         RN MAX_DT
- ---- -- ---- -- ------------------ --- ---------- -------- -------- ---------- -----------
1 VKP0 01 B100    000000000002043171 KG           1 20161221 99991231          2 29-MAR-2017

demo@ORA12C> merge into sap_recon_sdb_ret_price_dtl2 t1 using (
  2     select *
  3  from (
  4  select sdb_sap_kotabnr , sdb_sap_kschl ,
  5         sdb_sap_vtweg  , sdb_sap_vkorg  ,
  6         sdb_sap_pltyp , sdb_sap_matnr ,
  7         sdb_sap_vrkme , rownumber, sdb_datab,sdb_datbi,
  8         lag(rownumber) over( partition by sdb_sap_kotabnr , sdb_sap_kschl ,
  9         sdb_sap_vtweg  , sdb_sap_vkorg  ,
 10         sdb_sap_pltyp , sdb_sap_matnr ,
 11         sdb_sap_vrkme order by rownumber desc ) rn,
 12        max( to_date(sdb_datab,'yyyymmdd') ) over( partition by sdb_sap_kotabnr , sdb_sap_kschl ,
 13         sdb_sap_vtweg  , sdb_sap_vkorg  ,
 14         sdb_sap_pltyp , sdb_sap_matnr ,
 15         sdb_sap_vrkme order by rownumber desc )-1 max_dt
 16  from sap_recon_sdb_ret_price_dtl2
 17      )
 18  where rn = rownumber+1       ) t2
 19  on ( t1.sdb_sap_kotabnr = t2.sdb_sap_kotabnr and
 20     t1.sdb_sap_kschl = t2.sdb_sap_kschl and
 21     t1.sdb_sap_vtweg = t2.sdb_sap_vtweg and
 22     t1.sdb_sap_vkorg = t2.sdb_sap_vkorg and
 23     ( t1.sdb_sap_pltyp = t2.sdb_sap_pltyp or
 24             ( t1.sdb_sap_pltyp is null and t2.sdb_sap_pltyp is null) ) and
 25     t1.sdb_sap_matnr = t2.sdb_sap_matnr and
 26     t1.sdb_sap_vrkme = t2.sdb_sap_vrkme and
 27     t1.rownumber+1 = t2.rn )
 28  when matched then
 29     update set t1.sdb_datbi = to_char(t2.max_dt,'yyyymmdd')
 30  /

1 row merged.

demo@ORA12C> select sdb_sap_kotabnr , sdb_sap_kschl ,
  2         sdb_sap_vtweg  , sdb_sap_vkorg  ,
  3         sdb_sap_pltyp , sdb_sap_matnr ,
  4         sdb_sap_vrkme , rownumber, sdb_datab,sdb_datbi
  5  from sap_recon_sdb_ret_price_dtl2;

S SDB_ SD SDB_ SD SDB_SAP_MATNR      SDB  ROWNUMBER SDB_DATA SDB_DATB
- ---- -- ---- -- ------------------ --- ---------- -------- --------
1 VKP0 01 B100    000000000002043171 KG           1 20161221 20170328
1 VKP0 01 B100    000000000002043171 KG           2 20170329 99991231
1 VKP0 01 M100    000000000002043171 KG           1 20161224 99991231
2 VKP0 01 M100 P1 000000000002043171 KG           1 20161201 99991231
2 VKP0 01 M100 P2 000000000002043171 KG           1 20161201 99991231
2 VKP0 01 M100 P3 000000000002043171 KG           1 20161224 99991231
2 VKP0 01 M100 P5 000000000002043171 KG           1 20161011 99991231
2 VKP0 01 M100 P6 000000000002043171 KG           1 20161011 99991231
2 VKP0 01 M101 P4 000000000002043171 KG           1 20161011 99991231

9 rows selected.

demo@ORA12C> delete from plan_table;

8 rows deleted.

demo@ORA12C> explain plan for
  2  merge into sap_recon_sdb_ret_price_dtl2 t1 using (
  3     select *
  4  from (
  5  select sdb_sap_kotabnr , sdb_sap_kschl ,
  6         sdb_sap_vtweg  , sdb_sap_vkorg  ,
  7         sdb_sap_pltyp , sdb_sap_matnr ,
  8         sdb_sap_vrkme , rownumber, sdb_datab,sdb_datbi,
  9         lag(rownumber) over( partition by sdb_sap_kotabnr , sdb_sap_kschl ,
 10         sdb_sap_vtweg  , sdb_sap_vkorg  ,
 11         sdb_sap_pltyp , sdb_sap_matnr ,
 12         sdb_sap_vrkme order by rownumber desc ) rn,
 13        max( to_date(sdb_datab,'yyyymmdd') ) over( partition by sdb_sap_kotabnr , sdb_sap_kschl ,
 14         sdb_sap_vtweg  , sdb_sap_vkorg  ,
 15         sdb_sap_pltyp , sdb_sap_matnr ,
 16         sdb_sap_vrkme order by rownumber desc )-1 max_dt
 17  from sap_recon_sdb_ret_price_dtl2
 18      )
 19  where rn = rownumber+1       ) t2
 20  on ( t1.sdb_sap_kotabnr = t2.sdb_sap_kotabnr and
 21     t1.sdb_sap_kschl = t2.sdb_sap_kschl and
 22     t1.sdb_sap_vtweg = t2.sdb_sap_vtweg and
 23     t1.sdb_sap_vkorg = t2.sdb_sap_vkorg and
 24     ( t1.sdb_sap_pltyp = t2.sdb_sap_pltyp or
 25             ( t1.sdb_sap_pltyp is null and t2.sdb_sap_pltyp is null) ) and
 26     t1.sdb_sap_matnr = t2.sdb_sap_matnr and
 27     t1.sdb_sap_vrkme = t2.sdb_sap_vrkme and
 28     t1.rownumber+1 = t2.rn )
 29  when matched then
 30     update set t1.sdb_datbi = to_char(t2.max_dt,'yyyymmdd')
 31  /

Explained.

demo@ORA12C> @xplan

PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------------------------------------------
Plan hash value: 150652288

-------------------------------------------------------------------------------------------------------
| Id  | Operation              | Name                         | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------------------
|   0 | MERGE STATEMENT        |                              |     1 |    15 |     7  (15)| 00:00:01 |
|   1 |  MERGE                 | SAP_RECON_SDB_RET_PRICE_DTL2 |       |       |            |          |
|   2 |   VIEW                 |                              |       |       |            |          |
|*  3 |    HASH JOIN           |                              |     1 |   246 |     7  (15)| 00:00:01 |
|*  4 |     VIEW               |                              |     9 |   666 |     4  (25)| 00:00:01 |
|   5 |      WINDOW SORT       |                              |     9 |   549 |     4  (25)| 00:00:01 |
|   6 |       TABLE ACCESS FULL| SAP_RECON_SDB_RET_PRICE_DTL2 |     9 |   549 |     3   (0)| 00:00:01 |
|   7 |     TABLE ACCESS FULL  | SAP_RECON_SDB_RET_PRICE_DTL2 |     9 |  1548 |     3   (0)| 00:00:01 |
-------------------------------------------------------------------------------------------------------

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

   3 - access("T1"."SDB_SAP_KOTABNR"="from$_subquery$_007"."SDB_SAP_KOTABNR" AND
              "T1"."SDB_SAP_KSCHL"="from$_subquery$_007"."SDB_SAP_KSCHL" AND
              "T1"."SDB_SAP_VTWEG"="from$_subquery$_007"."SDB_SAP_VTWEG" AND
              "T1"."SDB_SAP_VKORG"="from$_subquery$_007"."SDB_SAP_VKORG" AND
              "T1"."SDB_SAP_MATNR"="from$_subquery$_007"."SDB_SAP_MATNR" AND
              "T1"."SDB_SAP_VRKME"="from$_subquery$_007"."SDB_SAP_VRKME" AND
              "from$_subquery$_007"."RN"="T1"."ROWNUMBER"+1)
       filter("T1"."SDB_SAP_PLTYP"="from$_subquery$_007"."SDB_SAP_PLTYP" AND
              "T1"."SDB_SAP_PLTYP" IS NOT NULL OR "T1"."SDB_SAP_PLTYP" IS NULL AND
              "from$_subquery$_007"."SDB_SAP_PLTYP" IS NULL)
   4 - filter("RN"="ROWNUMBER"+1)

29 rows selected.

demo@ORA12C>

you could see we do just do two full scan, one for analytics followed by a HASH join to just retain the matching resulsets to update them back.
more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's latest video and Chris's latest video from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

DBMS_XPLAN

More on PL/SQL routine DBMS_XPLAN here