jackrabbit-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Stefan Guggisberg" <stefan.guggisb...@gmail.com>
Subject Re: Oracle does not use index on NODE_ID column
Date Fri, 31 Aug 2007 12:44:01 GMT
On 8/31/07, Martijn Hendriks <martijnh@gx.nl> wrote:
> Hi Stefan,
>
> It seems that at least the bundle select statement:
>
> "select BUNDLE_DATA from " + schemaObjectPrefix + "BUNDLE where NODE_ID
> = ?"
>
> causes a full table scan. We're using Jackrabbit 1.3 with the patch for
> JCR-940. This patch also uses stmt.setBytes.

strange..., maybe it's a driver issue. however, i am not an oracle
expert. i suggest
you ask your oracle crack. you should be able to investigate this
issue with just a
sql console and query analyzer and  by executing something like e.g.

select BUNDLE_DATA from default_BUNDLE where NODE_ID =
HEXTORAW("cafebabecafebabecafebabecafebabe");

cheers
stefan
>
> Best regards,
>
> Martijn
>
> --
>
> Martijn Hendriks
> <GX> creative online development B.V.
>
> t: 024 - 3888 261
> f: 024 - 3888 621
> e: martijnh@gx.nl
>
> Wijchenseweg 111
> 6538 SW Nijmegen
> http://www.gx.nl/
>
> > -----Original Message-----
> > From: Stefan Guggisberg [mailto:stefan.guggisberg@gmail.com]
> > Sent: Friday, August 31, 2007 12:34 PM
> > To: dev@jackrabbit.apache.org
> > Subject: Re: Oracle does not use index on NODE_ID column
> >
> > hi martijn,
> >
> > On 8/31/07, Martijn Hendriks <martijnh@gx.nl> wrote:
> > > Hi all,
> > >
> > > We've noticed that some queries with the oracle bundle persistence
> > > manager are very slow (an order of magnitude slower than with MSSQL
> > > for instance). This seems to be due to an implicit
> > conversion from a
> > > raw value to a varchar2 which disables the index (see
> > > http://orafaq.com/maillist/oracle-l/2007/02/14/1050.htm).
> >
> > i don't think that those issues are related. with the oracle
> > bundle persistence there should be no implicit conversion
> > from raw to varchar2 since the stmt parameter is bound as byte[], i.e.
> >
> >             stmt.setBytes(pos++, uuid.getRawBytes());
> >
> > which statements are affected?
> >
> > >
> > > Would it be possible to use the suggested HEXTORAW
> > function, or does
> > > that have unwanted side effects?
> > >
> > > I.e., use "select BUNDLE_DATA from " + schemaObjectPrefix + "BUNDLE
> > > where NODE_ID = HEXTORAW(?)";
> >
> > IMO that wouldn't do any good, see above.
> >
> > cheers
> > stefan
> >
> > >
> > > Best regards,
> > >
> > > Martijn
> > >
> > >
> > > --
> > >
> > > Martijn Hendriks
> > > <GX> creative online development B.V.
> > >
> > > t: 024 - 3888 261
> > > f: 024 - 3888 621
> > > e: martijnh@gx.nl
> > >
> > > Wijchenseweg 111
> > > 6538 SW Nijmegen
> > > http://www.gx.nl/
> > >
> >
>

Mime
View raw message