慕哥9229398
为了获得最佳性能，最好避免在拆分器功能中使用分层(CONNECT BY)查询。将以下拆分器功能应用于更大的数据量时，其性能会更好CREATE OR REPLACE FUNCTION row2col(p_clob_text IN VARCHAR2) RETURN sys.dbms_debug_vc2coll PIPELINEDIS next_new_line_indx PLS_INTEGER; remaining_text VARCHAR2(20000); next_piece_for_piping VARCHAR2(20000); BEGIN remaining_text := p_clob_text; LOOP next_new_line_indx := instr(remaining_text, ','); next_piece_for_piping :=CASEWHEN next_new_line_indx <> 0 THEN TRIM(SUBSTR(remaining_text, 1, next_new_line_indx-1))ELSE TRIM(SUBSTR(remaining_text, 1))END; remaining_text := SUBSTR(remaining_text, next_new_line_indx+1 ); PIPE ROW(next_piece_for_piping); EXIT WHEN next_new_line_indx = 0 OR remaining_text IS NULL; END LOOP; RETURN; END row2col;/可以在下面观察到这种性能差异(我使用了本讨论前面给出的功能分配器)。SQL> SET TIMING ONSQL>SQL> WITH SRC AS ( 2 SELECT rownum||',a,b,c,d,e,f,g,h,i,j,k,l,m,n,o,p,q,r,s,t,u,v,w,x,y,z'||rownum txt 3 FROM DUAL 4 CONNECT BY LEVEL <=10000 5 ) 6 SELECT NULL 7 FROM SRC, TABLE(SYSTEM.row2col(txt)) t 8 HAVING MAX(t.column_value) > 'zzz' 9 ;no rows selectedElapsed: 00:00:00.93SQL>SQL> WITH SRC AS ( 2 SELECT rownum||',a,b,c,d,e,f,g,h,i,j,k,l,m,n,o,p,q,r,s,t,u,v,w,x,y,z'||rownum txt 3 FROM DUAL 4 CONNECT BY LEVEL <=10000 5 ) 6 SELECT NULL 7 FROM SRC, TABLE(splitter(txt)) t 8 HAVING MAX(t.column_value) > 'zzz' 9 ;no rows selectedElapsed: 00:00:14.90SQL>SQL> SET TIMING OFFSQL>
