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
>
>
|