sql - Performance tuning in oracle by adding index

Question

There is a query i would like to improve the performance. The tables have close to 10 million records. The exisiting indexes of both the tables are given below.

DISB_A TABLE INDEX:

INDEX_OWNER  INDEX_NAME     TABLE_OWNER   TABLE_NAME  COLUMN_NAME COLUMN_POSITION  COLUMN_LENGTH  CHAR_LENGTH   DESCEND  COLLATED_COLUMN_ID
-------------------------------------------------------------------------------------------------------------------------------------------
DEVL         DISB_A_INDX1   DEVL          DISB_A      V_ID         1               22             0             ASC 
DEVL         DISB_A_INDX1   DEVL          DISB_A      SEQNBR       2               22             0             ASC 
DEVL         DISB_A_INDX2   DEVL          DISB_A      ND_ID        1               22             0             ASC 
DEVL         DISB_A_INDX2   DEVL          DISB_A      DUE_DATE     2               7              0             ASC 
DEVL         DISB_A_INDX3   DEVL          DISB_A      WL_ID        1               22             0             ASC 
DEVL         DISB_A_INDX4   DEVL          DISB_A      CODE         1               8              8             ASC 
DEVL         DISB_A_PRIME   DEVL          DISB_A      ID           1               22             0             ASC 

DISB_B TABLE INDEX:

INDEX_OWNER   INDEX_NAME  TABLE_OWNER TABLE_NAME    COLUMN_NAME    COLUMN_POSITION  COLUMN_LENGTH   CHAR_LENGTH DESCEND COLLATED_COLUMN_ID
-------------------------------------------------------------------------------------------------------------------------------------
DEVL         DISB_B_INDX1   DEVL       DISB_B        DSBA_ID        1                  22                0        ASC   
DEVL         DISB_B_INDX1   DEVL       DISB_B        SEQNBR         2                  22                0        ASC   
DEVL         DISB_B_PRIME   DEVL       DISB_B        DSBA_ID        1                  22                0        ASC   
DEVL         DISB_B_PRIME   DEVL       DISB_B        ND_ID          2                  22                0        ASC   
DEVL         DISB_B_PRIME   DEVL       DISB_B        A_ID           3                  13                13       ASC   
DEVL         DISB_B_PRIME   DEVL       DISB_B        DIO_ID         4                  6                 6        ASC   
DEVL         DISB_B_PRIME   DEVL       DISB_B        AIO_QUAL       5                  22                0        ASC   
DEVL         DISB_B_PRIME   DEVL       DISB_B        DMT_CODE       6                  3                 3        ASC   
DEVL         DISB_B_PRIME   DEVL       DISB_B        DMT_SEQNBR     7                  22                0        ASC   
DEVL         DISB_B_PRIME   DEVL       DISB_B        ORDER_SEQNBR   8                  22                0        ASC   
DEVL         DISB_B_PRIME   DEVL       DISB_B        NBR            9                  20                20       ASC   

With the exsisting index when i checked the explain plan the cost was very high and the record used to take close to 1 second to 2 second to get displayed and the operation used to be FULL Table scan. Please find the details below.

SQL> explain plan 
     for 
SELECT SUM ( NVL ( dd.req_amt , 0 ) )
FROM DISB_A db ,
     DISB_B dd
WHERE db.id = dd.dsba_id
AND dd.nd_id = xxxxxxxx
AND dd.a_id = 'xx-xx'
AND DECODE (db.v_id , xxxxxxxxx , 'COMPLETE' , db.code ) = 'COMPLETE'
AND db.effdate BETWEEN TRUNC ( SYSDATE , 'YEAR' ) AND SYSDATE; 
 Explained.

 Elapsed: 00:00:00.04
 SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
