what's the best solution of "DELETE FROM dbtab"

[Former Member]

I found that when I program with the statement "DELETE FROM dbtab",as you can see there Isn't any Arguments,that is I want to delete all the Entries in the 'dbtab'.But when I trace it with ST05,finding that there will add a "MANDT = 'XX'" automatically(generally there would be just only one value of the field 'MANDT').What's worse the DB6 Optimizer Estimated  a Explain Plan of accessing the 'dbtab' with an index 'XX' while the 'key columns' is '0'.

In my opinion,the best way to delete the whole table is via a TBSCAN without any Arguments,any Arguments or index scan would be useless efforts.So I did some test with this situation,can anybody join me with the Discussion?

the ABAP source code:

*&---------------------------------------------------------------------*

*& Report  YWYZ01

*&

*&---------------------------------------------------------------------*

*&

*&

*&---------------------------------------------------------------------*

REPORT ywyz01.

DATA: itab TYPE TABLE OF ymean,

       t1 TYPE f,t2 LIKE t1.

GET RUN TIME FIELD t1.

DELETE FROM ymean CLIENT SPECIFIED

%_HINTS DB6 '<TBSCAN TABLE=''YMEAN'' />'.

COMMIT WORK.

GET RUN TIME FIELD t2.

t2 = t2 - t1.

WRITE / t2.

SELECT * INTO TABLE itab FROM mean UP TO 20000 ROWS.

IF sy-subrc EQ 0.

   INSERT ymean FROM TABLE itab ACCEPTING DUPLICATE KEYS.

   COMMIT WORK.

ENDIF.

GET RUN TIME FIELD t1.

DELETE FROM ymean.

COMMIT WORK.

GET RUN TIME FIELD t2.

t2 = t2 - t1.

WRITE / t2.

SELECT * INTO TABLE itab FROM mean UP TO 20000 ROWS.

IF sy-subrc EQ 0.

   INSERT ymean FROM TABLE itab ACCEPTING DUPLICATE KEYS.

   COMMIT WORK.

ENDIF.

GET RUN TIME FIELD t1.

DELETE FROM ymean

%_HINTS DB6 '<TBSCAN TABLE=''YMEAN'' />'.

COMMIT WORK.

GET RUN TIME FIELD t2.

t2 = t2 - t1.

WRITE / t2.

SELECT * INTO TABLE itab FROM mean UP TO 20000 ROWS.

IF sy-subrc EQ 0.

   INSERT ymean FROM TABLE itab ACCEPTING DUPLICATE KEYS.

   COMMIT WORK.

ENDIF.

GET RUN TIME FIELD t1.

DELETE FROM ymean CLIENT SPECIFIED.

COMMIT WORK.

GET RUN TIME FIELD t2.

t2 = t2 - t1.

WRITE / t2.

SELECT * INTO TABLE itab FROM mean UP TO 20000 ROWS.

IF sy-subrc EQ 0.

   INSERT ymean FROM TABLE itab ACCEPTING DUPLICATE KEYS.

   COMMIT WORK.

ENDIF.

The results are:

2.4406470000000000E+06

1.7100090000000000E+06

2.0519840000000000E+06

2.6726900000000000E+06

as you can see,the fastest way is 'leave where it is'.WHY?

These are Explain Plan,since the max attach file number is 3:

1:

SQL Statement

  DELETE

  FROM

    "YMEAN"

DB2 Optimizer Guidelines

/* <OPTGUIDELINES><TBSCAN TABLE='"YMEAN"' /></OPTGUIDELINES> */

Access Plan      Opt Level = 5 ; Parallelism = None

    0 DELETE STATEMENT ( Estimated Costs =  3.430E+02 [timerons] ) num_rows tot_cost i/o_cost

           1 RETURN  1.0000E+00  3.4300E+02  8.8000E+01

           2 DELETE  1.0000E+00  3.4300E+02  8.8000E+01

               3 TBSCAN YMEAN  1.0000E+00  3.3543E+02  8.7000E+01

