Oracle - Reuse SELECT with params

Question

I have SELECT like

SELECT t1.A,
       t2.A,
       t1.B,
       t2.B, 
       case when v_val2 < sysdate 
            then null 
            else t2.D 
      end
from t1
left join t2
   on t1.C = t2.C and t2.D = p_val1
where t2.ref_date = p_val2

Where p_val1 and p_val2 are params. The real SELECT is more complex and join tables with 140 million rows or more.

How can I reuse this select on more than one place? Now I use it in Business Object Report, VIEW, Insert and spool file. I tried to create VIEW but without params it is too slow.

I searched for something like view with parameter, but in this theme in Ask TOM а conclusion is that there isn't such thing.

UPDATE

p_val1 can be 4 fix values(like 'A','B','C','D'), but p_val2 is more complex because it is load date(date when the data was loaded). There is around 500 history load date and after that loading become daily. Every report, output and so on must be get by date.

@KaushikNayak can give example? I update my question with description of params. — Morticia A. Addams, Apr 13 '18 at 11:07

Thorsten Kettner · Answer 1 · 2018-04-13T12:02:26.660

There is nothing complicated about your query. I see no reason why a view should be any slower than the original query when called with the same criteria.

The view would have no parameters:

create view my_view as
  select 
    t2_d, t2_ref_date,
    t1.a as t1_a, t2.a as t2_a, t1.b as t1_b, t2.b as t2_b,
    case when v_val2 < sysdate then null else t2.d end as e
  from t2
  join t1 on t1.c = t2.c;

And when used in a query it should lead to the same execution plan and the same performance as the original query:

select t1_a, t2_a, t1_b, t2_b, e
from my_view
where t2_ref_date = p_val2
  and t2_d = p_val1;

Have you actually tried it? I really can't imagine why this should be slower.

The DBMS can access T2 records by ref_date and D and then join with T1. I'd hence change the order of the tables in the FROM clause as follows. (I did this in above view already.) This is just for readability. It's still the same query.

SELECT 
  t1.A,
  t2.A,
  t1.B,
  t2.B, 
  case when v_val2 < sysdate then null else t2.D end
from t2
join t1 on t1.C = t2.C
where t2.ref_date = p_val2
  and t2.D = p_val1

For this query to run fast I'd suggest the following indexes:

create index idx_t2 on t2(ref_date, d, c);
create index idx_t1 on t2(c);

or even better covering indexes:

create index idx_t2 on t2(ref_date, d, c, a, b);
create index idx_t1 on t2(c, a, b);

Of course with your real query that may be different. Not because of many rows or many tables, but in your example I can easily select the two columns in question. So the optimizer can use these directly to apply the WHERE clause. If, however, your query contains aggregations in subqueries for instance where the criteria is applied before aggregation, then it can no longer be applied early with a view.

It's hard to give advice without knowing the real query. Maybe writing a pipelined function would be a better option for you.

score 1 · Answer 2 · answered Apr 13 '18 at 09:15

Create function. you can re use in multiple places

CREATE OR REPLACE FUNCTION SampleFunction1(p_val1 in varchar2,p_val2 in varchar2 )

          RETURN sys_refcursor
        is
          l_rc sys_refcursor;
        begin
          open l_rc
           for        

        SELECT t1.A,t2.A,t1.B,t2.B, case when v_val2 < sysdate then null else t2.D end
        from t1
        join t2
           on t1.C = t2.C and t2.D = p_val1
        where t2.ref_date = p_val2;
          return l_rc;
        end;

How can I select after that from this sys_refcursor? [Here](https://stackoverflow.com/questions/2575653/oracle-pl-sql-selecting-from-sys-refcursor) writes that i can't — Morticia A. Addams, Apr 13 '18 at 10:38

score 0 · Answer 3 · answered Apr 13 '18 at 12:01

My answer (that is most close to me and clear about me and maybe that isn't a best choice) is to create user-defined type and type table of first type like:

CREATE TYPE my_type AS OBJECT
    ( a        VARCHAR2(20)
    , b    VARCHAR2(20)
    , c     VARCHAR2(20)
    , d    VARCHAR2(20)
    , e     VARCHAR2(20)
    ) ;

And type as type of my_type:

create TYPE T_my_type  AS TABLE OF my_type ;

After that (thanks to @user9405863) I create function that return T_my_type (instead sys_refcursor):

create or replace function myFunc(p_val1 varchar2, p_val2 date)
RETURN T_my_type  is

v_T_my_type T_my_type ;

BEGIN

  SELECT my_type (t1.A,t2.A,t1.B,t2.B, case when v_val2 < sysdate then null else t2.D end)
  BULK collect INTO v_T_my_type 
from t1
join t2
   on t1.C = t2.C and t2.D = p_val1
where t2.ref_date = p_val2;

  RETURN ttv_T_my_type 

END;

In the function I just cast my result columns to my_type and get result with BULK collect INTO.

Finally when I execute:

select *
from table(myFunc('A', '13.04.2018'));

Result is what I expected.

COMMENT

I will be happy if you say what you think about this solution and pro and cons about it.

My point of view is that you are going to far for such a simple thing. When you wont be there anymore, in 3 months or 3 years, nobody will understand what's going on there without reverse engineering the whole thing. Imagine if everyone does the same than you (developping complex structures to make a simple query), your DB would become a nightmare. The best solution is the one gaven by @ThorstenKettner. It is also the most simple and the natural thing to do to solve that kind of "_issue_" on a RDBMS. You should thus stick with it — Thomas G, Apr 13 '18 at 12:11
Yes, but I have missed that I have params in ON clause of LEFT JOIN. — Morticia A. Addams, Apr 13 '18 at 13:07

Oracle - Reuse SELECT with params

3 Answers3