amaach amaach - 5 months ago 25
SQL Question

Slow Oracle execution

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.

AND
(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)) || '%')
)

Answer

Make the pkg.orgname_format function DETERMINISTIC so Oracle knows not to run it over and over for every row.

In your local.pkg package...

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.

Comments