What is the performance difference between local text index and domain text index in oracle? Also what is the cost of rebuilding the domain text index when I have to split just one partition? Does the whole text index being rebuilt?


Sean Nguyen


Sean, I believe that the local text index is part of the Oracle Text feature of Oracle that is specific to working with documents, such as Web sites, digital libraries, document or warehouses. While the Domain Indexes are indexes that we use in day-to-day.

The following documentation will be helpful: About Oracle Text: http://docs.oracle.com/cd/B28359_01/text.111/b28303/overview.htm (In the third chapter there is information about the indexes)

About the Domain Indexes: http://docs.oracle.com/cd/B10500_01/appdev.920/a96595/dci07idx.htm

| improve this answer | |
  • Not entirely correct as Oracle Text uses domain indices. – Roy Sep 4 '16 at 23:55

Oracle Text provides tooling that enable so called "full text" searching in chunks of unstructured data - the term commonly translates to some amount of text stored in a table column using CLOB (Character Large OBject) or some similar datatype.

Oracle Text creates token list based indices for any sort of text resource and is commonly used to provide full text search in office documents, web pages, e-mail messages and XML data, but also things like comments or descriptions that come attached to otherwise structured data objects.

These days, and particularly for use cases that focus on full documents, it is more common to use some sort of external search index better suited to deal with unstructured data, such as the Apache Lucene which is not only a market leader by it's own right, but also sit at the core of several other leading products including Elastic (former Elasticsearch) and Apache Solr.

In the context of Oracle database, the term "local text index" does not really bring anything to mind. If you mean something like a local index on a VARCHAR2 field, those are great for short strings, such as book titles and author names, but horrible for working with larger chunks of text such as reviews of those same books, or even their content.

To comfortably navigate such data your search need things like the ability to judge relevancy, disregard unimportant words such as pronouns, include alternative spellings or consider words with similar meaning. In short, it needs to understand context.

That last is also the the name of the primary type of domain index used by the Oracle Text, and the feature itself is also formerly known as Oracle ConText.

As such, Oracle Text is almost beyond comparison in terms performance alone (and the dedicated search products even more so), but a basic document search could easily be several orders of magnitude faster, with the difference only increasing as you increment the complexity of data, queries, filters and presentation.

Oracle offers a range of options for creating, updating, optimizing, rebuilding or otherwise maintaining the indexes, allowing you to choose a design and maintenance strategy based on your specific requirements and acceptable tradeoffs.

In brief, the simplest answers that can still be said to hold some amount of accuracy, to each of the initial questions, may all be: 42 (considered by many as the traditional and everlasting symbol, representative of an answer that fully depend on specifics not defined in the question)

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.