RE: create text index as transactional

Hi Connor,

I've not used transactional (I think this is new in 10gR2 isn't it?). I have used sync(ON COMMIT) on transactional databases (context indexes are GREAT for %ABC% queries!!), and I've had no performance issues with it. If we were doing bulk updates into the table, I'd probably opt to sync after the loads instead.

From looking at the description of transactional, it looks like the ROWID's are synchronized in memory instead of in the index itself. I guess this might be faster than the sync on commit. You still have to sync the index eventually though (and I would wonder about the persistence of the in memory ROWID's in the event of a database recycle), so I think I'd prefer the on commit sync instead if there were no performance issues related to it's usage.

Cheers...

RF

Robert G. Freeman
Oracle Consultant/DBA/Author
Principle Engineer/Team Manager
The Church of Jesus Christ of Latter-Day Saints Father of Five, Husband of One,
Author of various geeky computer titles
from Osborne/McGraw Hill (Oracle Press)

-----Original Message-----

From: oracle-l-bounce_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org]On Behalf Of Connor McDonald Sent: Wednesday, May 02, 2007 8:13 AM
To: oracle-l_at_freelists.org
Subject: RE: create text index as transactional

-----Original Message-----

Has anybody ever created their Oracle Text index as transactional?

Such as:

create index myidx on mytable(mycolumn)
indextype is ctxsys.context
parameters ('transactional');

--

(Untested but) I would not be surprised if you get the same kind of index degradation that used to be the result of running ctxsrv...

Cheers
Connor

--

http://www.freelists.org/webpage/oracle-l

--

http://www.freelists.org/webpage/oracle-l Received on Wed May 02 2007 - 11:46:00 CDT