Plan hash value: 4272128008
--------------------------------------------------------------------------------------------------
| Id  | Operation                      | Name            | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT               |                 |     1 |    58 | 62271   (1)| 00:00:03 |
|   1 |  SORT AGGREGATE                |                 |     1 |    58 |            |          |
|*  2 |   FILTER                       |                 |       |       |            |          |
|   3 |    NESTED LOOPS                |                 |     1 |    58 | 62271   (1)| 00:00:03 |
|   4 |     NESTED LOOPS               |                 |    10 |    58 | 62271   (1)| 00:00:03 |
|*  5 |      TABLE ACCESS STORAGE FULL | DISB_A          |    10 |   300 | 62231   (1)| 00:00:03 |
|*  6 |      INDEX RANGE SCAN          | DISB_B_PRIME    |     1 |       |     3   (0)| 00:00:01 |
|   7 |     TABLE ACCESS BY INDEX ROWID| DISB_B          |     1 |    28 |     4   (0)| 00:00:01 |
--------------------------------------------------------------------------------------------------

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

   2 - filter(SYSDATE@!>=TRUNC(SYSDATE@!,'fmyear'))
   5 - storage(DECODE("DB"."EV_ID",1263421688,'COMPLETE',"DB"."STATUS_CODE")='COMPLETE'
              AND "DB"."EFFDATE">=TRUNC(SYSDATE@!,'fmyear') AND "DB"."EFFDATE"<=SYSDATE@!)
       filter(DECODE("DB"."EV_ID",1263421688,'COMPLETE',"DB"."STATUS_CODE")='COMPLETE'
              AND "DB"."EFFDATE">=TRUNC(SYSDATE@!,'fmyear') AND "DB"."EFFDATE"<=SYSDATE@!)
   6 - access("DB"."ID"="DD"."DSBA_ID" AND "DD"."IND_ID"=20972265 AND
              "DD"."GA_ID"='150563-01')

25 rows selected.

Elapsed: 00:00:00.02

SQL> SELECT SUM ( NVL ( dd.req_amt , 0 ) )
FROM DISB_A db ,
     DISB_B dd
WHERE db.id = dd.dsba_id
AND dd.nd_id = xxxxxxxx
AND dd.a_id = 'xx-xx'
AND DECODE (db.v_id , xxxxxxxxx , 'COMPLETE' , db.code ) = 'COMPLETE'
AND db.effdate BETWEEN TRUNC ( SYSDATE , 'YEAR' ) AND SYSDATE; 

 SUM(NVL(DD.REQ_AMT,0))
 ______________________
             62500

  **1 row selected.
  Elapsed: 00:00:01.65**

So in the previous post I was asked to add another index so that it will reduce the cost and the query might come out faster. Please find the index that i added below.

 **CREATE INDEX DISB_A_INDX5 ON DISB_A (ID,IND_id,EV_ID,status_code,EFFDATE ASC) NOPARALLEL;**

 PLAN_TABLE_OUTPUT
 Plan hash value: 2535999045

-----------------------------------------------------------------------------------------------------
| Id  | Operation                        | Name             | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                 |                  |     1 |    58 | 18669   (1)| 00:00:01 |
|   1 |  SORT AGGREGATE                  |                  |     1 |    58 |            |          |
|*  2 |   FILTER                         |                  |       |       |            |          |
|   3 |    NESTED LOOPS                  |                  |     1 |    58 | 18669   (1)| 00:00:01 |
|   4 |     NESTED LOOPS                 |                  |    35 |    58 | 18669   (1)| 00:00:01 |
|*  5 |      INDEX STORAGE FAST FULL SCAN| DISB_A_INDX5     |    35 |  1050 | 18598   (1)| 00:00:01 |
|*  6 |      INDEX RANGE SCAN            | DISB_B_PRIME     |     1 |       |     3   (0)| 00:00:01 |
|   7 |     TABLE ACCESS BY INDEX ROWID  | DISB_B           |     1 |    28 |     4   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------------

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

   2 - filter(SYSDATE@!>=TRUNC(SYSDATE@!,'fmyear'))
   5 - storage(DECODE("DB"."EV_ID",1263421688,'COMPLETE',"DB"."STATUS_CODE")='COMPLETE' AND
              "DB"."EFFDATE"<=SYSDATE@! AND "DB"."EFFDATE">=TRUNC(SYSDATE@!,'fmyear'))
       filter(DECODE("DB"."EV_ID",1263421688,'COMPLETE',"DB"."STATUS_CODE")='COMPLETE' AND
              "DB"."EFFDATE"<=SYSDATE@! AND "DB"."EFFDATE">=TRUNC(SYSDATE@!,'fmyear'))
   6 - access("DB"."ID"="DD"."DSBA_ID" AND "DD"."IND_ID"=20972265 AND
              "DD"."GA_ID"='150563-01')

