Re: Using external data in oracle?

Oracle 9i will have this capability (how's that for an utterly unhelpful reply). In the mean time you could try to pass in the data as a varray or nested table and then use the TABLE function to make the data selectable. were you inserting into a global temporary table when you tried the insert? if not (and if your on 8i) you could give that a try as well, it should speed up the insert by about 2x.

In article <94l526$qni$1_at_nnrp1.deja.com>,   gdas_at_my-deja.com wrote:
> Hi,
>
> I apologize for asking such an open-ended question, but I was
 wondering
> if Oracle had anything (either programmable or some kind of snap in
> cartridge) that would allow non-relational data outside the oracle
> database to appear as if it were in the database, essentially allowing
> sql operations (specifically joins) on that data.
>
> I believe Microsoft has something like this through OLE DB and (don't
> quote me on this), but I think Informix used to have the concept of
> data cartridges for external data.
>
> Our problem is this: Our application does some sophisticated
 searching
> and indexing. Oracle Intermedia couldn't do what we wanted and we
> ended up choosing Autonomy.
>
> The dilemma now is that we have our raw content files stored in text
> files for autonomy on a directory somewhere and we have all the
> interesting metadata about all those files stored in oracle tables.
>
> When a user comes in and does a search, the web server sends the
> request to the autonomy server which sends back the document ids and
> the ranks for the documents that met the search criteria. The
> challenge now is that the interesting stuff we want to display to the
> user is in Oracle, not autonomy.
>
> We tried taking this list of document ids and passing it into an
 oracle
> query using an in operator. However there could be thousands of them
> and the query is always slow as the optimizer gets caught up in an "in
> list iterator."
>
> We tried inserting these ids into a temporary table and joining it.
> Although the query itself was much faster, the overhead of inserting
> the rows sort of cancelled out the benefit.
>
> So I'm wondering if there is a way to write something in oracle that
> could allow us to dynamically define a virtual table outside of oracle
> that we could use in sql?
>
> I hope this question makes sense. I really don't know where to start
> looking for information.
>
> Thanks,
> Gavin
>
> Sent via Deja.com
> http://www.deja.com/
>

Sent via Deja.com
http://www.deja.com/ Received on Tue Jan 23 2001 - 18:15:07 CST