Forum Stats

  • 3,783,310 Users
  • 2,254,756 Discussions
  • 7,880,358 Comments

Discussions

Optimize query calling UDF with CLOB input parameter

UdayanSil
UdayanSil Member Posts: 1
edited Jul 10, 2020 11:01AM in Social Groups

Hi,

I'm trying to optimize oracle query which has clob field in the table. All the clob texts are unique.

The select statement is trying to calling multiple UDF (17 different UDFs) inline and send the clob field as the input parameter.

These UDFs are doing a pattern search using regex_substr() function.

e.g.

select  UDF1(clob field) as field1,

           UDF2(clob field) as field2,

               .

               .

               .

from table_with_clob_field

This table has little over 7000 records, yet it takes around 6 hour to execute.

Can anyone guide me how can I reduce the context switching / make the UDF functions more efficient / optimize the query.

Regards,

Udayan