You are viewing a plain text version of this content. The canonical link for it is here.
Posted to solr-dev@lucene.apache.org by "J. Delgado" <jd...@lendingclub.com> on 2007/09/14 00:27:24 UTC

Oracle-Lucene integration (OJVMDirectory and Lucene Domain Index) - LONG

I'm very happy to announce the partial rework and extension to LUCENE-724
(Oracle-Lucene Integration), primarily based on new requirements from
LendingClub.com, who commissioned the work to Marcelo Ochoa, the contributer
of the original patch (great job Marcelo!). As contribution of
LendingClub.com to the Lucene community we have posted the code on a public
CVS (sourceforge) as explained below.

Here at Lending Club (www.lendingclub.com) we have very specific needs
regarding the indexing of both structured and unstructured data, most of it
transactional in nature and siting in our Oracle !0gR2 DB, with a highly
complex schema. Our "ranking" of loans in the inventory includes components
of exact, textual and hardcore mathematical calculations including time,
amount and spatial constraints. This integration of Lucene into Oracle as a
Domain Index will now allow us to query this inventory in real-time. Going
against the Lucene index, created on "synthetic documents" comprised of
fields being populated from diverse tables (user data store), eliminates the
need to create very complex joins to link data from different tables at
query time. This, along with the support of the full Lucene query language,
makes this a great alternative to:

   1. Using Lucene outside the database which requires "crawling" the
   data and storing the index outside the database, loosing all the benefits of
   a fully transactional system and a secure environment.
   2. Using Oracle Text, which is very powerful but lacks the
   extensibility and flexibility that Lucene offers (for example, being able to
   query directly the index from the Java layer or implementing our our ranking
   algorithm), though to be completely fair some of it is addressed in the new
   Oracle DB 11g version.

If anyone is interested in learning more how we are going to use this within
Lending Club, please drop me a line. BTW, please make sure you check us out:
"Lending Club (http://www.lendingclub.com/), the rapidly growing
people-to-people (P2P) lending service that launched as a Facebook
application in May 2007, today announced the public availability of its
services with the launch of LendingClub.com. Lending Club connects lenders
and borrowers based upon shared affinities, enabling them to bypass banks to
secure better interest rates on loans"... more about the announcement here
http://www.sys-con.com/read/428678.htm. We have seen man entrepreneurs
applying for loans and being helped by regular people to build their
business with the money obtained at very low interest.

OK, without further marketing stuff (sorry for that), here is the original
note sent to me by Marcelo that summarizes all the new cool functionalities:

OJVMDirectory, a Lucene Integration running inside the Oracle JVM is going
one step further.

This new release includes:

   - Synchronized with latest Lucene 2.2.0 production
   - Replaced in memory storage using Vector based implementation by
   direct BLOB IO, reducing memory usage for large index.
   - Support for user data stores, it means you can not only index one
   column at time (limited by Data Cartridge API on 10g), now you can index
   multiples columns at base table and columns on related tabled joined
   together.
   - User Data Stores can be customized by the user, it means writing a
   simple Java Class users can control which column are indexed, padding
   - used or any other functionality previous to document adding step.
   - There is a DefaultUserDataStore which gets all columns of the query
   and built a Lucene Document with Fields representing each database
   - columns these fields are automatically padded if they have NUMBER or
   rounded if they have DATE data, for example.
   - lcontains() SQL operator support full Lucene's QueryParser syntax to
   provide access to all columns indexed, see examples below.
   - Support for DOMAIN_INDEX_SORT and FIRST_ROWS hint, it means that if
   you want to get rows order by lscore() operator (ascending,descending) the
   optimizer hint will assume that Lucene Domain Index will returns rowids in
   proper order avoided an inline-view to sort it.
   - Automatic index synchronization by using AQ's Call Back.
   - Lucene Domain Index creates extra tables named IndexName$T and an
   Oracle AQ named IndexName$Q with his storage table IndexName$QT at user's
   schema, so you can alter storage's preference if you want.
   - ojvm project is at SourceForge.net CVS, so anybody can get it and
   collaborate ;)
   - Tested against 10gR2 and 11g database.


Some sample usages:

create table t2 (
 f4 number primary key,
 f5 VARCHAR2(200));
create table t1 (
 f1 number,
 f2 CLOB,
 f3 number,
 CONSTRAINT t1_t2_fk FOREIGN KEY (f3)
     REFERENCES t2(f4) ON DELETE cascade);
