How to optimize next oracle query?

Question

I have 2 tables. Table "Accs" contains 9 million rows (3 columns: acc_id, month, year). First I need to extract the records that contain the partial entry of account number, and then in these records to find the complete match, if not - then the first partial match

WITH t AS (
  SELECT a.acc_id,
         t1.as,
         t1.cust,
         t1.curr,
         t1.code,
         t1.depart,
         t1.sdate,
         t1.stype,
         t1.amount,
         t1.s_id
  FROM table1 t1 
  LEFT JOIN Accs a 
  ON SUBSTR(a.acc_id,7,12)=t1.curr||LPAD(t1.code,4,'0')||LPAD(t1.depart,3,'0')
  WHERE t1.sdate='20.11.2011' AND t1.stype='A' AND a.month=11 ANd a.year=2011)
SELECT MAX(t.s_id),
       (CASE WHEN t.as='000000' 
             THEN (CASE WHEN ac2.acc_id IS NOT NULL THEN ac2.acc_id ELSE t.acc_id END) 
             ELSE t.cust||t.curr||LPAD(t.code,4,'0')||LPAD(t.depart,3,'0') END) acc_id
FROM t 
LEFT JOIN (SELECT t.acc_id FROM t) ac2 
ON SUBSTR(ac2.acc_id,1,6)='000'||LPAD(t.depart,3,'0')
GROUP BY      
       (CASE WHEN t.as='000000' 
             THEN (CASE WHEN ac2.acc_id IS NOT NULL THEN ac2.acc_id ELSE t.acc_id END) 
             ELSE t.cust||t.curr||LPAD(t.code,4,'0')||LPAD(t.depart,3,'0') END)

This query takes a long time. Whether correctly I do?

Answer 1

First attempt

Can't really test it without having your actual table structure and data, but I've made a few minor changes that do sometimes have big impact.

First of all, I changed the LEFT JOIN in the with part to an INNER JOIN. Since you use values of a in the WHERE clause, it would function as an inner join anyway, and it is usually a lot faster, especially with this amount of data and proper indexes.

I changed the inner CASE to NVL, because that's essentially what it does. Don't know if that would speed things up.

Moved the string concatenation from the outer query to the with part.

These are just small changes that may have anywhere from no to quite some effect. At least you can try these before altering the table structure itself, although it may be a good idea to do that anyway.

WITH t AS 
(
  SELECT 
    a.acc_id,
    t1.as,
    t1.cust,
    t1.curr,
    t1.code,
    t1.depart,
    t1.sdate,
    t1.stype,
    t1.amount,
    t1.s_id,
    t1.cust || t1.curr || LPAD(t1.code, 4, '0') || LPAD(t1.depart, 3, '0') as groupfield
  FROM 
    table1 t1 
    INNER JOIN Accs a
      ON SUBSTR(a.acc_id, 7, 12) = t1.curr || LPAD(t1.code, 4, '0') || LPAD(t1.depart, 3, '0')
  WHERE 
    t1.sdate = '20.11.2011' AND t1.stype = 'A' AND a.month = 11 ANd a.year = 2011
)
SELECT
  MAX(t.s_id),
  (CASE WHEN t.as = '000000' THEN 
    NVL(ac2.acc_id, t.acc_id)
  ELSE
    t.groupfield
  END) acc_id
FROM 
  t 
  LEFT JOIN t ac2 on ac2 
    ON SUBSTR(ac2.acc_id, 1, 6) = '000' || LPAD(t.depart, 3, '0')
GROUP BY      
  (CASE WHEN t.as = '000000' THEN 
    NVL(ac2.acc_id, t.acc_id)
  ELSE
    t.groupfield
  END)

Second attempt

After looking a little more into your query, I wonder if you cannot just make it a single/simple query instead of using with. I think by first inner joining Accs and then left joining Accs again with the extra condition, you're a good way to go really.

  SELECT 
    MAX(t1.s_id) AS s_id,
    CASE WHEN t.as = '000000' THEN
      NVL(a2.acc_id, a.acc_id)
    ELSE
      t1.cust || t1.curr || LPAD(t1.code, 4, '0') || LPAD(t1.depart, 3, '0')
    END AS acc_id
  FROM 
    table1 t1 
    INNER JOIN Accs a
      ON SUBSTR(a.acc_id, 7, 12) = t1.curr || LPAD(t1.code, 4, '0') || LPAD(t1.depart, 3, '0')
      AND a.month = 11 AND a.year = 2011
    LEFT JOIN Accs a2
      ON SUBSTR(a2.acc_id, 7, 12) = t1.curr || LPAD(t1.code, 4, '0') || LPAD(t1.depart, 3, '0')
      AND a2.month = 11 AND a2.year = 2011
      AND SUBSTR(a2.acc_id, 1, 6) = '000' || LPAD(t1.depart, 3, '0')
  WHERE 
    t1.sdate = '20.11.2011' AND t1.stype = 'A'
  GROUP BY 
    CASE WHEN t.as = '000000' THEN
      NVL(a2.acc_id, a.acc_id)
    ELSE
      t1.cust || t1.curr || LPAD(t1.code, 4, '0') || LPAD(t1.depart, 3, '0') as groupfield
    END AS acc_id

Answer 2

You could try creating function-based indexes like this one:

create index xxx on accs (SUBSTR(a.acc_id,7,12));