Using external data in oracle?

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/ Received on Tue Jan 23 2001 - 17:45:10 CST