Home » RDBMS Server » Performance Tuning » Long Running SQL- CLOB vs TO_CHAR
Long Running SQL- CLOB vs TO_CHAR [message #289383] Fri, 21 December 2007 02:52 Go to next message
mymot
Messages: 225
Registered: July 2005
Senior Member
Hello,

I have got below query runs for 17 hours to spool 8200 rows

SELECT FULL_DATA 
FROM 
( 
SELECT 
  P_ID 
, HD_ORD 
, TO_CLOB(FULL_DATA) as FULL_DATA 
FROM TMP_PAT1 
-- 
UNION ALL 
-- 
SELECT 
  P_ID 
, ACC_ORD 
, TO_CLOB('"' || REC_ID        || '","' || 
  ACC_NUM                 || '","' || 
  DPDAMT                          || '","' || 
  BALAMT                          || '","' || 
  LMTAMT                          || '","' || 
  PRODUCT                         || '","' || 
  Con_Amt      || '","' || 
  Con_Freq     || '","' || 
  ARR_AMT                     || '","' || 
  XFE_ACCT                     || '"')  AS FULL_DATA 
FROM TMP_PAT2
-- 
UNION ALL 
-- 
SELECT 
  P_ID 
, CUST_ORD 
, TO_CLOB('"' || REC_ID        || '","' || 
  CUST_NUM                 || '","' || 
  TITLE                           || '","' || 
  FIRST_NAME                      || '","' || 
  SURNAME                         || '"') AS FULL_DATA 
FROM TMP_PAT3 
-- 
UNION ALL 
-- 
SELECT 
  P_ID 
, POL_ORD 
, TO_CLOB('"' || REC_ID        || '","' || 
  LI_RUTE_NUM)              || '"' AS FULL_DATA 
FROM TMP_PAT4 
-- 
UNION ALL 
-- 
SELECT 
  P_ID 
, NOT_ORD
, TO_CLOB('"' || REC_ID             || '","' || 
  TO_CHAR(START_DTE_TIM, 'DD/MM/YYYY') || '","' || 
  START_TEXT                           || '"') AS FULL_DATA 
FROM 
   (SELECT 
     AR_LOCAL_ID_ORDER 
    , NOTES_ORDER 
    , RECORD_ID 
    , START_DTE_TIM 
    , START_TEXT 
   FROM TMP_PAT5 
  )
ORDER BY 1, 2 
; 


Below query perform little better than above but trade off is to_char used to contacte string instead of CLOB and then restrict total size of text to_char(s1) ||to_char(s2)||to_char(S3)..... using substr function


SELECT TO_CLOB(full_DATA) 
FROM 
( 
SELECT 
  P_ID  AS ID1   
, HD_ORD AS ID2 
, 0 AS ID3   
, FULL_DATA
FROM TMP_PAT a 
-- 
UNION ALL 
-- 
SELECT 
  P_ID AS ID1 
, ACT_ORD    AS ID2 
, 0 AS ID3                         
, TO_CHAR('"' || REC_ID        || '","' || 
  ACC_NUM                  || '","' || 
  DPDAMT                          || '","' || 
  BALAMT                          || '","' || 
  LMTAMT                          || '","' || 
  PRODUCT                         || '","' ||         
  Con_Amt      || '","' ||   
  Con_Freq     || '","' || 
  ARR_AMT                     || '","' ||             
  XF_ACCT                     || '"')  AS FULL_DATA         
FROM TMP_PAT2 b 
-- 
UNION ALL 
-- 
SELECT 
  P_ID       AS ID1     
, CUST_ORD          AS ID2 
, 0 AS ID3     
, TO_CHAR('"' || REC_ID        || '","' || 
  CUST_NUM                 || '","' ||             
  TITLE                           || '","' ||               
  FIRST_NAME                      || '","' ||           
  SURNAME                         || '"') AS FULL_DATA             
FROM TMP_PAT3 c 
-- 
UNION ALL 
-- 
SELECT 
  P_ID    AS ID1   
, POL_ORD         AS ID2 
, 0 AS ID3   
, TO_CHAR('"' || REC_ID        || '","' || 
  LI_RUTE_NUMBER)              || '"' AS FULL_DATA 
FROM TMP_PAT4 d 
-- 
UNION ALL 
-- 
SELECT 
  P_ID AS ID1 
, PT_ORD     AS ID2 
, TO_NUMBER (TO_CHAR(START_DTE_TIM, 'yyyymmddhh24miss')) AS ID3 
, TO_CHAR('"' || RECORD_ID             || '","' || 
  TO_CHAR(START_DTE_TIM, 'DD/MM/YYYY') || '","'  || 
  SUBSTR(FULL_TEXT,1,2999)) || 
  TO_CHAR(SUBSTR(FULL_TEXT,3000,4000)) || 
  TO_CHAR(SUBSTR(FULL_TEXT,7001,3999) || '"') AS FULL_DATA 
FROM TMP_PAT5 e   
) 
ORDER BY ID1,ID2,ID3; 



Any suggesion to improve query 1 with CLOB or any auto mechanism to replace to_CLOB instead of to_char

Thanks
Re: Long Running SQL- CLOB vs TO_CHAR [message #289386 is a reply to message #289383] Fri, 21 December 2007 03:07 Go to previous message
Michel Cadot
Messages: 64140
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Anyway you can't have more than 4000 bytes in string concatenation:
SQL> select lpad('X',4000,'X') from dual;
LPAD('X',4000,'X')
--------------------------------------------------------------------------------
XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX
XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX
XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX
[...]
XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX
XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX
XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX

1 row selected.

SQL> select lpad('X',4000,'X')||'Y' from dual;
select lpad('X',4000,'X')||'Y' from dual
                                    *
ERROR at line 1:
ORA-01489: result of string concatenation is too long


SQL> select to_clob(lpad('X',4000,'X')||'Y') from dual;
ERROR:
ORA-01489: result of string concatenation is too long



no rows selected

You can do it if you first convert the string into CLOB. As you saw it TO_CLOB is not cheap but this is the price to pay to get long strings.
SQL> select to_clob(lpad('X',4000,'X'))||'Y' from dual;
TO_CLOB(LPAD('X',4000,'X'))||'Y'
--------------------------------------------------------------------------------
XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX
XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX
XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX
[...]
XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX
XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX
XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX
Y

1 row selected.


Regards
Michel

[Updated on: Fri, 21 December 2007 03:17]

Report message to a moderator

Previous Topic: suggestions required to change the query to improve its performance
Next Topic: CLOB usage and issue with TEMP tablespace
Goto Forum:
  


Current Time: Fri Dec 09 04:07:22 CST 2016

Total time taken to generate the page: 0.16676 seconds