Query Tuning in stored procedure

Question

I need to tune this below query mentioned to avoid Union with the archive table every time because the below query might be unnecessarily scanning the Archive table even if in most of the cases the data will be present in the first table itself.

Procedure status(p_order_id in varchar2(25), p_stat out sys_refcursor) IS

Begin
open p_stat

select o.status_code,o.order_id FROM order o
where o.order_id=p_order_id 
union
select a.status_code,a.order_id FROM order_archive a
where a.order_id=p_order_id ;

end status;

Answer 1

You could add a not exists check against the main table as part of the archive branch:

select o.status_code,o.order_id
from order o
where o.order_id=p_order_id 
union all
select a.status_code,a.order_id
from order_archive a
where a.order_id=p_order_id
and not exists (
  select *
  from order o
  where o.order_id=p_order_id 
);

which now, of course, means it will look at the main table in both branches; but it may still be quicker, depending on your data, indexes, stats etc.

---

Another option, which will prevent the archive table being looked at if the ID exists in the main table, is to do a simple count against the main table first, and then conditionally open the ref cursor for one of two possible queries against a single table each; i.e. branching in PL/SQL instead of in SQL, eliminating the union completely. Something like:

procedure status(p_order_id in varchar2, p_stat out sys_refcursor) is
  l_count pls_integer;
begin
  select count(*) into l_count
  from order o
  where o.order_id=p_order_id;

  if l_count > 0 then
    -- exists in main table so only query that
    open p_stat for
      select o.status_code,o.order_id
      from order o
      where o.order_id=p_order_id;
  else
    -- does not exist in main table so only query archive
    open p_stat for
      select a.status_code,a.order_id
      from order_archive a
      where a.order_id=p_order_id;
  end if;
end status;
/

If the ID isn't unique in the main table then you could add and rownum = 1 to the initial query so it stops as soon as it finds any matching rows (and therefore l_count can be at most 1) instead of getting an accurate count, since you don't really care about the actual number found.

Can we use fetch and loop on the dataset of order table. If found then only go for archive table

Not really, because the fetch would consume the first row of the results. Here's a very contrived example:

var rc refcursor;

declare
  x varchar2(1);
begin
  open :rc for
    select 'A' from dual where 1 = 0
    union all
    select 'B' from dual where 1 = 0;
  fetch :rc into x;
  if :rc%notfound then
    open :rc for select 'C' from dual;
  end if;
end;
/

PL/SQL procedure successfully completed.

print rc

'
-
C

With this setup the 'main' query doesn't find any rows (because of the 1 = 0 check); after the fetch the cursor is notfound, so it reopens the cursor for the 'archive' query, and the caller sees that as expected.

But if the first query does return rows:

declare
  x varchar2(1);
begin
  open :rc for
    select 'A' from dual where 1=1
    union all
    select 'B' from dual where 1=1;
  fetch :rc into x;
  if :rc%notfound then
    open :rc for select 'C' from dual;
  end if;
end;
/

PL/SQL procedure successfully completed.

print rc

'
-
B

then after the fetch the cursor is found so it doesn't reopen the cursor for the archive query and the existing ref cursor is passed back to the caller. But the first row from the cursor has already been fetched into the x variable and is lost, so the caller no longer sees that. The print only shows a single row with 'B', and the other row that should be there with 'A' is missing.

For the caller to still see both rows you would have to reopen the cursor with the 'main' query:

declare
  x varchar2(1);
begin
  open :rc for
    select 'A' from dual where 1=1
    union all
    select 'B' from dual where 1=1;
  fetch :rc into x;
  if :rc%found then
    open :rc for
      select 'A' from dual where 1=1
      union all
      select 'B' from dual where 1=1;
  else
    open :rc for select 'C' from dual;
  end if;
end;
/

PL/SQL procedure successfully completed.

print rc

'
-
A
B

Logically that would really be the same as the initial count I'm doing above, I suppose, but would be less clear.

Answer 2

The first step is simple to use UNION ALL, because the UNION guarantuert a distinct result of both tables.

Using UNION ALL will fetch the data from the first table and only after fetching all row from it it will access the second table.

UNION would be only required, if tehre are identical rows with order_id and status_code in both tables and you need to dedup them; which is not a typical scenario.