clob datatype is causing performace issue

user_10806781 0
Viewed 777

UPDATED: The code is working as expected but the performance is very slow. When I do a search without including CLOB data then the query runs very fast but if I include CLOB variable in my search the query is very slow. I am using CLOB to pass large string data('aaaaaaa,bbbb,c,ddddd...') and store those data in global table for better performance, I thought doing such will maximize query performance. How can I improve/utilize my CLOB variable for better perfomance? Please look at the code below for more information. Appreciated for any help. I am still struggling with performance can anyone help/provide any suggestions please.

GLOBAL TT GlobalTemp_EMP( //this already exists
emp_refno (30 byte);
)


Create or replace PROCEDURE Employee(

emp_refno IN CLOB

)

AS

Begin
OPEN p_resultset FOR
with inputs ( str ) as (  //red error line here 
       select to_clob(emp_refno )
       from dual
     ),
     prep ( s, n, token, st_pos, end_pos ) as (
       select ',' || str || ',', -1, null, null, 1
         from inputs
       union all
       select s, n+1, substr(s, st_pos, end_pos - st_pos),
              end_pos + 1, instr(s, ',', 1, n+3)
         from prep
         where end_pos != 0
     )
INSERT into GlobalTemp_EMP   //red error line here 
select token from prep;


select e.empname, e.empaddress, f.department
from employee e
join department f on e.emp_id = t.emp_id
and e.emp_refno  in (SELECT emp_refno from GlobalTemp_EMP) //using GTT In subquery
spring-mvc plsql oracle
3 Answers

put this code between BEGIN and OPEN p_resultset FOR : this might have some performance issue though.
INSERT into GlobalTemp_EMP 
with inputs ( str ) as (  
       select to_clob(emp_refno )
       from dual
     ),
     prep ( s, n, token, st_pos, end_pos ) as (
       select ',' || str || ',', -1, null, null, 1
         from inputs
       union all
       select s, n+1, substr(s, st_pos, end_pos - st_pos),
              end_pos + 1, instr(s, ',', 1, n+3)
         from prep
         where end_pos != 0
     )
  
select token from prep where token is not NULL;

user_130797410

The below doesn't seem to be valid syntax:

GLOBAL TT GlobalTemp_EMP( //this already exists
emp_refno (30 byte);
)

I don't know the reason for using byte semantics, or whether you defined it as a clob or char or varchar2.

If it is currently a clob, then perhaps you could define the column as emp_refno varchar2(30 char) and add a unique index, changing the Employee procedure to only insert new IDs. An index would help the insertions more than when you read it out.

If you want to insert a huge amount of data into GlobalTemp_EMP faster, I would recommend making it a regular table, pre-processing the data (such as in Perl or other language) to split IDs outside Oracle and then use SQL*Loader. Or perhaps an external table.

user_44167930

I don't think using a global temporary table will improve your performance at all (at least without indexes). Are you sure these are CLOBs? At a glance, these seem to be varchars.

To compare CLOBs, you should be using dbms_lob.compare. I think = will do an implicit conversion to a varchar (and truncate), then do the comparison.

user_236690
Related