Re: Monitoring local transaction outcomes for external text indexes

On Thu, 22 May 2003 01:16:43 GMT, "Jim Kennedy" <kennedy-down_with_spammers_at_attbi.com> wrote:

>Look at a statement level trigger. Why would you want to update ona rolled
>back transaction? The transaction didn't occur so there is nothing to do.
>Once it commits it will run based upon what your job interval is in the
>init.ora. 1 seconds means the job will run an average of .5 second later.
>Jim

I guess this calls for an explanation. Until now, we have been using the Oracle Extensible Indexing interface for creating customized indexes in Oracle. The reason why this is a great idea is that it enables the use of index types and operators in SQL, so that users can create indexes on tables using

CREATE INDEX ON .... INDEXTYPE IS myindextype

And use the indexes in queries using operators such as

SELECT ... FROM mytable
WHERE mycontains(mypictures, 'background:blue')=1.0;

This all works fine as long as the indexes are stored within Oracle. However, for some types of indexes, this is not a very good idea. In particular, we utilize a special index type what does not fit well into Oracle tables, and it is instead stored directly in files or on raw partitions.

Now, the problem is that the Extensible Indexing Interface requires that all ROWID's returned by the index is in fact present in the database in the calling transaction. If a ROWID is returned by the index, which has just been deleted and committed by another person, then the call will fail with a serious warning. It will also fail when seeing a ROWID, which is not yet visible outside the current transaction (i.e. inserted but not committed).

I guess the above simply means that we should make sure to follow the transaction semantics of Oracle as accurately as possibly.

That is, if Extensible Indexing Interface is to be used at all for external indexes.

Thomas Received on Wed May 21 2003 - 21:06:15 CDT