How to use BULK COLLECT with limitin clause and FOR ALL while using update statement

WE HAVE 15K records are there in GT_ADD_ISBNS.I am using BULK COLLECT with limit_in clause but its not iterating for all records that exists in
GT_ADD_ISBNS.its iterating only for 1000.

one more thing is after retreiving V_NODE_IDD value,how to uSE FORALL cluase to update to GT_ADD_ISBNS table.

DECLARE


V_NODE_IDD  CLOB;


 
limit_in  PLS_INTEGER :=1000;
 
counter1 integer:=0;

TYPE PRODUCT_ISBN IS TABLE OF GT_ADD_ISBNS.STRING_VALUE%TYPE;
l_products PRODUCT_ISBN;

CURSOR products_cur IS

SELECT ANP.STRING_VALUE
FROM 
GT_ADD_ISBNS ANP;

BEGIN

OPEN products_cur;
LOOP
FETCH products_cur BULK COLLECT INTO l_products LIMIT limit_in;
EXIT WHEN l_products.COUNT =0;

   
   FOR indx IN 1 .. l_products.COUNT
        LOOP
 
         
           counter1:=counter1+1;
           
           DBMS_OUTPUT.PUT_LINE('isbn:'||l_products(indx));
           
     
    SELECT  rtrim(xmlagg(xmlelement(e,CHILD_NODE_ID,',').extract('//text()') order by CHILD_NODE_ID).GetClobVal(),',') into V_NODE_IDD
                  FROM TEMP_CHILD_ASSOC 
                  JOIN TEMP_NODE n on CHILD_NODE_ID = n.id
    WHERE n.TYPE_QNAME_ID  =623 
                  START WITH PARENT_NODE_ID IN(
                 SELECT CHILD_NODE_ID FROM TEMP_CHILD_ASSOC WHERE CHILD_NODE_NAME =l_products(indx)) 
                      CONNECT BY PRIOR CHILD_NODE_ID = PARENT_NODE_ID;
 
 FORALL indx IN 1 .. l_products.COUNT
    update GT_ADD_ISBNS set nodes=V_NODE_IDD where isbn=l_products(indx);
  
  
   END LOOP;
   DBMS_OUTPUT.PUT_LINE('counter1:'||counter1);
   END LOOP;
   
   
     
     COMMIT;
END;

Open in new window

chaituu chaituAsked:
Who is Participating?
 
slightwv (䄆 Netminder)Connect With a Mentor Commented:
I have NO IDEA if this is accurate or not but it at least produces your expected results.  I borrowed logic from one of your previous questions to try and figure out your requirements.

If it isn't right, hopefully you can take the concept I started with and get it working.

merge into gt_add_isbns g
using (
	select isbn, case when isbn=main_isbn and asset_pdf is null then 'Category not found' when asset_pdf is null then 'Product not found' end error_message,
		asset_pdf
	from (
		select isbn, main_isbn,
			listagg(case when asset_count>0 and node_type='asset' and prod_type='name' and string_value like '%pdf%' and string_value not like '%pod%' then string_value end,',') within group(order by string_value) asset_pdf
		from (
			select q.*,
				sum(case when node_type='asset' and prod_type='categories' and string_value in ('uPDF','Entire PDF') then 1 end ) over(partition by main_parent) asset_count,
				max(case when prod_type='product' then string_value end ) over(partition by main_parent) main_isbn
			from (
				select connect_by_root parent_id main_parent,
					level, parent_id, child_id, n.type_qname node_type, p.type_qname prod_type, string_value
				from temp_child_assoc c
					left join temp_node n on c.child_id=n.id
					left join temp_node_properties p on c.child_id=p.node_id
				connect by prior child_id = parent_id
				start with n.type_qname='product'
			) q
		) t right outer join gt_add_isbns g on g.isbn=t.main_isbn
		group by isbn, main_isbn
	)
) t on (g.isbn=t.isbn)
when matched then update set g.error_message=t.error_message, g.file_names=t.asset_pdf
/

Open in new window

0
 
Geert GOracle dbaCommented:
2 loops, nested with the same indx variable ?
0
 
