Oracle index for a static like clause

Question

I want to index this query clause -- note that the text is static.

SELECT * FROM tbl where flags LIKE '%current_step: complete%'

To re-iterate, the current_step: complete never changes in the query. I want to build an index that will effectively pre-calculate this boolean value, thereby preventing full table scans...

I would prefer not to add a boolean column to store the pre-calculated value as this would necessitate code changes in the application....

Answer 1

I would prefer not to add a boolean column to store the pre-calculated value as this would necessitate code changes in the application

There are two ways I can suggest :

1.

If you are on 11g and up, you could have a VIRTUAL COLUMN always generated as 1 when the value is complete else 0. All you need to do then :

select * from table where virtual_column = 1

To improve performance, you could have an index over it, which is equivalent to a function-based index.

2.

Update : Perhaps, I should be more clear with my second point : source

There are instances where Oracle will use an index to resolve a like with the pattern of '%text%'. If the query can be resolved without having to go back to the table (rowid lookup), the index may be chosen. Example:

select distinct first_nm from person where first_nm like '%EV%';

And in above case, Oracle will do an index fast full scan - a full scan of the smaller index.

Answer 2

If you don't want to change the query, and it isn't just an issue of nor changing the data maintenance (in which case a virtual column and/or index would do the job), you could use a materialised view that applies the filter, and let query rewrite take case of using that instead of the real table. Which may well be overkill but is an option.

The original plan for a mocked-up version:

explain plan for
SELECT * FROM tbl where flags LIKE '%current_step: complete%';
select * from table(dbms_xplan.display);

--------------------------------------------------------------------------                                                                                                                                                                                                                                   
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |                                                                                                                                                                                                                                   
--------------------------------------------------------------------------                                                                                                                                                                                                                                   
|   0 | SELECT STATEMENT  |      |     2 |    60 |     3   (0)| 00:00:01 |                                                                                                                                                                                                                                   
|*  1 |  TABLE ACCESS FULL| TBL  |     2 |    60 |     3   (0)| 00:00:01 |                                                                                                                                                                                                                                   
--------------------------------------------------------------------------                                                                                                                                                                                                                                   

Predicate Information (identified by operation id):                                                                                                                                                                                                                                                          
---------------------------------------------------                                                                                                                                                                                                                                                          

   1 - filter("FLAGS" IS NOT NULL AND "FLAGS" LIKE '%current_step:                                                                                                                                                                                                                                           
              complete%')                                                                                                                                                                                                                                                                                    

A materialised view that will only hold the records your query is interested in (this is a simple example but you'd need to decide how to refresh and add a log if needed):

create materialized view mvw
enable query rewrite as
SELECT * FROM tbl where flags LIKE '%current_step: complete%';

Now your query hits the materialised view, thanks to query rewrite:

explain plan for
SELECT * FROM tbl where flags LIKE '%current_step: complete%';
select * from table(dbms_xplan.display);

-------------------------------------------------------------------------------------                                                                                                                                                                                                                        
| Id  | Operation                    | Name | Rows  | Bytes | Cost (%CPU)| Time     |                                                                                                                                                                                                                        
-------------------------------------------------------------------------------------                                                                                                                                                                                                                        
|   0 | SELECT STATEMENT             |      |     2 |    60 |     3   (0)| 00:00:01 |                                                                                                                                                                                                                        
|   1 |  MAT_VIEW REWRITE ACCESS FULL| MVW  |     2 |    60 |     3   (0)| 00:00:01 |                                                                                                                                                                                                                        
-------------------------------------------------------------------------------------    

But any other query will still use the original table:

explain plan for
SELECT * FROM tbl where flags LIKE '%current_step: working%';
select * from table(dbms_xplan.display);

--------------------------------------------------------------------------                                                                                                                                                                                                                                   
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |                                                                                                                                                                                                                                   
--------------------------------------------------------------------------                                                                                                                                                                                                                                   
|   0 | SELECT STATEMENT  |      |     1 |    27 |     3   (0)| 00:00:01 |                                                                                                                                                                                                                                   
|*  1 |  TABLE ACCESS FULL| TBL  |     1 |    27 |     3   (0)| 00:00:01 |                                                                                                                                                                                                                                   
--------------------------------------------------------------------------                                                                                                                                                                                                                                   

Predicate Information (identified by operation id):                                                                                                                                                                                                                                                          
---------------------------------------------------                                                                                                                                                                                                                                                          

   1 - filter("FLAGS" LIKE '%current_step: success%' AND "FLAGS" IS NOT                                                                                                                                                                                                                                      
              NULL)              

Of course a virtual index would be simpler if you are allowed to modify the query...

Answer 3

A full text search index might be what you are looking for.

There are a few ways you can implement this:

• Oracle has Oracle Text where you can define which type of full text index you want.
• Lucene is a Java full text search framework.
• Solr is a server product that provides full text search.