Discussions
Categories
- 196.8K All Categories
- 2.2K Data
- 235 Big Data Appliance
- 1.9K Data Science
- 449.9K Databases
- 221.6K General Database Discussions
- 3.8K Java and JavaScript in the Database
- 31 Multilingual Engine
- 549 MySQL Community Space
- 478 NoSQL Database
- 7.9K Oracle Database Express Edition (XE)
- 3K ORDS, SODA & JSON in the Database
- 532 SQLcl
- 4K SQL Developer Data Modeler
- 186.9K SQL & PL/SQL
- 21.3K SQL Developer
- 295.5K Development
- 17 Developer Projects
- 138 Programming Languages
- 292.1K Development Tools
- 104 DevOps
- 3.1K QA/Testing
- 645.9K Java
- 28 Java Learning Subscription
- 37K Database Connectivity
- 154 Java Community Process
- 105 Java 25
- 22.1K Java APIs
- 138.1K Java Development Tools
- 165.3K Java EE (Java Enterprise Edition)
- 17 Java Essentials
- 158 Java 8 Questions
- 85.9K Java Programming
- 79 Java Puzzle Ball
- 65.1K New To Java
- 1.7K Training / Learning / Certification
- 13.8K Java HotSpot Virtual Machine
- 94.2K Java SE
- 13.8K Java Security
- 203 Java User Groups
- 24 JavaScript - Nashorn
- Programs
- 401 LiveLabs
- 37 Workshops
- 10.2K Software
- 6.7K Berkeley DB Family
- 3.5K JHeadstart
- 5.6K Other Languages
- 2.3K Chinese
- 171 Deutsche Oracle Community
- 1.1K Español
- 1.9K Japanese
- 230 Portuguese
loop cursor in plsql

pjsiong-JavaNet
Member Posts: 398 Green Ribbon
Hi,
I create a procedure which is something like below:
I want to run this procedure against some huge table, if the memory usage keep increase, I am afraid that it will exhaust the memory.
Is there any way to improve this?
Thanks
Vincent
Edited by: pj**** on 15-Sep-2011 00:42
I create a procedure which is something like below:
create or replace procedure INSERT_DATA is cursor cursor1(vfield varchar2) is ( select field1,field2,field3,field4 from tableb z, xmltable( .....) x where z.field1=vfield ); begin for c in (select fieldname from tablea) loop for t in cursor1(c.fieldname) loop insert into tablec (field1,field2,field3,field4) values (t.field1,t.field2,t.field3,t.field4); end loop; commit; end loop; end INSERT_DATA;when I run this procedure, I found that the memory usage of Oracle process in the server will keep increasing,
I want to run this procedure against some huge table, if the memory usage keep increase, I am afraid that it will exhaust the memory.
Is there any way to improve this?
Thanks
Vincent
Edited by: pj**** on 15-Sep-2011 00:42
Answers
-
Dont use Cursor and Loop just do it in SQL
insert into tablec (field1,field2,field3,field4) select field1,field2,field3,field4 from tableb z, tablea a, xmltable( .....) x where z.field1=a.fieldname
-
Hi, Karthick
Thanks for the reply but I tried but still the same ...any suggestion?
Thanks
Vincent -
Why are you using a loop in a loop instead of a single SQL statement?
Why don't you use a join? -
Hi,
If I recall correctly from your posts in the XML DB forum, you're using XML data stored as CLOB.
That's the worst-case scenario for large volumes of data.
Could you post the real query and an explain plan? (with up-to-date statistics) -
Here's an explanation regarding a similar construction (loop in loop):
http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:1288401763279#tom59921197472382
Try to join all tables rightaway., use an APPEND hint to speed things up even more.
Something like:insert /*+ APPEND */ into ... select ... from ... where ...
-
Hi, Odie
Thanks for remember me, the actual query is as belowselect testname,StepName,Status,Comp,Data,TESTSTATUS,SingleTest from cust_vw_test_log_clob z, xmltable( ' declare function local:getChildren($p as element()) as element()* { for $i in $p return ( if ($i/Prop[5]/@Name="Measurement") then ( for $t in $i/Prop[@Name="Measurement"]/Value return element r { element StepName1 {$t/Prop/@Name} ,element Status1 {$t/Prop/Prop[@Name="Status"]/Value/text()} ,element Comp1 {$t/Prop/Prop[@Name="Comp"]/Value/text()} ,element Data1 {$t/Prop/Prop[@Name="Data"]/Value/text()} ,element Testname1 {$i/Prop[@Name="TS"]/Prop[@Name="StepName"]/Value/text()} ,element TEST_STATUS {$i/Prop[@Name="Status"]/Value/text()} } ) else ( for $t in $i return element r { element SingleTest {$t/Prop[@Name="TS"]/Prop[@Name="StepName"]/Value/text()} ,element TEST_STATUS {$t/Prop[@Name="Status"]/Value/text()} ,element Data1 {$t/Prop[@Name="Numeric"]/Value/text()} } , for $a in $i/Prop[@Name="TS"]/Prop[@Name="SequenceCall"]/Prop[@Name="ResultList"]/Value return local:getChildren($a/Prop[@Type="TEResult"]) ) ) }; (: :) local:getChildren(/Reports/Report/Prop[@Type="TEResult"])' passing xmltype(z.test_log_text) columns StepName varchar2(500) path 'StepName1/@Name', Status varchar2(500) path 'Status1', Comp varchar2(500) path 'Comp1', Data varchar2(500) path 'Data1', TestName varchar2(100) path 'Testname1', TestStatus varchar2(100) path 'TEST_STATUS', SingleTest varchar2(100) path 'SingleTest' ) x where z.ticket=vTicket
vincent -
the actual query is as belowOh, that one...
It's using a recursive XQuery function, no wonder you hit performance and memory issues on big documents.
Furthermore, if you're still on a 10.2 database, the XQuery engine used in this case is Java-based.
Any chance you could at least use an XMLType column for XML storage? -
Hi, Odie
sign...it's a big document so I have to zip it and keep in a blob field, otherwise the db grow 32GB per month.
For the query, I actually unzip the blob field into clob in a view. will it be help if I unzip and cast it into xmltype?
Thanks
Vincent -
For the query, I actually unzip the blob field into clob in a view.Using UTL_COMPRESS?will it be help if I unzip and cast it into xmltype?I'm afraid not.
The data should at least be materialized (physically stored) as an XMLType column at some point, but even then I guess you won't see that much improvement if the underlying storage model is still CLOB.
Do you have an XML schema describing the document?
Another solution that sometimes performs better than XQuery is XSL transformation.
See this example which is close to your situation : {message:id=9753074}
Edited by: odie_63 on 15 sept. 2011 15:08 -
Yes...using utl_compressDo you mean XSL? I have the xsl which can be downloaded from http://124.82.216.245:81/test.xslDo you have an XML schema describing the document?
what can I do with the xsl?
Thanks
Vincent
This discussion has been closed.