1

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

Aht
  • 583
  • 4
  • 25

2 Answers2

1

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

Community
  • 1
  • 1
Fernando Gonzalez Sanchez
  • 3,315
  • 4
  • 32
  • 45
  • Timeout is thrown after 1 min so is quite long. In SQL Developer time to execute this query is about 0.2 s. Only i use this table right now so there so other connection to it. 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 `>` – Aht Nov 02 '16 at 19:55
  • Looks like an optimizer thing, that's a bit out of my scope, here a couple of links to start https://community.oracle.com/thread/996993 & http://dba.stackexchange.com/questions/104797/is-index-performance-on-a-timestamp6-column-impacted-when-converting-it-to-a-n – Fernando Gonzalez Sanchez Nov 02 '16 at 20:16
  • Thanks I was able to resolve my problem using other query – Aht Nov 03 '16 at 14:48
0

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.

Aht
  • 583
  • 4
  • 25