Efficiently Searching for Multiple patterns in a Text Column

Question

I have a table with a text column that contains a description and a GUID for each operation. Each GUID can appear in a maximum of three rows.

I've been given a list of about 1000 GUIDs, and I need to find every row that contains these GUIDs.

My current query is:

SELECT * 
FROM table_name 
WHERE CURR_DAY BETWEEN TO_DATE('2023-08-26', 'yyyy-mm-dd') AND TO_DATE('2023-10-11', 'yyyy-mm-dd') 
AND (
    purpose LIKE '%c016a45d-1e81-472d-a161-79369f87d6a0%'
    OR purpose LIKE '%7e5a1766-c26e-4050-8120-25160bbcf5fb%'
    OR purpose LIKE '%983006d0-b156-4aee-9ef2-21846c7a5b79%'
    OR purpose LIKE '%154e86ca-6436-41d9-8593-f47ed1167766%'
    -- ... repeated for about 1080 GUIDs
)

The problem is that this query takes more than 3 hours to execute.

Is there a more efficient way to perform this search?

Answer 1

Well, having both a GUID and other text in the same columns seems... less than an optimal design. But it probably isn't you who have designed the database so.

As for your question, it should be faster to create an temp table, (or a cte?) and just join against that one. But bear in mind, having like statements with wildcards can be very slow, espacially when have 1000 like statements. But try this code out, let me know if performance improve.

-- Create a global temporary table
CREATE GLOBAL TEMPORARY TABLE TEMP_GUID_LIST 
(
    Guid VARCHAR2(255)
) ON COMMIT DELETE ROWS;

DECLARE
BEGIN
    -- Insert GUIDs into the temporary table
    INSERT INTO TEMP_GUID_LIST (Guid) VALUES ('c016a45d-1e81-472d-a161-79369f87d6a0');
    INSERT INTO TEMP_GUID_LIST (Guid) VALUES ('7e5a1766-c26e-4050-8120-25160bbcf5fb');
    

    -- results
    FOR r in (SELECT t.*
              FROM YourTableName t
              INNER JOIN TEMP_GUID_LIST g ON t.purpose LIKE '%' || g.Guid || '%'
              WHERE CURR_DAY BETWEEN TO_DATE('2023-08-26', 'yyyy-mm-dd') AND TO_DATE('2023-10-11', 'yyyy-mm-dd')) 
    LOOP
    END LOOP;

    COMMIT; -- This will also clear the rows from the temporary table
END;