cancel
Showing results for 
Search instead for 
Did you mean: 

Delete from hana DB

Former Member
0 Kudos

I have a question about optimizing for hana db.

In old days we would remove rows from a big table like this:


DO.

EXEC SQL.

delete from table

where mandt = :sy-mandt

and rownum <= :c_block_size

ENDEXC.

IF sy-dbcnt EQ 0.

EXIT.

ENDIF.

CALL FUNCTION 'DB_COMMIT'.

ENDDO.

Because HANA is a in-memory DB can we just do this:


delete from table.

That should be working great right ?

Accepted Solutions (0)

Answers (1)

Answers (1)

rindia
Active Contributor
0 Kudos

Hi Shreyas,

If you know what data has to be deleted then you can write the procedure with delete statement with proper WHERE condition.

Whenever  you call the procedure, records meeting the criteria will be deleted.

First you try this in development environment and if you are happy with the results then only try in other environments.

Regards

Raj

Former Member
0 Kudos

Hi Raj,

Thanks for the answer but what if there are no "where" conditions and you just want to delete all in a table ?

rindia
Active Contributor
0 Kudos
  1. DO. 
  2. EXEC SQL. 
  3. delete from table 
  4. where mandt = :sy-mandt 
  5. and rownum <= :c_block_size 
  6. ENDEXC. 
  7. IF sy-dbcnt EQ 0
  8. EXIT. 
  9. ENDIF. 
  10. CALL FUNCTION 'DB_COMMIT'
  11. ENDDO. 

Your query has where condition, hence use procedure with input paramerets for mandt, rownum.

Else all the records would be deleted.

Former Member
0 Kudos

All records must be deleted. The where is only done for client dependency but the row_num is just s that the DB wouldn't hang/throw errors if you delete 1 million records at once thats why its in a DO statement.

This is not possible in HANA and i was also thinking because HANA DB is so fast do we still need to do it like this or can we just remove 1 million rows at once ?

Former Member
0 Kudos

If you want to get rid of all records, a TRUNCATE is usually the better option. Only in exceptional situations (e.g. RO enqueues on Oracle) a DELETE of a significant amount of records can be better.

Former Member
0 Kudos

But if i need the option to Rollback i can only use delete right

My case is just would a open sql delete from <big table> be sufficiant to make it work ?

Will HANA handle that without memory issues ?

Former Member
0 Kudos

Yes, a TRUNCATE can't be undone (unless you disable the DDL auto commit). A DELETE with SAP HANA should only touch the deletion bit in main and so there shouldn't be a significant memory increase based on my understanding.