chaituu chaituAuthor Commented:
what was the problem and how to rewrite it?
0
Cloud Class® Course: Ruby Fundamentals

This course will introduce you to Ruby, as well as teach you about classes, methods, variables, data structures, loops, enumerable methods, and finishing touches.

 
Geert GOracle dbaCommented:
read the development guide

https://docs.oracle.com/en/database/oracle/oracle-database/12.2/lnpls/plsql-control-statements.html#GUID-7E1AC17C-83EB-422C-98F4-2F13D9B694F4

you are using the indx loop variable here:
 FOR indx IN 1 .. l_products.COUNT
        LOOP

Open in new window


and then inside that loop, you are using the same indx variable for the FORALL
FORALL indx IN 1 .. l_products.COUNT
    update GT_ADD_ISBNS set nodes=V_NODE_IDD where isbn=l_products(indx);

Open in new window


each loop should must have it's own looping variable

use a different looping variable in the forall

FORALL indx2 IN 1 .. l_products.COUNT
    update GT_ADD_ISBNS set nodes=V_NODE_IDD where isbn=l_products(indx2);

Open in new window

0
 
slightwv (䄆 Netminder) Commented:
From all your previous questions on this same set of code, I'll give you the same advice:
You shouldn't need loops and pl/sql tables.  You should be able to do it in a single statement.  That is almost always the fastest method to do things.

No, I can't write it for you.  Even after all the previous questions and sample data there, I don't have the time to learn your requirements in enough detail to provide copy/paste code.
0
 
Mark GeerlingsDatabase AdministratorCommented:
I agree with slightwv.  For just 15,000 records, you shouldn't need complex PL\SQL logic.  Try to get the job done with simple SQL statements something like this:
update GT_ADD_ISBNS
set nodes=(select [whatever you need to select))
[optional ] where [if not all records in this table need to be updated]
0
 
chaituu chaituAuthor Commented:
Hi,

