Sunday, July 12, 2020

SQL Macro - Parameterized Views (since Oracle DB 19.6)


create or replace function dept_job_salaries (
  job varchar2
) return varchar2 sql_macro is
begin
  return '
     select department_id, sum ( salary ) 
     from   employees
     where  job_id = dept_job_salaries.job
     group  by department_id';
end;
/

select * from  dept_job_salaries ( 'FI_ACCOUNT' );

SQL> select * from  dept_job_salaries ( 'FI_ACCOUNT' );

DEPARTMENT_ID SUM(SALARY)
------------- -----------
          100       39600


Execution Plan
----------------------------------------------------------
Plan hash value: 55893400

---------------------------------------------------------------------------------------------------
| Id  | Operation                            | Name       | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                     |            |     4 |    64 |     3  (34)| 00:00:01 |
|   1 |  HASH GROUP BY                       |            |     4 |    64 |     3  (34)| 00:00:01 |
|   2 |   TABLE ACCESS BY INDEX ROWID BATCHED| EMPLOYEES  |     5 |    80 |     2   (0)| 00:00:01 |
|*  3 |    INDEX RANGE SCAN                  | EMP_JOB_IX |     5 |       |     1   (0)| 00:00:01 |
---------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------


   3 - access("JOB_ID"='FI_ACCOUNT')

Alternative implementation with pipelined table, although context switch between SQL and PL/SQL overhead for such implementation

create or replace type t_depart_role_sal as object (
    department_id       number,
    total_salaries      number
);

create or replace type t_depart_role_sal_tab 
    is table of t_depart_role_sal;

-- Build a pipelined table function.
CREATE OR REPLACE FUNCTION depart_role_sal_ptf(job varchar2)
return t_depart_role_sal_tab pipelined
as
cursor c is 
  select department_id, sum (salary) total_salary 
     from   employees
     where  job_id = job
     group  by department_id;
begin
  for c1 in c
  loop
    PIPE ROW(t_depart_role_sal(c1.department_id, c1.total_salary));
  end loop;
  return;
exception
  when no_data_needed then
    raise;
  when others then
    raise;
end;
/


select * from depart_role_sal_ptf('FI_ACCOUNT');

SQL> select * from depart_role_sal_ptf('FI_ACCOUNT');

DEPARTMENT_ID TOTAL_SALARIES
------------- --------------
          100          39600


Execution Plan
----------------------------------------------------------
Plan hash value: 1610656097

---------------------------------------------------------------------------------------------------------
| Id  | Operation                         | Name                | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                  |                     |  8168 | 16336 |    29   (0)| 00:00:01 |
|   1 |  COLLECTION ITERATOR PICKLER FETCH| DEPART_ROLE_SAL_PTF |  8168 | 16336 |    29   (0)| 00:00:01 |
---------------------------------------------------------------------------------------------------------

3 comments:

  1. This is achieved through planned out content production and an annotation strategy. susbscribers on youtube

    ReplyDelete
  2. Very informative article. Really looking forward to read more. Want more.
    how to post story to instagram from pc

    ReplyDelete