Hi,
thanks for the support, in the meantime I have upgraded the oracle
driver (table linked is a oracle table) and it seems to improve the
situation..
It seems from the trace that that the where clause seems to be missing
for the linked
table under some circumstances ....When I got more details i will
write an follow up...;-)
Thanks
Tobias
On 2 Aug., 21:21, Thomas Mueller <thomas.tom.muel...@gmail.com> wrote:
> Hi,
>
> I don't know what the problem could be, but you should be able to see
> what SQL statement is executed on the Oracle side by setting the trace
> level to 3 (debug):
>
> jdbc:h2:...;TRACE_LEVEL_FILE=3
>
> Regards,
> Thomas
>
>
>
> On Fri, Jul 30, 2010 at 3:08 PM, tunix <thu...@googlemail.com> wrote:
> > Hello,
>
> > I have an unusual use case, where I join a huge oracle table b with a
> > smaller local table a.
> > I know this not optimal for performance but in the older h2
> > distributions the response time
> > was quite reasonable. In the new version (new than 1.2.132) the
> > following scenario is terrible slow....
>
> > select a.A1 , b.B1 from tbl_local a, tbl_linked_to_oracle b
> > where
> > b.refdate=to_date('2010-11-01','YYYY-MM-DD')
> > and a.X1=b.X1
>
> > In the old versions the direct comparison of b.refdate="2010-11-01"
> > was possible, even the b was on the oracle table....
>
> > The new versions refuse to compare refdate with the ascii value and
> > complain with
> > an ORA error.
> > So I have changed return type of the to_date function from a pure
> > ascii string
> > to an java.sql.Date type.... I have no glue wheter this result in an
> > non working
> > index or what else has change under the hood of h2 ?
> > Any pointers are highly appreciated ;-)
>
> > Below the code of the to_date functions....
>
> > Thanks
> > Tobias
> > ---------------------------------
> > Old to_date:
> > --------------------------------
> > public static String toDate(String value,String format) {
> > //return value;
> > String tmp=value;
> > if (format.equalsIgnoreCase("YYYYMMDD"))
> > {
> > tmp=value.substring(0,4)+"-"+value.substring
> > (4,6)+"-"+value.substring(6,8);
> > //Log.info(0,"Info - HSQLDB toDate
> > Function"+value
> > +":"+format+" delivered "+tmp,null);
>
> > }
> > if ( format.equalsIgnoreCase("DD-MM-YYYY")){
> > //tmp=value.substring(0,4)+"-"+value.substring
> > (4,6)+"-"+value.substring(6,8);
> > tmp=value.substring(8,10)+"-"+value.substring
> > (5,7)+"-"+value.substring(0,4);
>
> > }
> > if ( format.equalsIgnoreCase("YYYY-MM-DD")){
> > tmp=value.substring(0,4)+"-"+value.substring
> > (5,7)+"-"+value.substring(8,10)+" 00:00:00.0";
> > //
> > tmp=value.substring(8,10)+"-"+value.substring
> > (5,7)+"-"+value.substring(0,4)+" 00:00:00.0";
> > }
>
> > return tmp ;
> > }
> > --------------------------------
> > New to_date
> > --------------------------------
> > public static Timestamp toDate(String p1,String p2) throws
> > ParseException
> > {
> > if("YYYY-MM-DD".equalsIgnoreCase(p2))
> > return new Timestamp(new SimpleDateFormat
> > ("yyyy-MM-dd").parse(p1).getTime());
> > if("YYYYMMDD".equalsIgnoreCase(p2))
> > return new Timestamp(new
> > SimpleDateFormat("yyyyMMdd").parse
> > (p1).getTime());
> > if("DD-MM-YYYY".equalsIgnoreCase(p2))
> > return new Timestamp(new SimpleDateFormat
> > ("dd-MM-yyyy").parse(p1).getTime());
>
> > throw new UnsupportedOperationException("Oracle to_date ");
>
> > }
>
> > --
> > You received this message because you are subscribed to the Google Groups
> > "H2 Database" group.
> > To post to this group, send email to h2-datab...@googlegroups.com.
> > To unsubscribe from this group, send email to
> > h2-database+unsubscr...@googlegroups.com.
> > For more options, visit this group
> > athttp://groups.google.com/group/h2-database?hl=en.- Zitierten Text
> > ausblenden -
>
> - Zitierten Text anzeigen -
--
You received this message because you are subscribed to the Google Groups "H2
Database" group.
To post to this group, send email to h2-datab...@googlegroups.com.
To unsubscribe from this group, send email to
h2-database+unsubscr...@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/h2-database?hl=en.
