CODEWITHSUNDEEP
sqloracle-databaseoptimizationoracle11gsql-view
nizam uddin
nizam uddin

Reputation: 341

How to Optimise View?

The following View returns result after very long time. How to optmise it for quick result ?

CREATE OR REPLACE VIEW v_e_soa
  AS
    SELECT substr(account_id,1,16) account_id,
           stmt_on_email,
           MIN(curr_no) curr_no,
           co_code,
           ( SELECT convert_to_date(MIN(date_time) )
             FROM ext_account_his_31122017 y
             WHERE y.stmt_on_email = x.stmt_on_email
                   AND   substr(y.account_id,1,16) = substr(x.account_id,1,16)
           ) date_time
    FROM ext_account_his_31122017 x
    WHERE stmt_on_email = 'YES'
    GROUP BY substr(account_id,1,16),
             stmt_on_email,
             co_code;

Upvotes: 2

Views: 59

Answers (2)

dnoeth
dnoeth

Reputation: 60462

To get the minimum date over per account you can switch to a Group Min instead of a Scalar Correlated Subquery:

CREATE OR REPLACE VIEW v_e_soa
  AS
    SELECT substr(account_id,1,16) account_id,
           stmt_on_email,
           MIN(curr_no) curr_no,
           co_code,
           convert_to_date(MIN(MIN(date_time)) -- aggregate Min nested in OLAP Min
                           OVER (PARTITION BY substr(account_id,1,16)) date_time
    FROM ext_account_his_31122017 x
    WHERE stmt_on_email = 'YES'
    GROUP BY substr(account_id,1,16),
             stmt_on_email,
             co_code;

Upvotes: 1

wolφi
wolφi

Reputation: 8361

Not knowing your table structure and minimal example data (please edit your post and show us!), I'd try to attact the following point:

  1. look into substr(account_id), what's the reason for chopping of digits after position 16. Is account_id a VARCHAR2 or a NUMBER?

  2. You filter on stmt_on_email = 'YES'. This could possibly be done at an earlier stage (for instance with a WITH clause). There is no reason to have it in the GROUP BY clause.

  3. The biggest gain is probaby in the SEELCT convert_to_date clause. It requires a self join of the table, and calls a function for each row. Both is probably not necessary.

I would try something along the lines of:

WITH 
filtered_stmt_yes AS (
  SELECT substr(account_id,1,16) account_id,
         stmt_on_email,
         curr_no,
         co_code,
         to_date(date_time,'YYYYMMDDHH24MI') as date_time
    FROM ext_account_his_31122017 x
   WHERE stmt_on_email = 'YES'
   ORDER BY account_id),
ranked AS (
  SELECT account_id, stmt_on_email, curr_no, co_code, date_time,
         min(curr_no) over (partition by account_id, co_code) as min_curr_no,
         min(date_time) over (partition by account_id) as min_date_time,
         row_number() over (partition by account_id order by rowid) as r
    FROM filtered_stmt_yes)
SELECT account_id, 
       stmt_on_email, 
       min_curr_no as cur_no, 
       co_code, 
       min_date_time as date_time
  FROM ranked
 WHERE r=1;

Upvotes: 2

Related Questions