25 rows selected.

Elapsed: 00:00:00.03


SQL> SELECT SUM ( NVL ( dd.req_amt , 0 ) )
FROM DISB_A db ,
     DISB_B dd
WHERE db.id = dd.dsba_id
AND dd.nd_id = xxxxxxxx
AND dd.a_id = 'xx-xx'
AND DECODE (db.v_id , xxxxxxxxx , 'COMPLETE' , db.code ) = 'COMPLETE'
AND db.effdate BETWEEN TRUNC ( SYSDATE , 'YEAR' ) AND SYSDATE; 

SUM(NVL(DD.REQ_AMT,0))
______________________
                 62500

**1 row selected.
Elapsed: 00:00:05.45**

Question :

1. When the cost reduced actually the query should have come faster. But it took 6 seconds for the query to come with the result. Can you please explain if i am doing something wrong/is there any other way to improve the performance.

2. The number of rows picked during FULL TABLE SCAN before adding the additional index was 10 rows but after adding the additional index the number of rows jumped from 10 to 35.

Answer 1

Based on the assumption that or expansion probably isn't happening on it's own by just replacing the decode. And assuming that the driving table should be DISB_A (it would be a whole lot easier to drive the query from DISB_B so let us know if the standalone filters against DISB_B are effective at cutting down the number of rows visited). I would rewrite the query to (there may be typos):

select sum(req_amt) 
from (
SELECT SUM ( dd.req_amt  ) req_amt
FROM DISB_A db ,
     DISB_B dd
WHERE db.id = dd.dsba_id
AND dd.nd_id = :xxxxxxxx
AND dd.a_id = 'xx-xx'
AND db.effdate BETWEEN TRUNC ( SYSDATE , 'YEAR' ) AND SYSDATE
and db.v_id = :xxxxxxxxx 
union all
SELECT SUM ( dd.req_amt  ) req_amt
FROM DISB_A db ,
     DISB_B dd
WHERE db.id = dd.dsba_id
AND dd.nd_id = :xxxxxxxx
AND dd.a_id = 'xx-xx'
AND db.effdate BETWEEN TRUNC ( SYSDATE , 'YEAR' ) AND SYSDATE
and (db.v_id is null and :xxxxxxxxx is null)
union all
SELECT SUM ( dd.req_amt  ) req_amt
FROM DISB_A db ,
     DISB_B dd
WHERE db.id = dd.dsba_id
AND dd.nd_id = :xxxxxxxx
AND dd.a_id = 'xx-xx'
AND db.effdate BETWEEN TRUNC ( SYSDATE , 'YEAR' ) AND SYSDATE
and  db.code = 'COMPLETE'
and  lnnvl(db.v_id = :xxxxxxxxx )
    )

And create the following indexes:

create index DISB_A_idx_v on DISB_A (v_id, effdate);
create index DISB_A_idx_code on DISB_A (code, effdate);
create index DISB_B_idx on DISB_B (nd_id,a_id,dsba_id);

If this is still relatively slow then it would be helpful to have some feedback about how selective these filters really are. Have a look at how to get row source execution statistics inside the plan (point 4 of https://ctandrewsayer.wordpress.com/2017/03/21/4-easy-lessons-to-enhance-your-performance-diagnostics/) and include that information in your original question.