Press "Enter" to skip to content

Indexing Multiple Columns in Oracle with DBMS_SEARCH

Brendan Tierney rounds up the usual suspects:

This type of index is a little different to your traditional index. With DBMS_SEARCH we can create an index across multiple schema objects using just a single index. This gives us greater indexing capabilities for scenarios where we need to search data across multiple objects. You can create a ubiquitous search index on multiple columns of a table or multiple columns from different tables in a given schema. All done using one index, rather than having to use multiples. Because of this wider search capability, you will see this (DBMS_SEARCH) being referred to as a Ubiquitous Search Index. A ubiquitous search index is a JSON search index and can be used for full-text and range-based searches.

This is an interesting approach to the problem, though as I think about it, it makes me wonder, if you’re constantly searching in A+B+C+D, is that really four separate attributes or has something gone wrong in the design? It’s early enough in the morning for me that I’m willing to accede to there being use cases in a well-designed database.