Too much delay for oracle function

Question

This is a sample of bill table: (billnr is not unique. The combination of renr,filnr, kassanr is unique)

id   renr(billnr)   filnr(store)       kassanr(cashier)     price  res_nr(reservation)        
10        1             2                 3                   10          100
11        1             2                 3                   15          null
12        1             2                 3                   6           null   
13        1             2                 4                   120         101

I need the sum(price) for unique combination of renr,filnr, kassanr if they have resnr 100. That means : 10+15+6 = 31

I have this query:

select sum(rk.price) 
from bill rk, 
     (select rk1.renr, rk1.filnr, rk1.kassanr from bill rk1 where rk1.res_nr = :IN_n_resnr)  tr
where rk.filnr = tr.filnr 
      and rk.kassanr = tr.kassanr 
      and rk.renr = tr.renr;

When I execute this query, it's take only 7 sec and this is OK! But When I write this in a function, the function takes 21 sec and I can't understand why?!

CREATE OR REPLACE FUNCTION FUN_TEST (IN_n_resnr in number) return number
is
  v_return    number := null;      
begin   
         select sum(rk.price) into v_return
         from bill rk, 
              (select rk1.renr, rk1.filnr, rk1.kassanr from bill rk1 where rk1.res_nr = :IN_n_resnr)  tr
         where rk.filnr = tr.filnr 
         and rk.kassanr = tr.kassanr 
         and rk.renr = tr.renr;              
  return(v_return);        
end;

I tried to write the function with WITH CLAUSE. But that takes 21 sec too.

with t_resnr as 
         (select rk1.renr, rk1.filnr, rk1.kassanr from bill rk1 where rk1.res_nr = IN_n_resnr)

         select sum(rk.price) into v_return
         from bill rk, 
              t_resnr tr   
         where rk.filnr = tr.filnr 
         and rk.kassanr = tr.kassanr 
         and rk.renr = tr.renr;

Because our DBA was dumm. filnr is "store number". Kassanr is "cash desk" and renr is "bill number". The problem is "kassanr" and "bilnr" are not unique. We need exact bill in exact store by exact cashdesk. and bilnr is not primary key. There is a random primary key. The bill table called something else. — , Oct 12 '20 at 06:59
Please post execution plan for both queries, post how many rows do you expect to be returned from the subquery and from the `Bill`table. See [here](https://stackoverflow.com/a/34975420/4808122) for hint how to post it. And BTW the problems with your DBA you could solve internally and here you could post an example with *correct* column naming! — Marmite Bomber, Oct 12 '20 at 09:59
Perhaps your just missing a unique constraint/index the unique column combination. BTW looks like your DBA did it correctly (other than missing the constraint/index). — Belayer, Oct 14 '20 at 18:05

Littlefoot · Answer 1 · 2020-10-12T06:46:47.013

0

Isn't your query simply

select sum(rk.price_euro)
from bill rk
where rk.res_nr = :in_n_resnr

What does the inline view do? Nothing, but filters rows regarding the in_n_resnr.

Regarding your comment that you need a recursive join: can't tell, I don't have your tables nor data, but - from what you posted so far, looks like you're kind of wrong.

This is what your query does (consider switching to explicit JOIN):

SQL> select sum(a.sal)
  2  from emp a,
  3      (select b.empno, b.ename, b.job
  4       from emp b
  5       where b.deptno = 10
  6      ) c
  7  where a.empno = c.empno
  8    and a.ename = c.ename
  9    and a.job   = c.job;

SUM(A.SAL)
----------
     13750

This is what my query does:

SQL> select sum(a.sal)
  2  from emp a
  3  where a.deptno = 10;

SUM(A.SAL)
----------
     13750

SQL>

Result is the same.

Once again: I don't have your tables nor data, but - there's no difference, except for the fact that my version should perform better. I suggest you try it and see what happens.

Also, if you posted test case, we'd be able to see what you really have and you could explain what result you expect out of it.

edited Oct 12 '20 at 06:46

answered Oct 12 '20 at 06:35

Littlefoot

131,892
15
35
57

No. This is not so simple. We need a recursive join. One bill has some reservations. But a bill has more items without reservation too and some stupid columns. Like I said, the query is right. The problem is with function. – Oct 12 '20 at 06:38
I added some more info. Have a look, please. – Littlefoot Oct 12 '20 at 06:46
That does not work for our table. Because our DBA was dumm. filnr is "store number". Kassanr is "cash desk" and renr is "bill number". The problem is "kassanr" and "bilnr" are not unique. We need exact bill in exact store by exact cashdesk. – Oct 12 '20 at 06:56
But, you are joining data from the **BILL** table with data from the same **BILL** table. I'm sorry, I'm afraid that we don't understand each other. – Littlefoot Oct 12 '20 at 07:02
@Littlefoot do not let you mislead with the formulations in Q and check the sample `bill` data: The combination of renr,filnr, kassanr is **NOT** unique; thats makes the difference. Apparently OP wants to say, those columns are unique if you constraint on `resnr = 100` – Marmite Bomber Oct 12 '20 at 10:07

score 0 · Answer 2 · answered Oct 12 '20 at 12:22

This is how I would write the query:

select sum(price) 
from bill 
where (renr, filnr, kassanr) in
  (select renr, filnr, kassanr from bill where res_nr = :IN_n_resnr);

You want to look at combinations of renr, filnr, kassanr, so there should be a index on this:

create index idx1 on bill (renr, filnr, kassanr);

As for such a combination we want to see the price, we can add that to the index to get this even faster.

create index idx1 on bill (renr, filnr, kassanr, price);

But we only want to look at combinations having an entry for a particular res_nr. There are two possible approaches the DBMS can take:

Get all combinations for the res_nr first and with ths list find the related rows with the prices.
Loop through all rows and for each check whether there exists a related row with the res_nr.

For approach #1 we'd want

create index idx2 on bill (res_nr);

or even better yet

create index idx2 on bill (res_nr, renr, filnr, kassanr);

For approach #2 we'd want the opposite order:

create index idx3 on bill (renr, filnr, kassanr, res_nr);

I would provide these indexes:

create index idx1 on bill (renr, filnr, kassanr);
create index idx2 on bill (res_nr, renr, filnr, kassanr);
create index idx3 on bill (renr, filnr, kassanr, res_nr);

Then see in the explain plan which of these the DBMS uses and drop the others.

Too much delay for oracle function

2 Answers2