Atempt delete rows in oracle database older than 1 hour causing timeouts

Question

I have strange problem that I can't fix.

I'm trying to delete all rows that them timestamp is older than 1 hour.

SQL :

DELETE  FROM TABLE WHERE TIMESTAMP <= SYSTIMESTAMP - 1/24

Whole code work perfect in SQL Developer but when i try do same in iBatis.net i got timeouts.

  <statements>
    <delete id="DeleteRows" parameterClass="int">
      <![CDATA[
      DELETE  FROM TABLE WHERE TIMESTAMP <= SYSTIMESTAMP - #VALUE#/24
<!--THIS DON'T WORK-->
      ]]>
    </delete>
  </statements>

Another strange thing is that problem don't exist when i hange less-than operator to 'equal-to' e.g

  <statements>
    <delete id="DeleteRows" parameterClass="int">
      <![CDATA[
      DELETE  FROM TABLE WHERE TIMESTAMP = SYSTIMESTAMP - #VALUE#/24
<!--THIS WORK-->
      ]]>
    </delete>
  </statements>

Timeouts i gen only with Les-than and grater-than operators and only in iBatis.net. Does any body know why ?

Funny fact. When i query DELETE FROM TABLE WHERE TIMESTAMP < '07-AUG-11' it work. Also when i try to query DELETE FROM TABLE WHERE TIMESTAMP BETWEEN '07-AUG-11' AND SYSTIMESTAMP it works too. It seems to be problem ony with < and > combine with SYSTIMESTAMP Is there other way to delete rows older than one hour without using those operators ? Thanks

Answer 1

Adding answer to my own question because I was able to resolve it maybe someone will need this in future.

First i created new select query :

<select id="DbTime" resultClass="DateTime">
  <![CDATA[SELECT SYSTIMESTAMP FROM DUAL]]>
</select>

Then I run this select

DateTime currentDate = DataSources.DB.QueryForObject<DateTime>("Map.DbTime", null);
DateTime finalDate= currentDate.AddHours(-24);

And pass finalDate as parameter

<delete id="DeleteRows">
  <![CDATA[
  DELETE FROM TABLE  WHERE TIMESTAMP < #value#
  ]]>
</delete>

Now it is working but still don't know why my first solution want to work.

Answer 2

You may need to

a) Increase the CommandTimeout for your DbCommand running the query (not sure how to do it in iBatis.NET)

b) Check if you have a transaction open, that is causing the a deadlock (the delete stmt waiting for the rows to be available), see View open transactions in Oracle