Skip to Content
0
Oct 07, 2010 at 06:45 PM

Indexes making program run slower...

133 Views

With the indexes, the program runs more slowly, much more slowly than when the indexes aren't there. However, the explain function to me indicates that with the indexes, it should be much better.

The explain when the indexes are present is:

SQL Statement
------------------------------------------------------------------------
SELECT
  T_01 . "MANDT" , T_01 . "VBELN" , T_01 . "VFDAT" , T_02 . "BUKRS" ,
  T_00 . "VKORG" ,   T_02 . "VKORG" , T_00 . "VBELN" , T_03 . "BUKRS"
FROM
 "LIPS" T_01 INNER JOIN "LIKP" T_00 ON T_01 . "MANDT" = :A0 AND T_00 . "VBELN" = T_01 . "VBELN"
  INNER JOIN "TVKO" T_02 ON T_02 . "MANDT" = :A1 AND T_00 . "VKORG" = T_02 . "VKORG" INNER JOIN "T9F
  ATLAS_CCO" T_03   ON T_03 . "MANDT" = :A2 AND T_02 . "BUKRS" = T_03 . "BUKRS"
WHERE
  T_00 . "MANDT" = :A3 AND ( T_01 . "ERDAT" <= :A4 OR T_01 . "AEDAT" <= :A5 )   AND ( ( T_01 .
  "ERDAT" >= :A6 OR T_01 . "AEDAT" <= :A7 )                        AND ( ( T_01 .   "ERDAT" <= :A8
  OR T_01 . "AEDAT" >= :A9 )                        AND ( T_01 . "ERDAT" >= :A10 OR   T_01 .
  "AEDAT" >= :A11 ) ) )


Execution Plan

--------------------------------------------------------------------------------------
| Id  | Operation                       | Name           | Rows  | Bytes | Cost (%CPU)|
---------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                |                |  4632 |  1248K| 73711   (3)|
|   1 |  CONCATENATION                  |                |       |       |            |
|   2 |   NESTED LOOPS                  |                |  2375 |   640K| 41855   (3)|
|*  3 |    HASH JOIN                    |                |  2375 |   619K| 41854   (3)|
|*  4 |     TABLE ACCESS FULL           | TVKO           |    59 |   826 |     2   (0)|
|   5 |     NESTED LOOPS                |                |  2558 |   632K| 41851   (3)|
|*  6 |      TABLE ACCESS BY INDEX ROWID| LIPS           |  2558 |   582K| 40814   (3)|
|*  7 |       INDEX RANGE SCAN          | LIPS~Z04       |   496K|       |   492   (7)|
|   8 |      TABLE ACCESS BY INDEX ROWID| LIKP           |     1 |    20 |     0   (0)|
|*  9 |       INDEX UNIQUE SCAN         | LIKP~0         |     1 |       |     0   (0)|
|* 10 |    INDEX UNIQUE SCAN            | T9FATLAS_CCO~0 |     1 |     9 |     0   (0)|
|  11 |   NESTED LOOPS                  |                |  2257 |   608K| 31856   (4)|
|* 12 |    HASH JOIN                    |                |  2257 |   588K| 31856   (4)|
|* 13 |     TABLE ACCESS FULL           | TVKO           |    59 |   826 |     2   (0)|
|  14 |     NESTED LOOPS                |                |  2430 |   600K| 31853   (4)|
|* 15 |      TABLE ACCESS BY INDEX ROWID| LIPS           |  2430 |   552K| 30868   (4)|
|* 16 |       INDEX RANGE SCAN          | LIPS~Z03       |   496K|       |   495   (7)|
|  17 |      TABLE ACCESS BY INDEX ROWID| LIKP           |     1 |    20 |     0   (0)|
|* 18 |       INDEX UNIQUE SCAN         | LIKP~0         |     1 |       |     0   (0)|
|* 19 |    INDEX UNIQUE SCAN            | T9FATLAS_CCO~0 |     1 |     9 |     0   (0)|
-----------------------------------------------

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

   3 - access("T_00"."VKORG"="T_02"."VKORG")
   4 - filter("T_02"."MANDT"=:A1)
   6 - filter(("T_01"."ERDAT">=:A6 OR "T_01"."AEDAT"<=:A7) AND
              ("T_01"."ERDAT"<=:A8 OR "T_01"."AEDAT">=:A9) AND ("T_01"."ERDAT">=:A10 OR
              "T_01"."AEDAT">=:A11))
   7 - access("T_01"."MANDT"=:A0 AND "T_01"."AEDAT"<=:A5)
   9 - access("T_00"."MANDT"=:A3 AND "T_00"."VBELN"="T_01"."VBELN")
  10 - access("T_03"."MANDT"=:A2 AND "T_02"."BUKRS"="T_03"."BUKRS")
  12 - access("T_00"."VKORG"="T_02"."VKORG")
  13 - filter("T_02"."MANDT"=:A1)
  15 - filter(("T_01"."ERDAT">=:A10 OR "T_01"."AEDAT">=:A11) AND
              ("T_01"."ERDAT"<=:A8 OR "T_01"."AEDAT">=:A9) AND ("T_01"."ERDAT">=:A6 OR
              "T_01"."AEDAT"<=:A7) AND LNNVL("T_01"."AEDAT"<=:A5))
  16 - access("T_01"."MANDT"=:A0 AND "T_01"."ERDAT"<=:A4)
  18 - access("T_00"."MANDT"=:A3 AND "T_00"."VBELN"="T_01"."VBELN")
  19 - access("T_03"."MANDT"=:A2 AND "T_02"."BUKRS"="T_03"."BUKRS")