I have this where clause condition in Oracle to check for names that are similar to a given value, it returns the correct results however it runs very slow. Is there a more efficient way to execute this condition? Thanks.
(P5_ORGN_NAME IS NULL OR
(D.ORGANIZATION_NAME LIKE '' || upper(P5_ORGN_NAME) || '%' OR D.FORMATTED_ORGANIZATION_NAME like
'' || local.pkg.orgname_format(upper(P5_ORGN_NAME)) || '%')
DETERMINISTIC so Oracle knows not to run it over and over for every row.
FUNCTION orgname_format (p_org_name VARCHAR2) RETURN VARCHAR2 DETERMINISTIC IS BEGIN ... END;
This will tell Oracle that the function always returns the same value for the same input parameter value. Knowing that, Oracle can execute the function one time instead of executing it for every row in the underlying table(s) in your query.
Of course, you can only use this if the function really is deterministic, which it sounds like it is, judging from the name of it.