Archive for December, 2010

New features Oracle Text 11g part 1: Mixed queries

8 December 2010

Oracle 11g gave Oracle Text a boost. Oracle added many new features. In the upcoming posts I will give the new features of Oracle Text 11g.

The first one I want to show is the feature called “Mixed Queries”. The feature is added to the Context Index, it was already an option for Cxtcat Index. By using “Mixed Queries”, you include in the text index not only the column on which you want to search but also on extra fields, on which you normally would put an index and on which you want an additional search.

Example:

select title from books
where contains(content,'oracle') > 0
and   published_year = 2006
and   price < 40
order by price asc

The above query searches for book containing the word oracle in the content, published in the year 2006 and it price is below €40 (yes. I’m in Europe), ordered by the price. In Oracle 10g and before you could index the column content with a Context index, but nor the other 2 columns (Ok, in 10g you have the option to use MDATA but only for equality). In 11g you have the option to include both columns in the Context Index, so Oracle can find them in the same index.

So in versions before 11g we made the following indexes:

CREATE INDEX book_year_ind01 ON books (year);

CREATE INDEX book_price_ind01 ON books (price);

CREATE INDEX book_content ON books (content) INDEXTYPE IS
ctxsys.context;

In 11G we can make this work in one single index:

CREATE INDEX book_content ON books(content)
INDEXTYPE IS ctxsys.context
FILTER BY year, price
SORT BY price;

So now you are done with one index. Oracle will make now a so-called SDATA section, you will find it creates an extra table for the index namely a table with at the end $S.

However, if you want to search lonely on the year, it can still be necessary to create a normal index on year. It will be more efficient then the text index.

Advertisements