oracle-databaseperformancefunctioncollectionsbulk-operations

Oracle - Null or Blank parameter value in function


I'm trying to get the result from a function that makes a query, processes the fields and returns a collection.

If I take the query of the function and execute separately, it returns in about 10 minutes, depending on the parameters that I put. If I pass the same parameters to the function, it keeps processing and I can't get any result after 45 minutes.

After the query, I only have a few if's which check's for zero values or values that higher than others.

I think the problem is that I'm passing some parameters null or blank and it makes the query crash. Here is my problem:

I have a type:

CREATE OR REPLACE TYPE TypeForFunction is OBJECT (
    -- all my fields here
 )
/

Then a make a collection:

CREATE OR REPLACE TYPE TypeForFunctionTable AS
    TABLE OF TypeForFunction
/

Then my function goes like this:

CREATE OR REPLACE FUNCTION MyFunction
(
  /* here I have five parameters and in the case that the query crashes, 
     two of them I'm trying to pass blank or null */

  COL in varchar2, -- This I pass a valid value
  INDEX in number, -- same here
  REF in varchar2, -- This one I'm trying to pass Blank ('') or Null and i 
                      get no result no matter which one I pass.
  P in varchar2,   
  BLOQ in varchar2 -- Same null or blank here

) RETURN TypeForFunctionTable
IS  
  result_table TypeForFunctionTable;
  i integer := 0;
begin 
     select      
            TypeForFunction(

                /* Here I have some subquerys that I use the parameters null which 
                   I use the same way as parameter REF. Like: */ 
                
                and (MyTable.FieldP = P or P is null)
                and (MyTable.FielBloq = BLOQ or BLOQ is null)

            ) BULK COLLECT into result_table   
     from              
        myTables
        
     where
        -- here I have a clause like

        (MyTable.FieldREF = REF or REF is null)
     ;  
     For i in 1..result_table.count loop                 
         /* Here I have some if's, but nothing to crash the query like it happens. 
            Things like: */

         if MyVar > 0 then
            COL = REF;
            INDEX = INDEX + 100;


              
     end loop;        
     return result_table;     
 
end MyFunction;
/

To call the function I try:

select * from table(MyFunction('59', 1, '', 'IV18', ''));

Also try:

select * from table(MyFunction('59', 1, Null, 'IV18', Null));

Anyway I get the same result, function don't return or give any error after 45 minutes.

Is there any better way to work with parameters that I may or may not pass value?


Solution

  • I could not make the query any faster. Turns out this query was already otimized a while ago, and it returns the bet of production for the next season, based on the previous season in the company I work (It's a womens clothes factory in Brazil), so it's heavy.

    But then I writed a procedure to make three simple updates and I haved the same issue, it kept hang and does'nt give me any result running from the procedure, but the updates queries runned separatedly worked fine.

    I started to search and found this answer:

    Stored procedure hangs seemingly without explanation

    Which is an SQL Server anwser, but then I started to search if the problem affects also Oracle and I came across with this post:

    https://dba.stackexchange.com/questions/198443/does-oracle-database-suffer-from-parameter-sniffing-issue

    So I declared local variables in both my function and procedure and did this variables receive the parameters.

    Now my function looks like this:

    CREATE OR REPLACE FUNCTION MyFunction
    (
      /* here i have five parameters and in the case that the query crashes, 
         two of them i'm trying to pass blank or null */
    
      COL in varchar2, -- This I pass a valid value
      INDEX in number, -- same here
      REF in varchar2, -- This one I'm trying to pass Blank ('') or Null and i 
                          get no result no matter wich one I pass.
      P in varchar2,   
      BLOQ in varchar2 -- Same null or blank here
    
    ) RETURN TypeForFunctionTable
    IS  
      result_table TypeForFunctionTable;
      i integer := 0;
      LOCAL_COL varchar2(4) := COL;
      LOCAL_REF varchar2(15) := REF;
      LOCAL_P varchar2(6) := P;
      LOCAL_BLOQ varchar2(1) :=;
    

    And I used the "LOCAL" variables in all queries and it worked fine. Solved the problem. I could not thank the guy whos comment on the original post because of my reputation, but i'm very thankfull.

    Also thankfull for the replies!