As you all suggested I have written single update/merge statement[https://www.experts-exchange.com/questions/29085637/how-to-update-the-table-where-results-are-coming-from-select-statement.html] but that one is also taking lot of time.

As per analysis,mainly below select statement is taking lot of time  for some of the isbn's  in TEMP_CHILD_ASSOC ,in some cases parent-child hierarchical records are going upto 2500+ records.Please note that these tables were generated by the CMS tool and dont have permission to create indexes on these tables.

So in this scenario ,As this particular query is taking lot of time so  my intention is first fetch the NodeIDS w.r.t ISBN and update in gt_add_isbns with comma separeted values(1,2,4,5,6.. this may go upto 3000)  and after that I will write one final big update/merge statement without calling this problematic query again that has written in previous link appended above.

 update gt_add_isbns gt1 set gt1.NODES=
 
 --this statement is taking lot of time-each ISBN can have 1-3000 parent-child hierarchical records.
  (SELECT  rtrim(xmlagg(xmlelement(e,CHILD_ID,',').extract('//text()') order by CHILD_ID).GetClobVal(),',')
                  FROM TEMP_CHILD_ASSOC
                  JOIN TEMP_NODE n on CHILD_ID = n.id
    WHERE n.TYPE_QNAME_ID  =12
                  START WITH PARENT_ID IN(
                 SELECT CHILD_ID FROM TEMP_CHILD_ASSOC WHERE CHILD_NODE_NAME =GT1.isbn)
                      CONNECT BY PRIOR CHILD_ID = PARENT_ID)
WHERE
 gt1.ISBN NOT IN(select n1.isbn from ISBN_PRD_NOT_FOUND n1);
 
 I hope you understood my issue and thats why created this case.
0
 
slightwv (䄆 Netminder) Commented:
>> have written single update/merge statement

From a quick scan of the previous question, what I can see, that update needs help:
You create a comma separated list.  I'm not sure I see the need for it.  Going from memory of previous questions, you were using it for an IN list in dynamic SQL.  I don't think it is necessary and if you can have 3000 entries, that won't work.

I'm also not sure about all the TEMP tables.  I see the need for the Global Temp Table that holds the ISBNs.  When I see tables called TEMP, I get concerned.
0
 
Mark GeerlingsDatabase AdministratorCommented:
I was also concerned about this: "update gt_add_isbns with comma separated values (1,2,4,5,6.. this may go upto 3000)".  I wondered what possible value there could be in this result.  It seems like you have a lot of complexity here.

Are you sure that you are using the simplest approach to the business problem?
0
 
chaituu chaituAuthor Commented:
<code>
SELECT (SELECT  rtrim(xmlagg(xmlelement(e,CHILD_ID,',').extract('//text()') order by CHILD_ID).GetClobVal(),',')
                  FROM TEMP_CHILD_ASSOC
                  JOIN TEMP_NODE n on CHILD_ID = n.id
    WHERE n.TYPE_QNAME_ID  =12
                  START WITH PARENT_ID IN(
                 SELECT CHILD_ID FROM TEMP_CHILD_ASSOC WHERE CHILD_NODE_NAME =GT.isbn)
                      CONNECT BY PRIOR CHILD_ID = PARENT_ID)
                                
                                FROM GT_ADD_ISBNS GT
WHERE
 GT.ISBN='1234567889123'.
 
</code>

 this  single isbn query is taking 0.35 minutes.If I keep all isbns from GT_ADD_ISBNS then its taking hours.
 
when I looked at the explain plan of the above query below statement is taking lot of time .what are the bind variables[:B1,:B2] that are showing in the explain plan.
 
cardinality its showing 4685010 and cost  and cpu_cost are 10629 ,841162104 respectively.At this place only query is taking lot of time. Please find the attached entire explain plan.Do I need to change the above query?


 EXISTS (SELECT 0 FROM TEMP_CHILD_ASSOC TEMP_CHILD_ASSOC WHERE CHILD_NODE_ID=:B1 AND CHILD_NODE_NAME=:B2 AND TYPE_QNAME_ID=37)
                                        
 INDEX
 SAMPLE_INDEX        4685010        10629        841162104       
 
 
 CREATE UNIQUE INDEX "XXXXX"."SAMPLE_INDEX" ON "XXXXX"."TEMP_CHILD_ASSOC" ("PARENT_ID", "CHILD_NODE_NAME", "TYPE_QNAME_ID", "CHILD_ID")
22.html
0
 
slightwv (䄆 Netminder) Commented:
As we have been saying throughout all the series of questions: Don't create a CSV.

That said:  Assuming that when you say "all ISBNs" you mean that you remove the WHERE clause: Do you need one massive CSV for ALL ISBNs?

If you can create a really simple example that shows us at a high level what you are trying to accomplish (with some sample data and expected results fro that data), we can probably help you with some sample SQL.

From your previous questions when you provided sample data it was way too involved for us to take the time to learn all the requirements.
0
 
chaituu chaituAuthor Commented:
We have products(20K) received from the client in the form of excel doc.we need to find whether each ISBN's are exists in the main tables or not and accordingly update against each ISBN.Initially ,dumped these product numbers into the normal temp table(GT_ADD_ISBNS);

From this temp table ,we are checking againt main tables given below.each ISBN has to fullfill below 3-steps.

1)to see whether product is exists or not by looking at TEMP_CHILD_ASSOC and TEMP_NODE.
2)to see whether product inside the assets are having the 'category' or not by looking into TEMP_NODE_PROPERTIES table.in the real scenario- 1 product can go upto 3000 assets.
3)finally we need to find the assets whose file names starts with '%pdf%'.

to explain about main tables ,products are stored in TEMP_CHILD_ASSOC and check whether it is product it will be defined in TEMP_NODE.1 product can go upto 3000 assets.

create table GT_ADD_ISBNS(ISBN VARCHAR2(15),ERROR_MESAGE VARCHAR2(100),FILE_NAMES VARCHAR2(100));
I am taking 3 different records;

Insert into GT_ADD_ISBNS (ISBN,CATEGORY11_FILENAME,CATEGORY11_ERROR_MESSAGE) values ('9780748700585',null,null);
Insert into GT_ADD_ISBNS (ISBN,CATEGORY11_FILENAME,CATEGORY11_ERROR_MESSAGE) values ('9780748700589',null,null);
Insert into GT_ADD_ISBNS (ISBN,CATEGORY11_FILENAME,CATEGORY11_ERROR_MESSAGE) values ('9780748700590',null,null);

