OraFAQ Forum: Performance Tuning » Long Running SQL- CLOB vs TO

Home » RDBMS Server » Performance Tuning » Long Running SQL- CLOB vs TO_CHAR

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Long Running SQL- CLOB vs TO_CHAR [message #289383]

Fri, 21 December 2007 02:52

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

Report message to a moderator

Re: Long Running SQL- CLOB vs TO_CHAR [message #289386 is a reply to message #289383]

Fri, 21 December 2007 03:07

Michel Cadot
Messages: 68665
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:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Fri Jun 28 06:03:02 CDT 2024