What is a good way to optimize an Oracle query to find a substring?

Question

I have a column in an unsegmented Oracle table defined as VARCHAR2 (50); the column has a standard b-tree index. I was wondering if there is an optimal way to query this column to determine if it contains a given value. Here is the current request:

SELECT * FROM my_table m WHERE m.my_column LIKE '%'||v_value||'%';

I looked at Oracle Text, but it seems like too much noise for such a small column. However, there are millions of entries in this table, so finding interlinear matches takes longer than you would like. Is there a better way?

Answer 1

:

• ;
• - Oracle Text ;
• , .

- , ( %), .

- , ( "est" "", "" ). MySQL (MyISAM) SQL Server , . , Oracle. , , .

Answer 2

.

. v_value , Oracle Text , . , , .

Answer 3

Oracle Text , . , CTXCAT.

SELECT * FROM my_table m 
WHERE catsearch(m.my_column, v_value, null) > 0
/

, CTXCAT , . , .

...

Answer 4

, REGEXP_LIKE. , fbi case, "1" , fbi.

.

:

CREATE INDEX regexp_like_on_myCol ON my_table (
      CASE WHEN REGEXP_LIKE(my_column, '[static exp]', 'i') 
           THEN 1
           END);

, :

SELECT * FROM my_table m WHERE m.my_column LIKE '%'||v_value||'%';

:

SELECT * FROM my_table m WHERE (
      CASE WHEN REGEXP_LIKE(m.my_column, '[static exp]', 'i')
           THEN 1
           END) IS NOT NULL;

, " exp" , . , .

, , , , regex, . , , .

Answer 5

INSTR:

...WHERE INSTR(m.my_column, v_value) > 0

Oracle , , LIKE .

Answer 6

, , , , , - . , , , , , , .