ibatis-user-java mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Jeff Butler" <jeffgbut...@gmail.com>
Subject Re: Custom Type Handler for Oracle DATE
Date Wed, 27 Sep 2006 14:45:08 GMT
That's very interesting - and you did a lot of work on this!

I'm no Oracle expert, but I did a google search too and found some others
reporting similar issues related to slow queries when using
setTimestamp.  So, barring someone else's ideas, I think you're probably
doing the right thing with the custom type handler.

BTW - With Abator 1.0, you can specify a custom type handler in the
<columnOverride> - so it would be possible for Abator to generate the SQL
maps just as you want - you would't have to go back and add the CTH to the
generated XML.

Jeff Butler


On 9/27/06, aris <aris@agilemovement.it> wrote:
>
> Thank you Jeff for your reply, I'll try Abator 1.0 but probably I
> haven't well explained the problem.
>
> I like the way Abator generates xml mappings and domain objects and I
> think it's right to set the oracle Date as (jdbc) TIMESTAMP (if set to
> (jdbc) DATE then hours, minutes and seconds are lost).
>
> The problem is related to this specific context:
> - Oracle 9
> - Oracle jdbc driver 9 or superior
> - Field of type DATE (oracle) with an index on it
>
> Oracle 9 introduces the new data type (oracle) TIMESTAMP.
>
> I'm using Oracle 9 and I've made this test. I've created following table
> and indexes and I've fill in the table with 500.000 records:
> ================================================================
> create table TABLE_TEST (
>     COL_DATE           DATE DEFAULT sysdate NULL,
>     COL_TIMESTAMP      TIMESTAMP(6) DEFAULT systimestamp NULL,
>     COL_WASTE_SPACE    VARCHAR2(1000) NULL
>     );
>
> create index IX_TABLE_TEST_COL_DATE
>     on TABLE_TEST(COL_DATE);
>
> create index IX_TABLE_TEST_COL_TIMESTAMP
>     on TABLE_TEST(COL_TIMESTAMP);
> ================================================================
>
> Then I test queries as follow (sql statement logged with
> net.rkbloom.logdriver):
>
> SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH.mm");
> String str_date = "2006-09-11 13.00";
> java.util.Date date = sdf.parse(sDate);
>
> ======================= TEST 1 =======================
> - sql statement:
>   "select count(*) as CNT from TABLE_TEST where COL_DATE >= ?" with
> bind parameters: {1=oracle.sql.DATE@1865b28}
>
> - prepared statement parameters:
>   pst.setObject(1, new oracle.sql.DATE(new Timestamp(date.getTime())));
>
> - result:
>   CNT: 88072 in 31 msec (optimizer uses index on COL_DATE)
>
> ======================= TEST 2 =======================
> - sql statement:
>   "select count(*) as CNT from TABLE_TEST where COL_DATE >= to_date(?,
> 'yyyy-mm-dd hh24.mi')" with bind parameters: {1=2006-09-11 13.00}
>
> - prepared statement parameters:
>    pst.setString(1, str_date);
>
> - result:
>   CNT: 88072 in 31 msec (optimizer uses index on COL_DATE)
>
> ======================= TEST 3 =======================
> - sql statement:
>   "select count(*) as CNT from TABLE_TEST where COL_DATE >= ?" with
> bind parameters: {1=2006-09-11 13:00:00.0}
>
> - prepared statement parameters:
>   pst.setTimestamp(1, new Timestamp(date.getTime()));
>
> - result:
>   CNT: 88072 in 234 msec (optimizer doesn't use index on COL_DATE so a
> full table scan is performed)
>
> ======================= TEST 4 =======================
> - sql statement:
> "select count(*) as CNT from TABLE_TEST where COL_TIMESTAMP >= ?" with
> bind parameters: {1=2006-09-11 13:00:00.0}
>
> - prepared statement parameters:
>   pst.setTimestamp(1, new Timestamp(date.getTime()));
>
> - result:
>   CNT: 88072 in 31 msec (optimizer uses index on COL_TIMESTAMP)
>
> ======================= TEST 5 =======================
> - sql statement:
>   "select count(*) as CNT from TABLE_TEST where COL_TIMESTAMP >=
> to_timestamp(?, 'yyyy-mm-dd hh24.mi')" with bind parameters:
> {1=2006-09-11 13.00}
>
> - prepared statement parameters:
>   pst.setString(1, str_date);
>
> - result:
>   CNT: 88072 in 31 msec (optimizer uses index on COL_TIMESTAMP)
>
> My conclusion:
> TIMESTAMP has no problem with prepared statement but for backward
> compatibility with Oracle 8 my db schema has to use DATE.
> If you don't want to lose hours, minute and seconds then you cannot use
> the method setDate() of a prepared statement but you have to use the
> setTimestamp().
> With Oracle jdbc driver version 9 or superior using setTimestamp()
> "confuses" the optimizer that generates a query that can't use index.
> TEST1 and TEST2 are the only ways I've found to force the use of the
> index (a third choice can be Oracle hints but I don't like them).
>
> This is the query in my SqlMapConfig:
> <select
>     id="selectCountByExample"
>     resultClass="java.math.BigDecimal"
>     parameterClass="java.util.Map">
>
>   select count(*) from TABLE_TEST
>        <include refid="abatorgenerated_Example_Where_Clause"/>
> </select>
>
> <sql id="abatorgenerated_Example_Where_Clause">
> <dynamic prepend="where">
>   <isPropertyAvailable prepend="and" property="AND_COL_DATE_GE">
>     COL_DATE <![CDATA[ >= ]]> #colDate#
>   </isPropertyAvailable>
>   ....
> </sql>
>
> Ibatis uses the type of each parameters in the parameterClass to handle
> it. A parameter of type java.util.Date is added to the prepared
> statement with a setTimestamp(). In a context like the one stated above
> this produce a very slow query. So I've thought that use a custom type
> handler (to implement a solution like in TEST 1) was the best compromise
> between portability and maintanability.
>
> Sorry for this very long post but I hope the context is more clear now.
>
> Best regards,
> aris
>
>

Mime
View raw message