finally GT_ADD_ISBNS table should be updated as

'9780748700585',null,9781424005468_p01_hipdf.zip,9781424005468_p01_covpdf.zip
'9780748700589','Category not Found',null
'9780748700590','Product not Found',null

sample data has been provided in the main tables below.
--these records are related to ISBN[9780748700585]
CREATE TABLE "TEMP_CHILD_ASSOC" ("PARENT_ID" VARCHAR2(20), "CHILD_ID" VARCHAR2(20), "NAME" VARCHAR2(2000))
Insert into TEMP_CHILD_ASSOC (PARENT_ID,CHILD_ID,NAME) values ('1141','1143','9780748700585');
Insert into TEMP_CHILD_ASSOC (PARENT_ID,CHILD_ID,NAME) values ('1143','1202','testfolder1');
Insert into TEMP_CHILD_ASSOC (PARENT_ID,CHILD_ID,NAME) values ('1143','1203','testfolder2');
Insert into TEMP_CHILD_ASSOC (PARENT_ID,CHILD_ID,NAME) values ('1202','1223','9781424005468_p01_covpdf.zip');
Insert into TEMP_CHILD_ASSOC (PARENT_ID,CHILD_ID,NAME) values ('1202','1226','9781424005468_p01_hipdf.zip');
Insert into TEMP_CHILD_ASSOC (PARENT_ID,CHILD_ID,NAME) values ('1202','1229','9781424005468_p01_pod.zip');
Insert into TEMP_CHILD_ASSOC (PARENT_ID,CHILD_ID,NAME) values ('1202','1232','9781424005468_p01_pod_chaitu.zip');

--these records are related to ISBN[9780748700589]
Insert into TEMP_CHILD_ASSOC (PARENT_ID,CHILD_ID,NAME) values ('1541','1543','9780748700589');
Insert into TEMP_CHILD_ASSOC (PARENT_ID,CHILD_ID,NAME) values ('1543','1544','xyz.zip');


 CREATE TABLE "TEMP_NODE" ("ID" VARCHAR2(20), "TYPE_QNAME" VARCHAR2(20))
REM INSERTING into TEMP_NODE
SET DEFINE OFF;
--these records are related to ISBN[9780748700585]
Insert into TEMP_NODE (ID,TYPE_QNAME) values ('1143','product');
Insert into TEMP_NODE (ID,TYPE_QNAME) values ('1202','folder');
Insert into TEMP_NODE (ID,TYPE_QNAME) values ('1203','folder');
Insert into TEMP_NODE (ID,TYPE_QNAME) values ('1223','asset');
Insert into TEMP_NODE (ID,TYPE_QNAME) values ('1226','asset');
Insert into TEMP_NODE (ID,TYPE_QNAME) values ('1229','asset');
Insert into TEMP_NODE (ID,TYPE_QNAME) values ('1232','asset');

--these records are related to ISBN[9780748700589]
Insert into TEMP_NODE (ID,TYPE_QNAME) values ('1543','product');
Insert into TEMP_NODE (ID,TYPE_QNAME) values ('1544','asset');


CREATE TABLE "TEMP_NODE_PROPERTIES" ("NODE_ID" VARCHAR2(20), "TYPE_QNAME" VARCHAR2(20), "STRING_VALUE" VARCHAR2(300))
REM INSERTING into TEMP_NODE_PROPERTIES
SET DEFINE OFF;

