FAST SEARCH across 150 columns with Oracle Text Index

Introduction

Recently, we were handed an application feature requirement for users to be able to search across approximately 150 different columns distributed across 14 different tables. While this initially sounded like an everyday SQL task easily solved with standard relational queries, it quickly morphed into an architectural challenge. What started as a straightforward feature request rapidly evolved into a complex data engineering puzzle, forcing us to abandon basic SQL operations and fundamentally rethink how we index, aggregate, and query our entire data layer.

In this blog, we’ll explore the different solutions we attempted, which ones worked and which ones didn’t. By the end, you should know how to set up your own Oracle Text Search Configuration in Oracle Database 19c that follows RADAPEX’s best practices!

The Initial Solutions

A newbie SQL developer may have some naive ideas about using LIKE conditions to check each column for matches. They may try to write something like this:

SELECT *
FROM my_table
WHERE col_one LIKE ‘%’ || :P1_SEARCH_FIELD || ‘%’
OR col_two LIKE ‘%’ || :P1_SEARCH_FIELD || ‘%’
OR col_three ...

With this approach, you can write a ‘good enough’ search engine in pure ANSI SQL. Sounds perfect, but you very quickly end up LIKE-ing yourself into corners. Namely ‘performance’ and ‘maintainability’. A quick LIKE check works for 1 or 2 columns but, for 150 columns, this method becomes completely unmanageable. LIKEs are slow, and 150 of them for 1 query might get you the results you want, but only if you are willing to wait 6 months for the query to finish!

Our initial solution was not too dissimilar from the above, we originally tried to create a database view that joined all of the tables and collected the data we wanted to search, then create a Standard Text Search Configuration that would automatically handle the searching for us. If you have experience with Oracle Search Configurations, you’ll know that the ‘Standard’ search option actually works by generating LIKE expressions for each column you add to the search. That’s the exact ‘naive’ solution hidden as an Oracle APEX feature!

Almost immediately, we noticed that Standard searches could not handle the volume of data we had. This search method did not just slow down the application, every search attempt completely hung the session! It was clear that we would need to make use of Oracle Text, which is Oracle’s native solution to lightning-fast, index-based text searching.

After some research into Oracle Text, we decided the way forward was a Materialized View which would join our columns into a single CLOB, then we could use an Oracle Text Index on the CLOB column. It seems reasonable, simple, and fairly maintainable. However, this solution was not without problems either.

Some Unexpected Complications

Creating the Materialized View was trivial, we already knew exactly what columns and what tables we needed to join. Refreshing or populating the view, however, was a different story. We tried creating the MV with BUILD IMMEDIATE and BUILD DEFERRED, as well as using synchronous refresh on commit and automatic scheduled syncing; however, the process would stall for an hour and then crash.

The Oracle Text Index was no easier to create. We put the refreshing of the view aside and used BUILD DEFERRED to get a working data structure that we could build an index on. The SQL statement was simple:

CREATE INDEX idx_search_engine ON my_mview ( search_clob ) INDEXTYPE IS CTXSYS.CONTEXT;

Unfortunately, this would fail every time with an obscure error message relating to missing LOB_DATATYPE and NONE_DATATYPE preferences. At this point, the task seemed almost hopeless; we had no MView, no index, and no search.

Starting Anew

We refused to give up, and our first step was to resolve the issues creating the index. With the help of the DB management team, we figured out that Oracle Text was not completely installed properly in our database. These kinds of misconfiguration errors happen frequently in database systems, as such it is important to be able to identify when a problem can be resolved by correcting configuration. We re-installed Oracle Text and the index creation worked.

The next step was to bin off the Materialized View. Clearly, the database couldn’t handle collecting all of the data we wanted into the view, and therefore it was not going to work. We needed to rethink our solution from the ground up. Luckily, Oracle Text has other ways to join data from multiple sources. The method we chose to use was to create a custom datastore that is handled by a PL/SQL procedure.

In Rapid Application Development, you need to be able to quickly make decisions about the best way to deal with problems. We could have kept our initial model and kept working on it until it finally gave us what we wanted, but balancing correctness, performance, and sprint timings all at once can be a daunting challenge. It is best to know when to stop chasing a dead end and think of something better, so that’s what we did.

A Working Search Engine

We first create a database procedure that will build a JSON data structure containing the columns we want. This performs far better than refreshing a Materialized View:

CREATE OR REPLACE PROCEDURE p_serialize_search (
	p_rowid IN rowid;
	p_clob IN OUT NOCOPY CLOB;
) AS
BEGIN
	SELECT JSON_OBJECT(
		‘col_one’ VALUE mt.col_one
		, ‘col_two’ VALUE mt.col_two
		, ‘col_three’ VALUE st.col_three

		...

		RETURNING CLOB
	)
	INTO p_clob
	FROM main_table mt
	JOIN second_table st ON st.main_table_id = mt.id
	WHERE mt.rowid = p_rowid;
EXCEPTION
	WHEN OTHERS THEN
		p_clob := ‘{}’;
		RAISE;
END;
/

Creating a custom datastore is achieved by calling the CTX_DDL PL/SQL package, we need to configure its ‘PROCEDURE’ attribute to point to our procedure:

BEGIN
	ctx_ddl.create_preference(‘search_ds’, ‘USER_DATASTORE’);
	ctx_ddl.set_attribute(‘search_ds’, ‘PROCEDURE’, ‘p_serialize_search’);
END;
/

We can then use this datastore in our index’s parameters to force the index to use the procedure. Since we don’t have a view anymore, we can just attach the index to our largest table, and use any VARCHAR2 non-nullable column to anchor it. Our index will still search every column that we specify in the procedure. We specify the ‘section group’ as CTXSYS.JSON_SECTION_GROUP which tells Oracle that the procedure we have is returning a JSON object. There are more advanced searching techniques you can use with section groups, for example searching specific fields. For this example, we don’t need the advanced capabilities, and our index will be set to automatically sync every minute:

CREATE INDEX idx_search_engine ON main_table ( anchor_column ) INDEXTYPE IS CTXSYS.CONTEXT PARAMETERS (‘
DATASTORE search_ds
SECTION_GROUP CTXSYS.JSON_SECTION_GROUP
SYNC (EVERY “FREQ=MINUTELY;INTERVAL=1”)’);

With all of our database objects in place, we are finally ready to create an Oracle Text Search Configuration in the APEX Shared Components. Setting this up is trivial, just follow the wizard steps and select your selected anchor column when asked for the ‘Oracle Text Index Column’.

After creating this search configuration, you can use the APEX ‘Search Results’ region on any page with our Search Configuration as the source to allow users to search across all 150 columns.

Key Takeaway

The most valuable lesson learned from this experience is that your first solution most likely isn’t the best one, and that you should never give in to the ‘sunken cost’ ideology. Adapting to unexpected problems as they arise is part of Rapid Application Development, being able to come up with multiple viable solutions is invaluable when experimenting with new features.

This blog has been focused mainly on Oracle Database 19c, as there have been many advancements to text searching techniques in Oracle Database 26ai. In particular, the addition of the Ubiquitous Search features makes building and searching JSON documents much simpler using the DBMS_SEARCH package. Unfortunately, some of us are still using 19c.

Please check out more of our blogs to find out more about Oracle APEX 26.1 / Database 26ai features and read more of our success stories.