2:

SQL Statement

  DELETE

  FROM

    "YMEAN"

  WHERE

    "MANDT" = ?

Access Plan      Opt Level = 5 ; Parallelism = None

    0 DELETE STATEMENT ( Estimated Costs =  3.131E-01 [timerons] ) num_rows tot_cost i/o_cost

           1 RETURN  3.9999E-02  3.1306E-01  3.9999E-02

           2 DELETE  1.0000E+00  3.1306E-01  3.9999E-02

               3 IXSCAN YMEAN~N #key columns:  0  3.9999E-02  1.0534E-02  0.0000E+00

3:

SQL Statement

  DELETE

  FROM

    "YMEAN"

  WHERE

    "MANDT" = ?

DB2 Optimizer Guidelines

/* <OPTGUIDELINES><TBSCAN TABLE='"YMEAN"' /></OPTGUIDELINES> */

Access Plan      Opt Level = 5 ; Parallelism = None

    0 DELETE STATEMENT ( Estimated Costs =  3.523E+03 [timerons] ) num_rows tot_cost i/o_cost

           1 RETURN  4.2072E+02  3.5234E+03  5.0772E+02

           2 DELETE  1.0518E+04  3.5234E+03  5.0772E+02

               3 TBSCAN YMEAN  4.2072E+02  3.4135E+02  8.7000E+01

4:

SQL Statement

  DELETE

  FROM

    "YMEAN"

Access Plan      Opt Level = 5 ; Parallelism = None

    0 DELETE STATEMENT ( Estimated Costs =  7.574E+00 [timerons] ) num_rows tot_cost i/o_cost

           1 RETURN  1.0000E+00  7.5742E+00  1.0000E+00

           2 DELETE  1.0000E+00  7.5742E+00  1.0000E+00

               3 IXSCAN YMEAN~N #key columns:  0  1.0000E+00  1.0827E-02  0.0000E+00

[Former Member]

Actually, the fastest way to delete all data from a table is to drop the table and then recreate it.

Rob

[Former Member]

Agreed that DROP is fastest way, but keep in mind that the table is client-dependent, so will delete data in all clients.

Jim

[Former Member]

I think that this is what the OP wants.

Rob

[Former Member]

Thank you Bob,I would say that the Statement 'DROP' is a DML which is embedded  into ABAP  Dictionary.So it's Impossible for me to use 'DROP' in my program,or do it with SE14 which is not my desired solution,is there any other solution which can be implemented with open sql.

[Former Member]

You should be able to use native SQL to do the DROP in your ABAP program.

Rob

[former_member195402]

Hi,

please have a look at this thread regarding your issue: http://scn.sap.com/thread/1019549

Regards,

Klaus

[Former Member]

Hey Klaus,

I know with 'SE14' I can deal with it very well,but when it comes to custom program,I can't do it like that,as to 'Native SQL',it's something like complexity and not flexible.

[former_member195402]

Hi,

do you want it to use in your own company systems, or do you want to deliver this code as part of an application for customer systems?

In your on company Native SQL may not be an issue, if you all have the same database DB6. But if you have another database for SAP systems in your company and you want to deliver your code, or if you have to delver your code to customer systems, then your DB6-hint may not be helpful as well. In this case also other client data may exist and you shouldn't delete the complete table content.

I would do the following code:

• Lock the table (Lock object E_TABLE)
• Call an update function module Z_DELETE_YMEAN_COMPLETE IN UPDATE TASK, where you delete all client rows.
• COMMIT WORK AND WAIT
• Lock the table (Lock object E_TABLE)
• INSERT the entries into YMEAN with an update function module Z_INSERT_YMEAN_COMPLETE IN UPDATE TASK in one or several calls.
• COMMIT WORK AND WAIT

This should work database independent.

Regards,

Klaus