# Tables with XMLTYPE (CLOB) take a VERY long time to query in SB and Editor **URL:** \ **Category:** Toad for Oracle - Beta\ **Created:** [August 14, 2013, 10:30am UTC](https://forums.toadworld.com/t/tables-with-xmltype-clob-take-a-very-long-time-to-query-in-sb-and-editor/26290 "2013-08-14T10:30:59Z")\ **Posts on this page:** 7\ **Page:** 1
**Author:** ![droeschley\_109](https://avatars.discourse-cdn.com/v4/letter/d/9f8e36/32.png) [@droeschley\_109](https://forums.toadworld.com/u/droeschley_109)\ **Post date:** [August 14, 2013, 10:30am UTC](https://forums.toadworld.com/t/tables-with-xmltype-clob-take-a-very-long-time-to-query-in-sb-and-editor/26290/1 "2013-08-14T10:30:59Z")
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? ---
**Author:** ![GTDG](https://sea1.discourse-cdn.com/quest/user_avatar/forums.toadworld.com/gtdg/32/140_2.png) [@GTDG](https://forums.toadworld.com/u/GTDG)\ **Post date:** [August 14, 2013, 10:33am UTC](https://forums.toadworld.com/t/tables-with-xmltype-clob-take-a-very-long-time-to-query-in-sb-and-editor/26290/2 "2013-08-14T10:33:54Z")
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. ---
**Author:** ![JohnDorlon](https://sea1.discourse-cdn.com/quest/user_avatar/forums.toadworld.com/johndorlon/32/9997_2.png) [@JohnDorlon](https://forums.toadworld.com/u/JohnDorlon)\ **Post date:** [August 14, 2013, 10:47am UTC](https://forums.toadworld.com/t/tables-with-xmltype-clob-take-a-very-long-time-to-query-in-sb-and-editor/26290/3 "2013-08-14T10:47:12Z")
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](mailto:bounce-droeschley_109@toadworld.com)] **Sent:** Wednesday, August 14, 2013 12:31 PM **To:** [toadoraclebeta@toadworld.com](mailto: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](http://gyk.r.mailjet.com/redirect/mddrtj6lyfohrgbzh6k8r5/www.toadworld.com/products/toad-for-oracle/toad_for_oracle_beta_program/f/86/t/20113.aspx)** 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](http://gyk.r.mailjet.com/redirect/1r31hy8trzlxfpfq50blpl/www.toadworld.com/products/toad-for-oracle/toad_for_oracle_beta_program/f/86/t/20113.aspx?subscribe=false) on this subject. Or [ Unsubscribe from Toad for Oracle - Beta](http://gyk.r.mailjet.com/redirect/gm3nhocqfz9cy5bgjpq2fm/www.toadworld.com/products/toad-for-oracle/toad_for_oracle_beta_program/f/86.aspx?subscribe=false) notifications altogether. Toad for Oracle - Beta Discussion Forum [Flag](http://gyk.r.mailjet.com/redirect/y79zhjqpd0drtpmbjpqkwh/www.toadworld.com/products/toad-for-oracle/toad_for_oracle_beta_program/f/86/t/20113.aspx?AbuseContentId=6c648aaf-ec61-4fe1-8e27-ca01722a953e&AbuseContentTypeId=46448885-d0e6-4133-bbfb-f0cd7b0fd6f7&AbuseFlag=true) this post as spam/abuse. ![](http://gyk.r.mailjet.com/5de6f3cf/4sqFjh.gif) ---
**Author:** ![droeschley\_109](https://avatars.discourse-cdn.com/v4/letter/d/9f8e36/32.png) [@droeschley\_109](https://forums.toadworld.com/u/droeschley_109)\ **Post date:** [August 14, 2013, 1:18pm UTC](https://forums.toadworld.com/t/tables-with-xmltype-clob-take-a-very-long-time-to-query-in-sb-and-editor/26290/4 "2013-08-14T13:18:56Z")
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 ---
**Author:** ![JohnDorlon](https://sea1.discourse-cdn.com/quest/user_avatar/forums.toadworld.com/johndorlon/32/9997_2.png) [@JohnDorlon](https://forums.toadworld.com/u/JohnDorlon)\ **Post date:** [August 14, 2013, 1:28pm UTC](https://forums.toadworld.com/t/tables-with-xmltype-clob-take-a-very-long-time-to-query-in-sb-and-editor/26290/5 "2013-08-14T13:28:58Z")
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. ---
**Author:** ![droeschley\_109](https://avatars.discourse-cdn.com/v4/letter/d/9f8e36/32.png) [@droeschley\_109](https://forums.toadworld.com/u/droeschley_109)\ **Post date:** [August 14, 2013, 4:14pm UTC](https://forums.toadworld.com/t/tables-with-xmltype-clob-take-a-very-long-time-to-query-in-sb-and-editor/26290/6 "2013-08-14T16:14:57Z")
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” ---
**Author:** ![droeschley\_109](https://avatars.discourse-cdn.com/v4/letter/d/9f8e36/32.png) [@droeschley\_109](https://forums.toadworld.com/u/droeschley_109)\ **Post date:** [August 14, 2013, 4:24pm UTC](https://forums.toadworld.com/t/tables-with-xmltype-clob-take-a-very-long-time-to-query-in-sb-and-editor/26290/7 "2013-08-14T16:24:44Z")
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.