how to improve performance using BULK COLLECT and also catch if record NOT_DATA_FOUND

We have products(100k) received in the form of excel doc.Initially ,dumped those product numbers into the normal temp table(GT_ADD_ISBNS);

From this temp table ,we are checking againt main tables.So initially in the cursor (products_cur),checking whether the product exists or not.
as we are using BULK COLLECT to iterate the cursor so not able to store 'product not found' if it not found in the main table(TEMP_NODE_PROPERTIES) .

How to catch the error and update it in GT_ADD_ISBNS table against product no?

I don't want to execute this program sequentially as this will take lot of time ,So used bulk collect to perform parallel processing.

1)to see whether product is exists or not.
2)to see whether product inside the assets are having the 'category' or not.here we will got multiple nodes ,so used LISTAGG funtiopns and passed those values to the 3rd step and will executed dynamically.
3)to see whether the assets are having file names starts with '%pdf%' or not.

So each record has to process these 3 steps and final document names will be updated in the table for the successful record. If any error comes in any of the steps then error message will be stored for that record.

Please find the attached sample data and DDL.

Below stored procedure is not giving enough performance as I am expecting.Can please review and share your comments.
DECLARE
V_NODE_ID  VARCHAR2(20);
V_FILENAME VARCHAR2(100);
V_FINAL_ERRORMESSAGE VARCHAR2(2000);
sql_stmt  VARCHAR2(2000);
limit_in  PLS_INTEGER :=100;

TYPE products_aat IS TABLE OF TEMP_NODE_PROPERTIES%ROWTYPE;

l_products products_aat;

CURSOR products_cur IS
SELECT ANP.*
FROM TEMP_NODE AN,
	  TEMP_NODE_PROPERTIES ANP
WHERE
AN.ID=ANP.NODE_ID AND
ANP.TYPE_QNAME = 'product' AND
ANP.STRING_VALUE in(SELECT isbn FROM GT_ADD_ISBNS GT);

BEGIN
--Iterating all Products
OPEN products_cur;
    LOOP
        FETCH products_cur 
            BULK COLLECT INTO l_products LIMIT limit_in;

DBMS_OUTPUT.PUT_LINE('l_employees.COUNT:'||l_products.COUNT);

        FOR indx IN 1 .. l_products.COUNT 
        LOOP
			DBMS_OUTPUT.PUT_LINE('Product:'|| l_products(indx).STRING_VALUE || ':PRODUCT NODE ID:' ||l_products(indx).NODE_ID);
           V_FINAL_ERRORMESSAGE :='';
		   
--Check Whether Product inside the assets are having the Category assigned or not
SELECT DISTINCT LISTAGG(ANP.NODE_ID, ',') 
WITHIN GROUP (ORDER BY ANP.NODE_ID) INTO V_NODE_ID
		FROM 
		TEMP_NODE_PROPERTIES ANP,
		TEMP_NODE_PROPERTIES ANPP,
		TEMP_NODE AN
		WHERE 
	  ANP.NODE_ID=AN.ID AND
      ANPP.NODE_ID=ANP.NODE_ID AND
      AN.TYPE_QNAME ='asset' AND
	  --required catgeories
   (ANP.STRING_VALUE ='uPDF' AND
    ANPP.STRING_VALUE ='Entire PDF')  AND
    ANP.TYPE_QNAME='categories'  AND
 		ANP.NODE_ID IN(SELECT CHILD_ID 
                  FROM TEMP_CHILD_ASSOC START WITH PARENT_ID IN(
                 SELECT CHILD_ID FROM TEMP_CHILD_ASSOC WHERE NAME in( l_products(indx).STRING_VALUE)) 
                      CONNECT BY PRIOR CHILD_ID = PARENT_ID);
 

