Re: bitand functions and NUMBER(20)
- From: Stefan Kuhn <skuhn@xxxxxxxxxxxx>
- To: oracle-l@xxxxxxxxxxxxx
- Date: Thu, 19 Jul 2007 16:11:57 +0200
Ok, sorry for bad explanation. What I need to do is a chemical substructure
search. I have a database with chemical structures (graphs, in a mathematical
term) and want all entries which contain another structure (graph, again for
the mathematicians) as substructure. An exact subgraph match might take
several seconds (or even more than a minute) on a complicated and/or large
structure, so no chance with 20000 structures (and hopefully more in the
future) in my db. With some clever tricks you can transform the structure
into a bitset and having the substructure property being translated to a
sub-bitset property. This makes it possible to do a fast prefiltering (due to
the algorithm there may be false positives, but it still helps to cut down
the candidate list).
On Thursday 19 July 2007 15:59, you wrote:
> I have always been in commercial rather than scientific operations. I
> gathering you are storing some form of imaging data? The question I had
> was much more fundamental The question was, why do you store bitwise data
> as opposed to user readable data? I had this argument with a C programmer
> several years ago, he was actually storing a bitmap that indicated types of
> information stored in another column in the database. I am really curious
> what you are doing that requires bitwise data in an Oracle database, since
> I yet to see any good reason for it in commercial operations.
>
>
> -----Original Message-----
> From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx]
> On Behalf Of Stefan Kuhn Sent: Thursday, July 19, 2007 7:53 AM
> To: oracle-l@xxxxxxxxxxxxx
> Subject: Re: bitand functions and NUMBER(20)
>
> On Thursday 19 July 2007 14:44, Kerber, Andrew W. wrote:
> > I have to ask this question because no one has yet given me an answer.
> > Why do you use an Oracle database to store bitwise data? Any performance
> > gain you might possible get by doing bitwise operations is going to be
> > completely overshadowed by the retrieval time from the database.
>
> What is the alternative? Hold the bitset in memory by the appliation? That
> makes it necessary to synchronize this cache and it's not that nice to
> query. Btw, the bit functions take milliseconds even on a few hundred
> thousand entries - that's no too bad, I would say.
>
> > -----Original Message-----
> > From: oracle-l-bounce@xxxxxxxxxxxxx
> > [mailto:oracle-l-bounce@xxxxxxxxxxxxx] On Behalf Of Stefan Kuhn Sent:
> > Thursday, July 19, 2007 7:18 AM
> > To: oracle-l@xxxxxxxxxxxxx
> > Subject: bitand functions and NUMBER(20)
> >
> > Hi all,
> > I have got a column defined as NUMBER(20) and want to use bit functions
> > on it. It seems that oracle bitand function is restricted in length. To
> > give an example:
> > select bitand(10846370260800065548,9368617832122679304) from TABLE;
> > returns 9.2234E+18, although the second figure is a subset of bits in
> > first figure. So result should be 9368617832122679304.
> > To make sure my figures are right, I did
> > select 10846370260800065548 & 9368617832122679304;
> > in Mysql and it gave 9368617832122679304.
> > The problem does arise with figures of a certain length.
> > What to do best (apart from changing the column type, which I would like
> > to avoid)?
> > I hope the question isn't too trivia...
> > Stefan
--
Stefan Kuhn BSc MA
IPB Halle
AG Bioinformatik & Massenspektrometrie
Weinberg 3
06120 Halle
http://www.ipb-halle.de http://msbi.bic-gh.de
skuhn@xxxxxxxxxxxx Tel. +49 (0) 345 5582 1474 Fax.+49 (0) 345 5582 1409
--
//www.freelists.org/webpage/oracle-l
Other related posts:
