Toad World® Forums

Tables with XMLTYPE (CLOB) take a VERY long time to query in SB and Editor


#1

It took over 9 minutes to query a table with 73 rows, from SB and Editor, even when I use Execute as script (which seemed to have even greater performance problems). SQL*Plus returns in seconds with a truncated version of the XMLTYPE data. Is there any way to do a quick query of the table from TOAD (other than the obvious of using the Editor and specifying all columns except the XMLTYPE column)? If I want the full XML data, I’d click on the XMLTYPE data cell and incur the cost of querying/converting the full XML data. I know there were issues with NOT converting XML data when querying. Choosing to not to display the XMLTYPE column in the SB data grid doesn’t help. Any other suggestions?


#2

I can speak to the script output…if you run queries via F5 (not recommended), instead of truncating, we pull all of the information back. In addition, we pull back all of the rows so the final output of data can be VERY large.


#3

In the main options window (under SB, Data Types) you have an option to not select BLOB/CLOB, but you said you didn’t want that.

In the main options windows (under Data Grids, Data) you can uncheck “Preview CLOB and LONG data”. That should help, but then you will just see (HUGECLOB)
in the cells. After that you could double-click one to see it in the popup editor.

I could maybe add an option to use to DBMS_LOB.substr when to show only part of the xmldata in the SB, but that would have to wait for after 12.1, as we are
trying to wind down this beta cycle already.

-John

From: droeschley_109 [mailto:bounce-droeschley_109@toadworld.com]

Sent: Wednesday, August 14, 2013 12:31 PM

To: toadoraclebeta@toadworld.com

Subject: [Toad for Oracle - Beta Discussion Forum] Tables with XMLTYPE (CLOB) take a VERY long time to query in SB and Editor

Tables with XMLTYPE (CLOB) take
a VERY long time to query in SB and Editor

Thread created by droeschley_109

It took over 9 minutes to query a table with 73 rows, from SB and Editor, even when I use Execute as script (which seemed to have even greater performance problems). SQL*Plus returns in seconds
with a truncated version of the XMLTYPE data. Is there any way to do a quick query of the table from TOAD (other than the obvious of using the Editor and specifying all columns except the XMLTYPE column)? If I want the full XML data, I’d click on the XMLTYPE
data cell and incur the cost of querying/converting the full XML data. I know there were issues with NOT converting XML data when querying. Choosing to not to display the XMLTYPE column in the SB data grid doesn’t help. Any other suggestions?

To reply, please reply-all to this email.

Stop receiving emails on this subject.

Or
Unsubscribe from Toad for Oracle - Beta
notifications altogether.

Toad for Oracle - Beta Discussion Forum

Flag
this post as spam/abuse.


#4

table 1 has XMLTYPE BLOB column - 3 rows

table 2 has XMLTYPE CLOB column - 75 rows

table 3 has XMLTYPE CLOB column - 2 rows

When I uncheck “Preview CLOB and LONG data”

table 1 displays (HUGECLOB) - and returns immediately

table 2 displays full XML data and still takes close to 10 minutes (75 rows)

table 3 displays full XML data and takes around 5 seconds

table 1 is using .GetClobVal() in the SELECT list

tables 2 and 3 are not

under Options -> Data Types - Native Oracle Types I unchecked BLOB, CLOB, XMLTYPE

I get the same results


#5

Sounds like Table1 has the XMLTYPE column stored as binary, and the other two do not. That would explain why Toad only casts that table’s XMLTYPE column.

If you manually put GetClobVal in the query for the for the 2nd table, does it load faster that way?

The Options -> Data Types settings you refer to only impact Toad in one place, and that is the create/alter table dialog.


#6

Manually querying using GetClobVal returns immediately for table2 and table3 and displays (HugeClob) as the value (“Preview CLOB…” unchecked)

I tried the Options -> Data Types because I didn’t find the option you mentioned

“main options window (under SB, Data Types) you have an option to not select BLOB/CLOB”


#7

Also, if I start a query on XML data that takes a long time and try to stop it, from SB or Editor, TOAD goes into a funky state where it is not doing anything on the database side, nor is it doing anything on my PC according to Task Manager, but TOAD is unresponsive. I’ve waited a full 10 minutes or more, but no response so I have to kill the process.