Section 1: Queries
Section 2: Indexing
Section 3: Updates
What do we mean by "query performance" anyway?
There are generally two measures of query performance - response time (the time to get an answer to an individual query), and throughput (the number of queries that can be run in any time period, eg queries per second). These two are related, but are not the same. In a heavily loaded system, we would normally want maximum throughput, whereas in a relatively lightly loaded system, we probably want minimum response time. Also, some applications will require a query to deliver all its hits to the user, whereas others may just require the first 20 hits from an ordered set. It is important to distinguish between these two scenarios.
What is the fastest type of text query?
The fastest type of query will meet the following conditions:
How does the size of my data affect queries?
The speed at which the text index can deliver ROWIDs is not affected by the actual size of the data, but by the size of the Token Table which holds the list of words, and information about the rows in which they appear. Text query speed will be related to the number of rows that must be fetched from this Token Table, and the length of each row.
Hence it should be nearly as fast to find a rare word in a large document set as it is to find a common word - or many uncommon words - in a smaller document set.
How does the source type of my data affect queries?
The format of the documents (eg. plain ascii text, HTML or Microsoft Word) should make no difference to query speed. The documents are filtered to plain text at indexing time, not query time.
The "cleanliness" of the data will make a difference. Spell-checked and sub-edited text for publication will tend to have a much smaller total vocabulary (and therefore size of token table) than informal text such as emails, which will contain many spelling errors and abbreviations which bloat the token table.
Does sorting the results slow a text-only query?
It most certainly does - except when sorting by relevence, or when the table is partitioned by the sort column (of which more later). If you sort by any other criteria - such as date, or price - then Oracle must resolve the entire query before it can pass back any results to the user application. If there is no sorting, then Oracle can return results as it finds them, which is very much quicker in the common case where the application needs to only display a page of results at a time.
There is a special case - sorting by relevence (SCORE(n)) can be extremely quick if the FIRST_ROWS hint is used. In this case, the kernel allows the text engine to perform a high speed internal sort when fetching from the text index tables.
An example of such a query:
SELECT /*+ FIRST_ROWS */ ID, SCORE(1), TEXT FROM MYTABLE WHERE CONTAINS (TEXT, 'searchterm', 1) > 0 ORDER BY SCORE(1) DESC;
Note that for this to work efficiently, there must be no other criteria in the WHERE clause other than a single CONTAINS.
Where the query contains non-text conditions, it may be that the query is "driven" from a B-tree index on a non-text column. In this case, an ORDER BY on this column will not be an overhead, since the B-tree index can deliver ROWIDs in sorted order. Such a query will use a functional lookup in the text index, which can be expensive if many rows are fetched from the B-tree index.
What is a "functional" versus an "indexed" lookup?
There are two ways the kernel can query the text index. In the first - and most common - case, the kernel asks the text index for all the rowids that satisfy a particular text search. These rowids are returned in blocks. In the second, the kernel passes individual rowids to the text index, and asks whether that particular rowid satisfies a certain text criterion. The second is known as a functional lookup, and is most commonly done where there is a very selective structured clause, so that only a few rowids must be checked against the text index.
An example of a search where a functional lookup may be used:
SELECT ID, SCORE(1), TEXT FROM MYTABLE WHERE START_DATE = '21 Oct 1992' <- highly selective AND CONTAINS (TEXT, 'commonword') > 0 <- unselective
What tables are involved in queries?
All queries will need to look at the "$I" or token table. Its name has the form DR$indexname$I. This contains the list of tokens (column TOKEN_TEXT) and the information about the row and word positions where the token occurs (column TOKEN_INFO).
The row information is stored as internal DOCID values. These must be translated into external ROWID values. The table used for this depends on the type of lookup: For functional lookups, the "$K" table - DR$indexname$K - is used. This is a simple Index Organized Table (IOT) which contains a row for each DOCID/ROWID pair.
For indexed lookups, the "$R" table - DR$indexname$R - is used. This holds the complete list of ROWIDs in a BLOB column (spread over up to 20 rows). Since ROWIDs are fixed length (18 characters), we can easily find the ROWID corresponding to DOCID 3, for example, by looking at byte position 55 (18 x 3 + 1).
Hence we can easily find out whether a functional or indexed lookup is being used by examining a SQL trace, and looking for the $K or $R tables.
Why should I use section searching?
As we've seen above, the fastest type of query is one where there is only a single CONTAINS clause, and no other conditions in the WHERE clause. Rather than having a query like:
SELECT title, isbn FROM booklist WHERE CONTAINS (title, 'horse') > 0 AND CONTAINS (abstract, 'racing') > 0
We would prefer to run a query such as
SELECT title, isbn FROM booklist WHERE CONTAINS (alltext, 'horse WITHIN title AND racing WITHIN abstract')>0
This will be a much faster query. In order to use a query like this, we must copy all the data into a single text column for indexing, with section tags around each column's data. This can be done via PL/SQL procedures before indexing, or by making use of the USER DATASTORE.
Is it OK to have lots of expansions in a query?
Each distinct word used in a query will require at least one row to be fetched from the $I token table. It is therefore best to keep the number of expansions down as much as possible. You should not use expansions such as wild cards, stemming and fuzzy matching unless they are necessary to the task. In general, a few expansions (say up to 20) is OK, but you should try to avoid more than 100 or so expansions in a query. The query feedback mechanism may be used to establish the number of expansions for any particular query expression.
How can local partition indexes help?
Oracle Text version 9i introduced the idea of "local partition indexes". This means that on a partitioned table, each partition has its own set of index tables. Effectively, there are multiple indexes, but the results from each are combined as necessary to produce the final result set.
The index is created using the
CREATE INDEX index_name ON table_name (column_name) INDEXTYPE IS ctxsys.context PARAMETERS ('...') LOCALThis method is particularly useful if
In the second example, with the table partitioned by price, the query may only need to get hits from the first partition in order to satisfy the first page of results.
There are, however a couple of points to watch out for:
When should I use a CTXCAT index?
In Oracle 8i Release 3 (Version 8.1.7), Oracle introduced a new type of index - the "catalog" or CTXCAT index type. The name derives from the main purpose of the index type - online catlogs.
CTXCAT indexes work best when text is in "small chunks" - maybe a couple of lines maximum - and searches need to restrict and/or sort the result set according to certain structured criteria - usually numbers or dates.
For example, lets consider an on-line auction site. Each item for sale has a short description, a current bid price and dates for the start and end of the auction. A user might want to see all the records with "antique cabinet" in the description, with a current bid price less than $500. Since he's particularly interested in new items, he wants the results sorted by auction start time.
Such a search would be fairly inefficient using a normal CONTEXT index. The kernel would have to find all the records that matched the text search, then restrict the set to those with the correct price (which requires the use of a different index), and then sort the results using a third index.
By including structured information such as price and date within the CTXCAT index, we are able to make this search very much more efficient.
When is a CTXCAT index NOT suitable?
The query language with CTXCAT is considerably simpler than for CONTEXT indexes. Basically, you can search for phrases and words (with wild cards if required), using AND and OR operators, but that's about it. If your application needs more complex text retrieval featurs - such as stemming, thesaurus, fuzzy matching and so on, you should be using a CONTEXT index.
There are also differences in the time and space needed to create the index. CTXCAT indexes take quite a bit longer to create - and use considerably more disk space - than CONTEXT indexes. If you are tight on disk space, you should consider carefully whether CTXCAT indexes are appropriate for you.
What optimizer hints are available, and what do they do?
The optimizer hint
Finally, if your query has an ORDER BY clause on a B-tree indexed column, it may be worth considering use of the INDEX_DESC hint. This plan is never chosen by the cost-based optimizer, but may be optimal in some circumstances.
How long should indexing take?
Indexing text is a resource-intensive process. Obviously, the speed of indexing will depend on the power of the hardware involved, but you should expect somewhere between 50MB / hour on workstation-class NT machine (approx 400MHz CPU, 128MB memory) to more than 1GB per hour on a large multi-CPU, multi-gigabyte server machine. The latter figure assume you are using parallel indexing on a partitioned table - a new option for 8.1.6 - see the documentation for more details.
For most real-life systems, the time to index a complete table of documents will be measured in hours, and in some cases days.
How can I tell how far my indexing has got?
You can use the
Otheriwse, for a course-grained answer, you can count the number of rows in the DR$xxx$K table. There will be one row in here for each row that has been indexed, BUT these rows are only committed when the indexing process runs out of indexing memory and does a "flush" to the database. It is even possible that this will never happen until indexing is complete.
How much disk overhead will the indexing require?
The overhead - the amount of space needed for the DR$ index tables - varies between about 25% of the original text volume, and 100%. Generally, the larger the total amount of text, the smaller the overhead, but many small records will use more overhead than fewer large records. Also, "clean" data (such as published text) will require less overhead than "dirty" data such as emails or discussion notes, since the "dirty" data is likely to include many unique words from mis-spellings, abbreviations, etc.
Theme indexes are generally much smaller than text indexes. Creating ONLY a theme index will generally require very little storage, but creating only a text index will not save you much space over a combined index (though it is likely to be significantly faster).
How does the format of my data affect indexing?
Looking at indexing overhead, you can expect much lower overheads for formatted documents (eg. Microsoft Word files) since such documents tend to be very large compared to the actual text held in them. So 1GB of Word documents might only require 50MB of index space, whereas 1GB of plain text might require 500MB, since there is ten times as much "plain text" in the latter set.
Indexing time is less clear-cut. Although the reduction in the amount of text to be indexed will have an obvious effect, we must balance this out against the cost of filtering the documents. In general, these will roughly cancel out, so the time to index 1GB of formatted docs will be about the same as to index 1GB of plain text, although it may be a little longer.
Can I index in parallel?
In 8.1.6 and later, you may use parallel indexing to index partitioned tables, with one indexing process per partition.
How you do this depends on whether or not you are creating a
local partition index. If you are not, you simply use the
CREATE INDEX index_name ON table_name (column_name) INDEXTYPE IS ctxsys.context PARAMETERS ('...') PARALLEL 3This will create the index with three separate indexing processes. If you are using a local partition index, the process is a little more complex. You must first create the index using the
ALTER INDEX index_name REBUILD PARTITION partition_name
What INDEX MEMORY settings should I use?
You should aim to set the INDEX MEMORY value as high as possible, without causing paging. Experience has shown that using a large INDEX MEMORY setting - even into hundreds of megabytes - will improve the speed of indexing and reduce the fragmentation of the final indexes. However, if set too high, then the memory paging that occurs will cripple indexing speed. If using parallel indexing, don't forget that each stream will require its own index memory.
When dealing with very large tables, you may wish to tune your database SGA differently for indexing and retrieval. For retrieval, you are hoping to get as much information buffered in the SGA (block buffer cache) as possible. So you should be allocating a large amount of memory to the block buffer cache. But this will not make any difference to indexing, so you would be better off reducing the size of the SGA to make more room for a large INDEX MEMORY setting.
How often should I index new/updated records?
How often do you need to? The less often you run reindexing (via the command
Many systems are OK with overnight indexing. This means data that is less than a day old is not searchable. Other systems use hourly, ten minute, or five minute updates.
Note that running a CTXSRV process may cause records to be indexed as soon as they are committed. If records are batch loaded in large numbers before being committed, this is probably OK. However, if they are added one at a time and committed after each add, then a CTXSRV process is likely to cause drastic fragmentation of the indexes.
How can I tell when my indexes are getting fragmented?
The best way is to time some queries, run index optimization, then time the same queries (restarting the database to clear the SGA each time, of course). If the queries speed up significantly, then optimization was worthwhile. If they don't ... well, you can wait longer next time.
A more scientific method involves counting the number of rows for each term in the DR$xxx$I table:
SELECT AVG(COUNT(*)) FROM DR$index_name$I GROUP BY TOKEN_TEXT HAVING COUNT(*) > 1;
(note we ignore all words with only a single row in the index table).
A value greater than 10 from this query may indicate the need to optimize the index, but experimentation should yield the best value in any particular circumstances. Very large tables will inevitably have a lot of rows where the TOKEN_INFO data overflows the 4K internal limit, so you would expect the average to be greater on large systems.
Does INDEX MEMORY affect ALTER INDEX ... 'SYNC'?
Yes - just the same way as for normal indexing. But of course, there are often far fewer records to be indexed during a SYNC, so it is not usually necessary to provide hundreds of megabytes of indexing memory.
Last modified August 28 2001 by Roger Ford