You are viewing a plain text version of this content. The canonical link for it is here.
Posted to dev@jackrabbit.apache.org by Martijn Hendriks <ma...@gx.nl> on 2007/08/31 11:44:46 UTC
Oracle does not use index on NODE_ID column
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).
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(?)";
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/
RE: Oracle does not use index on NODE_ID column
Posted by Martijn Hendriks <ma...@gx.nl>.
Hi Stefan,
> strange..., maybe it's a driver issue. however, i am not an
> oracle expert.
Neither am I :)
> 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");
We'll look into it further. Thanks for the tip.
Best regards,
Martijn
Re: Oracle does not use index on NODE_ID column
Posted by Stefan Guggisberg <st...@gmail.com>.
On 8/31/07, Martijn Hendriks <ma...@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 <ma...@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/
> > >
> >
>
RE: Oracle does not use index on NODE_ID column
Posted by Martijn Hendriks <ma...@gx.nl>.
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.
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 <ma...@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/
> >
>
Re: Oracle does not use index on NODE_ID column
Posted by Stefan Guggisberg <st...@gmail.com>.
hi martijn,
On 8/31/07, Martijn Hendriks <ma...@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/
>