0 Replies Latest reply: Jun 4, 2013 5:24 AM by srini RSS

    Some Design Queries around Oracle Text

    srini
      Hi all,


      I am faced with a few design questions in our design around some complex search requirements for which Oracle Text would be a good fit for us.

      Version: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production


      Now for the questions:

      1. Our application stores records based on EFFECTIVE_DATES and queries based on these dates. EFFECTIVE_START_DATE & EFFECTIVE_END_DATE are part of the primary key. At any point of time, only one of the records should be displayed based on the effective dates. An example -
      <PROFILE>
      <COMPETENCY><name>Java</name><EFFECTIVE_DATE_FROM>01-Jan-2011</EFFECTIVE_DATE_FROM><EFFECTIVE_DATE_TO>31-Dec-2011</EFFECTIVE_DATE_TO><RATING>Meeting Expectations<RATING></COMPETENCY>
      <COMPETENCY><name>Java</name><EFFECTIVE_DATE_FROM>01-Jan-2012</EFFECTIVE_DATE_FROM><EFFECTIVE_DATE_TO/><RATING>Very Good<RATING></COMPETENCY>
      </PROFILE>

      My Approach: I am planning for SDATA sections for EFFECTIVE_DATE data and fetch based on the queried date. But since I am planning to collate all effective date records inside one master document, is it possible to selectively exclude certain sections of the document? Or is there a better way of achieving this?

      2. My requirements necessitate me to use USER_DATESTORE as the data to be indexed comes from multiple tables. If there is a change to an information that needs to trigger a re-index, what is the optimal way to achieve this?

      My Approach: Planning to add a new column on master table and update that to notify of any change for re-index. My OText context index will look at this new column for any change. Is this a good approach or are there better ways to achieve this?

      3. Selective Indexing of Documents - I have a few changes that need to be immediately re-indexed while other changes could wait. Is there someway to re-index specific documents?

      My Approach: I did not find any way of reindexing specific documents. So am thinking of an intermediate priority table which store the primary key of my master entity and a priority against it. If a change is priority 1 (and will keep such changes to a minimum), then invoke the SYNC_INDEX API, else will push the primary key in my intermediate priority table to be picked up later for End of Day processing.

      Do let me know your thoughts on these.


      Thanks,
      Srini