Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

help on to_clob function

Posted on 2014-12-31
5
Medium Priority
?
276 Views
Last Modified: 2015-01-03
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
Comment
Question by:sam_2012
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
5 Comments
 
LVL 29

Expert Comment

by:MikeOM_DBA
ID: 40525775
You can remove the "TO_CLOB" and just use 'N/A'
;)
0
 
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 40526079
>>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
 

Author Comment

by:sam_2012
ID: 40526437
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
 
LVL 77

Accepted Solution

by:
slightwv (䄆 Netminder) earned 2000 total points
ID: 40526796
>>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
 

Author Closing Comment

by:sam_2012
ID: 40529881
awesome.
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction A previously published article on Experts Exchange ("Joins in Oracle", http://www.experts-exchange.com/Database/Oracle/A_8249-Joins-in-Oracle.html) makes a statement about "Oracle proprietary" joins and mixes the join syntax with gen…
Have you ever had to make fundamental changes to a table in Oracle, but haven't been able to get any downtime?  I'm talking things like: * Dropping columns * Shrinking allocated space * Removing chained blocks and restoring the PCTFREE * Re-or…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
This video shows how to recover a database from a user managed backup

722 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question