• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 295
  • Last Modified:

help on to_clob function

Hi ,

I have an table called comments which has an column called comment . In the query , I need to check whether any comment is present for an product , if present show the comment else display 'N/A'. Below is the table structure and the query written. Need an alternative to the usage of to_clob function in the query

Table Structure
Product Table:

Product_id number ,
place_id number,
customer_id number,
prod_desc varchar2(400)

Comments Table
Comment_id number,
comments clob,
product_id number,
customer1_id number,
customer2_id number,
customer3_id number

Data


Product table :
Product_id     place_id     customer_id  prod_desc
1                         10              1                      abc
2                          12             2                      cvb
3                           13            3                        sdsd
 
Comments:
Comment_id    comments  product_id  customer1_id customer2_id  customer3_id
1                           asdasd           1                      1               null                          null
2                            asdasdas         3                      null             2                           null



Select
p.product_id,
p.prod_desc,
nvl2(com.comment_id,com.comments,to_clob('N/A') comments
,comment_id,

from product p left outer join comments com on (com.product_id=p.product_id and p.customer_id in (com.customer1_id, com.customer2_id, com.customer3_id)

Output :
product_id                  prod_desc                                       comments    comment_id
1                                        abc                                                  asdasd             1
2                                            cvb                                                N/A                  NULL
3                                          sdsd                                                 N/A                 NULL

The issue is the query takes an long time because of the to_clob('N/A') function used. Is there an alternative , with out using the to_clob function , so as to improve the performance of the query.
0
sam_2012
Asked:
sam_2012
  • 2
  • 2
1 Solution
 
MikeOM_DBACommented:
You can remove the "TO_CLOB" and just use 'N/A'
;)
0
 
slightwv (䄆 Netminder) Commented:
>>The issue is the query takes an long time because of the to_clob('N/A') function used

What makes you think to_clob is the reason?

Please post the execution plan.
0
 
sam_2012Author Commented:
I checked , the execution plan says , all the indexes are used , but when I remove the to_clob ('N/A') code  and display only comments , it takes only 5 milli secs. But when I include the to_clob('N/A') code it takes around 10 secs which is not meeting the sla.

Also , If i remove to_clob('N/A') and add only 'N/A' getting an error like below
got clob when expected error.
0
 
slightwv (䄆 Netminder) Commented:
>>got clob when expected error.

What is your exact version of Oracle?

I just set up a test case using your samples and after correcting the syntax errors with the select, it runs just fine on my 11.2.0.3.

--drop table product purge;
create table product(
Product_id number ,
place_id number,
customer_id number,
prod_desc varchar2(400)
);

--drop table comments purge;
create table comments(
Comment_id number,
comments clob,
product_id number,
customer1_id number,
customer2_id number,
customer3_id number
);


insert into product values(1,10,1,'abc');
insert into product values(2,12,2,'cvb');
insert into product values(3,13,3,'sdsd');
 
insert into comments values(1,'asdasd',1,1,null,null);
insert into comments values(2,'asdasdas',3,null,2,null);
commit;


Select
p.product_id,
p.prod_desc,
nvl2(com.comment_id,com.comments,'N/A') comments
,comment_id
from product p left outer join comments com on com.product_id=p.product_id and p.customer_id in (com.customer1_id, com.customer2_id, com.customer3_id)
/

Open in new window

0
 
sam_2012Author Commented:
awesome.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now