You work in software and your stack includes an Oracle database. One day the business approaches you and says ‘I want a search page for our product/order/customer data. Make it work like Google’. You think to yourself, “If I could make a search page work like Google I would work at Google”!!! Fear not, developer. This problem has been solved many times in the past. In this blog post I’m going to show you how to approach this problem, and show you a shortcut in case your environment’s stack includes an Oracle database.
Approaching Full Text Search
The problem you’re solving has a name and that name is Full Text Search. The problem is that your Relational database, while presumably well normalized, is not good at searching for single words across huge data sets. You need a different kind of database which is optimized for full text search. A Search database will physically store the data differently so that it can quickly look up your search terms and return some metadata associated with those terms. In your RDMBS, records are identified by keys. In your Search index, they keys are the search terms.
There are several well known full text search solutions. The bare minimum list you should probably know about is Solr/Lucene, Sphinx, and ElasticSearch. These are all great full text search solutions, but they all require a lot of overhead to operate. New servers, new software to install, new syntaxes to learn, admin consoles, and new interfaces or libraries to build into your front end application.
Oracle Stack Solution: Oracle Text
One drawback of each of the aforementioned search solutions is that you will likely want to run it on a dedicated machine (or VM). If you work in an Oracle shop it likely means that you work in an enterprise where provisioning hardware (even virtual hardware) can be annoyingly difficult and time-consuming process. I this environment, Oracle Text jumps out as a really nice solution. Oracle Text is a full text search solution that is built in to all modern version of Oracle’s database. This means that you don’t have to request a new machine, and request for new software to be installed on that machine in each of your QA and Production environments (or request for root access to do it yourself). With Oracle Text you just run some DDL to create the index and start using it!* The only hardware issue you should consider is the amount of disk in use on your Oracle database.
Here’s a simple example of how to take advantage of an Oracle Text search index. Let’s assume that I have a database with products and reviews (a product has many reviews) and I want to be able to return search results for both at once.
The most straight-forward way to start is to gather all of the data you want to index into a single VARCHAR2 column named SEARCH_TEXT on our PRODUCT table. If you need to index more than 4000 characters, use a CLOB.
alter table PRODUCTS add SEARCH_TEXT varchar2(4000);
Now we need to populate that column with the search data we want to index from the PRODUCTS and REVIEWS tables We are going to fetch the data into the search text column as a big space delimited string. The below query is called a correlated update, and is specific to Oracle. You can accomplish the same thing with a procedure but I find this more concise.
update PRODUCTS set SEARCH_TEXT = ( select P.name ||' '|| P.description ||' '||R.title ||' '||R.review_text from PRODUCTS P, REVIEWS R where P.ID = R.PRODUCT_ID ) where PRODUCTS.id = P.ID;
Next we create Oracle Text index on that column. The important part is the ctxsys.context at the end of this statement. Context is one of the three types of text indexes that oracle offers, but the best one for blocks of structured text.
create index PRODUCT_REVIEW_SEARCH_IDX on PRODUCT(SEARCH_TEXT) indextype is ctxsys.context;
It is worth noting that you can configure the index to use a separate tablespace so that you can control where on the disk your index lives. See the docs for more info.
Next we we run a command to ‘sync‘ the index. This actually indexes the data for the first time. Run it again after you’ve inserted or updated data to update the index. In fact, you should plan on running this command periodically as part of a dbms_scheduler or whatever your enterprise’s favorite scheduler is.
Now we can run a full text search query and see some results. A statement like this will return all product records which have the word ‘paper’ in the title, description, or reviews. yay! It’s pretty awesome that we can run searches on this index in our existing RDBMS and apply whatever filters, sorts, and joins we want without having to call out to another system.
select * from PRODUCTS where contains(SEARCH_TEXT, 'paper') > 0;
Finally, we create a job to periodically ‘optimize‘ the index. According to the docs your index gets fragmented and slower over time and this will fix it up. I’ve had luck with running this nightly but YMMV.
After you’ve got your index up and running you can get some useful info and stats out of it with the CTX_REPORTS package. Among other things it will tell you how fragmented your index is, and what words are the most frequently indexed.
I’ve really just scratched the surface to show you how to get a text index up and running fast. Oracle has a ton of options to tune the index, and search features like fuzzy searching, stemming, and wildcards.
*Ok, maybe you should still consult a DBA first if you have access to one.