--these records are related to ISBN[9780748700585]
Insert into TEMP_NODE_PROPERTIES (NODE_ID,TYPE_QNAME,STRING_VALUE) values ('1143','product','9780748700585');
Insert into TEMP_NODE_PROPERTIES (NODE_ID,TYPE_QNAME,STRING_VALUE) values ('1223','categories','uPDF');
Insert into TEMP_NODE_PROPERTIES (NODE_ID,TYPE_QNAME,STRING_VALUE) values ('1223','categories','Entire PDF');
Insert into TEMP_NODE_PROPERTIES (NODE_ID,TYPE_QNAME,STRING_VALUE) values ('1223','name','9781424005468_p01_hipdf.zip');
Insert into TEMP_NODE_PROPERTIES (NODE_ID,TYPE_QNAME,STRING_VALUE) values ('1226','name','9781424005468_p01_covpdf.zip');
Insert into TEMP_NODE_PROPERTIES (NODE_ID,TYPE_QNAME,STRING_VALUE) values ('1226','categories','Entire PDF');
Insert into TEMP_NODE_PROPERTIES (NODE_ID,TYPE_QNAME,STRING_VALUE) values ('1226','categories','uPDF');

--these records are related to ISBN[9780748700589]
Insert into TEMP_NODE_PROPERTIES (NODE_ID,TYPE_QNAME,STRING_VALUE) values ('1543','product','9780748700589');
Insert into TEMP_NODE_PROPERTIES (NODE_ID,TYPE_QNAME,STRING_VALUE) values ('1544','name','xyz.zip');
Insert into TEMP_NODE_PROPERTIES (NODE_ID,TYPE_QNAME,STRING_VALUE) values ('1544','categories','Image');

Open in new window

0
 
slightwv (䄆 Netminder) Commented:
Are the TEMP tables ones that you have created for this process?
If so, are they part of what is slowing the processing down?
0
 
chaituu chaituAuthor Commented:
TEMP tables were created to present sample data.


This is query that i have written but in that below particular part is taking lot of time. if product is having multiple childs i.e. assets(lets say 3000 assets).on the whole is there any way to modify this query ?

--this particular part is taking lot of time
SELECT CHILD_ID FROM TEMP_CHILD_ASSOC START WITH PARENT_NODE_ID =GT_CHILD_NODE_ID
                                                CONNECT BY PRIOR CHILD_ID = PARENT_ID
                                                
								
--create type my_number_tab as table of varchar2(20);
SELECT ISBN ,(SELECT LISTAGG(ANP.STRING_VALUE, ',') WITHIN GROUP (ORDER BY ANP.STRING_VALUE)  
		FROM 
		TEMP_NODE_PROPERTIES ANP,
    TEMP_NODE_PROPERTIES ANPP
		WHERE 
       ANP.TYPE_QNAME='name'  AND 
       ANP.NODE_ID=ANPP.NODE_ID AND
       (
       ANP.STRING_VALUE LIKE '%pdf%'  AND 
       ANP.STRING_VALUE NOT LIKE '%updfch%')   AND
       ANP.NODE_ID MEMBER OF CATEGORIES)FILE_NAME FROM
(

-- to check whether product is having required categories or not['uPDF','categories']
SELECT ISBN,(SELECT  (CASE WHEN COUNT(DISTINCT AN.ID) > 0 THEN
             CAST(COLLECT(AN.ID) AS my_number_tab)
              END
              )
               FROM TEMP_CHILD_ASSOC aca JOIN ALF_NODE AN ON ACA.CHILD_ID=AN.ID 
                  JOIN TEMP_NODE_PROPERTIES ANP ON  ANP.NODE_ID=AN.ID 
                  JOIN TEMP_NODE_PROPERTIES ANPP ON ANPP.NODE_ID=ANP.NODE_ID 
                  WHERE
                    AN.TYPE_QNAME ='asset' AND 
                    ANP.TYPE_QNAME='categories' AND
                     (ANP.STRING_VALUE ='uPDF' AND
                       ANPP.STRING_VALUE ='Entire PDF')
                       ANP.NODE_ID IN(SELECT CHILD_ID 
							FROM TEMP_CHILD_ASSOC START WITH PARENT_NODE_ID =GT_CHILD_NODE_ID
								CONNECT BY PRIOR CHILD_ID = PARENT_ID)) CATEGORIES  
 FROM(
 SELECT ISBN, (SELECT ac.CHILD_ID 
                 FROM TEMP_CHILD_ASSOC ac
                 WHERE CHILD_NODE_NAME=GT.ISBN) GT_CHILD_NODE_ID
FROM
 GT_ADD_ISBNS GT
 ));  