DBMS_OUTPUT.PUT_LINE('products_cur.ISBN:'|| l_products(indx).STRING_VALUE || ':ASSET NODEIDS:' ||V_NODE_ID);
      
 --Check whether Product inside the assets have applied the 'category' But those assets are having documents LIKE '%pdf%' or not    
  sql_stmt := 'SELECT LISTAGG(ANP.STRING_VALUE, '','') WITHIN GROUP (ORDER BY ANP.STRING_VALUE) 		FROM 		TEMP_NODE_PROPERTIES ANP
		WHERE   ANP.TYPE_QNAME=''name''  AND  (( ANP.STRING_VALUE LIKE ''%pdf%''  AND  ANP.STRING_VALUE NOT LIKE ''%pod%'') 
       )  AND  ANP.NODE_ID IN (' || V_NODE_ID ||')';
       
     
  if(V_NODE_ID  IS NOT NULL)  THEN
  EXECUTE IMMEDIATE sql_stmt into V_FILENAME;
  else
   V_FINAL_ERRORMESSAGE:=V_FINAL_ERRORMESSAGE|| 'Category is not applied to none of the Assets for this Product';
   DBMS_OUTPUT.PUT_LINE('Category is not applied to none of the Assets for this Product:'|| l_products(indx).STRING_VALUE );
   UPDATE GT_ADD_ISBNS SET CATEGORY11_ERROR_MESSAGE=  V_FINAL_ERRORMESSAGE  WHERE ISBN= l_products(indx).STRING_VALUE;
  GOTO CATEGORY_NOT_FOUND;
  END IF;
  
   
   IF(V_FILENAME is NULL) THEN
    DBMS_OUTPUT.PUT_LINE(V_FINAL_ERRORMESSAGE|| 'Category is applied for this Product But for the asset:'||  V_NODE_ID|| ':Documents[LIKE %pdf% AND LIKE %pod% ] were not found ;');
    V_FINAL_ERRORMESSAGE:=V_FINAL_ERRORMESSAGE|| 'Category is applied for this Product But for the asset:'||  V_NODE_ID|| ':Documents[LIKE %pdf% AND LIKE %pod% ] were not found ;';
     UPDATE GT_ADD_ISBNS SET CATEGORY11_ERROR_MESSAGE=  V_FINAL_ERRORMESSAGE  WHERE ISBN= l_products(indx).STRING_VALUE;
   END IF;
   
   DBMS_OUTPUT.PUT_LINE( l_products(indx).STRING_VALUE || '..Final documents for the Product:'||V_FILENAME);
      UPDATE GT_ADD_ISBNS SET CATEGORY11_FILENAME=V_FILENAME WHERE ISBN= l_products(indx).STRING_VALUE;
  
		<<CATEGORY_NOT_FOUND>> 
		NULL; 
        END LOOP;
        
       


        EXIT WHEN l_products.COUNT < limit_in;

   END LOOP;
 
   CLOSE products_cur;

END;

Open in new window

add_sample_data.zip
chaituu chaituAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

johnsoneSenior Oracle DBACommented:
Change this:
CURSOR products_cur IS
SELECT ANP.*
FROM TEMP_NODE AN,
	  TEMP_NODE_PROPERTIES ANP
WHERE
AN.ID=ANP.NODE_ID AND
ANP.TYPE_QNAME = 'product' AND
ANP.STRING_VALUE in(SELECT isbn FROM GT_ADD_ISBNS GT);

Open in new window

to this:
    CURSOR products_cur IS 
      SELECT ANP.* 
      FROM   temp_node AN 
             join temp_node_properties ANP 
               ON AN.id = ANP.node_id 
             left outer join gt_add_isbns gt 
                          ON gt.isbn = anp.string_value 
      WHERE  ANP.type_qname = 'product'; 

Open in new window

Now, you should get your no product found.  When looping through the results if L_PRODUCTS.STRING_VALUE is null then that would be a not found.  Also, not sure why the TEMP_NODE table is part of the query, it isn't used.

As for performance, your sample data shows no indexing.  That is likely an issue.  Without looking at it too deeply, I would think that you need indexes on at least these:

GT_ADD_ISBNS(ISBN)
TEMP_NODE(ID)
TEMP_NODE_PROPERTIES(TYPE_QNAME)
TEMP_NODE_PROPERTIES(NODE_ID)
TEMP_CHILD_ASSOC(NAME)

Beyond that, profile each query individually.  Put timings in so you know how long each one took.  That should give you an idea where you need to look for tuning.  As suggested in your other post, I would think you can combine some of the queries and only pass the same set of data once.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.

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.