Re: Including application-generated data in an SQL query

Solutions:

1. Context Option
2. OCI (oracle call interface) supports arrays of rows for SQL.
3. Is INSERT INTO TEMP_TABLE SELECT ID FROM.... too slow?

--
Mark Malakanov
Oracle DBA
Sapience, Toronto

Raoul Gough <raoulgough_at_my-deja.com> wrote in message
news:888qjk$ccq$1_at_nnrp1.deja.com...


> I've got a question about how to combine a set of data generated by


> application code into an SQL query.

>

> As background: I'm working on a problem tracking system that supports

> full text search in combination with normal field-based criterea. This

> allows a search like: find all reports that were entered in January 2000

> that contain the words "system crash".

>

> In order to do this, the application generates the set of IDs for

> reports that match the full text criterea (using it's own text index)

> and stores this list in a temporary table for combination with the SQL

> query. I can then add a "WHERE EXISTS (SELECT * FROM ...)" from the

> temporary table to the ordinary query.

>

> The problem with this approach is the insert performance for the

> temporary table - the text index lookup is fast, but inserting the

> results one at a time into the temporary table is relatively slow (at

> least when the client and server are communicating over the network). If

> the text lookup returns a large number of matches, say five to six

> hundred items, then the inserts start to take longer than the rest of

> the query put together.

>

> SQL doesn't seem to support any kind of array operations where I could

> insert a large set of IDs in one go. Does anybody have a suggestion as

> to how I can improve the performance of this kind of combined query? It

> will have to be more or less standard SQL, because we don't have

> anything fancy here :-)

>

> database client is Oracle Rdb V7.0-31 on OpenVMS 7.1

> database server is Oracle Rdb V7.0-0 on OpenVMS 7.1-2

>

> Any assistance appreciated,

> Ray.

>

>

> Sent via Deja.com http://www.deja.com/

> Before you buy.