Open in new window

0
 
sdstuberCommented:
It would help if the example queries and the example tables had corresponding columns.

Please post new tables and data with code that matches it along with expected results.
0
 
chaituu chaituAuthor Commented:
slightwv,
 
 Thanks for your time and your understanding is correct.when I execute entire query is taking time, but mainly below query is taking more than 1 hr.I took the explain plan and see the statistics below.currently below indexes have taken by the query as per the explain plan .
   
   IDX_TEMP_NODE_TQN created index on columns temp_node(type_qname,ID);
   IDX_TEMP_NPROP_S created index on columns temp_node_properties(type_qname,string_value,NODE_ID);
   IDX_TEMP_CASS_PRI created index on columns temp_child_assoc(parent_id,child_id);
   
   explain plan for
  select q.*,
				sum(case when node_type='asset' and prod_type='categories' and string_value in ('uPDF','Entire PDF') then 1 end ) over(partition by main_parent) asset_count,
				max(case when prod_type='product' then string_value end ) over(partition by main_parent) main_isbn
			from (
				select connect_by_root parent_id main_parent,
					level, parent_id, child_id, n.type_qname node_type, p.type_qname prod_type, string_value
				from temp_child_assoc c
					left join temp_node n on c.child_id=n.id
					left join temp_node_properties p on c.child_id=p.node_id
				connect by prior child_id = parent_id
				start with n.type_qname='product'
			) q

Plan hash value: 2054238456
 
----------------------------------------------------------------------------------------------------------------------
| Id  | Operation                                 | Name             | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                          |                  |    31M|    60G|       |    13M  (1)| 45:33:56 |
|   1 |  WINDOW SORT                              |                  |    31M|    60G|    78G|    13M  (1)| 45:33:56 |
|   2 |   VIEW                                    |                  |    31M|    60G|       |   773K (76)| 02:34:44 |
|*  3 |    CONNECT BY NO FILTERING WITH START-WITH|                  |       |       |       |            |          |
|*  4 |     HASH JOIN OUTER                       |                  |    45M|  2225M|   143M|   192K  (1)| 00:38:33 |
|*  5 |      HASH JOIN RIGHT OUTER                |                  |  4688K|    89M|    96M| 18298   (1)| 00:03:40 |
|   6 |       INDEX FAST FULL SCAN                | IDX_TEMP_NODE_TQN |  4621K|    44M|       |  4085   (1)| 00:00:50 |
|   7 |       INDEX FAST FULL SCAN                | IDX_TEMP_CASS_PRI |  4688K|    44M|       |  4471   (1)| 00:00:54 |
|   8 |      INDEX FAST FULL SCAN                 | IDX_TEMP_NPROP_S  |    45M|  1340M|       | 74767   (1)| 00:14:58 |
----------------------------------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   3 - access("C"."parent_id"=PRIOR "C"."child_id")
       filter("N"."type_qname"='product')
   4 - access("C"."child_id"="P"."NODE_ID"(+))
   5 - access("C"."child_id"="N"."ID"(+))
   
   

Open in new window

0
 
slightwv (䄆 Netminder) Commented:
Let me guess: you cannot change the design of the tables?

I'll try to get some time later to see if I can come up with something that might be faster.
0
 
chaituu chaituAuthor Commented:
I cannot change the design of the tables but i can create indexes if required.
0
 
slightwv (䄆 Netminder) Commented:
See if these indexes help any:
create index test1_idx on temp_child_assoc(child_id,parent_id);
create index test2_idx on temp_node(id,type_qname);
create index test3_idx on temp_node_properties(node_id,type_qname,string_value);

If it performs better, I would look at removing the others since I just moved the column order around.


You might also look at taking the large table to a Full Table Scan and using Parallel query.

From the plan that looks like temp_node_properties.

You can try (depends on how many cores you have on the server adjust the 16 accordingly):

...
select /*+ full(temp_node_properties) parallel(16) */ connect_by_root parent_id main_parent,
                              level, parent_id, child_id, n.type_qname node_type, p.type_qname prod_type, string_value
...
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.

All Courses

From novice to tech pro — start learning today.