create index it1 on t1(f3) indextype is lucene.LuceneIndex
 parameters('Analyzer:org.apache.lucene.analysis
.SimpleAnalyzer;ExtraCols:f2');

alter index it1
parameters('ExtraCols:f2,t2.f5;ExtraTabs:t2;WhereCondition:t1.f3=t2.f4;DecimalFormat:000');


Lucene domain index will store f2 and f3 columns of table t1 plus f5 of
table t2.

So you can query then with:

 select lscore(1),f2 from t1 where lcontains(f3, 'f2:test',1) > 0;
or
 select lscore(1),f2 from t1 where lcontains(f3, 'f2:test and f3:[001 to
200]',1) > 0;

 select /*+ DOMAIN_INDEX_SORT */ lscore(1),f2,t2.f5
 from t1,t2
 where lcontains(f3, 'f2:test1 and f3:[001 to 200] and t2.f5:test2',1) > 0
 and t1.f3=t2.f4
 order by lscore(1) asc;

In latest example Oracle's optimizer will assume that Lucene Domain Index
will resolve first a set of rowid matching "f2:test1 and f3:[001 to 200] and
t2.f5:test2" then will direct access by by index rowid on table t1 and
perform the join with t2.

More examples and information can be found at:
http://dbprism.cvs.sourceforge.net/dbprism/ojvm/Readme.txt?revision=1.10&view=markup

--
Marcelo F. Ochoa
http://marcelo.ochoa.googlepages.com/home

Cheers!

Joaquin Delgado, PhD
CTO, Lending Club
www.lendingclub.com

 <http://marcelo.ochoa.googlepages.com/home>

Re: Oracle-Lucene integration (OJVMDirectory and Lucene Domain Index) - LONG

Posted by Marcelo Ochoa <ma...@gmail.com>.
Hi Chris:
  First sorry for the delay :(
  I have some preliminary performance test using Oracle 11g running on
in a VMWare virtual Machine with 400Mb SGA (Virtual Machine using
812Mb RAM for Oracle Enterprise Linux 4.0). This virtual machine is
hosted in a modest hardware, a Pentium IV 2.18Ghz with 2Gb RAM linux
Mandriva 2007.
  Here some result:
  Indexing all_source system view took 23 minutes, all_source view
have 220731 rows with 50Mb of data, sure this text is not free text
because many rows have wrapped code with hexadecimal numbers. Here the
table and the index:
SQL> desc test_source_big
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 OWNER                                              VARCHAR2(30)
 NAME                                               VARCHAR2(30)
 TYPE                                               VARCHAR2(12)
 LINE                                               NUMBER
 TEXT                                               VARCHAR2(4000)
SQL> create index source_big_lidx on test_source_big(text)
indextype is lucene.LuceneIndex
parameters('Stemmer:English;MaxBufferedDocs:5000;DecimalFormat:0000;ExtraCols:line');

Index created.

Elapsed: 00:23:02.74
   Index storage (45Mb, 220K Lucene docs) is:
 FILE_SIZE NAME
---------- ------------------------------
         9 parameters
         2 updateCount
        20 segments.gen
  45941031 _1d.cfs
        42 segments_2t

   A query like this:

select /*+ FIRST_ROWS(10) */ lscore(1) from test_source_big where
lcontains(text,'"procedure java"~10',1)>0 order by lscore(1) desc;

   It took 11ms, and will be faster if you don't need lscore(1) value,
here other example:

select /*+ FIRST_ROWS(10) DOMAIN_INDEX_SORT */ lscore(1) from
test_source_big where lcontains(text,'(optimize OR sync) AND "LANGUAGE
JAVA"',1)>0 order by lscore(1) asc;

  It took 7ms.

  But there are other benefits related to the Domain Index
implementation using Data Cartridge API:
  - Any modification on the table is notified to Lucene automatically,
you can apply this modification on line or deferred, except for
deletion that are always synced.
  - The execution plan is calculated by the optimizer using the domain
index, and with latest additions (User Data Store) you can reduce with
Lucene how many rows the database will process using multiples column
at lcontains operator.
   For example this query use Lucene to search a free text at TEXT
column and Oracle's filter reduction at LINE column:

SQL> select count(text) from test_source_big where
lcontains(text,'function')>0 and line>=6000;

COUNT(TEXT)
-----------
          2

Elapsed: 00:00:00.74
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 2350958379

------------------------------------------------------------------------------------------------
| Id  | Operation                    | Name            | Rows  | Bytes
| Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |                 |     1 |  2027
|  2968   (1)| 00:00:36 |
|   1 |  SORT AGGREGATE              |                 |     1 |  2027
|            |          |
|*  2 |   TABLE ACCESS BY INDEX ROWID| TEST_SOURCE_BIG |     7 | 14189
|  2968   (1)| 00:00:36 |
|*  3 |    DOMAIN INDEX              | SOURCE_BIG_LIDX |       |
|            |          |
------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter("LINE">=6000)
   3 - access("LUCENE"."LCONTAINS"("TEXT",'function')>0)

   But if you use Lucene to reduce the number of rows visited by
Oracle by using User Data Store to index LINE column too, you can
perform a query like this:

SQL> select count(text) from test_source_big where
lcontains(text,'function AND line:[6000 TO 7000]')>0;

COUNT(TEXT)
-----------
          2

Elapsed: 00:00:00.05
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 2350958379

------------------------------------------------------------------------------------------------
| Id  | Operation                    | Name            | Rows  | Bytes
| Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |                 |     1 |  2014
|  2968   (1)| 00:00:36 |
|   1 |  SORT AGGREGATE              |                 |     1 |  2014
|            |          |
|   2 |   TABLE ACCESS BY INDEX ROWID| TEST_SOURCE_BIG | 11587 |
22M|  2968   (1)| 00:00:36 |
|*  3 |    DOMAIN INDEX              | SOURCE_BIG_LIDX |       |
|            |          |
------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   3 - access("LUCENE"."LCONTAINS"("TEXT",'function AND line:[6000 TO 7000]')>0)

   Note different execution plan used in both queries.
   The trick here is that, if you minimize the number of rowids
returned by lcontains() operator by reducing it with more Lucene
filters the query will be faster.
   Also, I had executed all Lucene JUnit Test suites inside the Oracle
JVM and have similar execution time compared to run it outside the
database with a JDK 1.5.
   Sure, Lucene Index using a File System Store is faster than
OJVMDirectory implementation, but with OJVMDirectory you have another
benefits inherit from BLOB storage, transaction isolation, no network
round trip during indexing time, among others.
   Best regards, Marcelo.
-- 
Marcelo F. Ochoa
http://marceloochoa.blogspot.com/
http://marcelo.ochoa.googlepages.com/home
______________
Do you Know DBPrism? Look @ DB Prism's Web Site
http://www.dbprism.com.ar/index.html
More info?
Chapter 17 of the book "Programming the Oracle Database using Java &
Web Services"
http://www.amazon.com/gp/product/1555583296/
Chapter 21 of the book "Professional XML Databases" - Wrox Press
http://www.amazon.com/gp/product/1861003587/
Chapter 8 of the book "Oracle & Open Source" - O'Reilly
http://www.oreilly.com/catalog/oracleopen/

---------------------------------------------------------------------
To unsubscribe, e-mail: java-user-unsubscribe@lucene.apache.org
For additional commands, e-mail: java-user-help@lucene.apache.org

Re: Oracle-Lucene integration (OJVMDirectory and Lucene Domain Index) - LONG

Posted by Chris Lu <ch...@gmail.com>.
Hi, Joaquin,

Very interested to know the indexing performance inside Oracle JVM,
especially with large amount of data.

-- 
Chris Lu
-------------------------
Instant Scalable Full-Text Search On Any Database/Application
site: http://www.dbsight.net
demo: http://search.dbsight.com
Lucene Database Search in 3 minutes:
http://wiki.dbsight.com/index.php?title=Create_Lucene_Database_Search_in_3_minutes

On 9/14/07, Marcelo Ochoa <ma...@gmail.com> wrote:
>
> From: J. Delgado <jd...@lendingclub.com>
> Date: Sep 13, 2007 7:27 PM
> Subject: Oracle-Lucene integration (OJVMDirectory and Lucene Domain
> Index) - LONG
> To: java-dev@lucene.apache.org
> Cc: solr-dev@lucene.apache.org
>
>
> I'm very happy to announce the partial rework and extension to
> LUCENE-724 (Oracle-Lucene Integration), primarily based on new
> requirements from LendingClub.com, who commissioned the work to
> Marcelo Ochoa, the contributer of the original patch (great job
> Marcelo!). As contribution of LendingClub.com to the Lucene community
> we have posted the code on a public CVS (sourceforge) as explained
> below.
>
> Here at Lending Club ( www.lendingclub.com) we have very specific
> needs regarding the indexing of both structured and unstructured data,
> most of it transactional in nature and siting in our Oracle !0gR2 DB,
> with a highly complex schema. Our "ranking" of loans in the inventory
> includes components of exact, textual and hardcore mathematical
> calculations including time, amount and spatial constraints. This
> integration of Lucene into Oracle as a Domain Index will now allow us
> to query this inventory in real-time. Going against the Lucene index,
> created on "synthetic documents" comprised of fields being populated
> from diverse tables (user data store), eliminates the need to create
> very complex joins to link data from different tables at query time.
> This, along with the support of the full Lucene query language, makes
> this a great alternative to:
>
> Using Lucene outside the database which requires "crawling" the data
> and storing the index outside the database, loosing all the benefits
> of a fully transactional system and a secure environment.
>
> Using Oracle Text, which is very powerful but lacks the extensibility
> and flexibility that Lucene offers (for example, being able to query
> directly the index from the Java layer or implementing our our ranking
> algorithm), though to be completely fair some of it is addressed in
> the new Oracle DB 11g version. If anyone is interested in learning
> more how we are going to use this within Lending Club, please drop me
> a line. BTW, please make sure you check us out: "Lending Club (
> http://www.lendingclub.com/), the rapidly growing people-to-people
> (P2P) lending service that launched as a Facebook application in May
> 2007, today announced the public availability of its services with the
> launch of LendingClub.com. Lending Club connects lenders and borrowers
> based upon shared affinities, enabling them to bypass banks to secure
> better interest rates on loans"... more about the announcement here
> http://www.sys-con.com/read/428678.htm. We have seen man entrepreneurs
> applying for loans and being helped by regular people to build their
> business with the money obtained at very low interest.
>
> OK, without further marketing stuff (sorry for that), here is the
> original note sent to me by Marcelo that summarizes all the new cool
> functionalities:
>
> OJVMDirectory, a Lucene Integration running inside the Oracle JVM is
> going one step further.
>
> This new release includes:
>
> Synchronized with latest Lucene 2.2.0 production
> Replaced in memory storage using Vector based implementation by direct
> BLOB IO, reducing memory usage for large index.
> Support for user data stores, it means you can not only index one
> column at time (limited by Data Cartridge API on 10g), now you can
> index multiples columns at base table and columns on related tabled
> joined together.
> User Data Stores can be customized by the user, it means writing a
> simple Java Class users can control which column are indexed, padding
> used or any other functionality previous to document adding step.
> There is a DefaultUserDataStore which gets all columns of the query
> and built a Lucene Document with Fields representing each database
> columns these fields are automatically padded if they have NUMBER or
> rounded if they have DATE data, for example.
> lcontains() SQL operator support full Lucene's QueryParser syntax to
> provide access to all columns indexed, see examples below.
> Support for DOMAIN_INDEX_SORT and FIRST_ROWS hint, it means that if
> you want to get rows order by lscore() operator (ascending,descending)
> the optimizer hint will assume that Lucene Domain Index will returns
> rowids in proper order avoided an inline-view to sort it.
> Automatic index synchronization by using AQ's Call Back.
> Lucene Domain Index creates extra tables named IndexName$T and an
> Oracle AQ named IndexName$Q with his storage table IndexName$QT at
> user's schema, so you can alter storage's preference if you want.
> ojvm project is at SourceForge.net CVS, so anybody can get it and
> collaborate ;)
> Tested against 10gR2 and 11g database.
> Some sample usages:
>
> create table t2 (
>   f4 number primary key,
>   f5 VARCHAR2(200));
> create table t1 (
>   f1 number,
>   f2 CLOB,
>   f3 number,
>   CONSTRAINT t1_t2_fk FOREIGN KEY (f3)
>       REFERENCES t2(f4) ON DELETE cascade);
> create index it1 on t1(f3) indextype is lucene.LuceneIndex
>   parameters('Analyzer:org.apache.lucene.analysis
> .SimpleAnalyzer;ExtraCols:f2');
>
> alter index it1
> parameters('ExtraCols:f2,t2.f5;ExtraTabs:t2;WhereCondition:t1.f3=t2.f4
> ;DecimalFormat:000');
>
> Lucene domain index will store f2 and f3 columns of table t1 plus f5
> of table t2.
>
> So you can query then with:
>
> select lscore(1),f2 from t1 where lcontains(f3, 'f2:test',1) > 0;
> or
> select lscore(1),f2 from t1 where lcontains(f3, 'f2:test and f3:[001
> to 200]',1) > 0;
>
> select /*+ DOMAIN_INDEX_SORT */ lscore(1),f2,t2.f5
>   from t1,t2
>   where lcontains(f3, 'f2:test1 and f3:[001 to 200] and t2.f5:test2',1) >
> 0
>   and t1.f3=t2.f4
>   order by lscore(1) asc;
>
> In latest example Oracle's optimizer will assume that Lucene Domain
> Index will resolve first a set of rowid matching "f2:test1 and f3:[001
> to 200] and t2.f5:test2" then will direct access by by index rowid on
> table t1 and perform the join with t2.
>
> More examples and information can be found at:
>
> http://dbprism.cvs.sourceforge.net/dbprism/ojvm/Readme.txt?revision=1.10&view=markup
>
> --
> Marcelo F. Ochoa
> http://marcelo.ochoa.googlepages.com/home
>
> Cheers!
>
> Joaquin Delgado, PhD
> CTO, Lending Club
> www.lendingclub.com
>
>
>
> --
> Marcelo F. Ochoa
> http://marceloochoa.blogspot.com/
> http://marcelo.ochoa.googlepages.com/home
> ______________
> Do you Know DBPrism? Look @ DB Prism's Web Site
> http://www.dbprism.com.ar/index.html
> More info?
> Chapter 17 of the book "Programming the Oracle Database using Java &
> Web Services"
> http://www.amazon.com/gp/product/1555583296/
> Chapter 21 of the book "Professional XML Databases" - Wrox Press
> http://www.amazon.com/gp/product/1861003587/
> Chapter 8 of the book "Oracle & Open Source" - O'Reilly
> http://www.oreilly.com/catalog/oracleopen/
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: java-user-unsubscribe@lucene.apache.org
> For additional commands, e-mail: java-user-help@lucene.apache.org
>
>

Fwd: Oracle-Lucene integration (OJVMDirectory and Lucene Domain Index) - LONG

Posted by Marcelo Ochoa <ma...@gmail.com>.
From: J. Delgado <jd...@lendingclub.com>
Date: Sep 13, 2007 7:27 PM
Subject: Oracle-Lucene integration (OJVMDirectory and Lucene Domain
Index) - LONG
To: java-dev@lucene.apache.org
Cc: solr-dev@lucene.apache.org


I'm very happy to announce the partial rework and extension to
LUCENE-724 (Oracle-Lucene Integration), primarily based on new
requirements from LendingClub.com, who commissioned the work to
Marcelo Ochoa, the contributer of the original patch (great job
Marcelo!). As contribution of LendingClub.com to the Lucene community
we have posted the code on a public CVS (sourceforge) as explained
below.

Here at Lending Club ( www.lendingclub.com) we have very specific
needs regarding the indexing of both structured and unstructured data,
most of it transactional in nature and siting in our Oracle !0gR2 DB,
with a highly complex schema. Our "ranking" of loans in the inventory
includes components of exact, textual and hardcore mathematical
calculations including time, amount and spatial constraints. This
integration of Lucene into Oracle as a Domain Index will now allow us
to query this inventory in real-time. Going against the Lucene index,
created on "synthetic documents" comprised of fields being populated
from diverse tables (user data store), eliminates the need to create
very complex joins to link data from different tables at query time.
This, along with the support of the full Lucene query language, makes
this a great alternative to:

Using Lucene outside the database which requires "crawling" the data
and storing the index outside the database, loosing all the benefits
of a fully transactional system and a secure environment.

Using Oracle Text, which is very powerful but lacks the extensibility
and flexibility that Lucene offers (for example, being able to query
directly the index from the Java layer or implementing our our ranking
algorithm), though to be completely fair some of it is addressed in
the new Oracle DB 11g version. If anyone is interested in learning
more how we are going to use this within Lending Club, please drop me
a line. BTW, please make sure you check us out: "Lending Club (
http://www.lendingclub.com/), the rapidly growing people-to-people
(P2P) lending service that launched as a Facebook application in May
2007, today announced the public availability of its services with the
launch of LendingClub.com. Lending Club connects lenders and borrowers
based upon shared affinities, enabling them to bypass banks to secure
better interest rates on loans"... more about the announcement here
http://www.sys-con.com/read/428678.htm. We have seen man entrepreneurs
applying for loans and being helped by regular people to build their
business with the money obtained at very low interest.

OK, without further marketing stuff (sorry for that), here is the
original note sent to me by Marcelo that summarizes all the new cool
functionalities:

OJVMDirectory, a Lucene Integration running inside the Oracle JVM is
going one step further.

This new release includes:

Synchronized with latest Lucene 2.2.0 production
Replaced in memory storage using Vector based implementation by direct
BLOB IO, reducing memory usage for large index.
Support for user data stores, it means you can not only index one
column at time (limited by Data Cartridge API on 10g), now you can
index multiples columns at base table and columns on related tabled
joined together.
User Data Stores can be customized by the user, it means writing a
simple Java Class users can control which column are indexed, padding
used or any other functionality previous to document adding step.
There is a DefaultUserDataStore which gets all columns of the query
and built a Lucene Document with Fields representing each database
columns these fields are automatically padded if they have NUMBER or
rounded if they have DATE data, for example.
lcontains() SQL operator support full Lucene's QueryParser syntax to
provide access to all columns indexed, see examples below.
Support for DOMAIN_INDEX_SORT and FIRST_ROWS hint, it means that if
you want to get rows order by lscore() operator (ascending,descending)
the optimizer hint will assume that Lucene Domain Index will returns
rowids in proper order avoided an inline-view to sort it.
Automatic index synchronization by using AQ's Call Back.
Lucene Domain Index creates extra tables named IndexName$T and an
Oracle AQ named IndexName$Q with his storage table IndexName$QT at
user's schema, so you can alter storage's preference if you want.
 ojvm project is at SourceForge.net CVS, so anybody can get it and
collaborate ;)
Tested against 10gR2 and 11g database.
Some sample usages:

create table t2 (
  f4 number primary key,
  f5 VARCHAR2(200));
create table t1 (
  f1 number,
  f2 CLOB,
  f3 number,
  CONSTRAINT t1_t2_fk FOREIGN KEY (f3)
      REFERENCES t2(f4) ON DELETE cascade);
create index it1 on t1(f3) indextype is lucene.LuceneIndex
  parameters('Analyzer:org.apache.lucene.analysis
.SimpleAnalyzer;ExtraCols:f2');

alter index it1
parameters('ExtraCols:f2,t2.f5;ExtraTabs:t2;WhereCondition:t1.f3=t2.f4;DecimalFormat:000');

Lucene domain index will store f2 and f3 columns of table t1 plus f5
of table t2.

So you can query then with:

 select lscore(1),f2 from t1 where lcontains(f3, 'f2:test',1) > 0;
or
 select lscore(1),f2 from t1 where lcontains(f3, 'f2:test and f3:[001
to 200]',1) > 0;

 select /*+ DOMAIN_INDEX_SORT */ lscore(1),f2,t2.f5
  from t1,t2
  where lcontains(f3, 'f2:test1 and f3:[001 to 200] and t2.f5:test2',1) > 0
  and t1.f3=t2.f4
  order by lscore(1) asc;

In latest example Oracle's optimizer will assume that Lucene Domain
Index will resolve first a set of rowid matching "f2:test1 and f3:[001
to 200] and t2.f5:test2" then will direct access by by index rowid on
table t1 and perform the join with t2.

More examples and information can be found at:
 http://dbprism.cvs.sourceforge.net/dbprism/ojvm/Readme.txt?revision=1.10&view=markup

--
Marcelo F. Ochoa
http://marcelo.ochoa.googlepages.com/home

Cheers!

Joaquin Delgado, PhD
CTO, Lending Club
www.lendingclub.com



-- 
Marcelo F. Ochoa
http://marceloochoa.blogspot.com/
http://marcelo.ochoa.googlepages.com/home
______________
Do you Know DBPrism? Look @ DB Prism's Web Site
http://www.dbprism.com.ar/index.html
More info?
Chapter 17 of the book "Programming the Oracle Database using Java &
Web Services"
http://www.amazon.com/gp/product/1555583296/
Chapter 21 of the book "Professional XML Databases" - Wrox Press
http://www.amazon.com/gp/product/1861003587/
Chapter 8 of the book "Oracle & Open Source" - O'Reilly
http://www.oreilly.com/catalog/oracleopen/

---------------------------------------------------------------------
To unsubscribe, e-mail: java-user-unsubscribe@lucene.apache.org
For additional commands, e-mail: java-user-help@lucene.apache.org