This discussion is archived
1 2 Previous Next 27 Replies Latest reply: Nov 25, 2009 12:26 PM by Randolf Geist Go to original post RSS
  • 15. Re: I have to deactivate the "_OPTIMIZER_COST_BASED_TRANSFORMATION". WHY????
    CharlesHooper Expert
    Currently Being Moderated
    Randolf,

    Nice explanation(s).

    Personally, I would not say that Oracle is ignoring the hints in the VCRDAL view with CBQT disabled, but perhaps that the hints are "lost/discarded" when the view containing those hints is merged. But I could be wrong in this statement - it could be that the hints are preserved but become invalid when the view is merged. It might be interesting to see the section of a 10053 trace that shows the hints and which hints could not be used (this probably will not help the OP in this specific case, but might be interesting for other analyses - Joze's follow-up comment has me a little concerned about what might happen if the hints are preserved after the view is merged).

    At the time of my last post in this thread I had not yet started looking at ID 1-5 in the plan containing the analytic function as that portion of the plan seemed to be similar in both the fast and slow plans (other than the extra information exposed by Joze's suggestion to add a GATHER_PLAN_STATISTICS hint: A-Rows, A-Time, Buffers, Reads, Writes). I am certain that I would not have been able to state that the RANK() analytic function appeared in a subquery located in a column position of the view. What did you see in the plan that told you that was happening? Was it the lack of a nested loop/hash/Cartesian join between ID 1 and 6?

    Charles Hooper
    IT Manager/Oracle DBA
    K&M Machine-Fabricating, Inc.
  • 16. Re: I have to deactivate the "_OPTIMIZER_COST_BASED_TRANSFORMATION". WHY????
    705249 Newbie
    Currently Being Moderated
    Effectively the hint is the issue.
    There were a lot of hint in this query. I had suppressed all the hints but I had forgotten this hint on the VCRDAL view.

    Here is the new explain plan :
    1     Plan hash value: 2398373518
    2      
    3     ------------------------------------------------------------------------------------------------------------------------------
    4     | Id  | Operation                                         | Name             | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
    5     ------------------------------------------------------------------------------------------------------------------------------
    6     |   0 | SELECT STATEMENT                                  |                  |    19 |  2907 |       |   166K  (2)| 00:33:20 |
    7     |   1 |  SORT AGGREGATE                                   |                  |     1 |    88 |       |            |          |
    8     |*  2 |   VIEW                                            |                  |     1 |    88 |       |     7  (15)| 00:00:01 |
    9     |*  3 |    WINDOW SORT PUSHED RANK                        |                  |     1 |    51 |       |     7  (15)| 00:00:01 |
    10     |*  4 |     TABLE ACCESS BY INDEX ROWID                   | HISMVC           |     1 |    51 |       |     6   (0)| 00:00:01 |
    11     |*  5 |      INDEX RANGE SCAN                             | HISMVC1          |     6 |       |       |     3   (0)| 00:00:01 |
    12     |   6 |  SORT GROUP BY                                    |                  |    19 |  2907 |       |   166K  (2)| 00:33:20 |
    13     |   7 |   NESTED LOOPS                                    |                  |    19 |  2907 |       |   166K  (2)| 00:33:20 |
    14     |   8 |    NESTED LOOPS                                   |                  |    19 |  2793 |       |   166K  (2)| 00:33:20 |
    15     |   9 |     NESTED LOOPS OUTER                            |                  |    19 |  2527 |       |   166K  (2)| 00:33:19 |
    16     |  10 |      NESTED LOOPS OUTER                           |                  |    19 |  2223 |       |   166K  (2)| 00:33:19 |
    17     |  11 |       NESTED LOOPS OUTER                          |                  |    19 |  2147 |       |   166K  (2)| 00:33:19 |
    18     |* 12 |        HASH JOIN                                  |                  |    19 |  2071 |       |   166K  (2)| 00:33:19 |
    19     |  13 |         VIEW                                      |                  |    37 |  3552 |       |   166K  (2)| 00:33:19 |
    20     |* 14 |          HASH JOIN RIGHT OUTER                    |                  |    37 | 11988 |       |   166K  (2)| 00:33:19 |
    21     |* 15 |           INDEX FAST FULL SCAN                    | RGCCAL1          |    45 |   495 |       |     2   (0)| 00:00:01 |
    22     |  16 |           NESTED LOOPS OUTER                      |                  |    37 | 11581 |       |   166K  (2)| 00:33:19 |
    23     |* 17 |            HASH JOIN OUTER                        |                  |    37 | 11248 |       |   166K  (2)| 00:33:19 |
    24     |* 18 |             HASH JOIN RIGHT OUTER                 |                  |    37 | 10767 |       |   166K  (2)| 00:33:19 |
    25     |  19 |              TABLE ACCESS FULL                    | COMPTE           |   308 |  3696 |       |     5   (0)| 00:00:01 |
    26     |  20 |              NESTED LOOPS                         |                  |    37 | 10323 |       |   166K  (2)| 00:33:19 |
    27     |  21 |               NESTED LOOPS OUTER                  |                  |    37 | 10175 |       |   166K  (2)| 00:33:19 |
    28     |  22 |                NESTED LOOPS OUTER                 |                  |    37 | 10064 |       |   166K  (2)| 00:33:19 |
    29     |  23 |                 NESTED LOOPS OUTER                |                  |    37 |  9731 |       |   166K  (2)| 00:33:19 |
    30     |  24 |                  NESTED LOOPS OUTER               |                  |    37 |  9398 |       |   166K  (2)| 00:33:19 |
    31     |  25 |                   NESTED LOOPS OUTER              |                  |    37 |  9065 |       |   166K  (2)| 00:33:19 |
    32     |  26 |                    NESTED LOOPS OUTER             |                  |    37 |  8732 |       |   166K  (2)| 00:33:19 |
    33     |  27 |                     NESTED LOOPS OUTER            |                  |    37 |  8399 |       |   166K  (2)| 00:33:19 |
    34     |* 28 |                      HASH JOIN                    |                  |    37 |  8066 |       |   166K  (2)| 00:33:19 |
    35     |* 29 |                       HASH JOIN                   |                  |   129 | 17286 |       | 35145   (2)| 00:07:02 |
    36     |  30 |                        TABLE ACCESS BY INDEX ROWID| HISNEG           | 14478 |   975K|       |  7220   (1)| 00:01:27 |
    37     |* 31 |                         INDEX RANGE SCAN          | HISNEG4          | 14478 |       |       |    42   (0)| 00:00:01 |
    38     |* 32 |                        TABLE ACCESS FULL          | IHSDEP           | 32994 |  2094K|       | 27925   (2)| 00:05:36 |
    39     |  33 |                       VIEW                        | VCRDAL2          |  3589K|   287M|       |   131K  (1)| 00:26:17 |
    40     |  34 |                        HASH GROUP BY              |                  |  3589K|   314M|   719M|   131K  (1)| 00:26:17 |
    41     |* 35 |                         HASH JOIN                 |                  |  3589K|   314M|       | 55217   (2)| 00:11:03 |
    42     |* 36 |                          TABLE ACCESS FULL        | TYPSCR           |    14 |   112 |       |     5   (0)| 00:00:01 |
    43     |* 37 |                          HASH JOIN                |                  |  2564K|   205M|       | 55190   (2)| 00:11:03 |
    44     |  38 |                           TABLE ACCESS FULL       | PARMAR           |    72 |   648 |       |     5   (0)| 00:00:01 |
    45     |* 39 |                           HASH JOIN               |                  |  2564K|   183M|       | 55169   (2)| 00:11:03 |
    46     |  40 |                            MERGE JOIN CARTESIAN   |                  |    44 |   484 |       |    10   (0)| 00:00:01 |
    47     |  41 |                             TABLE ACCESS FULL     | RENGEN           |     1 |     4 |       |     5   (0)| 00:00:01 |
    48     |  42 |                             BUFFER SORT           |                  |    44 |   308 |       |     5   (0)| 00:00:01 |
    49     |  43 |                              TABLE ACCESS FULL    | DEVISE           |    44 |   308 |       |     5   (0)| 00:00:01 |
    50     |* 44 |                            TABLE ACCESS FULL      | HISMVC           |  2564K|   156M|       | 55143   (2)| 00:11:02 |
    51     |* 45 |                      INDEX UNIQUE SCAN            | INTERV1          |     1 |     9 |       |     0   (0)| 00:00:01 |
    52     |* 46 |                     INDEX UNIQUE SCAN             | INTERV1          |     1 |     9 |       |     0   (0)| 00:00:01 |
    53     |* 47 |                    INDEX UNIQUE SCAN              | INTERV1          |     1 |     9 |       |     0   (0)| 00:00:01 |
    54     |* 48 |                   INDEX UNIQUE SCAN               | INTERV1          |     1 |     9 |       |     0   (0)| 00:00:01 |
    55     |* 49 |                  INDEX UNIQUE SCAN                | INTERV1          |     1 |     9 |       |     0   (0)| 00:00:01 |
    56     |* 50 |                 INDEX UNIQUE SCAN                 | INTERV1          |     1 |     9 |       |     0   (0)| 00:00:01 |
    57     |* 51 |                INDEX UNIQUE SCAN                  | PORTEF2          |     1 |     3 |       |     0   (0)| 00:00:01 |
    58     |* 52 |               INDEX UNIQUE SCAN                   | COMPTE3          |     1 |     4 |       |     0   (0)| 00:00:01 |
    59     |  53 |             VIEW                                  | index$_join$_028 |   379 |  4927 |       |     3   (0)| 00:00:01 |
    60     |* 54 |              HASH JOIN                            |                  |       |       |       |            |          |
    61     |  55 |               INDEX FAST FULL SCAN                | INTERV1          |   379 |  4927 |       |     1   (0)| 00:00:01 |
    62     |  56 |               INDEX FAST FULL SCAN                | INTERV3          |   379 |  4927 |       |     1   (0)| 00:00:01 |
    63     |* 57 |            INDEX UNIQUE SCAN                      | INTERV1          |     1 |     9 |       |     0   (0)| 00:00:01 |
    64     |  58 |         TABLE ACCESS FULL                         | INTERV           |   379 |  4927 |       |     5   (0)| 00:00:01 |
    65     |* 59 |        INDEX UNIQUE SCAN                          | SIEGE1           |     1 |     4 |       |     0   (0)| 00:00:01 |
    66     |* 60 |       INDEX UNIQUE SCAN                           | MOIECH1          |     1 |     4 |       |     0   (0)| 00:00:01 |
    67     |* 61 |      INDEX UNIQUE SCAN                            | COMPTE1          |     1 |    16 |       |     0   (0)| 00:00:01 |
    68     |  62 |     TABLE ACCESS BY INDEX ROWID                   | NATACF           |     1 |    14 |       |     1   (0)| 00:00:01 |
    69     |* 63 |      INDEX UNIQUE SCAN                            | NATACF1          |     1 |       |       |     0   (0)| 00:00:01 |
    70     |* 64 |    INDEX UNIQUE SCAN                              | OBJCON1          |     1 |     6 |       |     0   (0)| 00:00:01 |
    71     ------------------------------------------------------------------------------------------------------------------------------
    72      
    73     Predicate Information (identified by operation id):
    74     ---------------------------------------------------
    75      
    76        2 - filter("H"."RK"=1)
    77        3 - filter(RANK() OVER ( PARTITION BY "H"."DATRA","H"."DAVAL","H"."NUBIX","H"."NUFDP","H"."CDCOF","H"."NTUBX" 
    78                   ORDER BY "H"."CTCOF")<=1)
    79        4 - filter("H"."DAVAL"=:B1 AND "H"."DATRA"=:B2 AND "H"."NCCOF"=11 AND "H"."CDCOF"=:B3)
    80        5 - access("H"."NUBIX"=:B1 AND "H"."NUFDP"=:B2)
    81       12 - access("I"."COINT"="X"."COINL")
    82       14 - access("R"."COINT"(+)="H"."COINL" AND "R"."COMAR"(+)="H"."COMAR")
    83       15 - filter("R"."COMAR"(+)='ICE')
    84       17 - access("I"."IDINT"(+)="H"."ID_COINC")
    85       18 - access("C"."ID_NUCPT"(+)="H"."ID_NUCPT")
    86       28 - access("V"."NUBIX"="H"."NUBIX" AND "V"."NUFDP"="H"."NUFDP")
    87            filter("V"."MTHEX"<>0 OR "H"."CTFDE"='N')
    88       29 - access("Z"."NUBIX"="H"."NUBIX" AND "Z"."DATRA"="H"."DATRA")
    89       31 - access("Z"."DATOP">=TO_DATE(' 2009-11-02 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND "Z"."DATOP"<=TO_DATE(' 
    90                   2009-11-08 23:59:59', 'syyyy-mm-dd hh24:mi:ss'))
    91       32 - filter("H"."COINL" IS NOT NULL AND "H"."COMAR"='ICE' AND "H"."NUORD" LIKE '%NY%' AND ("H"."CTFDE"='A' OR 
    92                   "H"."CTFDE"='E' OR "H"."CTFDE"='L' OR "H"."CTFDE"='N' OR "H"."CTFDE"='S'))
    93       35 - access("S"."CTCOF"="X"."CTCOF")
    94       36 - filter("S"."COLAN"='A')
    95       37 - access("P"."COMAR"="X"."COMAR")
    96       39 - access("D"."CODEV"="X"."CDCOF")
    97       44 - filter("X"."NCCOF"=11)
    98       45 - access("O"."COINT"(+)="H"."COINA")
    99       46 - access("M"."COINT"(+)="H"."COINV")
    100       47 - access("L"."COINT"(+)="H"."COINI")
    101       48 - access("N"."COINT"(+)="Z"."COINK")
    102       49 - access("K"."COINT"(+)="Z"."COINN")
    103       50 - access("J"."COINT"(+)="Z"."COINF")
    104       51 - access("P"."ID_COPOR"(+)="H"."ID_COPOR")
    105       52 - access("A"."ID_NUCPT"="H"."ID_NUCPI")
    106       54 - access(ROWID=ROWID)
    107       57 - access("I"."COINT"(+)="I"."COINT")
    108       59 - access("I"."COSIE"="S"."COSIE"(+))
    109       60 - access("M"."CMECH"(+)="X"."CMECH")
    110       61 - access("C"."COINT"(+)="X"."COINC" AND "C"."NUCPT"(+)="X"."NUCPT")
    111       63 - access("A"."CNACT"="X"."CNACT")
    112       64 - access("O"."COBCN"="A"."COBCN")
    113      
    114     Note
    115     -----
    116        - 'PLAN_TABLE' is old version
    117        - dynamic sampling used for this statement
    The COST is better (166K < 10M).
    However the query took more time to complete (9 minutes instead of 3 secondes)
    SQL> ALTER session SET "_optimizer_cost_based_transformation" = OFF;
    
    Session altered.
    
    Elapsed: 00:00:00.00
    SQL> SELECT '02/11/09','08/11/09',X.COINL,X.CNACT,SUM(X.QTFDP*X.ISCHC),X.CODEV,SUM(X.MTHEX),SUM(X.MTVEX),SUM(X.MTTGL),0
      2  FROM TOTO X
      3  WHERE   X.DATOP>=TO_DATE('02112009 00:00:00','DDMMYYYY HH24:MI:SS')  AND   X.DATOP<=TO_DATE('08112009 23:59:59','DDMMYYYY HH24:MI:SS')
      4  AND   X.COMAR='ICE'  AND   X.NUORD LIKE '%NY%'
      5  GROUP BY X.COINL,X.CNACT,X.CODEV ORDER BY 3,6,4;
    
    '02/11/0 '08/11/0 COINL    CNACT    SUM(X.QTFDP*X.ISCHC) COD SUM(X.MTHEX) SUM(X.MTVEX) SUM(X.MTTGL)       0
    -------- -------- -------- -------- -------------------- --- ------------ ------------ ------------ ----------
    02/11/09 08/11/09 VMLN AM  WBSFICE                   784 USD         1176            0         1176       0
    
    Elapsed: 00:00:03.01
    SQL> ALTER session SET "_optimizer_cost_based_transformation" = LINEAR;
    
    Session altered.
    
    Elapsed: 00:00:00.00
    SQL> SELECT '02/11/09','08/11/09',X.COINL,X.CNACT,SUM(X.QTFDP*X.ISCHC),X.CODEV,SUM(X.MTHEX),SUM(X.MTVEX),SUM(X.MTTGL),0
      2  FROM TOTO X
      3  WHERE   X.DATOP>=TO_DATE('02112009 00:00:00','DDMMYYYY HH24:MI:SS')  AND   X.DATOP<=TO_DATE('08112009 23:59:59','DDMMYYYY HH24:MI:SS')
      4  AND   X.COMAR='ICE'  AND   X.NUORD LIKE '%NY%'
      5  GROUP BY X.COINL,X.CNACT,X.CODEV ORDER BY 3,6,4;
    
    '02/11/0 '08/11/0 COINL    CNACT    SUM(X.QTFDP*X.ISCHC) COD SUM(X.MTHEX) SUM(X.MTVEX) SUM(X.MTTGL)       0
    -------- -------- -------- -------- -------------------- --- ------------ ------------ ------------ ----------
    02/11/09 08/11/09 VMLN AM  WBSFICE                   784 USD         1176            0         1176       0
    
    Elapsed: 00:09:03.08
  • 17. Re: I have to deactivate the "_OPTIMIZER_COST_BASED_TRANSFORMATION". WHY????
    705249 Newbie
    Currently Being Moderated
    here is the execution stats (the view seems to not be merged):
    SQL> select * from table(dbms_xplan.display_cursor(NULL,NULL, 'allstats last'));
    
    PLAN_TABLE_OUTPUT
    -------------------------------------------------------------------------------------------------------------------------------------------------
    -------------------------------------------------------
    SQL_ID  98azu6fmrzpxa, child number 2
    -------------------------------------
    SELECT '02/11/09','08/11/09',X.COINL,X.CNACT,SUM(X.QTFDP*X.ISCHC),X.CODEV,SUM(X.MTHEX),SUM(X.MTVEX),SUM(X.MTTGL),0 FROM TOTO X WHERE   X.DATOP>=T
    O_DATE('02112009
    00:00:00','DDMMYYYY HH24:MI:SS')  AND   X.DATOP<=TO_DATE('08112009 23:59:59','DDMMYYYY HH24:MI:SS') AND   X.COMAR='ICE'  AND   X.NUORD LIKE '%NY%
    ' GROUP BY
    X.COINL,X.CNACT,X.CODEV ORDER BY 3,6,4
    
    Plan hash value: 2398373518
    
    -------------------------------------------------------------------------------------------------------------------------------------------------
    --------------------------------------
    | Id  | Operation                                         | Name             | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Reads  | Writes
    |  OMem |  1Mem | Used-Mem | Used-Tmp|
    -------------------------------------------------------------------------------------------------------------------------------------------------
    --------------------------------------
    |   1 |  SORT AGGREGATE                                   |                  |   1999K|      1 |   1999K|00:09:20.54 |      11M|   5245K|      0
    |        |       |          |         |
    |*  2 |   VIEW                                            |                  |   1999K|      1 |   2099K|00:09:12.83 |      11M|   5245K|      0
    |        |       |          |         |
    |*  3 |    WINDOW SORT PUSHED RANK                        |                  |   1999K|      1 |   2099K|00:09:07.94 |      11M|   5245K|      0
    |  2048 |  2048 | 2048  (0)|          |
    |*  4 |     TABLE ACCESS BY INDEX ROWID                   | HISMVC           |   1999K|      1 |   2099K|00:08:40.27 |      11M|   5245K|      0
    |        |       |          |         |
    |*  5 |      INDEX RANGE SCAN                             | HISMVC1          |   1999K|      6 |     10M|00:02:59.65 |    4026K|   1297K|      0
    |        |       |          |         |
    |   6 |  SORT GROUP BY                                    |                  |      1 |     19 |      1 |00:04:33.36 |      11M|   5631K|   5456
    |  2048 |  2048 | 2048  (0)|          |
    |   7 |   NESTED LOOPS                                    |                  |      1 |     19 |      3 |00:04:33.36 |      11M|   5631K|   5456
    |        |       |          |         |
    |   8 |    NESTED LOOPS                                   |                  |      1 |     19 |      3 |00:04:33.35 |      11M|   5631K|   5456
    |        |       |          |         |
    |   9 |     NESTED LOOPS OUTER                            |                  |      1 |     19 |      3 |00:04:33.35 |      11M|   5631K|   5456
    |        |       |          |         |
    |  10 |      NESTED LOOPS OUTER                           |                  |      1 |     19 |      3 |00:04:33.35 |      11M|   5631K|   5456
    |        |       |          |         |
    |  11 |       NESTED LOOPS OUTER                          |                  |      1 |     19 |      3 |00:04:33.35 |      11M|   5631K|   5456
    |        |       |          |         |
    |* 12 |        HASH JOIN                                  |                  |      1 |     19 |      3 |00:04:33.35 |      11M|   5631K|   5456
    |   837K|   837K|  362K (0)|          |
    |  13 |         VIEW                                      |                  |      1 |     37 |      3 |00:04:33.34 |      11M|   5631K|   5456
    |        |       |          |         |
    |* 14 |          HASH JOIN RIGHT OUTER                    |                  |      1 |     37 |      3 |00:04:33.34 |      11M|   5631K|   5456
    |  1155K|  1155K| 1164K (0)|          |
    |* 15 |           INDEX FAST FULL SCAN                    | RGCCAL1          |      1 |     45 |     45 |00:00:00.01 |       3 |      1 |      0
    |        |       |          |         |
    |  16 |           NESTED LOOPS OUTER                      |                  |      1 |     37 |      3 |00:04:33.34 |      11M|   5631K|   5456
    |        |       |          |         |
    |* 17 |            HASH JOIN OUTER                        |                  |      1 |     37 |      3 |00:04:33.34 |      11M|   5631K|   5456
    |   825K|   825K|  360K (0)|          |
    |* 18 |             HASH JOIN RIGHT OUTER                 |                  |      1 |     37 |      3 |00:04:33.32 |      11M|   5631K|   5456
    |  1155K|  1155K| 1183K (0)|          |
    |  19 |              TABLE ACCESS FULL                    | COMPTE           |      1 |    308 |    309 |00:00:00.02 |      15 |     14 |      0
    |        |       |          |         |
    |  20 |              NESTED LOOPS                         |                  |      1 |     37 |      3 |00:04:33.32 |      11M|   5631K|   5456
    |        |       |          |         |
    |  21 |               NESTED LOOPS OUTER                  |                  |      1 |     37 |      3 |00:04:33.31 |      11M|   5631K|   5456
    |        |       |          |         |
    |  22 |                NESTED LOOPS OUTER                 |                  |      1 |     37 |      3 |00:04:33.31 |      11M|   5631K|   5456
    |        |       |          |         |
    |  23 |                 NESTED LOOPS OUTER                |                  |      1 |     37 |      3 |00:04:33.31 |      11M|   5631K|   5456
    |        |       |          |         |
    |  24 |                  NESTED LOOPS OUTER               |                  |      1 |     37 |      3 |00:04:33.31 |      11M|   5631K|   5456
    |        |       |          |         |
    |  25 |                   NESTED LOOPS OUTER              |                  |      1 |     37 |      3 |00:04:33.31 |      11M|   5631K|   5456
    |        |       |          |         |
    |  26 |                    NESTED LOOPS OUTER             |                  |      1 |     37 |      3 |00:04:33.30 |      11M|   5631K|   5456
    |        |       |          |         |
    |  27 |                     NESTED LOOPS OUTER            |                  |      1 |     37 |      3 |00:04:33.30 |      11M|   5631K|   5456
    |        |       |          |         |
    |* 28 |                      HASH JOIN                    |                  |      1 |     37 |      3 |00:04:33.30 |      11M|   5631K|   5456
    |   789K|   789K|  575K (0)|          |
    |* 29 |                       HASH JOIN                   |                  |      1 |    129 |      3 |00:00:09.35 |     140K|    128K|      0
    |  2868K|  1148K| 3220K (0)|          |
    |  30 |                        TABLE ACCESS BY INDEX ROWID| HISNEG           |      1 |  14478 |  32487 |00:00:01.01 |   12877 |    897 |      0
    |        |       |          |         |
    |* 31 |                         INDEX RANGE SCAN          | HISNEG4          |      1 |  14478 |  32487 |00:00:00.12 |     100 |    100 |      0
    |        |       |          |         |
    |* 32 |                        TABLE ACCESS FULL          | IHSDEP           |      1 |  32994 |   4141 |00:00:08.26 |     127K|    127K|      0
    |        |       |          |         |
    |  33 |                       VIEW                        | VCRDAL2          |      1 |   3589K|   1999K|00:10:09.27 |      11M|   5503K|   5456
    |        |       |          |         |
    |  34 |                        HASH GROUP BY              |                  |      1 |   3589K|   1999K|00:00:39.49 |     252K|    257K|   5456
    |   145M|  8302K|  105M (1)|    45056 |
    |* 35 |                         HASH JOIN                 |                  |      1 |   3589K|   2099K|00:00:27.37 |     252K|    252K|      0
    |  1348K|  1348K| 1231K (0)|          |
    |* 36 |                          TABLE ACCESS FULL        | TYPSCR           |      1 |     14 |     14 |00:00:00.01 |      15 |     14 |      0
    |        |       |          |         |
    |* 37 |                          HASH JOIN                |                  |      1 |   2564K|   2099K|00:00:25.26 |     252K|    252K|      0
    |  1179K|  1179K| 1233K (0)|          |
    |  38 |                           TABLE ACCESS FULL       | PARMAR           |      1 |     72 |     72 |00:00:00.01 |      15 |     14 |      0
    |        |       |          |         |
    |* 39 |                           HASH JOIN               |                  |      1 |   2564K|   2099K|00:00:21.04 |     252K|    252K|      0
    |  1155K|  1155K| 1215K (0)|          |
    |  40 |                            MERGE JOIN CARTESIAN   |                  |      1 |     44 |     44 |00:00:00.01 |      30 |     14 |      0
    |        |       |          |         |
    |  41 |                             TABLE ACCESS FULL     | RENGEN           |      1 |      1 |      1 |00:00:00.01 |      15 |     14 |      0
    |        |       |          |         |
    |  42 |                             BUFFER SORT           |                  |      1 |     44 |     44 |00:00:00.01 |      15 |      0 |      0
    |  2048 |  2048 | 2048  (0)|          |
    |  43 |                              TABLE ACCESS FULL    | DEVISE           |      1 |     44 |     44 |00:00:00.01 |      15 |      0 |      0
    |        |       |          |         |
    |* 44 |                            TABLE ACCESS FULL      | HISMVC           |      1 |   2564K|   2099K|00:00:18.93 |     252K|    252K|      0
    |        |       |          |         |
    |* 45 |                      INDEX UNIQUE SCAN            | INTERV1          |      3 |      1 |      0 |00:00:00.01 |       0 |      0 |      0
    |        |       |          |         |
    |* 46 |                     INDEX UNIQUE SCAN             | INTERV1          |      3 |      1 |      0 |00:00:00.01 |       0 |      0 |      0
    |        |       |          |         |
    |* 47 |                    INDEX UNIQUE SCAN              | INTERV1          |      3 |      1 |      3 |00:00:00.01 |       2 |      2 |      0
    |        |       |          |         |
    |* 48 |                   INDEX UNIQUE SCAN               | INTERV1          |      3 |      1 |      0 |00:00:00.01 |       0 |      0 |      0
    |        |       |          |         |
    |* 49 |                  INDEX UNIQUE SCAN                | INTERV1          |      3 |      1 |      3 |00:00:00.01 |       2 |      0 |      0
    |        |       |          |         |
    |* 50 |                 INDEX UNIQUE SCAN                 | INTERV1          |      3 |      1 |      3 |00:00:00.01 |       2 |      0 |      0
    |        |       |          |         |
    |* 51 |                INDEX UNIQUE SCAN                  | PORTEF2          |      3 |      1 |      0 |00:00:00.01 |       0 |      0 |      0
    |        |       |          |         |
    |* 52 |               INDEX UNIQUE SCAN                   | COMPTE3          |      3 |      1 |      3 |00:00:00.01 |       2 |      2 |      0
    |        |       |          |         |
    |  53 |             VIEW                                  | index$_join$_028 |      1 |    379 |    382 |00:00:00.02 |       6 |      3 |      0
    |        |       |          |         |
    |* 54 |              HASH JOIN                            |                  |      1 |        |    382 |00:00:00.02 |       6 |      3 |      0
    |  1023K|  1023K| 1483K (0)|          |
    |  55 |               INDEX FAST FULL SCAN                | INTERV1          |      1 |    379 |    382 |00:00:00.01 |       3 |      1 |      0
    |        |       |          |         |
    |  56 |               INDEX FAST FULL SCAN                | INTERV3          |      1 |    379 |    382 |00:00:00.01 |       3 |      2 |      0
    |        |       |          |         |
    |* 57 |            INDEX UNIQUE SCAN                      | INTERV1          |      3 |      1 |      0 |00:00:00.01 |       0 |      0 |      0
    |        |       |          |         |
    |  58 |         TABLE ACCESS FULL                         | INTERV           |      1 |    379 |    382 |00:00:00.01 |      15 |     14 |      0
    |        |       |          |         |
    |* 59 |        INDEX UNIQUE SCAN                          | SIEGE1           |      3 |      1 |      0 |00:00:00.01 |       0 |      0 |      0
    |        |       |          |         |
    |* 60 |       INDEX UNIQUE SCAN                           | MOIECH1          |      3 |      1 |      3 |00:00:00.01 |       2 |      0 |      0
    |        |       |          |         |
    |* 61 |      INDEX UNIQUE SCAN                            | COMPTE1          |      3 |      1 |      0 |00:00:00.01 |       0 |      0 |      0
    |        |       |          |         |
    |  62 |     TABLE ACCESS BY INDEX ROWID                   | NATACF           |      3 |      1 |      3 |00:00:00.01 |       8 |      0 |      0
    |        |       |          |         |
    |* 63 |      INDEX UNIQUE SCAN                            | NATACF1          |      3 |      1 |      3 |00:00:00.01 |       5 |      0 |      0
    |        |       |          |         |
    |* 64 |    INDEX UNIQUE SCAN                              | OBJCON1          |      3 |      1 |      3 |00:00:00.01 |       5 |      1 |      0
    |        |       |          |         |
    -------------------------------------------------------------------------------------------------------------------------------------------------
    --------------------------------------
    
    Predicate Information (identified by operation id):
    ---------------------------------------------------
    
       2 - filter("H"."RK"=1)
       3 - filter(RANK() OVER ( PARTITION BY "H"."DATRA","H"."DAVAL","H"."NUBIX","H"."NUFDP","H"."CDCOF","H"."NTUBX" ORDER BY "H"."CTCOF")<=1)
       4 - filter(("H"."DAVAL"=:B1 AND "H"."DATRA"=:B2 AND "H"."NCCOF"=11 AND "H"."CDCOF"=:B3))
       5 - access("H"."NUBIX"=:B1 AND "H"."NUFDP"=:B2)
      12 - access("I"."COINT"="X"."COINL")
      14 - access("R"."COINT"="H"."COINL" AND "R"."COMAR"="H"."COMAR")
      15 - filter("R"."COMAR"='ICE')
      17 - access("I"."IDINT"="H"."ID_COINC")
      18 - access("C"."ID_NUCPT"="H"."ID_NUCPT")
      28 - access("V"."NUBIX"="H"."NUBIX" AND "V"."NUFDP"="H"."NUFDP")
           filter(("V"."MTHEX"<>0 OR "H"."CTFDE"='N'))
      29 - access("Z"."NUBIX"="H"."NUBIX" AND "Z"."DATRA"="H"."DATRA")
      31 - access("Z"."DATOP">=TO_DATE(' 2009-11-02 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND "Z"."DATOP"<=TO_DATE(' 2009-11-08 23:59:59', 'syyyy-mm-d
    d hh24:mi:ss'))
      32 - filter(("H"."COINL" IS NOT NULL AND "H"."COMAR"='ICE' AND "H"."NUORD" LIKE '%NY%' AND INTERNAL_FUNCTION("H"."CTFDE")))
      35 - access("S"."CTCOF"="X"."CTCOF")
      36 - filter("S"."COLAN"='A')
      37 - access("P"."COMAR"="X"."COMAR")
      39 - access("D"."CODEV"="X"."CDCOF")
      44 - filter("X"."NCCOF"=11)
      45 - access("O"."COINT"="H"."COINA")
      46 - access("M"."COINT"="H"."COINV")
      47 - access("L"."COINT"="H"."COINI")
      48 - access("N"."COINT"="Z"."COINK")
      49 - access("K"."COINT"="Z"."COINN")
      50 - access("J"."COINT"="Z"."COINF")
      51 - access("P"."ID_COPOR"="H"."ID_COPOR")
      52 - access("A"."ID_NUCPT"="H"."ID_NUCPI")
      54 - access(ROWID=ROWID)
      57 - access("I"."COINT"="I"."COINT")
      59 - access("I"."COSIE"="S"."COSIE")
      60 - access("M"."CMECH"="X"."CMECH")
      61 - access("C"."COINT"="X"."COINC" AND "C"."NUCPT"="X"."NUCPT")
      63 - access("A"."CNACT"="X"."CNACT")
      64 - access("O"."COBCN"="A"."COBCN")
    
    Note
    -----
       - dynamic sampling used for this statement
  • 18. Re: I have to deactivate the "_OPTIMIZER_COST_BASED_TRANSFORMATION". WHY????
    Randolf Geist Oracle ACE Director
    Currently Being Moderated
    Charles Hooper wrote:
    Personally, I would not say that Oracle is ignoring the hints in the VCRDAL view with CBQT disabled, but perhaps that the hints are "lost/discarded" when the view containing those hints is merged. But I could be wrong in this statement - it could be that the hints are preserved but become invalid when the view is merged. It might be interesting to see the section of a 10053 trace that shows the hints and which hints could not be used (this probably will not help the OP in this specific case, but might be interesting for other analyses - Joze's follow-up comment has me a little concerned about what might happen if the hints are preserved after the view is merged).
    Charles,

    thanks, it was actually my intention to express something along that way - "ignore" was probably the wrong word here - as we know hints get usually not ignored.
    At the time of my last post in this thread I had not yet started looking at ID 1-5 in the plan containing the analytic function as that portion of the plan seemed to be similar in both the fast and slow plans (other than the extra information exposed by Joze's suggestion to add a GATHER_PLAN_STATISTICS hint: A-Rows, A-Time, Buffers, Reads, Writes). I am certain that I would not have been able to state that the RANK() analytic function appeared in a subquery located in a column position of the view. What did you see in the plan that told you that was happening? Was it the lack of a nested loop/hash/Cartesian join between ID 1 and 6?
    In recent Oracle versions nested subqueries in the projection clause get shown in the execution at the top-level prior to the actual execution plan of the main query part - you'll notice that both parts of the plan are on the same indentation level and there is no connecting operator.

    Regards,
    Randolf

    Oracle related stuff blog:
    http://oracle-randolf.blogspot.com/

    Co-author of the forthcoming "OakTable Expert Oracle Practices" book:
    http://www.apress.com/book/view/1430226684
    http://www.amazon.com/Expert-Oracle-Practices-Database-Administration/dp/1430226684
  • 19. Re: I have to deactivate the "_OPTIMIZER_COST_BASED_TRANSFORMATION". WHY????
    CharlesHooper Expert
    Currently Being Moderated
    Farenheiit,

    Do you know if system (CPU) statistics have been gathered? What do you see when you execute the following in SQL*Plus:
    SET PAGESIZE 1000
    COLUMN PNAME FORMAT A15
    COLUMN PVAL2 FORMAT A20
    
    SELECT PNAME,PVAL1,PVAL2 FROM SYS.AUX_STATS$;
    The following output shows a case where system statistics have not been gathered:
    PNAME                PVAL1 PVAL2
    --------------- ---------- ----------------
    STATUS                     COMPLETED
    DSTART                     07-11-2009 00:04
    DSTOP                      07-11-2009 07:43
    FLAGS                    0
    CPUSPEEDNW      2551.04281
    IOSEEKTIM               10
    IOTFRSPEED            4096
    SREADTIM
    MREADTIM
    CPUSPEED              2752
    MBRC
    MAXTHR
    SLAVETHR
    The following shows a case where the system statistics have been gathered, but possibly with not a heavy workload:
    PNAME                PVAL1 PVAL2
    --------------- ---------- ----------------
    STATUS                     COMPLETED
    DSTART                     08-01-2009 23:29
    DSTOP                      08-01-2009 23:29
    FLAGS                    1
    CPUSPEEDNW      2545.45455
    IOSEEKTIM               10
    IOTFRSPEED            4096
    SREADTIM              .427
    MREADTIM             3.782
    CPUSPEED              2664
    MBRC                    32
    MAXTHR            19181568
    SLAVETHR
    The subquery in the column position in the VCRDAL view may be a problem - you might try to eliminate that subquery. The subquery I am mentioning is this one:
    (SELECT ABS(SUM(DECODE(H.RK,1,DECODE(H.CTMVC,'N',0,'D',0,H.QTCOF*H.CSCOF),0)))
     FROM
     (SELECT H.DATRA,H.DAVAL,H.NUBIX,H.NUFDP,H.CDCOF,RANK() OVER(PARTITION BY H.DATRA,H.DAVAL,H.NUBIX,H.NUFDP,H.CDCOF,H.NTUBX ORDER BY H.CTCOF) RK,H.
    CTMVC,H.QTCOF,H.CSCOF
      FROM HISMVC H
      WHERE
      H.NCCOF=11
     ) H
     WHERE
     H.DATRA=X.DATRA AND
     H.DAVAL=X.DAVAL AND
     H.NUBIX=X.NUBIX AND
     H.NUFDP=X.NUFDP AND
     H.CDCOF=X.CDCOF AND
     H.RK=1
    ) QTCOF
    You might want to double-check, but if I were to re-write the above into an inline view, I would probably write it like this:
      (SELECT
        DATRA,
        DAVAL,
        NUBIX,
        NUFDP,
        CDCOF,
        ABS(SUM(NVL(DECODE(H.CTMVC,'N',0,'D',0,H.QTCOF*H.CSCOF),0),0)) QTCOF
      FROM
        (SELECT
          H.DATRA,
          H.DAVAL,
          H.NUBIX,
          H.NUFDP,
          H.CDCOF,
          ROW_NUMBER() OVER (PARTITION BY H.DATRA,H.DAVAL,H.NUBIX,H.NUFDP,H.CDCOF,H.NTUBX ORDER BY H.CTCOF) RK,
          H.CTMVC,
          H.QTCOF,
          H.CSCOF
        FROM
          HISMVC H
        WHERE
          H.NCCOF=11) H
      WHERE
        H.RK=1
        AND H.CTMVC NOT IN ('N','D')) H
    Note in the above that I have removed the following - we will need these later:
     H.DATRA=X.DATRA AND
     H.DAVAL=X.DAVAL AND
     H.NUBIX=X.NUBIX AND
     H.NUFDP=X.NUFDP AND
     H.CDCOF=X.CDCOF AND
    This is how the inline view would look in your VCRDAL view:
    SELECT
      X.COMAR,
      X.DATRA,
      X.DAVAL,
      X.NUBIX,
      X.NUFDP,
      X.CDCOF,
      H.QTCOF,
      SUM(/*RNDTRC*/DECODE(S.IDFEE,'O',ROUND(X.MTCOF*X.QTCOF*X.CSCOF,DECODE(R.NUSPF,825,0,2)),DECODE(P.ARRCO,0,ROUND(X.MTCOF*X.QTCOF*
    X.CSCOF,D.FACAR)                         ,TRUNC(X.MTCOF*
    X.QTCOF*X.CSCOF,D.FACAR)))),
    SUM(/*RNDTRC*/DECODE(S.IDFEE,'O',ROUND(X.MTCOF*X.QTCOF*X.CSCOF*(NVL(X.TTCOF,0)/100),DECODE(R.NUSPF,825,0,2)),DECODE(P.ARRTV,0,ROUND(X.MTCOF*X.QTC
    OF*X.CSCOF*(NVL(X.TTCOF,0)/100),D.FACAR),TRUNC(X.MTCOF*
    X.QTCOF*X.CSCOF*(NVL(X.TTCOF,0)/100),D.FACAR)))),
    0,0,
    SUM(/*RNDTRC*/DECODE(S.IDFEE,'O',ROUND(X.MTCOF*X.QTCOF*X.CSCOF,DECODE(R.NUSPF,825,0,2))                  ,DECODE(P.ARRCO,0,ROUND(X.MTCOF*X.QTCOF*
    X.CSCOF,D.FACAR)                         ,TRUNC(X.MTCOF*
    X.QTCOF*X.CSCOF,D.FACAR))))+
    SUM(/*RNDTRC*/DECODE(S.IDFEE,'O',ROUND(X.MTCOF*X.QTCOF*X.CSCOF*(NVL(X.TTCOF,0)/100),DECODE(R.NUSPF,825,0,2)),DECODE(P.ARRTV,0,ROUND(X.MTCOF*X.QTC
    OF*X.CSCOF*(NVL(X.TTCOF,0)/100),D.FACAR),TRUNC(X.MTCOF*
    X.QTCOF*X.CSCOF*(NVL(X.TTCOF,0)/100),D.FACAR)))),
    MIN(DECODE(X.DATRA,X.DAORI,1,DECODE(X.CTMVC,'N',0,'D',0,1)))
    FROM
      HISMVC X,
      PARMAR P,
      DEVISE D,
      TYPSCR S,
      RENGEN R,
      (SELECT
        DATRA,
        DAVAL,
        NUBIX,
        NUFDP,
        CDCOF,
        NCCOF,
        ABS(SUM(NVL(DECODE(H.CTMVC,'N',0,'D',0,H.QTCOF*H.CSCOF),0),0)) QTCOF
      FROM
        (SELECT
          H.DATRA,
          H.DAVAL,
          H.NUBIX,
          H.NUFDP,
          H.CDCOF,
          ROW_NUMBER() OVER (PARTITION BY H.DATRA,H.DAVAL,H.NUBIX,H.NUFDP,H.CDCOF,H.NTUBX ORDER BY H.CTCOF) RK,
          H.CTMVC,
          H.QTCOF,
          H.CSCOF,
          H.NCCOF
        FROM
          HISMVC H
        WHERE
          H.NCCOF=11) H
      WHERE
        H.RK=1
        AND H.CTMVC NOT IN ('N','D')) H
    WHERE
      X.NCCOF=11      
      AND P.COMAR=X.COMAR
      AND D.CODEV=X.CDCOF
      AND S.CTCOF=X.CTCOF
      AND S.COLAN='A'
      AND H.DATRA(+)=X.DATRA
      AND H.DAVAL(+)=X.DAVAL
      AND H.NUBIX(+)=X.NUBIX
      AND H.NUFDP(+)=X.NUFDP
      AND H.CDCOF(+)=X.CDCOF
      AND H.NCCOF(+)=X.NCCOF
    GROUP BY
      X.COMAR,
      X.DATRA,X.DAVAL,X.NUBIX,X.NUFDP,X.CDCOF;
    If it were me, I would then try to eliminate the VCRDAL view like this:
    CREATE OR REPLACE VIEW toto2
    (coinl, nminl, adinl1, adinl2, adinl3, adinl4, adinl5, colal, cosie, lisie, nubnl, coinf, coinn, coope, coint, nucpt, copor, idety, coini, comar, comsc, cnact, ntact, lnact, cnacn, cnama, caini, caloc, cdvac, ctacf, cobcn, ctstr, cnach, datra, datop, daval, csens, qtfdp, mtneg, mvneg, csopt, cmech, nmech, caech, mtsna, nuver, nuins, nucon, nubix, nufdp, nuord, nutic, ctfne, ctfic, coglo, cnaop, cntra, codev, mthex, mtvex, mthgx, mtvgx, mttgl, copro, coinv, coina, couti, ctcot, cotsj, coink, ischc, daecb, tytrn, saima, corig, rgcod, rgcid, mtref, daeca, cotal, cotax, cotaf, cotan, cotai, cotav, cotak, cotaa, info1, guref, rgcof, rgcif)
    AS
    SELECT 
    X.COINL,
    I.NMINT,I.ADINT1,I.ADINT2,I.ADINT3,I.ADINT4,I.ADINT5,I.COLAN,I.COSIE,S.LISIE,I.NUBNK,
    X.COINF,X.COINN,X.COOPE,X.COINC,X.NUCPT,X.COPOR,X.IDETY,X.COINI,
    X.COMAR,A.COMSC,
    X.CNACT,
    DECODE(A.CNACN,'T',0,1),A.LNACT,A.CNACN,A.CNAMA,A.CAINI,A.CALOC,A.CODEV,A.CTACF,A.COBCN,
    X.CTSTR,X.CNACE,
    X.DATRA,X.DATOP,X.DAVAL,X.CSENS,X.QTCOF,X.MTNEG,X.MVNEG,
    X.CSOPT,X.CMECH,M.NMECH,X.CAECH,X.MTSNA,X.NUVER,X.NUINS,
    X.NUCON,X.NUBIX,X.NUFDP,X.NUORD,X.NUTIC,X.CTFNE,X.CTFIC,X.COGLO,X.CNAOP,X.CNTRA,
    X.CODEV,
    X.MTHEX,X.MTVEX,X.MTHGX,X.MTVGX,X.MTTGL,
    O.COPRO,X.COINV,X.COINA,X.COUTI,
    A.CTCOT,A.COTSJ,X.COINK,X.ISCHC,X.DAECB,X.TYTRN,
    DECODE(X.CORIG,NULL,'O','F','F','REG','REG','N'),X.CORIG,C.RGCOD,C.RGCID,X.MTREF,X.DAECA, 
    I.COTAX,X.COTAX,X.COTAF,X.COTAN,X.COTAI,X.COTAV,X.COTAK,X.COTAA,X.INFO1,X.GUREF,X.RGCOF,X.RGCIF
    FROM
    (SELECT 
     Y.COINL,
     Z.COINF,Z.COINN,Z.COOPE,Y.COINC,Y.NUCPT,Y.COPOR,Y.IDETY,Y.COINI,
     Y.COMAR,
     Z.CNACT,
     Y.CTSTR,Y.CNACE,
     V.DATRA,Z.DATOP,V.DAVAL,Z.CSENS,V.QTCOF,Z.MTNEG,Z.MVNEG,
     Z.CSOPT,Z.CMECH,Z.CAECH,Z.MTSNA,Z.NUVER,Z.NUINS,
     Z.NUCON,Y.NUBIX,Y.NUFDP,Y.NUORD,Y.NUTIC,Y.CTFNE,Y.CTFDE,Y.CTFIC,Z.COGLO,Y.CNAOP,Y.CNTRA,
     V.CODEV,
     V.MTHEX,V.MTVEX,V.MTHGX,V.MTVGX,V.MTTGL,
     Y.COINV,Y.COINA,Y.COUTI,
     Z.COINK,V.ISCHC,Z.DAECB,Z.TYTRN,Z.CORIG,Z.MTREF,Z.DAECA, 
     I.COTAX,J.COTAX COTAF,K.COTAX COTAN,L.COTAX COTAI,M.COTAX COTAV,N.COTAX COTAK,O.COTAX COTAA, 
     Y.INFO1,Y.GUREF,R.RGCOD RGCOF,R.RGCID RGCIF
     FROM
     HISDEP Y,
     HISNEG Z,
    
     (SELECT
      X.COMAR,
      X.DATRA,
      X.DAVAL,
      X.NUBIX,
      X.NUFDP,
      X.CDCOF,
      H.QTCOF,
      SUM(/*RNDTRC*/DECODE(S.IDFEE,'O',ROUND(X.MTCOF*X.QTCOF*X.CSCOF,DECODE(R.NUSPF,825,0,2)),DECODE(P.ARRCO,0,ROUND(X.MTCOF*X.QTCOF*
    X.CSCOF,D.FACAR)                         ,TRUNC(X.MTCOF*
    X.QTCOF*X.CSCOF,D.FACAR)))),
    SUM(/*RNDTRC*/DECODE(S.IDFEE,'O',ROUND(X.MTCOF*X.QTCOF*X.CSCOF*(NVL(X.TTCOF,0)/100),DECODE(R.NUSPF,825,0,2)),DECODE(P.ARRTV,0,ROUND(X.MTCOF*X.QTC
    OF*X.CSCOF*(NVL(X.TTCOF,0)/100),D.FACAR),TRUNC(X.MTCOF*
    X.QTCOF*X.CSCOF*(NVL(X.TTCOF,0)/100),D.FACAR)))),
    0,0,
    SUM(/*RNDTRC*/DECODE(S.IDFEE,'O',ROUND(X.MTCOF*X.QTCOF*X.CSCOF,DECODE(R.NUSPF,825,0,2))                  ,DECODE(P.ARRCO,0,ROUND(X.MTCOF*X.QTCOF*
    X.CSCOF,D.FACAR)                         ,TRUNC(X.MTCOF*
    X.QTCOF*X.CSCOF,D.FACAR))))+
    SUM(/*RNDTRC*/DECODE(S.IDFEE,'O',ROUND(X.MTCOF*X.QTCOF*X.CSCOF*(NVL(X.TTCOF,0)/100),DECODE(R.NUSPF,825,0,2)),DECODE(P.ARRTV,0,ROUND(X.MTCOF*X.QTC
    OF*X.CSCOF*(NVL(X.TTCOF,0)/100),D.FACAR),TRUNC(X.MTCOF*
    X.QTCOF*X.CSCOF*(NVL(X.TTCOF,0)/100),D.FACAR)))),
    MIN(DECODE(X.DATRA,X.DAORI,1,DECODE(X.CTMVC,'N',0,'D',0,1)))
    FROM
      HISMVC X,
      PARMAR P,
      DEVISE D,
      TYPSCR S,
      RENGEN R,
      (SELECT
        DATRA,
        DAVAL,
        NUBIX,
        NUFDP,
        CDCOF,
        NCCOF,
        ABS(SUM(NVL(DECODE(H.CTMVC,'N',0,'D',0,H.QTCOF*H.CSCOF),0),0)) QTCOF
      FROM
        (SELECT
          H.DATRA,
          H.DAVAL,
          H.NUBIX,
          H.NUFDP,
          H.CDCOF,
          ROW_NUMBER() OVER (PARTITION BY H.DATRA,H.DAVAL,H.NUBIX,H.NUFDP,H.CDCOF,H.NTUBX ORDER BY H.CTCOF) RK,
          H.CTMVC,
          H.QTCOF,
          H.CSCOF,
          H.NCCOF
        FROM
          HISMVC H
        WHERE
          H.NCCOF=11) H
      WHERE
        H.RK=1
        AND H.CTMVC NOT IN ('N','D')) H
    WHERE
      X.NCCOF=11      
      AND P.COMAR=X.COMAR
      AND D.CODEV=X.CDCOF
      AND S.CTCOF=X.CTCOF
      AND S.COLAN='A'
      AND H.DATRA(+)=X.DATRA
      AND H.DAVAL(+)=X.DAVAL
      AND H.NUBIX(+)=X.NUBIX
      AND H.NUFDP(+)=X.NUFDP
      AND H.CDCOF(+)=X.CDCOF
      AND H.NCCOF(+)=X.NCCOF
    GROUP BY
      X.COMAR,
      X.DATRA,X.DAVAL,X.NUBIX,X.NUFDP,X.CDCOF) V,
    
     INTERV I,
     INTERV J,
     INTERV K,
     INTERV L,
     INTERV M,
     INTERV N,
     INTERV O,
     RGCCAL R
     WHERE
     Y.CTFDE IN ('N','L','A','S','E') AND
     Y.COINL IS NOT NULL              AND
     Z.NUBIX=Y.NUBIX                  AND
     Z.DATRA=Y.DATRA                  AND -- perf
     V.NUBIX=Y.NUBIX                  AND
     V.NUFDP=Y.NUFDP                  AND
     I.COINT (+)= Y.COINC             AND
     J.COINT (+)= Z.COINF             AND
     K.COINT (+)= Z.COINN             AND
     L.COINT (+)= Y.COINI             AND
     M.COINT (+)= Y.COINV             AND
     N.COINT (+)= Z.COINK             AND
     O.COINT (+)= Y.COINA             AND
     R.COINT (+)= Y.COINL             AND
     R.COMAR (+)= Y.COMAR             
    ) X,
    INTERV I,
    NATACF A,
    OBJCON O,
    MOIECH M,
    COMPTE C,
    SIEGES S
    WHERE
    (X.MTHEX!=0 OR X.CTFDE='N') AND
    I.COINT=X.COINL             AND
    A.CNACT=X.CNACT             AND
    O.COBCN=A.COBCN             AND
    M.CMECH(+)=X.CMECH          AND
    C.COINT(+)=X.COINC          AND
    C.NUCPT(+)=X.NUCPT          AND
    I.COSIE=S.COSIE(+);
    Now, ask yourself how much of the above view is actually used by the original query of TOTO - what may be safely eliminated from the TOTO2 view? Your original query of TOTO is this:
    SELECT '02/11/09','08/11/09',X.COINL,X.CNACT,SUM(X.QTFDP*X.ISCHC),X.CODEV,SUM(X.MTHEX),SUM(X.MTVEX),SUM(X.MTTGL),0 
    FROM TOTO X  
    WHERE   X.DATOP>=TO_DATE('02112009 00:00:00','DDMMYYYY HH24:MI:SS')  AND   X.DATOP<=TO_DATE('08112009 23:59:59','DDMMYYYY HH24:MI:SS')  
    AND   X.COMAR='ICE'  AND   X.NUORD LIKE '%NY%' 
    GROUP BY X.COINL,X.CNACT,X.CODEV ORDER BY 3,6,4;
    Charles Hooper
    IT Manager/Oracle DBA
    K&M Machine-Fabricating, Inc.
  • 20. Re: I have to deactivate the "_OPTIMIZER_COST_BASED_TRANSFORMATION". WHY????
    Randolf Geist Oracle ACE Director
    Currently Being Moderated
    Farenheiit wrote:
    here is the execution stats (the view seems to not be merged):
    Interesting... The main difference between the good and bad plan seems to be the very early elimination of the rows from HISMVC due to the join with HISDEP which is only possible when merging the views... By the way, are there more views involved - it looks like. I'm a bit irritated by the H alias showing up in the Predicate Information section.

    Any chance that these additional views contain more hints?

    I'm not sure why the CBQT doesn't select this early elimination with merging - just for curiosity can you add a /*+ MERGE */ hint to the new view VCRDAL2 to request a merge (or simply add the VCRDAL2 as inline view with the MERGE hint added) ?

    Regards,
    Randolf

    Oracle related stuff blog:
    http://oracle-randolf.blogspot.com/

    Co-author of the forthcoming "OakTable Expert Oracle Practices" book:
    http://www.apress.com/book/view/1430226684
    http://www.amazon.com/Expert-Oracle-Practices-Database-Administration/dp/1430226684
  • 21. Re: I have to deactivate the "_OPTIMIZER_COST_BASED_TRANSFORMATION". WHY????
    705249 Newbie
    Currently Being Moderated
    Any chance that these additional views contain more hints?
    HISDEP is also a view but there is no hint in it. its script is here:
    SELECT
     H.NUBIX,H.COMAR,H.NUCON,H.NCOTC,H.CSENS,H.CTFNE,H.CTFIC,H.NUFDP,H.NFOTC,H.CTFDE,I.COINT,C.NUCPT,P.COPOR,
     H.CTCPT,H.COINI,A.NUCPT,H.CNAOP,H.QTFDP,H.CTSTR,H.CNACE,H.CSTRA,H.NUSTR,H.NUTIC,H.LIDEP,H.CSTCL,H.CSTIN,
     H.CSTAP,H.DATRA,H.CORIG,H.CDVRF,H.NUGBX,H.CDVPC,H.NUORD,H.COINL,H.CSTAL,H.NTUBX,H.NUBTR,H.NUFTR,H.FILE1,
     H.FILE2,H.COVER,H.CXORD,H.CRORD,H.QTBAS,H.CNBAS,H.CTORD,H.NUSER,H.UTEXT,H.CRFOP,H.COUTI,H.DAMOD,H.CNTRA,
     H.FILE3,H.CIANN,H.FOREX,H.COINA,H.COINV,H.CSTSA,H.MJMVC,H.NLUBX,H.DLTRA,H.ADJPO,H.COEAS,H.COCTI,H.GUAPS,
     H.IDMOD,H.NUBIO,H.NUFDO,H.INFO1,H.INFO2,H.INFO3,H.INFO4,H.INFO5,H.CREGR,H.IDMRG,H.STRAT,H.OEXID,H.OTRID,
     H.ONUOR,H.DANEO,H.DACEO,H.MGRAT,H.GUREF,H.ISSCR,H.DAFCY,H.IDETY,H.NURGE,H.NURGD,H.NURFD,H.NOREP,H.ID_COINC,
     H.ID_NUCPT,H.ID_NUCPI,H.ID_COPOR,H.AUTCV
    FROM IHSDEP H,
         INTERV I,
         COMPTE C,
         COMPTE A,
         PORTEF P
    WHERE
         I.IDINT(+)   =H.ID_COINC AND
         C.ID_NUCPT(+)=H.ID_NUCPT AND
         A.ID_NUCPT   =H.ID_NUCPI AND
         P.ID_COPOR(+)=H.ID_COPOR;
    I'm not sure why the CBQT doesn't select this early elimination with merging - just for curiosity can you add a /*+ MERGE */ hint to the new view VCRDAL2 to request a merge (or simply add >the VCRDAL2 as inline view with the MERGE hint added) ?
    I've tried to add the MERGE hint into the VCRDAL2 view but I still get the same execution plan:
    SQL> CREATE OR REPLACE VIEW VCRDAL2
      2  (comar, datra, daval, nubix, nufdp, codev, qtcof, mthex, mtvex, mthgx, mtvgx, mttgl, ischc)
      3  AS
      4  SELECT  /*+ MERGE */
      5  X.COMAR,X.DATRA,X.DAVAL,X.NUBIX,X.NUFDP,X.CDCOF,
      6  (SELECT ABS(SUM(DECODE(H.RK,1,DECODE(H.CTMVC,'N',0,'D',0,H.QTCOF*H.CSCOF),0)))
      7   FROM
      8   (SELECT H.DATRA,H.DAVAL,H.NUBIX,H.NUFDP,H.CDCOF,RANK() OVER(PARTITION BY H.DATRA,H.DAVAL,H.NUBIX,H.NUFDP,H.CDCOF,H.NTUBX ORDER BY H.CTCOF)
    RK,H.CTMVC,H.QTCOF,H.CSCOF
      9    FROM HISMVC H
     10    WHERE
     11    H.NCCOF=11
     12   ) H
     13   WHERE
     14   H.DATRA=X.DATRA AND
     15   H.DAVAL=X.DAVAL AND
     16   H.NUBIX=X.NUBIX AND
     17   H.NUFDP=X.NUFDP AND
     18   H.CDCOF=X.CDCOF AND
     19   H.RK=1
     20  ) QTCOF,
     21  SUM(/*RNDTRC*/DECODE(S.IDFEE,'O',ROUND(X.MTCOF*X.QTCOF*X.CSCOF,DECODE(R.NUSPF,825,0,2))                     ,DECODE(P.ARRCO,0,ROUND(X.MTCOF*
    X.QTCOF*X.CSCOF,D.FACAR)                     ,TRUNC(X.MTCOF*X.QTCOF*X.CSCOF,D.FACAR)))),
     22  SUM(/*RNDTRC*/DECODE(S.IDFEE,'O',ROUND(X.MTCOF*X.QTCOF*X.CSCOF*(NVL(X.TTCOF,0)/100),DECODE(R.NUSPF,825,0,2)),DECODE(P.ARRTV,0,ROUND(X.MTCOF*
    X.QTCOF*X.CSCOF*(NVL(X.TTCOF,0)/100),D.FACAR),TRUNC(X.MTCOF*X.QTCOF*X.CSCOF*(NVL(X.TTCOF,0)/100),D.FACAR)))),
     23  0,0,
     24  SUM(/*RNDTRC*/DECODE(S.IDFEE,'O',ROUND(X.MTCOF*X.QTCOF*X.CSCOF,DECODE(R.NUSPF,825,0,2))                     ,DECODE(P.ARRCO,0,ROUND(X.MTCOF*
    X.QTCOF*X.CSCOF,D.FACAR)                     ,TRUNC(X.MTCOF*X.QTCOF*X.CSCOF,D.FACAR))))+
     25  SUM(/*RNDTRC*/DECODE(S.IDFEE,'O',ROUND(X.MTCOF*X.QTCOF*X.CSCOF*(NVL(X.TTCOF,0)/100),DECODE(R.NUSPF,825,0,2)),DECODE(P.ARRTV,0,ROUND(X.MTCOF*
    X.QTCOF*X.CSCOF*(NVL(X.TTCOF,0)/100),D.FACAR),TRUNC(X.MTCOF*X.QTCOF*X.CSCOF*(NVL(X.TTCOF,0)/100),D.FACAR)))),
     26  MIN(DECODE(X.DATRA,X.DAORI,1,DECODE(X.CTMVC,'N',0,'D',0,1)))
     27  FROM
     28  HISMVC X,
     29  PARMAR P,
     30  DEVISE D,
     31  TYPSCR S,
     32  RENGEN R
     33  WHERE
     34  X.NCCOF=11      AND
     35  P.COMAR=X.COMAR AND
     36  D.CODEV=X.CDCOF AND
     37  S.CTCOF=X.CTCOF AND
     38  S.COLAN='A'
     39  GROUP BY
     40  X.COMAR,X.DATRA,X.DAVAL,X.NUBIX,X.NUFDP,X.CDCOF;
    
    View created.
    
    Elapsed: 00:00:00.06
    SQL> explain plan for
      2  SELECT '02/11/09','08/11/09',X.COINL,X.CNACT,SUM(X.QTFDP*X.ISCHC),X.CODEV,SUM(X.MTHEX),SUM(X.MTVEX),SUM(X.MTTGL),0
      3  FROM TOTO X
      4  WHERE   X.DATOP>=TO_DATE('02112009 00:00:00','DDMMYYYY HH24:MI:SS')  AND   X.DATOP<=TO_DATE('08112009 23:59:59','DDMMYYYY HH24:MI:SS')
      5  AND   X.COMAR='ICE'  AND   X.NUORD LIKE '%NY%'
      6  GROUP BY X.COINL,X.CNACT,X.CODEV ORDER BY 3,6,4;
    
    Explained.
    
    Elapsed: 00:00:00.08
    SQL> select * from table(dbms_xplan.display);
    
    PLAN_TABLE_OUTPUT
    -------------------------------------------------------------------------------------------------------------------------------------------------
    -------------------------------------------------------
    Plan hash value: 2398373518
    
    ------------------------------------------------------------------------------------------------------------------------------
    | Id  | Operation                                         | Name             | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
    ------------------------------------------------------------------------------------------------------------------------------
    |   0 | SELECT STATEMENT                                  |                  |    19 |  2907 |       |   166K  (2)| 00:33:20 |
    |   1 |  SORT AGGREGATE                                   |                  |     1 |    88 |       |            |          |
    |*  2 |   VIEW                                            |                  |     1 |    88 |       |     7  (15)| 00:00:01 |
    |*  3 |    WINDOW SORT PUSHED RANK                        |                  |     1 |    51 |       |     7  (15)| 00:00:01 |
    |*  4 |     TABLE ACCESS BY INDEX ROWID                   | HISMVC           |     1 |    51 |       |     6   (0)| 00:00:01 |
    |*  5 |      INDEX RANGE SCAN                             | HISMVC1          |     6 |       |       |     3   (0)| 00:00:01 |
    |   6 |  SORT GROUP BY                                    |                  |    19 |  2907 |       |   166K  (2)| 00:33:20 |
    |   7 |   NESTED LOOPS                                    |                  |    19 |  2907 |       |   166K  (2)| 00:33:20 |
    |   8 |    NESTED LOOPS                                   |                  |    19 |  2793 |       |   166K  (2)| 00:33:20 |
    |   9 |     NESTED LOOPS OUTER                            |                  |    19 |  2527 |       |   166K  (2)| 00:33:19 |
    |  10 |      NESTED LOOPS OUTER                           |                  |    19 |  2223 |       |   166K  (2)| 00:33:19 |
    |  11 |       NESTED LOOPS OUTER                          |                  |    19 |  2147 |       |   166K  (2)| 00:33:19 |
    |* 12 |        HASH JOIN                                  |                  |    19 |  2071 |       |   166K  (2)| 00:33:19 |
    |  13 |         VIEW                                      |                  |    37 |  3552 |       |   166K  (2)| 00:33:19 |
    |* 14 |          HASH JOIN RIGHT OUTER                    |                  |    37 | 11988 |       |   166K  (2)| 00:33:19 |
    |* 15 |           INDEX FAST FULL SCAN                    | RGCCAL1          |    45 |   495 |       |     2   (0)| 00:00:01 |
    |  16 |           NESTED LOOPS OUTER                      |                  |    37 | 11581 |       |   166K  (2)| 00:33:19 |
    |* 17 |            HASH JOIN OUTER                        |                  |    37 | 11248 |       |   166K  (2)| 00:33:19 |
    |* 18 |             HASH JOIN RIGHT OUTER                 |                  |    37 | 10767 |       |   166K  (2)| 00:33:19 |
    |  19 |              TABLE ACCESS FULL                    | COMPTE           |   308 |  3696 |       |     5   (0)| 00:00:01 |
    |  20 |              NESTED LOOPS                         |                  |    37 | 10323 |       |   166K  (2)| 00:33:19 |
    |  21 |               NESTED LOOPS OUTER                  |                  |    37 | 10175 |       |   166K  (2)| 00:33:19 |
    |  22 |                NESTED LOOPS OUTER                 |                  |    37 | 10064 |       |   166K  (2)| 00:33:19 |
    |  23 |                 NESTED LOOPS OUTER                |                  |    37 |  9731 |       |   166K  (2)| 00:33:19 |
    |  24 |                  NESTED LOOPS OUTER               |                  |    37 |  9398 |       |   166K  (2)| 00:33:19 |
    |  25 |                   NESTED LOOPS OUTER              |                  |    37 |  9065 |       |   166K  (2)| 00:33:19 |
    |  26 |                    NESTED LOOPS OUTER             |                  |    37 |  8732 |       |   166K  (2)| 00:33:19 |
    |  27 |                     NESTED LOOPS OUTER            |                  |    37 |  8399 |       |   166K  (2)| 00:33:19 |
    |* 28 |                      HASH JOIN                    |                  |    37 |  8066 |       |   166K  (2)| 00:33:19 |
    |* 29 |                       HASH JOIN                   |                  |   129 | 17286 |       | 35145   (2)| 00:07:02 |
    |  30 |                        TABLE ACCESS BY INDEX ROWID| HISNEG           | 14478 |   975K|       |  7220   (1)| 00:01:27 |
    |* 31 |                         INDEX RANGE SCAN          | HISNEG4          | 14478 |       |       |    42   (0)| 00:00:01 |
    |* 32 |                        TABLE ACCESS FULL          | IHSDEP           | 32994 |  2094K|       | 27925   (2)| 00:05:36 |
    |  33 |                       VIEW                        | VCRDAL2          |  3589K|   287M|       |   131K  (1)| 00:26:17 |
    |  34 |                        HASH GROUP BY              |                  |  3589K|   314M|   719M|   131K  (1)| 00:26:17 |
    |* 35 |                         HASH JOIN                 |                  |  3589K|   314M|       | 55217   (2)| 00:11:03 |
    |* 36 |                          TABLE ACCESS FULL        | TYPSCR           |    14 |   112 |       |     5   (0)| 00:00:01 |
    |* 37 |                          HASH JOIN                |                  |  2564K|   205M|       | 55190   (2)| 00:11:03 |
    |  38 |                           TABLE ACCESS FULL       | PARMAR           |    72 |   648 |       |     5   (0)| 00:00:01 |
    |* 39 |                           HASH JOIN               |                  |  2564K|   183M|       | 55169   (2)| 00:11:03 |
    |  40 |                            MERGE JOIN CARTESIAN   |                  |    44 |   484 |       |    10   (0)| 00:00:01 |
    |  41 |                             TABLE ACCESS FULL     | RENGEN           |     1 |     4 |       |     5   (0)| 00:00:01 |
    |  42 |                             BUFFER SORT           |                  |    44 |   308 |       |     5   (0)| 00:00:01 |
    |  43 |                              TABLE ACCESS FULL    | DEVISE           |    44 |   308 |       |     5   (0)| 00:00:01 |
    |* 44 |                            TABLE ACCESS FULL      | HISMVC           |  2564K|   156M|       | 55143   (2)| 00:11:02 |
    |* 45 |                      INDEX UNIQUE SCAN            | INTERV1          |     1 |     9 |       |     0   (0)| 00:00:01 |
    |* 46 |                     INDEX UNIQUE SCAN             | INTERV1          |     1 |     9 |       |     0   (0)| 00:00:01 |
    |* 47 |                    INDEX UNIQUE SCAN              | INTERV1          |     1 |     9 |       |     0   (0)| 00:00:01 |
    |* 48 |                   INDEX UNIQUE SCAN               | INTERV1          |     1 |     9 |       |     0   (0)| 00:00:01 |
    |* 49 |                  INDEX UNIQUE SCAN                | INTERV1          |     1 |     9 |       |     0   (0)| 00:00:01 |
    |* 50 |                 INDEX UNIQUE SCAN                 | INTERV1          |     1 |     9 |       |     0   (0)| 00:00:01 |
    |* 51 |                INDEX UNIQUE SCAN                  | PORTEF2          |     1 |     3 |       |     0   (0)| 00:00:01 |
    |* 52 |               INDEX UNIQUE SCAN                   | COMPTE3          |     1 |     4 |       |     0   (0)| 00:00:01 |
    |  53 |             VIEW                                  | index$_join$_028 |   379 |  4927 |       |     3   (0)| 00:00:01 |
    |* 54 |              HASH JOIN                            |                  |       |       |       |            |          |
    |  55 |               INDEX FAST FULL SCAN                | INTERV1          |   379 |  4927 |       |     1   (0)| 00:00:01 |
    |  56 |               INDEX FAST FULL SCAN                | INTERV3          |   379 |  4927 |       |     1   (0)| 00:00:01 |
    |* 57 |            INDEX UNIQUE SCAN                      | INTERV1          |     1 |     9 |       |     0   (0)| 00:00:01 |
    |  58 |         TABLE ACCESS FULL                         | INTERV           |   379 |  4927 |       |     5   (0)| 00:00:01 |
    |* 59 |        INDEX UNIQUE SCAN                          | SIEGE1           |     1 |     4 |       |     0   (0)| 00:00:01 |
    |* 60 |       INDEX UNIQUE SCAN                           | MOIECH1          |     1 |     4 |       |     0   (0)| 00:00:01 |
    |* 61 |      INDEX UNIQUE SCAN                            | COMPTE1          |     1 |    16 |       |     0   (0)| 00:00:01 |
    |  62 |     TABLE ACCESS BY INDEX ROWID                   | NATACF           |     1 |    14 |       |     1   (0)| 00:00:01 |
    |* 63 |      INDEX UNIQUE SCAN                            | NATACF1          |     1 |       |       |     0   (0)| 00:00:01 |
    |* 64 |    INDEX UNIQUE SCAN                              | OBJCON1          |     1 |     6 |       |     0   (0)| 00:00:01 |
    ------------------------------------------------------------------------------------------------------------------------------
    
    Predicate Information (identified by operation id):
    ---------------------------------------------------
    
       2 - filter("H"."RK"=1)
       3 - filter(RANK() OVER ( PARTITION BY "H"."DATRA","H"."DAVAL","H"."NUBIX","H"."NUFDP","H"."CDCOF","H"."NTUBX"
                  ORDER BY "H"."CTCOF")<=1)
       4 - filter("H"."DAVAL"=:B1 AND "H"."DATRA"=:B2 AND "H"."NCCOF"=11 AND "H"."CDCOF"=:B3)
       5 - access("H"."NUBIX"=:B1 AND "H"."NUFDP"=:B2)
      12 - access("I"."COINT"="X"."COINL")
      14 - access("R"."COINT"(+)="H"."COINL" AND "R"."COMAR"(+)="H"."COMAR")
      15 - filter("R"."COMAR"(+)='ICE')
      17 - access("I"."IDINT"(+)="H"."ID_COINC")
      18 - access("C"."ID_NUCPT"(+)="H"."ID_NUCPT")
      28 - access("V"."NUBIX"="H"."NUBIX" AND "V"."NUFDP"="H"."NUFDP")
           filter("V"."MTHEX"<>0 OR "H"."CTFDE"='N')
      29 - access("Z"."NUBIX"="H"."NUBIX" AND "Z"."DATRA"="H"."DATRA")
      31 - access("Z"."DATOP">=TO_DATE(' 2009-11-02 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND "Z"."DATOP"<=TO_DATE('
                  2009-11-08 23:59:59', 'syyyy-mm-dd hh24:mi:ss'))
      32 - filter("H"."COINL" IS NOT NULL AND "H"."COMAR"='ICE' AND "H"."NUORD" LIKE '%NY%' AND ("H"."CTFDE"='A' OR
                  "H"."CTFDE"='E' OR "H"."CTFDE"='L' OR "H"."CTFDE"='N' OR "H"."CTFDE"='S'))
      35 - access("S"."CTCOF"="X"."CTCOF")
      36 - filter("S"."COLAN"='A')
      37 - access("P"."COMAR"="X"."COMAR")
      39 - access("D"."CODEV"="X"."CDCOF")
      44 - filter("X"."NCCOF"=11)
      45 - access("O"."COINT"(+)="H"."COINA")
      46 - access("M"."COINT"(+)="H"."COINV")
      47 - access("L"."COINT"(+)="H"."COINI")
      48 - access("N"."COINT"(+)="Z"."COINK")
      49 - access("K"."COINT"(+)="Z"."COINN")
      50 - access("J"."COINT"(+)="Z"."COINF")
      51 - access("P"."ID_COPOR"(+)="H"."ID_COPOR")
      52 - access("A"."ID_NUCPT"="H"."ID_NUCPI")
      54 - access(ROWID=ROWID)
      57 - access("I"."COINT"(+)="I"."COINT")
      59 - access("I"."COSIE"="S"."COSIE"(+))
      60 - access("M"."CMECH"(+)="X"."CMECH")
      61 - access("C"."COINT"(+)="X"."COINC" AND "C"."NUCPT"(+)="X"."NUCPT")
      63 - access("A"."CNACT"="X"."CNACT")
      64 - access("O"."COBCN"="A"."COBCN")
    
    Note
    -----
       - 'PLAN_TABLE' is old version
       - dynamic sampling used for this statement
    
    117 rows selected.
    Elapsed: 00:00:00.08
    SQL> SELECT '02/11/09','08/11/09',X.COINL,X.CNACT,SUM(X.QTFDP*X.ISCHC),X.CODEV,SUM(X.MTHEX),SUM(X.MTVEX),SUM(X.MTTGL),0
      2  FROM TOTO X
      3  WHERE   X.DATOP>=TO_DATE('02112009 00:00:00','DDMMYYYY HH24:MI:SS')  AND   X.DATOP<=TO_DATE('08112009 23:59:59','DDMMYYYY HH24:MI:SS')
      4  AND   X.COMAR='ICE'  AND   X.NUORD LIKE '%NY%'
      5  GROUP BY X.COINL,X.CNACT,X.CODEV ORDER BY 3,6,4;
    
    '02/11/0 '08/11/0 COINL    CNACT    SUM(X.QTFDP*X.ISCHC) COD SUM(X.MTHEX) SUM(X.MTVEX) SUM(X.MTTGL)       0
    -------- -------- -------- -------- -------------------- --- ------------ ------------ ------------ ----------
    02/11/09 08/11/09 VMLN AM  WBSFICE                   784 USD         1176            0         1176       0
    
    Elapsed: 00:16:10.05
    Furthermore, I've tried to create the view with the new script given by Charles but it doesn't work:
    SQL> CREATE OR REPLACE VIEW VCRDAL3
      2  (comar, datra, daval, nubix, nufdp, codev, qtcof, mthex, mtvex, mthgx, mtvgx, mttgl, ischc)
      3  AS
      4  SELECT
      5    X.COMAR,
      6    X.DATRA,
      7    X.DAVAL,
      8    X.NUBIX,
      9    X.NUFDP,
     10    X.CDCOF,
     11    H.QTCOF,
     12    SUM(/*RNDTRC*/DECODE(S.IDFEE,'O',ROUND(X.MTCOF*X.QTCOF*X.CSCOF,DECODE(R.NUSPF,825,0,2)),DECODE(P.ARRCO,0,ROUND(X.MTCOF*X.QTCOF*X.CSCOF,D.F
    ACAR),
     13    TRUNC(X.MTCOF*X.QTCOF*X.CSCOF,D.FACAR)))),
     14    SUM(/*RNDTRC*/DECODE(S.IDFEE,'O',ROUND(X.MTCOF*X.QTCOF*X.CSCOF*(NVL(X.TTCOF,0)/100),DECODE(R.NUSPF,825,0,2)),DECODE(P.ARRTV,0,ROUND(X.MTCO
    F*X.QTCOF*X.CSCOF*(NVL(X.TTCOF,0)/100),D.FACAR),TRUNC(X.MTCOF*X.QTCOF*X.CSCOF*(NVL(X.TTCOF,0)/100),D.FACAR)))),
     15    0,0, SUM(/*RNDTRC*/DECODE(S.IDFEE,'O',ROUND(X.MTCOF*X.QTCOF*X.CSCOF,DECODE(R.NUSPF,825,0,2)),DECODE(P.ARRCO,0,ROUND(X.MTCOF*X.QTCOF*X.CSCO
    F,D.FACAR),TRUNC(X.MTCOF*X.QTCOF*X.CSCOF,D.FACAR))))+SUM(/*RNDTRC*/DECODE(S.IDFEE,'O',ROUND(X.MTCOF*X.QTCOF*X.CSCOF*(NVL(X.TTCOF,0)/100),DECODE(R
    .NUSPF,825,0,2)),DECODE(P.ARRTV,0,ROUND(X.MTCOF*X.QTCOF*X.CSCOF*(NVL(X.TTCOF,0)/100),D.FACAR),TRUNC(X.MTCOF*X.QTCOF*X.CSCOF*(NVL(X.TTCOF,0)/100),
    D.FACAR)))),
     16    MIN(DECODE(X.DATRA,X.DAORI,1,DECODE(X.CTMVC,'N',0,'D',0,1)))
     17  FROM
     18    HISMVC X,
     19    PARMAR P,
     20    DEVISE D,
     21    TYPSCR S,
     22    RENGEN R,
     23    (SELECT
     24      DATRA,
     25      DAVAL,
     26      NUBIX,
     27      NUFDP,
     28      CDCOF,
     29      NCCOF,
     30      ABS(SUM(NVL(DECODE(H.CTMVC,'N',0,'D',0,H.QTCOF*H.CSCOF),0))) QTCOF
     31    FROM
     32      (SELECT
     33        H.DATRA,
     34        H.DAVAL,
     35        H.NUBIX,
     36        H.NUFDP,
     37        H.CDCOF,
     38        ROW_NUMBER() OVER (PARTITION BY H.DATRA,H.DAVAL,H.NUBIX,H.NUFDP,H.CDCOF,H.NTUBX ORDER BY H.CTCOF) RK,
     39        H.CTMVC,
     40        H.QTCOF,
     41        H.CSCOF,
     42        H.NCCOF
     43      FROM
     44        HISMVC H
     45      WHERE
     46        H.NCCOF=11) H
     47    WHERE
     48      H.RK=1
     49      AND H.CTMVC NOT IN ('N','D')) H
     50  WHERE
     51    X.NCCOF=11
     52    AND P.COMAR=X.COMAR
     53    AND D.CODEV=X.CDCOF
     54    AND S.CTCOF=X.CTCOF
     55    AND S.COLAN='A'
     56    AND H.DATRA(+)=X.DATRA
     57    AND H.DAVAL(+)=X.DAVAL
     58    AND H.NUBIX(+)=X.NUBIX
     59    AND H.NUFDP(+)=X.NUFDP
     60    AND H.CDCOF(+)=X.CDCOF
     61    AND H.NCCOF(+)=X.NCCOF
     62  GROUP BY
     63    X.COMAR,
     64    X.DATRA,X.DAVAL,X.NUBIX,X.NUFDP,X.CDCOF;
        DATRA,
        *
    ERROR at line 24:
    ORA-00937: not a single-group group function
    I asked yesterday the production DBA to give me a 10053 trace file but I'm still waiting for.
  • 22. Re: I have to deactivate the "_OPTIMIZER_COST_BASED_TRANSFORMATION". WHY????
    CharlesHooper Expert
    Currently Being Moderated
    Farenheiit wrote:
    Furthermore, I've tried to create the view with the new script given by Charles but it doesn't work:
    SQL> CREATE OR REPLACE VIEW VCRDAL3
    2  (comar, datra, daval, nubix, nufdp, codev, qtcof, mthex, mtvex, mthgx, mtvgx, mttgl, ischc)
    3  AS
    4  SELECT
    5    X.COMAR,
    6    X.DATRA,
    7    X.DAVAL,
    8    X.NUBIX,
    9    X.NUFDP,
    10    X.CDCOF,
    11    H.QTCOF,
    12    SUM(/*RNDTRC*/DECODE(S.IDFEE,'O',ROUND(X.MTCOF*X.QTCOF*X.CSCOF,DECODE(R.NUSPF,825,0,2)),DECODE(P.ARRCO,0,ROUND(X.MTCOF*X.QTCOF*X.CSCOF,D.F
    ACAR),
    ...
    62 GROUP BY
    63 X.COMAR,
    64 X.DATRA,X.DAVAL,X.NUBIX,X.NUFDP,X.CDCOF;
    DATRA,
    *
    ERROR at line 24:
    ORA-00937: not a single-group group function
    I asked yesterday the production DBA to give me a 10053 trace file but I'm still waiting for.
    I added the column "NCCOF" to the query, and you have it listed as "H.QTCOF" - it is a good idea to alias the columns as you have done. The problem is that I did NOT add the column to the GROUP BY list at line 64. Line 64 should show:
    X.DATRA,X.DAVAL,X.NUBIX,X.NUFDP,X.CDCOF,H.QTCOF;
    Charles Hooper
    IT Manager/Oracle DBA
    K&M Machine-Fabricating, Inc.
  • 23. Re: I have to deactivate the "_OPTIMIZER_COST_BASED_TRANSFORMATION". WHY????
    705249 Newbie
    Currently Being Moderated
    I've modifed line 64:
    SQL> CREATE OR REPLACE VIEW VCRDAL3
      2  (comar, datra, daval, nubix, nufdp, codev, qtcof, mthex, mtvex, mthgx, mtvgx, mttgl, ischc)
      3  AS
      4  SELECT
      5    X.COMAR,
      6    X.DATRA,
      7    X.DAVAL,
      8    X.NUBIX,
      9    X.NUFDP,
     10    X.CDCOF,
     11    H.QTCOF,
     12    SUM(/*RNDTRC*/DECODE(S.IDFEE,'O',ROUND(X.MTCOF*X.QTCOF*X.CSCOF,DECODE(R.NUSPF,825,0,2)),DECODE(P.ARRCO,0,ROUND(X.MTCOF*X.QTCOF*X.CSCOF,D.F
    ACAR),
     13    TRUNC(X.MTCOF*X.QTCOF*X.CSCOF,D.FACAR)))),
     14    SUM(/*RNDTRC*/DECODE(S.IDFEE,'O',ROUND(X.MTCOF*X.QTCOF*X.CSCOF*(NVL(X.TTCOF,0)/100),DECODE(R.NUSPF,825,0,2)),DECODE(P.ARRTV,0,ROUND(X.MTCO
    F*X.QTCOF*X.CSCOF*(NVL(X.TTCOF,0)/100),D.FACAR),TRUNC(X.MTCOF*X.QTCOF*X.CSCOF*(NVL(X.TTCOF,0)/100),D.FACAR)))),
     15    0,0, SUM(/*RNDTRC*/DECODE(S.IDFEE,'O',ROUND(X.MTCOF*X.QTCOF*X.CSCOF,DECODE(R.NUSPF,825,0,2)),DECODE(P.ARRCO,0,ROUND(X.MTCOF*X.QTCOF*X.CSCO
    F,D.FACAR),TRUNC(X.MTCOF*X.QTCOF*X.CSCOF,D.FACAR))))+SUM(/*RNDTRC*/DECODE(S.IDFEE,'O',ROUND(X.MTCOF*X.QTCOF*X.CSCOF*(NVL(X.TTCOF,0)/100),DECODE(R
    .NUSPF,825,0,2)),DECODE(P.ARRTV,0,ROUND(X.MTCOF*X.QTCOF*X.CSCOF*(NVL(X.TTCOF,0)/100),D.FACAR),TRUNC(X.MTCOF*X.QTCOF*X.CSCOF*(NVL(X.TTCOF,0)/100),
    D.FACAR)))),
     16    MIN(DECODE(X.DATRA,X.DAORI,1,DECODE(X.CTMVC,'N',0,'D',0,1)))
     17  FROM
     18    HISMVC X,
     19    PARMAR P,
     20    DEVISE D,
     21    TYPSCR S,
     22    RENGEN R,
     23    (SELECT
     24      DATRA,
     25      DAVAL,
     26      NUBIX,
     27      NUFDP,
     28      CDCOF,
     29      NCCOF,
     30      ABS(SUM(NVL(DECODE(H.CTMVC,'N',0,'D',0,H.QTCOF*H.CSCOF),0))) QTCOF
     31    FROM
     32      (SELECT
     33        H.DATRA,
     34        H.DAVAL,
     35        H.NUBIX,
     36        H.NUFDP,
     37        H.CDCOF,
     38        ROW_NUMBER() OVER (PARTITION BY H.DATRA,H.DAVAL,H.NUBIX,H.NUFDP,H.CDCOF,H.NTUBX ORDER BY H.CTCOF) RK,
     39        H.CTMVC,
     40        H.QTCOF,
     41        H.CSCOF,
     42        H.NCCOF
     43      FROM
     44        HISMVC H
     45      WHERE
     46        H.NCCOF=11) H
     47    WHERE
     48      H.RK=1
     49      AND H.CTMVC NOT IN ('N','D')) H
     50  WHERE
     51    X.NCCOF=11
     52    AND P.COMAR=X.COMAR
     53    AND D.CODEV=X.CDCOF
     54    AND S.CTCOF=X.CTCOF
     55    AND S.COLAN='A'
     56    AND H.DATRA(+)=X.DATRA
     57    AND H.DAVAL(+)=X.DAVAL
     58    AND H.NUBIX(+)=X.NUBIX
     59    AND H.NUFDP(+)=X.NUFDP
     60    AND H.CDCOF(+)=X.CDCOF
     61    AND H.NCCOF(+)=X.NCCOF
     62  GROUP BY
     63    X.COMAR,
     64    X.DATRA,X.DAVAL,X.NUBIX,X.NUFDP,X.CDCOF,H.QTCOF;
        DATRA,
        *
    ERROR at line 24:
    ORA-00937: not a single-group group function
    
    
    Elapsed: 00:00:00.00
    SQL>
    I have to add a group by clause:
    CREATE OR REPLACE VIEW VCRDAL3
    (comar, datra, daval, nubix, nufdp, codev, qtcof, mthex, mtvex, mthgx, mtvgx, mttgl, ischc)
    AS
    SELECT
      X.COMAR,
      X.DATRA,
      X.DAVAL,
      X.NUBIX,
      X.NUFDP,
      X.CDCOF,
      H.QTCOF,
      SUM(/*RNDTRC*/DECODE(S.IDFEE,'O',ROUND(X.MTCOF*X.QTCOF*X.CSCOF,DECODE(R.NUSPF,825,0,2)),DECODE(P.ARRCO,0,ROUND(X.MTCOF*X.QTCOF*X.CSCOF,D.FACAR),
      TRUNC(X.MTCOF*X.QTCOF*X.CSCOF,D.FACAR)))), 
      SUM(/*RNDTRC*/DECODE(S.IDFEE,'O',ROUND(X.MTCOF*X.QTCOF*X.CSCOF*(NVL(X.TTCOF,0)/100),DECODE(R.NUSPF,825,0,2)),DECODE(P.ARRTV,0,ROUND(X.MTCOF*X.QTCOF*X.CSCOF*(NVL(X.TTCOF,0)/100),D.FACAR),TRUNC(X.MTCOF*X.QTCOF*X.CSCOF*(NVL(X.TTCOF,0)/100),D.FACAR)))),
      0,0, SUM(/*RNDTRC*/DECODE(S.IDFEE,'O',ROUND(X.MTCOF*X.QTCOF*X.CSCOF,DECODE(R.NUSPF,825,0,2)),DECODE(P.ARRCO,0,ROUND(X.MTCOF*X.QTCOF*X.CSCOF,D.FACAR),TRUNC(X.MTCOF*X.QTCOF*X.CSCOF,D.FACAR))))+SUM(/*RNDTRC*/DECODE(S.IDFEE,'O',ROUND(X.MTCOF*X.QTCOF*X.CSCOF*(NVL(X.TTCOF,0)/100),DECODE(R.NUSPF,825,0,2)),DECODE(P.ARRTV,0,ROUND(X.MTCOF*X.QTCOF*X.CSCOF*(NVL(X.TTCOF,0)/100),D.FACAR),TRUNC(X.MTCOF*X.QTCOF*X.CSCOF*(NVL(X.TTCOF,0)/100),D.FACAR)))), 
      MIN(DECODE(X.DATRA,X.DAORI,1,DECODE(X.CTMVC,'N',0,'D',0,1)))
    FROM
      HISMVC X,
      PARMAR P,
      DEVISE D,
      TYPSCR S,
      RENGEN R,
      (SELECT
        DATRA,
        DAVAL,
        NUBIX,
        NUFDP,
        CDCOF,
        NCCOF,   
        ABS(SUM(NVL(DECODE(H.CTMVC,'N',0,'D',0,H.QTCOF*H.CSCOF),0))) QTCOF
      FROM
        (SELECT
          H.DATRA,
          H.DAVAL,
          H.NUBIX,
          H.NUFDP,
          H.CDCOF,
          ROW_NUMBER() OVER (PARTITION BY H.DATRA,H.DAVAL,H.NUBIX,H.NUFDP,H.CDCOF,H.NTUBX ORDER BY H.CTCOF) RK,
          H.CTMVC,
          H.QTCOF,
          H.CSCOF,
          H.NCCOF
        FROM
          HISMVC H
        WHERE
          H.NCCOF=11) H
      WHERE
        H.RK=1
        AND H.CTMVC NOT IN ('N','D') group by DATRA, DAVAL, NUBIX, NUFDP, CDCOF, NCCOF) H    
    WHERE
      X.NCCOF=11      
      AND P.COMAR=X.COMAR
      AND D.CODEV=X.CDCOF
      AND S.CTCOF=X.CTCOF
      AND S.COLAN='A'
      AND H.DATRA(+)=X.DATRA
      AND H.DAVAL(+)=X.DAVAL
      AND H.NUBIX(+)=X.NUBIX
      AND H.NUFDP(+)=X.NUFDP
      AND H.CDCOF(+)=X.CDCOF
      AND H.NCCOF(+)=X.NCCOF
    GROUP BY
      X.COMAR,
      X.DATRA,X.DAVAL,X.NUBIX,X.NUFDP,X.CDCOF,H.QTCOF;
    
    View created.
    
    Elapsed: 00:00:01.01
    SQL> CREATE OR REPLACE VIEW toto
      2  (coinl, nminl, adinl1, adinl2, adinl3, adinl4, adinl5, colal, cosie, lisie, nubnl, coinf, coinn, coope, coint, nucpt, copor, idety, coini, c
    omar, comsc, cnact, ntact, lnact, cnacn, cnama, caini, caloc, cdvac, ctacf, cobcn, ctstr, cnach, datra, datop, daval, csens, qtfdp, mtneg, mvneg,
     csopt, cmech, nmech, caech, mtsna, nuver, nuins, nucon, nubix, nufdp, nuord, nutic, ctfne, ctfic, coglo, cnaop, cntra, codev, mthex, mtvex, mthg
    x, mtvgx, mttgl, copro, coinv, coina, couti, ctcot, cotsj, coink, ischc, daecb, tytrn, saima, corig, rgcod, rgcid, mtref, daeca, cotal, cotax, co
    taf, cotan, cotai, cotav, cotak, cotaa, info1, guref, rgcof, rgcif)
      3  AS
      4  SELECT
      5  X.COINL,
      6  I.NMINT,I.ADINT1,I.ADINT2,I.ADINT3,I.ADINT4,I.ADINT5,I.COLAN,I.COSIE,S.LISIE,I.NUBNK,
      7  X.COINF,X.COINN,X.COOPE,X.COINC,X.NUCPT,X.COPOR,X.IDETY,X.COINI,
      8  X.COMAR,A.COMSC,
      9  X.CNACT,
     10  DECODE(A.CNACN,'T',0,1),A.LNACT,A.CNACN,A.CNAMA,A.CAINI,A.CALOC,A.CODEV,A.CTACF,A.COBCN,
     11  X.CTSTR,X.CNACE,
     12  X.DATRA,X.DATOP,X.DAVAL,X.CSENS,X.QTCOF,X.MTNEG,X.MVNEG,
     13  X.CSOPT,X.CMECH,M.NMECH,X.CAECH,X.MTSNA,X.NUVER,X.NUINS,
     14  X.NUCON,X.NUBIX,X.NUFDP,X.NUORD,X.NUTIC,X.CTFNE,X.CTFIC,X.COGLO,X.CNAOP,X.CNTRA,
     15  X.CODEV,
     16  X.MTHEX,X.MTVEX,X.MTHGX,X.MTVGX,X.MTTGL,
     17  O.COPRO,X.COINV,X.COINA,X.COUTI,
     18  A.CTCOT,A.COTSJ,X.COINK,X.ISCHC,X.DAECB,X.TYTRN,
     19  DECODE(X.CORIG,NULL,'O','F','F','REG','REG','N'),X.CORIG,C.RGCOD,C.RGCID,X.MTREF,X.DAECA,
     20  I.COTAX,X.COTAX,X.COTAF,X.COTAN,X.COTAI,X.COTAV,X.COTAK,X.COTAA,X.INFO1,X.GUREF,X.RGCOF,X.RGCIF
     21  FROM
     22   -- vue pour pouvoir utiliser HISMVC1 (clause X.MTHEX!=0 sortie)
     23  (SELECT
     24   Y.COINL,
     25   Z.COINF,Z.COINN,Z.COOPE,Y.COINC,Y.NUCPT,Y.COPOR,Y.IDETY,Y.COINI,
     26   Y.COMAR,
     27   Z.CNACT,
     28   Y.CTSTR,Y.CNACE,
     29   V.DATRA,Z.DATOP,V.DAVAL,Z.CSENS,V.QTCOF,Z.MTNEG,Z.MVNEG,
     30   Z.CSOPT,Z.CMECH,Z.CAECH,Z.MTSNA,Z.NUVER,Z.NUINS,
     31   Z.NUCON,Y.NUBIX,Y.NUFDP,Y.NUORD,Y.NUTIC,Y.CTFNE,Y.CTFDE,Y.CTFIC,Z.COGLO,Y.CNAOP,Y.CNTRA,
     32   V.CODEV,
     33   V.MTHEX,V.MTVEX,V.MTHGX,V.MTVGX,V.MTTGL,
     34   Y.COINV,Y.COINA,Y.COUTI,
     35   Z.COINK,V.ISCHC,Z.DAECB,Z.TYTRN,Z.CORIG,Z.MTREF,Z.DAECA,
     36   I.COTAX,J.COTAX COTAF,K.COTAX COTAN,L.COTAX COTAI,M.COTAX COTAV,N.COTAX COTAK,O.COTAX COTAA,
     37   Y.INFO1,Y.GUREF,R.RGCOD RGCOF,R.RGCID RGCIF
     38   FROM
     39   HISDEP Y,
     40   HISNEG Z,
     41   VCRDAL3 V,
     42   INTERV I,
     43   INTERV J,
     44   INTERV K,
     45   INTERV L,
     46   INTERV M,
     47   INTERV N,
     48   INTERV O,
     49   RGCCAL R
     50   WHERE
     51   Y.CTFDE IN ('N','L','A','S','E') AND
     52   Y.COINL IS NOT NULL              AND
     53   Z.NUBIX=Y.NUBIX                  AND
     54   Z.DATRA=Y.DATRA                  AND -- perf
     55   V.NUBIX=Y.NUBIX                  AND
     56   V.NUFDP=Y.NUFDP                  AND
     57   I.COINT (+)= Y.COINC             AND
     58   J.COINT (+)= Z.COINF             AND
     59   K.COINT (+)= Z.COINN             AND
     60   L.COINT (+)= Y.COINI             AND
     61   M.COINT (+)= Y.COINV             AND
     62   N.COINT (+)= Z.COINK             AND
     63   O.COINT (+)= Y.COINA             AND
     64   R.COINT (+)= Y.COINL             AND
     65   R.COMAR (+)= Y.COMAR
     66  ) X,
     67  INTERV I,
     68  NATACF A,
     69  OBJCON O,
     70  MOIECH M,
     71  COMPTE C,
     72  SIEGES S
     73  WHERE
     74  (X.MTHEX!=0 OR X.CTFDE='N') AND
     75  I.COINT=X.COINL             AND
     76  A.CNACT=X.CNACT             AND
     77  O.COBCN=A.COBCN             AND
     78  M.CMECH(+)=X.CMECH          AND
     79  C.COINT(+)=X.COINC          AND
     80  C.NUCPT(+)=X.NUCPT          AND
     81  I.COSIE=S.COSIE(+);
    
    View created.
    
    Elapsed: 00:00:01.00
    SQL> explain plan for
      2  --SELECT /*+ NO_MERGE(X) */ '02/11/09','08/11/09',X.COINL,X.CNACT,SUM(X.QTFDP*X.ISCHC),X.CODEV,SUM(X.MTHEX),SUM(X.MTVEX),SUM(X.MTTGL),0
      3  SELECT '02/11/09','08/11/09',X.COINL,X.CNACT,SUM(X.QTFDP*X.ISCHC),X.CODEV,SUM(X.MTHEX),SUM(X.MTVEX),SUM(X.MTTGL),0
      4  FROM TOTO X
      5  WHERE   X.DATOP>=TO_DATE('02112009 00:00:00','DDMMYYYY HH24:MI:SS')  AND   X.DATOP<=TO_DATE('08112009 23:59:59','DDMMYYYY HH24:MI:SS')
      6  AND   X.COMAR='ICE'  AND   X.NUORD LIKE '%NY%'
      7  GROUP BY X.COINL,X.CNACT,X.CODEV ORDER BY 3,6,4;
    
    Explained.
    
    Elapsed: 00:00:00.08
    SQL> select * from table(dbms_xplan.display);
    
    PLAN_TABLE_OUTPUT
    -------------------------------------------------------------------------------------------------------------------------------------------------
    -------------------------------------------------------
    Plan hash value: 2609244192
    
    ----------------------------------------------------------------------------------------------------------------------------------
    | Id  | Operation                                             | Name             | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
    ----------------------------------------------------------------------------------------------------------------------------------
    |   0 | SELECT STATEMENT                                      |                  |    19 |  2907 |    |   304K  (1)| 01:00:51 |
    |   1 |  SORT GROUP BY                                        |                  |    19 |  2907 |    |   304K  (1)| 01:00:51 |
    |   2 |   NESTED LOOPS                                        |                  |    19 |  2907 |    |   304K  (1)| 01:00:51 |
    |   3 |    NESTED LOOPS                                       |                  |    19 |  2793 |    |   304K  (1)| 01:00:51 |
    |   4 |     NESTED LOOPS OUTER                                |                  |    19 |  2527 |    |   304K  (1)| 01:00:51 |
    |   5 |      NESTED LOOPS OUTER                               |                  |    19 |  2223 |    |   304K  (1)| 01:00:51 |
    |   6 |       NESTED LOOPS OUTER                              |                  |    19 |  2147 |    |   304K  (1)| 01:00:51 |
    |*  7 |        HASH JOIN                                      |                  |    19 |  2071 |    |   304K  (1)| 01:00:51 |
    |   8 |         VIEW                                          |                  |    37 |  3552 |    |   304K  (1)| 01:00:51 |
    |*  9 |          HASH JOIN RIGHT OUTER                        |                  |    37 | 11988 |    |   304K  (1)| 01:00:51 |
    |* 10 |           INDEX FAST FULL SCAN                        | RGCCAL1          |    45 |   495 |    |     2   (0)| 00:00:01 |
    |  11 |           NESTED LOOPS OUTER                          |                  |    37 | 11581 |    |   304K  (1)| 01:00:51 |
    |* 12 |            HASH JOIN OUTER                            |                  |    37 | 11248 |    |   304K  (1)| 01:00:51 |
    |* 13 |             HASH JOIN RIGHT OUTER                     |                  |    37 | 10767 |    |   304K  (1)| 01:00:51 |
    |  14 |              TABLE ACCESS FULL                        | COMPTE           |   308 |  3696 |    |     5   (0)| 00:00:01 |
    |  15 |              NESTED LOOPS                             |                  |    37 | 10323 |    |   304K  (1)| 01:00:51 |
    |  16 |               NESTED LOOPS OUTER                      |                  |    37 | 10175 |    |   304K  (1)| 01:00:51 |
    |  17 |                NESTED LOOPS OUTER                     |                  |    37 | 10064 |    |   304K  (1)| 01:00:51 |
    |  18 |                 NESTED LOOPS OUTER                    |                  |    37 |  9731 |    |   304K  (1)| 01:00:51 |
    |  19 |                  NESTED LOOPS OUTER                   |                  |    37 |  9398 |    |   304K  (1)| 01:00:51 |
    |  20 |                   NESTED LOOPS OUTER                  |                  |    37 |  9065 |    |   304K  (1)| 01:00:51 |
    |  21 |                    NESTED LOOPS OUTER                 |                  |    37 |  8732 |    |   304K  (1)| 01:00:51 |
    |  22 |                     NESTED LOOPS OUTER                |                  |    37 |  8399 |    |   304K  (1)| 01:00:51 |
    |* 23 |                      HASH JOIN                        |                  |    37 |  8066 |    |   304K  (1)| 01:00:51 |
    |* 24 |                       HASH JOIN                       |                  |   129 | 17286 |    | 35145   (2)| 00:07:02 |
    |  25 |                        TABLE ACCESS BY INDEX ROWID    | HISNEG           | 14478 |   975K|    |  7220   (1)| 00:01:27 |
    |* 26 |                         INDEX RANGE SCAN              | HISNEG4          | 14478 |       |    |    42   (0)| 00:00:01 |
    |* 27 |                        TABLE ACCESS FULL              | IHSDEP           | 32994 |  2094K|    | 27925   (2)| 00:05:36 |
    |  28 |                       VIEW                            | VCRDAL3          |  3589K|   287M|    |   269K  (1)| 00:53:49 |
    |  29 |                        HASH GROUP BY                  |                  |  3589K|   469M|  1019M|   269K  (1)| 00:53:49 |
    |* 30 |                         HASH JOIN                     |                  |  3589K|   469M|    |   159K  (2)| 00:31:56 |
    |* 31 |                          TABLE ACCESS FULL            | TYPSCR           |    14 |   112 |    |     5   (0)| 00:00:01 |
    |* 32 |                          HASH JOIN                    |                  |  2564K|   315M|    |   159K  (2)| 00:31:56 |
    |  33 |                           TABLE ACCESS FULL           | PARMAR           |    72 |   648 |    |     5   (0)| 00:00:01 |
    |* 34 |                           HASH JOIN                   |                  |  2564K|   293M|    |   159K  (2)| 00:31:56 |
    |  35 |                            TABLE ACCESS FULL          | DEVISE           |    44 |   308 |    |     5   (0)| 00:00:01 |
    |* 36 |                            HASH JOIN RIGHT OUTER      |                  |  2564K|   276M|   139M|   159K  (2)| 00:31:55 |
    |  37 |                             VIEW                      |                  |  2564K|   110M|    | 87750   (2)| 00:17:34 |
    |  38 |                              HASH GROUP BY            |                  |  2564K|   178M|    | 87750   (2)| 00:17:34 |
    |* 39 |                               VIEW                    |                  |  2564K|   178M|    | 87750   (2)| 00:17:34 |
    |* 40 |                                WINDOW SORT PUSHED RANK|                  |  2564K|   124M|   392M| 87750   (2)| 00:17:34 |
    |* 41 |                                 TABLE ACCESS FULL     | HISMVC           |  2564K|   124M|    | 55143   (2)| 00:11:02 |
    |  42 |                             MERGE JOIN CARTESIAN      |                  |  2564K|   166M|    | 55148   (2)| 00:11:02 |
    |  43 |                              TABLE ACCESS FULL        | RENGEN           |     1 |     4 |    |     5   (0)| 00:00:01 |
    |  44 |                              BUFFER SORT              |                  |  2564K|   156M|    | 55143   (2)| 00:11:02 |
    |* 45 |                               TABLE ACCESS FULL       | HISMVC           |  2564K|   156M|    | 55143   (2)| 00:11:02 |
    |* 46 |                      INDEX UNIQUE SCAN                | INTERV1          |     1 |     9 |    |     0   (0)| 00:00:01 |
    |* 47 |                     INDEX UNIQUE SCAN                 | INTERV1          |     1 |     9 |    |     0   (0)| 00:00:01 |
    |* 48 |                    INDEX UNIQUE SCAN                  | INTERV1          |     1 |     9 |    |     0   (0)| 00:00:01 |
    |* 49 |                   INDEX UNIQUE SCAN                   | INTERV1          |     1 |     9 |    |     0   (0)| 00:00:01 |
    |* 50 |                  INDEX UNIQUE SCAN                    | INTERV1          |     1 |     9 |    |     0   (0)| 00:00:01 |
    |* 51 |                 INDEX UNIQUE SCAN                     | INTERV1          |     1 |     9 |    |     0   (0)| 00:00:01 |
    |* 52 |                INDEX UNIQUE SCAN                      | PORTEF2          |     1 |     3 |    |     0   (0)| 00:00:01 |
    |* 53 |               INDEX UNIQUE SCAN                       | COMPTE3          |     1 |     4 |    |     0   (0)| 00:00:01 |
    |  54 |             VIEW                                      | index$_join$_029 |   379 |  4927 |    |     3   (0)| 00:00:01 |
    |* 55 |              HASH JOIN                                |                  |       |       |    |       |          |
    |  56 |               INDEX FAST FULL SCAN                    | INTERV1          |   379 |  4927 |    |     1   (0)| 00:00:01 |
    |  57 |               INDEX FAST FULL SCAN                    | INTERV3          |   379 |  4927 |    |     1   (0)| 00:00:01 |
    |* 58 |            INDEX UNIQUE SCAN                          | INTERV1          |     1 |     9 |    |     0   (0)| 00:00:01 |
    |  59 |         TABLE ACCESS FULL                             | INTERV           |   379 |  4927 |    |     5   (0)| 00:00:01 |
    |* 60 |        INDEX UNIQUE SCAN                              | SIEGE1           |     1 |     4 |    |     0   (0)| 00:00:01 |
    |* 61 |       INDEX UNIQUE SCAN                               | MOIECH1          |     1 |     4 |    |     0   (0)| 00:00:01 |
    |* 62 |      INDEX UNIQUE SCAN                                | COMPTE1          |     1 |    16 |    |     0   (0)| 00:00:01 |
    |  63 |     TABLE ACCESS BY INDEX ROWID                       | NATACF           |     1 |    14 |    |     1   (0)| 00:00:01 |
    |* 64 |      INDEX UNIQUE SCAN                                | NATACF1          |     1 |       |    |     0   (0)| 00:00:01 |
    |* 65 |    INDEX UNIQUE SCAN                                  | OBJCON1          |     1 |     6 |    |     0   (0)| 00:00:01 |
    ----------------------------------------------------------------------------------------------------------------------------------
    
    Predicate Information (identified by operation id):
    ---------------------------------------------------
    
       7 - access("I"."COINT"="X"."COINL")
       9 - access("R"."COINT"(+)="H"."COINL" AND "R"."COMAR"(+)="H"."COMAR")
      10 - filter("R"."COMAR"(+)='ICE')
      12 - access("I"."IDINT"(+)="H"."ID_COINC")
      13 - access("C"."ID_NUCPT"(+)="H"."ID_NUCPT")
      23 - access("V"."NUBIX"="H"."NUBIX" AND "V"."NUFDP"="H"."NUFDP")
           filter("V"."MTHEX"<>0 OR "H"."CTFDE"='N')
      24 - access("Z"."NUBIX"="H"."NUBIX" AND "Z"."DATRA"="H"."DATRA")
      26 - access("Z"."DATOP">=TO_DATE(' 2009-11-02 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND "Z"."DATOP"<=TO_DATE('
                  2009-11-08 23:59:59', 'syyyy-mm-dd hh24:mi:ss'))
      27 - filter("H"."COINL" IS NOT NULL AND "H"."COMAR"='ICE' AND "H"."NUORD" LIKE '%NY%' AND ("H"."CTFDE"='A' OR
                  "H"."CTFDE"='E' OR "H"."CTFDE"='L' OR "H"."CTFDE"='N' OR "H"."CTFDE"='S'))
      30 - access("S"."CTCOF"="X"."CTCOF")
      31 - filter("S"."COLAN"='A')
      32 - access("P"."COMAR"="X"."COMAR")
      34 - access("D"."CODEV"="X"."CDCOF")
      36 - access("H"."DATRA"(+)="X"."DATRA" AND "H"."DAVAL"(+)="X"."DAVAL" AND "H"."NUBIX"(+)="X"."NUBIX" AND
                  "H"."NUFDP"(+)="X"."NUFDP" AND "H"."CDCOF"(+)="X"."CDCOF" AND "H"."NCCOF"(+)="X"."NCCOF")
      39 - filter("H"."RK"=1 AND "H"."CTMVC"<>'N' AND "H"."CTMVC"<>'D' AND "NCCOF"=11)
      40 - filter(ROW_NUMBER() OVER ( PARTITION BY "H"."DATRA","H"."DAVAL","H"."NUBIX","H"."NUFDP","H"."CDCOF","H"."NTUBX"
                  ORDER BY "H"."CTCOF")<=1)
      41 - filter("H"."NCCOF"=11)
      45 - filter("X"."NCCOF"=11)
      46 - access("O"."COINT"(+)="H"."COINA")
      47 - access("M"."COINT"(+)="H"."COINV")
      48 - access("L"."COINT"(+)="H"."COINI")
      49 - access("N"."COINT"(+)="Z"."COINK")
      50 - access("K"."COINT"(+)="Z"."COINN")
      51 - access("J"."COINT"(+)="Z"."COINF")
      52 - access("P"."ID_COPOR"(+)="H"."ID_COPOR")
      53 - access("A"."ID_NUCPT"="H"."ID_NUCPI")
      55 - access(ROWID=ROWID)
      58 - access("I"."COINT"(+)="I"."COINT")
      60 - access("I"."COSIE"="S"."COSIE"(+))
      61 - access("M"."CMECH"(+)="X"."CMECH")
      62 - access("C"."COINT"(+)="X"."COINC" AND "C"."NUCPT"(+)="X"."NUCPT")
      64 - access("A"."CNACT"="X"."CNACT")
      65 - access("O"."COBCN"="A"."COBCN")
    
    Note
    -----
       - 'PLAN_TABLE' is old version
       - dynamic sampling used for this statement
    
    119 rows selected.
  • 24. Re: I have to deactivate the "_OPTIMIZER_COST_BASED_TRANSFORMATION". WHY????
    CharlesHooper Expert
    Currently Being Moderated
    Farenheiit wrote:
    Elapsed: 00:00:01.00
    SQL> explain plan for
    2  --SELECT /*+ NO_MERGE(X) */ '02/11/09','08/11/09',X.COINL,X.CNACT,SUM(X.QTFDP*X.ISCHC),X.CODEV,SUM(X.MTHEX),SUM(X.MTVEX),SUM(X.MTTGL),0
    3  SELECT '02/11/09','08/11/09',X.COINL,X.CNACT,SUM(X.QTFDP*X.ISCHC),X.CODEV,SUM(X.MTHEX),SUM(X.MTVEX),SUM(X.MTTGL),0
    4  FROM TOTO X
    5  WHERE   X.DATOP>=TO_DATE('02112009 00:00:00','DDMMYYYY HH24:MI:SS')  AND   X.DATOP<=TO_DATE('08112009 23:59:59','DDMMYYYY HH24:MI:SS')
    6  AND   X.COMAR='ICE'  AND   X.NUORD LIKE '%NY%'
    7  GROUP BY X.COINL,X.CNACT,X.CODEV ORDER BY 3,6,4;
    The only thing that I am able to tell from your explain plan is that the plan changed, not if the plan is better or worse.

    Please, do not supply the NO_MERGE hint. Do supply the gather_plan_statistics hint suggested by Joze, and then use ALLSTATS LAST as the third parameter for DBMS_XPLAN.

    Charles Hooper
    Co-author of "Expert Oracle Practices: Oracle Database Administration from the Oak Table"
    IT Manager/Oracle DBA
    K&M Machine-Fabricating, Inc.
  • 25. Re: I have to deactivate the "_OPTIMIZER_COST_BASED_TRANSFORMATION". WHY????
    705249 Newbie
    Currently Being Moderated
    It's better but not better than the explain plan generated with query transformation turned off
    SQL> SELECT /*+ gather_plan_statistics */ '02/11/09','08/11/09',X.COINL,X.CNACT,SUM(X.QTFDP*X.ISCHC),X.CODEV,SUM(X.MTHEX),SUM(X.MTVEX),SUM(X.MTTG
    L),0
      2  FROM TOTO X
      3  WHERE   X.DATOP>=TO_DATE('02112009 00:00:00','DDMMYYYY HH24:MI:SS')  AND   X.DATOP<=TO_DATE('08112009 23:59:59','DDMMYYYY HH24:MI:SS')
      4  AND   X.COMAR='ICE'  AND   X.NUORD LIKE '%NY%'
      5  GROUP BY X.COINL,X.CNACT,X.CODEV ORDER BY 3,6,4;
    
    '02/11/0 '08/11/0 COINL    CNACT    SUM(X.QTFDP*X.ISCHC) COD SUM(X.MTHEX) SUM(X.MTVEX) SUM(X.MTTGL)       0
    -------- -------- -------- -------- -------------------- --- ------------ ------------ ------------ ----------
    02/11/09 08/11/09 VMLN AM  WBSFICE                       USD         1176            0         1176       0
    
    Elapsed: 00:01:30.02
    SQL> select * from table(dbms_xplan.display_cursor(NULL,NULL, 'allstats last'));
    
    PLAN_TABLE_OUTPUT
    -------------------------------------------------------------------------------------------------------------------------------------------------
    -------------------------------------------------------------------------------------------------------------------------------------------------
    ----------
    SQL_ID  2dtj7kqknfprz, child number 0
    -------------------------------------
    SELECT /*+ gather_plan_statistics */ '02/11/09','08/11/09',X.COINL,X.CNACT,SUM(X.QTFDP*X.ISCHC),X.CODEV,SUM(X.MTHEX),SUM(X.MTVEX),SUM(X.MTTGL),0
    FROM TOTO X WHERE
    X.DATOP>=TO_DATE('02112009 00:00:00','DDMMYYYY HH24:MI:SS')  AND   X.DATOP<=TO_DATE('08112009 23:59:59','DDMMYYYY HH24:MI:SS') AND   X.COMAR='ICE
    '  AND   X.NUORD LIKE '%NY%'
    GROUP BY X.COINL,X.CNACT,X.CODEV ORDER BY 3,6,4
    
    Plan hash value: 2609244192
    
    -------------------------------------------------------------------------------------------------------------------------------------------------
    ------------------------------------------
    | Id  | Operation                                             | Name             | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Reads  | Wri
    tes |   OMem |  1Mem | Used-Mem | Used-Tmp|
    -------------------------------------------------------------------------------------------------------------------------------------------------
    ------------------------------------------
    |   1 |  SORT GROUP BY                                        |                  |      1 |     19 |   1 |00:01:27.32 |     647K|       672K|  43
    789 |   2048 |  2048 | 2048  (0)|         |
    |   2 |   NESTED LOOPS                                        |                  |      1 |     19 |   3 |00:01:27.32 |     647K|       672K|  43
    789 |        |       |          |         |
    |   3 |    NESTED LOOPS                                       |                  |      1 |     19 |   3 |00:01:27.31 |     647K|       672K|  43
    789 |        |       |          |         |
    |   4 |     NESTED LOOPS OUTER                                |                  |      1 |     19 |   3 |00:01:27.31 |     647K|       672K|  43
    789 |        |       |          |         |
    |   5 |      NESTED LOOPS OUTER                               |                  |      1 |     19 |   3 |00:01:27.31 |     647K|       672K|  43
    789 |        |       |          |         |
    |   6 |       NESTED LOOPS OUTER                              |                  |      1 |     19 |   3 |00:01:27.31 |     647K|       672K|  43
    789 |        |       |          |         |
    |*  7 |        HASH JOIN                                      |                  |      1 |     19 |   3 |00:01:27.31 |     647K|       672K|  43
    789 |    845K|   845K|  362K (0)|         |
    |   8 |         VIEW                                          |                  |      1 |     37 |   3 |00:01:27.31 |     647K|       672K|  43
    789 |        |       |          |         |
    |*  9 |          HASH JOIN RIGHT OUTER                        |                  |      1 |     37 |   3 |00:01:27.31 |     647K|       672K|  43
    789 |   1155K|  1155K| 1213K (0)|         |
    |* 10 |           INDEX FAST FULL SCAN                        | RGCCAL1          |      1 |     45 |  45 |00:00:00.01 |  3 |      1 |      0 |
        |        |          |         |
    |  11 |           NESTED LOOPS OUTER                          |                  |      1 |     37 |   3 |00:01:27.31 |     647K|       672K|  43
    789 |        |       |          |         |
    |* 12 |            HASH JOIN OUTER                            |                  |      1 |     37 |   3 |00:01:27.31 |     647K|       672K|  43
    789 |    832K|   832K|  343K (0)|         |
    |* 13 |             HASH JOIN RIGHT OUTER                     |                  |      1 |     37 |   3 |00:01:27.31 |     647K|       672K|  43
    789 |   1155K|  1155K| 1222K (0)|         |
    |  14 |              TABLE ACCESS FULL                        | COMPTE           |      1 |    308 | 310 |00:00:00.01 | 15 |      5 |      0 |
        |        |          |         |
    |  15 |              NESTED LOOPS                             |                  |      1 |     37 |   3 |00:01:27.30 |     647K|       672K|  43
    789 |        |       |          |         |
    |  16 |               NESTED LOOPS OUTER                      |                  |      1 |     37 |   3 |00:01:27.30 |     647K|       672K|  43
    789 |        |       |          |         |
    |  17 |                NESTED LOOPS OUTER                     |                  |      1 |     37 |   3 |00:01:27.30 |     647K|       672K|  43
    789 |        |       |          |         |
    |  18 |                 NESTED LOOPS OUTER                    |                  |      1 |     37 |   3 |00:01:27.30 |     647K|       672K|  43
    789 |        |       |          |         |
    |  19 |                  NESTED LOOPS OUTER                   |                  |      1 |     37 |   3 |00:01:27.30 |     647K|       672K|  43
    789 |        |       |          |         |
    |  20 |                   NESTED LOOPS OUTER                  |                  |      1 |     37 |   3 |00:01:27.30 |     647K|       672K|  43
    789 |        |       |          |         |
    |  21 |                    NESTED LOOPS OUTER                 |                  |      1 |     37 |   3 |00:01:27.30 |     647K|       672K|  43
    789 |        |       |          |         |
    |  22 |                     NESTED LOOPS OUTER                |                  |      1 |     37 |   3 |00:01:27.30 |     647K|       672K|  43
    789 |        |       |          |         |
    |* 23 |                      HASH JOIN                        |                  |      1 |     37 |   3 |00:01:27.30 |     647K|       672K|  43
    789 |    789K|   789K|  570K (0)|         |
    |* 24 |                       HASH JOIN                       |                  |      1 |    129 |   3 |00:00:04.91 |     141K|       124K|
      0 |   2868K|  1148K| 3220K (0)|         |
    |  25 |                        TABLE ACCESS BY INDEX ROWID    | HISNEG           |      1 |  14478 |  32487 |00:00:01.04 |   13490 |    937 |
      0 |        |       |          |         |
    |* 26 |                         INDEX RANGE SCAN              | HISNEG4          |      1 |  14478 |  32487 |00:00:00.15 |     104 |    104 |
      0 |        |       |          |         |
    |* 27 |                        TABLE ACCESS FULL              | IHSDEP           |      1 |  32994 |   4151 |00:00:03.79 |     127K|    123K|
      0 |        |       |          |         |
    |  28 |                       VIEW                            | VCRDAL3          |      1 |   3589K|   2001K|00:01:20.21 |     505K|    548K|  43
    789 |        |       |          |         |
    |  29 |                        HASH GROUP BY                  |                  |      1 |   3589K|   2001K|00:01:18.21 |     505K|    548K|  43
    789 |    150M|  8280K|  100M (1)|   53248 |
    |* 30 |                         HASH JOIN                     |                  |      1 |   3589K|   2103K|00:01:12.22 |     505K|    541K|  37
    403 |   1348K|  1348K| 1194K (0)|         |
    |* 31 |                          TABLE ACCESS FULL            | TYPSCR           |      1 |     14 |  14 |00:00:00.02 | 15 |     13 |      0 |
        |        |          |         |
    |* 32 |                          HASH JOIN                    |                  |      1 |   2564K|   2103K|00:01:07.99 |     505K|    541K|  37
    403 |   1179K|  1179K| 1219K (0)|         |
    |  33 |                           TABLE ACCESS FULL           | PARMAR           |      1 |     72 |  72 |00:00:00.01 | 15 |      0 |      0 |
        |        |          |         |
    |* 34 |                           HASH JOIN                   |                  |      1 |   2564K|   2103K|00:01:05.89 |     505K|    541K|  37
    403 |   1269K|  1269K| 1210K (0)|         |
    |  35 |                            TABLE ACCESS FULL          | DEVISE           |      1 |     44 |  44 |00:00:00.01 | 15 |      0 |      0 |
        |        |          |         |
    |* 36 |                            HASH JOIN RIGHT OUTER      |                  |      1 |   2564K|   2103K|00:01:03.79 |     505K|    541K|  37
    403 |   1593K|  1593K| 2101K (0)|         |
    |  37 |                             VIEW                      |                  |      1 |   2564K|   0 |00:00:32.45 |     252K|       268K|  16
    581 |        |       |          |         |
    |  38 |                              HASH GROUP BY            |                  |      1 |   2564K|   0 |00:00:32.45 |     252K|       268K|  16
    581 |    774K|   774K|          |         |
    |* 39 |                               VIEW                    |                  |      1 |   2564K|   0 |00:00:32.45 |     252K|       268K|  16
    581 |        |       |          |         |
    |* 40 |                                WINDOW SORT PUSHED RANK|                  |      1 |   2564K|   2103K|00:00:30.02 |     252K|    268K|  16
    581 |    145M|  4078K|   31M (1)|     130K|
    |* 41 |                                 TABLE ACCESS FULL     | HISMVC           |      1 |   2564K|   2103K|00:00:18.96 |     252K|    252K|
      0 |        |       |          |         |
    |  42 |                             MERGE JOIN CARTESIAN      |                  |      1 |   2564K|   2103K|00:00:27.13 |     252K|    272K|  20
    822 |        |       |          |         |
    |  43 |                              TABLE ACCESS FULL        | RENGEN           |      1 |      1 |   1 |00:00:00.02 | 15 |      2 |      0 |
        |        |          |         |
    |  44 |                              BUFFER SORT              |                  |      1 |   2564K|   2103K|00:00:25.00 |     252K|    272K|  20
    822 |    183M|  4543K|   66M (0)|     163K|
    |* 45 |                               TABLE ACCESS FULL       | HISMVC           |      1 |   2564K|   2103K|00:00:21.06 |     252K|    252K|
      0 |        |       |          |         |
    |* 46 |                      INDEX UNIQUE SCAN                | INTERV1          |      3 |      1 |   0 |00:00:00.01 |  0 |      0 |      0 |
        |        |          |         |
    |* 47 |                     INDEX UNIQUE SCAN                 | INTERV1          |      3 |      1 |   0 |00:00:00.01 |  0 |      0 |      0 |
        |        |          |         |
    |* 48 |                    INDEX UNIQUE SCAN                  | INTERV1          |      3 |      1 |   3 |00:00:00.01 |  2 |      0 |      0 |
        |        |          |         |
    |* 49 |                   INDEX UNIQUE SCAN                   | INTERV1          |      3 |      1 |   0 |00:00:00.01 |  0 |      0 |      0 |
        |        |          |         |
    |* 50 |                  INDEX UNIQUE SCAN                    | INTERV1          |      3 |      1 |   3 |00:00:00.01 |  2 |      0 |      0 |
        |        |          |         |
    |* 51 |                 INDEX UNIQUE SCAN                     | INTERV1          |      3 |      1 |   3 |00:00:00.01 |  2 |      0 |      0 |
        |        |          |         |
    |* 52 |                INDEX UNIQUE SCAN                      | PORTEF2          |      3 |      1 |   0 |00:00:00.01 |  0 |      0 |      0 |
        |        |          |         |
    |* 53 |               INDEX UNIQUE SCAN                       | COMPTE3          |      3 |      1 |   3 |00:00:00.01 |  2 |      1 |      0 |
        |        |          |         |
    |  54 |             VIEW                                      | index$_join$_029 |      1 |    379 | 383 |00:00:00.01 |  6 |      0 |      0 |
        |        |          |         |
    |* 55 |              HASH JOIN                                |                  |      1 |        | 383 |00:00:00.01 |  6 |      0 |      0 |  1
    023K|   1023K| 1388K (0)|         |
    |  56 |               INDEX FAST FULL SCAN                    | INTERV1          |      1 |    379 | 383 |00:00:00.01 |  3 |      0 |      0 |
        |        |          |         |
    |  57 |               INDEX FAST FULL SCAN                    | INTERV3          |      1 |    379 | 383 |00:00:00.01 |  3 |      0 |      0 |
        |        |          |         |
    |* 58 |            INDEX UNIQUE SCAN                          | INTERV1          |      3 |      1 |   0 |00:00:00.01 |  0 |      0 |      0 |
        |        |          |         |
    |  59 |         TABLE ACCESS FULL                             | INTERV           |      1 |    379 | 383 |00:00:00.01 | 15 |      0 |      0 |
        |        |          |         |
    |* 60 |        INDEX UNIQUE SCAN                              | SIEGE1           |      3 |      1 |   0 |00:00:00.01 |  0 |      0 |      0 |
        |        |          |         |
    |* 61 |       INDEX UNIQUE SCAN                               | MOIECH1          |      3 |      1 |   3 |00:00:00.01 |  2 |      0 |      0 |
        |        |          |         |
    |* 62 |      INDEX UNIQUE SCAN                                | COMPTE1          |      3 |      1 |   0 |00:00:00.01 |  0 |      0 |      0 |
        |        |          |         |
    |  63 |     TABLE ACCESS BY INDEX ROWID                       | NATACF           |      3 |      1 |   3 |00:00:00.01 |  8 |      0 |      0 |
        |        |          |         |
    |* 64 |      INDEX UNIQUE SCAN                                | NATACF1          |      3 |      1 |   3 |00:00:00.01 |  5 |      0 |      0 |
        |        |          |         |
    |* 65 |    INDEX UNIQUE SCAN                                  | OBJCON1          |      3 |      1 |   3 |00:00:00.01 |  5 |      1 |      0 |
        |        |          |         |
    -------------------------------------------------------------------------------------------------------------------------------------------------
    ------------------------------------------
    
    Predicate Information (identified by operation id):
    ---------------------------------------------------
    
       7 - access("I"."COINT"="X"."COINL")
       9 - access("R"."COINT"="H"."COINL" AND "R"."COMAR"="H"."COMAR")
      10 - filter("R"."COMAR"='ICE')
      12 - access("I"."IDINT"="H"."ID_COINC")
      13 - access("C"."ID_NUCPT"="H"."ID_NUCPT")
      23 - access("V"."NUBIX"="H"."NUBIX" AND "V"."NUFDP"="H"."NUFDP")
           filter(("V"."MTHEX"<>0 OR "H"."CTFDE"='N'))
      24 - access("Z"."NUBIX"="H"."NUBIX" AND "Z"."DATRA"="H"."DATRA")
      26 - access("Z"."DATOP">=TO_DATE(' 2009-11-02 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND "Z"."DATOP"<=TO_DATE(' 2009-11-08 23:59:59', 'syyyy-mm-d
    d hh24:mi:ss'))
      27 - filter(("H"."COINL" IS NOT NULL AND "H"."COMAR"='ICE' AND "H"."NUORD" LIKE '%NY%' AND INTERNAL_FUNCTION("H"."CTFDE")))
      30 - access("S"."CTCOF"="X"."CTCOF")
      31 - filter("S"."COLAN"='A')
      32 - access("P"."COMAR"="X"."COMAR")
      34 - access("D"."CODEV"="X"."CDCOF")
      36 - access("H"."DATRA"="X"."DATRA" AND "H"."DAVAL"="X"."DAVAL" AND "H"."NUBIX"="X"."NUBIX" AND "H"."NUFDP"="X"."NUFDP" AND "H"."CDCOF"="X"."CD
    COF" AND "H"."NCCOF"="X"."NCCOF")
      39 - filter(("H"."RK"=1 AND "H"."CTMVC"<>'N' AND "H"."CTMVC"<>'D' AND "NCCOF"=11))
      40 - filter(ROW_NUMBER() OVER ( PARTITION BY "H"."DATRA","H"."DAVAL","H"."NUBIX","H"."NUFDP","H"."CDCOF","H"."NTUBX" ORDER BY "H"."CTCOF")<=1)
      41 - filter("H"."NCCOF"=11)
      45 - filter("X"."NCCOF"=11)
      46 - access("O"."COINT"="H"."COINA")
      47 - access("M"."COINT"="H"."COINV")
      48 - access("L"."COINT"="H"."COINI")
      49 - access("N"."COINT"="Z"."COINK")
      50 - access("K"."COINT"="Z"."COINN")
      51 - access("J"."COINT"="Z"."COINF")
      52 - access("P"."ID_COPOR"="H"."ID_COPOR")
      53 - access("A"."ID_NUCPT"="H"."ID_NUCPI")
      55 - access(ROWID=ROWID)
      58 - access("I"."COINT"="I"."COINT")
      60 - access("I"."COSIE"="S"."COSIE")
      61 - access("M"."CMECH"="X"."CMECH")
      62 - access("C"."COINT"="X"."COINC" AND "C"."NUCPT"="X"."NUCPT")
      64 - access("A"."CNACT"="X"."CNACT")
      65 - access("O"."COBCN"="A"."COBCN")
    
    Note
    -----
       - dynamic sampling used for this statement
  • 26. Re: I have to deactivate the "_OPTIMIZER_COST_BASED_TRANSFORMATION". WHY????
    705249 Newbie
    Currently Being Moderated
    here is an extract of the 10053 trace file.
    How to interpret this part?
    ********************************
    COST-BASED QUERY TRANSFORMATIONS
    ********************************
    FPD: Considering simple filter push (pre rewrite) in SEL$F5BB74E1 (#0)
    FPD:   Current where clause predicates in SEL$F5BB74E1 (#0) :
             
    "X"."DATOP">=TO_DATE('02112009 00:00:00','DDMMYYYY HH24:MI:SS') AND "X"."DATOP"<=TO_DATE('08112009 23:59:59','DDMMYYYY HH24:MI:SS') AND "X"."COMAR"='ICE' AND "X"."NUORD" LIKE '%NY%' AND ("X"."MTHEX"<>0 OR "X"."CTFDE"='N') AND "I"."COINT"="X"."COINL" AND "A"."CNACT"="X"."CNACT" AND "O"."COBCN"="A"."COBCN" AND "M"."CMECH"(+)="X"."CMECH" AND "C"."COINT"(+)="X"."COINC" AND "C"."NUCPT"(+)="X"."NUCPT" AND "I"."COSIE"="S"."COSIE"(+)
    kkogcp: try to generate transitive predicate from check constraints for SEL$F5BB74E1 (#0)
    predicates with check contraints: 
    "X"."DATOP">=TO_DATE('02112009 00:00:00','DDMMYYYY HH24:MI:SS') AND "X"."DATOP"<=TO_DATE('08112009 23:59:59','DDMMYYYY HH24:MI:SS') AND "X"."COMAR"='ICE' AND "X"."NUORD" LIKE '%NY%' AND ("X"."MTHEX"<>0 OR "X"."CTFDE"='N') AND "I"."COINT"="X"."COINL" AND "A"."CNACT"="X"."CNACT" AND "O"."COBCN"="A"."COBCN" AND "M"."CMECH"(+)="X"."CMECH" AND "C"."COINT"(+)="X"."COINC" AND "C"."NUCPT"(+)="X"."NUCPT" AND "I"."COSIE"="S"."COSIE"(+)
    after transitive predicate generation: 
    "X"."DATOP">=TO_DATE('02112009 00:00:00','DDMMYYYY HH24:MI:SS') AND "X"."DATOP"<=TO_DATE('08112009 23:59:59','DDMMYYYY HH24:MI:SS') AND "X"."COMAR"='ICE' AND "X"."NUORD" LIKE '%NY%' AND ("X"."MTHEX"<>0 OR "X"."CTFDE"='N') AND "I"."COINT"="X"."COINL" AND "A"."CNACT"="X"."CNACT" AND "O"."COBCN"="A"."COBCN" AND "M"."CMECH"(+)="X"."CMECH" AND "C"."COINT"(+)="X"."COINC" AND "C"."NUCPT"(+)="X"."NUCPT" AND "I"."COSIE"="S"."COSIE"(+)
    finally: 
    "X"."DATOP">=TO_DATE('02112009 00:00:00','DDMMYYYY HH24:MI:SS') AND "X"."DATOP"<=TO_DATE('08112009 23:59:59','DDMMYYYY HH24:MI:SS') AND "X"."COMAR"='ICE' AND "X"."NUORD" LIKE '%NY%' AND ("X"."MTHEX"<>0 OR "X"."CTFDE"='N') AND "I"."COINT"="X"."COINL" AND "A"."CNACT"="X"."CNACT" AND "O"."COBCN"="A"."COBCN" AND "M"."CMECH"(+)="X"."CMECH" AND "C"."COINT"(+)="X"."COINC" AND "C"."NUCPT"(+)="X"."NUCPT" AND "I"."COSIE"="S"."COSIE"(+)
    FPD:   Following are pushed to where clause of SEL$07BDC5B4 (#0) :
             "Z"."DATOP">=TO_DATE('02112009 00:00:00','DDMMYYYY HH24:MI:SS') AND "Z"."DATOP"<=TO_DATE('08112009 23:59:59','DDMMYYYY HH24:MI:SS') AND "H"."COMAR"='ICE' AND "H"."NUORD" LIKE '%NY%' AND ("V"."MTHEX"<>0 OR "H"."CTFDE"='N')
    FPD: Considering simple filter push (pre rewrite) in SEL$07BDC5B4 (#0)
    FPD:   Current where clause predicates in SEL$07BDC5B4 (#0) :
             
    ("H"."CTFDE"='N' OR "H"."CTFDE"='L' OR "H"."CTFDE"='A' OR "H"."CTFDE"='S' OR "H"."CTFDE"='E') AND "H"."COINL" IS NOT NULL AND "Z"."NUBIX"="H"."NUBIX" AND "Z"."DATRA"="H"."DATRA" AND "V"."NUBIX"="H"."NUBIX" AND "V"."NUFDP"="H"."NUFDP" AND "I"."COINT"(+)="I"."COINT" AND "J"."COINT"(+)="Z"."COINF" AND "K"."COINT"(+)="Z"."COINN" AND "L"."COINT"(+)="H"."COINI" AND "M"."COINT"(+)="H"."COINV" AND "N"."COINT"(+)="Z"."COINK" AND "O"."COINT"(+)="H"."COINA" AND "R"."COINT"(+)="H"."COINL" AND "R"."COMAR"(+)="H"."COMAR" AND "I"."IDINT"(+)="H"."ID_COINC" AND "C"."ID_NUCPT"(+)="H"."ID_NUCPT" AND "A"."ID_NUCPT"="H"."ID_NUCPI" AND "P"."ID_COPOR"(+)="H"."ID_COPOR" AND "Z"."DATOP">=TO_DATE('02112009 00:00:00','DDMMYYYY HH24:MI:SS') AND "Z"."DATOP"<=TO_DATE('08112009 23:59:59','DDMMYYYY HH24:MI:SS') AND "H"."COMAR"='ICE' AND "H"."NUORD" LIKE '%NY%' AND ("V"."MTHEX"<>0 OR "H"."CTFDE"='N')
    kkogcp: try to generate transitive predicate from check constraints for SEL$07BDC5B4 (#0)
    predicates with check contraints: 
    ("H"."CTFDE"='N' OR "H"."CTFDE"='L' OR "H"."CTFDE"='A' OR "H"."CTFDE"='S' OR "H"."CTFDE"='E') AND "H"."COINL" IS NOT NULL AND "Z"."NUBIX"="H"."NUBIX" AND "Z"."DATRA"="H"."DATRA" AND "V"."NUBIX"="H"."NUBIX" AND "V"."NUFDP"="H"."NUFDP" AND "I"."COINT"(+)="I"."COINT" AND "J"."COINT"(+)="Z"."COINF" AND "K"."COINT"(+)="Z"."COINN" AND "L"."COINT"(+)="H"."COINI" AND "M"."COINT"(+)="H"."COINV" AND "N"."COINT"(+)="Z"."COINK" AND "O"."COINT"(+)="H"."COINA" AND "R"."COINT"(+)="H"."COINL" AND "R"."COMAR"(+)="H"."COMAR" AND "I"."IDINT"(+)="H"."ID_COINC" AND "C"."ID_NUCPT"(+)="H"."ID_NUCPT" AND "A"."ID_NUCPT"="H"."ID_NUCPI" AND "P"."ID_COPOR"(+)="H"."ID_COPOR" AND "Z"."DATOP">=TO_DATE('02112009 00:00:00','DDMMYYYY HH24:MI:SS') AND "Z"."DATOP"<=TO_DATE('08112009 23:59:59','DDMMYYYY HH24:MI:SS') AND "H"."COMAR"='ICE' AND "H"."NUORD" LIKE '%NY%' AND ("V"."MTHEX"<>0 OR "H"."CTFDE"='N') AND "R"."COMAR"(+)='ICE'
    after transitive predicate generation: 
    ("H"."CTFDE"='N' OR "H"."CTFDE"='L' OR "H"."CTFDE"='A' OR "H"."CTFDE"='S' OR "H"."CTFDE"='E') AND "H"."COINL" IS NOT NULL AND "Z"."NUBIX"="H"."NUBIX" AND "Z"."DATRA"="H"."DATRA" AND "V"."NUBIX"="H"."NUBIX" AND "V"."NUFDP"="H"."NUFDP" AND "I"."COINT"(+)="I"."COINT" AND "J"."COINT"(+)="Z"."COINF" AND "K"."COINT"(+)="Z"."COINN" AND "L"."COINT"(+)="H"."COINI" AND "M"."COINT"(+)="H"."COINV" AND "N"."COINT"(+)="Z"."COINK" AND "O"."COINT"(+)="H"."COINA" AND "R"."COINT"(+)="H"."COINL" AND "R"."COMAR"(+)="H"."COMAR" AND "I"."IDINT"(+)="H"."ID_COINC" AND "C"."ID_NUCPT"(+)="H"."ID_NUCPT" AND "A"."ID_NUCPT"="H"."ID_NUCPI" AND "P"."ID_COPOR"(+)="H"."ID_COPOR" AND "Z"."DATOP">=TO_DATE('02112009 00:00:00','DDMMYYYY HH24:MI:SS') AND "Z"."DATOP"<=TO_DATE('08112009 23:59:59','DDMMYYYY HH24:MI:SS') AND "H"."COMAR"='ICE' AND "H"."NUORD" LIKE '%NY%' AND ("V"."MTHEX"<>0 OR "H"."CTFDE"='N') AND "R"."COMAR"(+)='ICE'
    finally: 
    ("H"."CTFDE"='N' OR "H"."CTFDE"='L' OR "H"."CTFDE"='A' OR "H"."CTFDE"='S' OR "H"."CTFDE"='E') AND "H"."COINL" IS NOT NULL AND "Z"."NUBIX"="H"."NUBIX" AND "Z"."DATRA"="H"."DATRA" AND "V"."NUBIX"="H"."NUBIX" AND "V"."NUFDP"="H"."NUFDP" AND "I"."COINT"(+)="I"."COINT" AND "J"."COINT"(+)="Z"."COINF" AND "K"."COINT"(+)="Z"."COINN" AND "L"."COINT"(+)="H"."COINI" AND "M"."COINT"(+)="H"."COINV" AND "N"."COINT"(+)="Z"."COINK" AND "O"."COINT"(+)="H"."COINA" AND "R"."COINT"(+)="H"."COINL" AND "R"."COMAR"(+)="H"."COMAR" AND "I"."IDINT"(+)="H"."ID_COINC" AND "C"."ID_NUCPT"(+)="H"."ID_NUCPT" AND "A"."ID_NUCPT"="H"."ID_NUCPI" AND "P"."ID_COPOR"(+)="H"."ID_COPOR" AND "Z"."DATOP">=TO_DATE('02112009 00:00:00','DDMMYYYY HH24:MI:SS') AND "Z"."DATOP"<=TO_DATE('08112009 23:59:59','DDMMYYYY HH24:MI:SS') AND "H"."COMAR"='ICE' AND "H"."NUORD" LIKE '%NY%' AND ("V"."MTHEX"<>0 OR "H"."CTFDE"='N') AND "R"."COMAR"(+)='ICE'
    FPD:   Following transitive predicates are generated in SEL$07BDC5B4 (#0) :
             "R"."COMAR"(+)='ICE'
    FPD: Considering simple filter push (pre rewrite) in SEL$5 (#0)
    FPD:   Current where clause predicates in SEL$5 (#0) :
             
    "X"."NCCOF"=11 AND "P"."COMAR"="X"."COMAR" AND "D"."CODEV"="X"."CDCOF" AND "S"."CTCOF"="X"."CTCOF" AND "S"."COLAN"='A' AND "H"."DATRA"(+)="X"."DATRA" AND "H"."DAVAL"(+)="X"."DAVAL" AND "H"."NUBIX"(+)="X"."NUBIX" AND "H"."NUFDP"(+)="X"."NUFDP" AND "H"."CDCOF"(+)="X"."CDCOF" AND "H"."NCCOF"(+)="X"."NCCOF"
    kkogcp: try to generate transitive predicate from check constraints for SEL$5 (#0)
    predicates with check contraints: 
    "X"."NCCOF"=11 AND "P"."COMAR"="X"."COMAR" AND "D"."CODEV"="X"."CDCOF" AND "S"."CTCOF"="X"."CTCOF" AND "S"."COLAN"='A' AND "H"."DATRA"(+)="X"."DATRA" AND "H"."DAVAL"(+)="X"."DAVAL" AND "H"."NUBIX"(+)="X"."NUBIX" AND "H"."NUFDP"(+)="X"."NUFDP" AND "H"."CDCOF"(+)="X"."CDCOF" AND "H"."NCCOF"(+)="X"."NCCOF" AND "H"."NCCOF"(+)=11
    after transitive predicate generation: 
    "X"."NCCOF"=11 AND "P"."COMAR"="X"."COMAR" AND "D"."CODEV"="X"."CDCOF" AND "S"."CTCOF"="X"."CTCOF" AND "S"."COLAN"='A' AND "H"."DATRA"(+)="X"."DATRA" AND "H"."DAVAL"(+)="X"."DAVAL" AND "H"."NUBIX"(+)="X"."NUBIX" AND "H"."NUFDP"(+)="X"."NUFDP" AND "H"."CDCOF"(+)="X"."CDCOF" AND "H"."NCCOF"(+)="X"."NCCOF" AND "H"."NCCOF"(+)=11
    finally: 
    "X"."NCCOF"=11 AND "P"."COMAR"="X"."COMAR" AND "D"."CODEV"="X"."CDCOF" AND "S"."CTCOF"="X"."CTCOF" AND "S"."COLAN"='A' AND "H"."DATRA"(+)="X"."DATRA" AND "H"."DAVAL"(+)="X"."DAVAL" AND "H"."NUBIX"(+)="X"."NUBIX" AND "H"."NUFDP"(+)="X"."NUFDP" AND "H"."CDCOF"(+)="X"."CDCOF" AND "H"."NCCOF"(+)="X"."NCCOF" AND "H"."NCCOF"(+)=11
    FPD:   Following transitive predicates are generated in SEL$5 (#0) :
             "H"."NCCOF"(+)=11
    FPD: Considering simple filter push (pre rewrite) in SEL$6 (#0)
    FPD:   Current where clause predicates in SEL$6 (#0) :
             "H"."RK"=1 AND "H"."CTMVC"<>'N' AND "H"."CTMVC"<>'D'
    kkogcp: try to generate transitive predicate from check constraints for SEL$6 (#0)
    predicates with check contraints: "H"."RK"=1 AND "H"."CTMVC"<>'N' AND "H"."CTMVC"<>'D'
    after transitive predicate generation: "H"."RK"=1 AND "H"."CTMVC"<>'N' AND "H"."CTMVC"<>'D'
    finally: "H"."RK"=1 AND "H"."CTMVC"<>'N' AND "H"."CTMVC"<>'D'
    FPD: Considering simple filter push (pre rewrite) in SEL$7 (#0)
    FPD:   Current where clause predicates in SEL$7 (#0) :
             "H"."NCCOF"=11
    Registered qb: SEL$F5BB74E1 0x9e154de0 (COPY SEL$F5BB74E1)
      signature(): NULL
    Registered qb: SEL$07BDC5B4 0x8c9095a8 (COPY SEL$07BDC5B4)
      signature(): NULL
    Registered qb: SEL$5 0xc8a9e8d0 (COPY SEL$5)
      signature(): NULL
    Registered qb: SEL$6 0xc8a9de18 (COPY SEL$6)
      signature(): NULL
    Registered qb: SEL$7 0x9e8814d0 (COPY SEL$7)
      signature(): NULL
    *****************************
    Cost-Based Subquery Unnesting
    *****************************
    SU: No subqueries to consider in query block SEL$7 (#5).
    SU: No subqueries to consider in query block SEL$6 (#4).
    SU: No subqueries to consider in query block SEL$5 (#3).
    SU: No subqueries to consider in query block SEL$07BDC5B4 (#2).
    SU: No subqueries to consider in query block SEL$F5BB74E1 (#1).
    *******************************
    Cost-Based Complex View Merging
    *******************************
    CVM: Finding query blocks in SEL$F5BB74E1 (#1) that are valid to merge.
    voptcojrj: logp:0x874ac420
    voptcojrj:"I"."COINT"="X"."COINL"
    rejected
    voptcojrj:"A"."CNACT"="X"."CNACT"
    rejected
    voptcojrj:"O"."COBCN"="A"."COBCN"
    rejected
    voptcojrj:"M"."CMECH"(+)="X"."CMECH"
    rejected
    voptcojrj:"C"."COINT"(+)="X"."COINC"
    rejected
    voptcojrj:"C"."NUCPT"(+)="X"."NUCPT"
    rejected
    voptcojrj:"I"."COSIE"="S"."COSIE"(+)
    rejected
    CVM:   Checking validity of merging SEL$07BDC5B4 (#2)
    voptcojrj: logp:0xc2f6d030
    voptcojrj:"H"."COINL" IS NOT NULL
    considered
    voptcojrj:"Z"."NUBIX"="H"."NUBIX"
    rejected
    voptcojrj:"Z"."DATRA"="H"."DATRA"
    rejected
    voptcojrj:"V"."NUBIX"="H"."NUBIX"
    rejected
    voptcojrj:"V"."NUFDP"="H"."NUFDP"
    rejected
    voptcojrj:"I"."COINT"(+)="I"."COINT"
    rejected
    voptcojrj:"J"."COINT"(+)="Z"."COINF"
    rejected
    voptcojrj:"K"."COINT"(+)="Z"."COINN"
    rejected
    voptcojrj:"L"."COINT"(+)="H"."COINI"
    rejected
    voptcojrj:"M"."COINT"(+)="H"."COINV"
    rejected
    voptcojrj:"N"."COINT"(+)="Z"."COINK"
    rejected
    voptcojrj:"O"."COINT"(+)="H"."COINA"
    rejected
    voptcojrj:"R"."COINT"(+)="H"."COINL"
    rejected
    voptcojrj:"R"."COMAR"(+)="H"."COMAR"
    rejected
    voptcojrj:"I"."IDINT"(+)="H"."ID_COINC"
    rejected
    voptcojrj:"C"."ID_NUCPT"(+)="H"."ID_NUCPT"
    rejected
    voptcojrj:"A"."ID_NUCPT"="H"."ID_NUCPI"
    rejected
    voptcojrj:"P"."ID_COPOR"(+)="H"."ID_COPOR"
    rejected
    voptcojrj:"Z"."DATOP">=TO_DATE('02112009 00:00:00','DDMMYYYY HH24:MI:SS')
    rejected
    voptcojrj:"Z"."DATOP"<=TO_DATE('08112009 23:59:59','DDMMYYYY HH24:MI:SS')
    rejected
    voptcojrj:"H"."COMAR"='ICE'
    rejected
    voptcojrj:"H"."NUORD" LIKE '%NY%'
    rejected
    voptcojrj:"R"."COMAR"(+)='ICE'
    rejected
    CVM:   Checking validity of merging SEL$5 (#3)
    voptcojrj: logp:0x773e26c8
    voptcojrj:"X"."NCCOF"=11
    rejected
    voptcojrj:"P"."COMAR"="X"."COMAR"
    rejected
    voptcojrj:"D"."CODEV"="X"."CDCOF"
    rejected
    voptcojrj:"S"."CTCOF"="X"."CTCOF"
    rejected
    voptcojrj:"S"."COLAN"='A'
    rejected
    voptcojrj:"H"."DATRA"(+)="X"."DATRA"
    rejected
    voptcojrj:"H"."DAVAL"(+)="X"."DAVAL"
    rejected
    voptcojrj:"H"."NUBIX"(+)="X"."NUBIX"
    rejected
    voptcojrj:"H"."NUFDP"(+)="X"."NUFDP"
    rejected
    voptcojrj:"H"."CDCOF"(+)="X"."CDCOF"
    rejected
    voptcojrj:"H"."NCCOF"(+)="X"."NCCOF"
    rejected
    voptcojrj:"H"."NCCOF"(+)=11
    rejected
    CVM:   Checking validity of merging SEL$6 (#4)
    CVM:   Checking validity of merging SEL$7 (#5)
    CVM:     CVM bypassed: Window functions in this view
    CVM:     CVM bypassed: View on right side of outer join contains view with illegal column.
    CVM:     CVM bypassed: View on right side of outer join + multiple table
    CVM:     CVM bypassed: Aggregate in OR predicate.
    *************************
    Set-Join Conversion (SJC)
    *************************
    SJC: Considering set-join conversion in SEL$F5BB74E1 (#1).
    *************************
    Set-Join Conversion (SJC)
    *************************
    SJC: Considering set-join conversion in SEL$07BDC5B4 (#2).
    *************************
    Set-Join Conversion (SJC)
    *************************
    SJC: Considering set-join conversion in SEL$5 (#3).
    *************************
    Set-Join Conversion (SJC)
    *************************
    SJC: Considering set-join conversion in SEL$6 (#4).
    *************************
    Set-Join Conversion (SJC)
    *************************
    SJC: Considering set-join conversion in SEL$7 (#5).
    voptcojrj: logp:0xd1edcd60
    voptcojrj:"I"."COINT"="X"."COINL"
    rejected
    voptcojrj:"A"."CNACT"="X"."CNACT"
    rejected
    voptcojrj:"O"."COBCN"="A"."COBCN"
    rejected
    voptcojrj:"M"."CMECH"(+)="X"."CMECH"
    rejected
    voptcojrj:"C"."COINT"(+)="X"."COINC"
    rejected
    voptcojrj:"C"."NUCPT"(+)="X"."NUCPT"
    rejected
    voptcojrj:"I"."COSIE"="S"."COSIE"(+)
    rejected
    voptcojrj: logp:0x9d1e2c00
    voptcojrj:"H"."COINL" IS NOT NULL
    considered
    voptcojrj:"Z"."NUBIX"="H"."NUBIX"
    rejected
    voptcojrj:"Z"."DATRA"="H"."DATRA"
    rejected
    voptcojrj:"V"."NUBIX"="H"."NUBIX"
    rejected
    voptcojrj:"V"."NUFDP"="H"."NUFDP"
    rejected
    voptcojrj:"I"."COINT"(+)="I"."COINT"
    rejected
    voptcojrj:"J"."COINT"(+)="Z"."COINF"
    rejected
    voptcojrj:"K"."COINT"(+)="Z"."COINN"
    rejected
    voptcojrj:"L"."COINT"(+)="H"."COINI"
    rejected
    voptcojrj:"M"."COINT"(+)="H"."COINV"
    rejected
    voptcojrj:"N"."COINT"(+)="Z"."COINK"
    rejected
    voptcojrj:"O"."COINT"(+)="H"."COINA"
    rejected
    voptcojrj:"R"."COINT"(+)="H"."COINL"
    rejected
    voptcojrj:"R"."COMAR"(+)="H"."COMAR"
    rejected
    voptcojrj:"I"."IDINT"(+)="H"."ID_COINC"
    rejected
    voptcojrj:"C"."ID_NUCPT"(+)="H"."ID_NUCPT"
    rejected
    voptcojrj:"A"."ID_NUCPT"="H"."ID_NUCPI"
    rejected
    voptcojrj:"P"."ID_COPOR"(+)="H"."ID_COPOR"
    rejected
    voptcojrj:"Z"."DATOP">=TO_DATE('02112009 00:00:00','DDMMYYYY HH24:MI:SS')
    rejected
    voptcojrj:"Z"."DATOP"<=TO_DATE('08112009 23:59:59','DDMMYYYY HH24:MI:SS')
    rejected
    voptcojrj:"H"."COMAR"='ICE'
    rejected
    voptcojrj:"H"."NUORD" LIKE '%NY%'
    rejected
    voptcojrj:"R"."COMAR"(+)='ICE'
    rejected
    voptcojrj: logp:0x9cb17088
    voptcojrj:"X"."NCCOF"=11
    rejected
    voptcojrj:"P"."COMAR"="X"."COMAR"
    rejected
    voptcojrj:"D"."CODEV"="X"."CDCOF"
    rejected
    voptcojrj:"S"."CTCOF"="X"."CTCOF"
    rejected
    voptcojrj:"S"."COLAN"='A'
    rejected
    voptcojrj:"H"."DATRA"(+)="X"."DATRA"
    rejected
    voptcojrj:"H"."DAVAL"(+)="X"."DAVAL"
    rejected
    voptcojrj:"H"."NUBIX"(+)="X"."NUBIX"
    rejected
    voptcojrj:"H"."NUFDP"(+)="X"."NUFDP"
    rejected
    voptcojrj:"H"."CDCOF"(+)="X"."CDCOF"
    rejected
    voptcojrj:"H"."NCCOF"(+)="X"."NCCOF"
    rejected
    voptcojrj:"H"."NCCOF"(+)=11
    rejected
    Query block (0xc2b0b998) before join elimination:
    SQL:******* UNPARSED QUERY IS *******
    SELECT "X"."COMAR" "COMAR","X"."DATRA" "DATRA","X"."DAVAL" "DAVAL","X"."NUBIX" "NUBIX","X"."NUFDP" "NUFDP","X"."CDCOF" "CODEV","H"."QTCOF" "QTCOF",SUM(DECODE("S"."IDFEE",'O',ROUND("X"."MTCOF"*"X"."QTCOF"*"X"."CSCOF",DECODE("R"."NUSPF",825,0,2)),DECODE("P"."ARRCO",0,ROUND("X"."MTCOF"*"X"."QTCOF"*"X"."CSCOF","D"."FACAR"),TRUNC("X"."MTCOF"*"X"."QTCOF"*"X"."CSCOF","D"."FACAR")))) "MTHEX",SUM(DECODE("S"."IDFEE",'O',ROUND("X"."MTCOF"*"X"."QTCOF"*"X"."CSCOF"*(NVL("X"."TTCOF",0)/100),DECODE("R"."NUSPF",825,0,2)),DECODE("P"."ARRTV",0,ROUND("X"."MTCOF"*"X"."QTCOF"*"X"."CSCOF"*(NVL("X"."TTCOF",0)/100),"D"."FACAR"),TRUNC("X"."MTCOF"*"X"."QTCOF"*"X"."CSCOF"*(NVL("X"."TTCOF",0)/100),"D"."FACAR")))) "MTVEX",0 "MTHGX",0 "MTVGX",SUM(DECODE("S"."IDFEE",'O',ROUND("X"."MTCOF"*"X"."QTCOF"*"X"."CSCOF",DECODE("R"."NUSPF",825,0,2)),DECODE("P"."ARRCO",0,ROUND("X"."MTCOF"*"X"."QTCOF"*"X"."CSCOF","D"."FACAR"),TRUNC("X"."MTCOF"*"X"."QTCOF"*"X"."CSCOF","D"."FACAR"))))+SUM(DECODE("S"."IDFEE",'O',ROUND("X"."MTCOF"*"X"."QTCOF"*"X"."CSCOF"*(NVL("X"."TTCOF",0)/100),DECODE("R"."NUSPF",825,0,2)),DECODE("P"."ARRTV",0,ROUND("X"."MTCOF"*"X"."QTCOF"*"X"."CSCOF"*(NVL("X"."TTCOF",0)/100),"D"."FACAR"),TRUNC("X"."MTCOF"*"X"."QTCOF"*"X"."CSCOF"*(NVL("X"."TTCOF",0)/100),"D"."FACAR")))) "MTTGL",MIN(DECODE("X"."DATRA","X"."DAORI",1,DECODE("X"."CTMVC",'N',0,'D',0,1))) "ISCHC" FROM F362."HISMVC" "X",F362."PARMAR" "P","COMMON"."DEVISE" "D",F362."TYPSCR" "S",F362."RENGEN" "R", (SELECT "H"."DATRA" "DATRA","H"."DAVAL" "DAVAL","H"."NUBIX" "NUBIX","H"."NUFDP" "NUFDP","H"."CDCOF" "CDCOF","H"."NCCOF" "NCCOF",ABS(SUM(NVL(DECODE("H"."CTMVC",'N',0,'D',0,"H"."QTCOF"*"H"."CSCOF"),0))) "QTCOF" FROM  (SELECT "H"."DATRA" "DATRA","H"."DAVAL" "DAVAL","H"."NUBIX" "NUBIX","H"."NUFDP" "NUFDP","H"."CDCOF" "CDCOF",ROW_NUMBER() OVER ( PARTITION BY "H"."DATRA","H"."DAVAL","H"."NUBIX","H"."NUFDP","H"."CDCOF","H"."NTUBX" ORDER BY "H"."CTCOF") "RK","H"."CTMVC" "CTMVC","H"."QTCOF" "QTCOF","H"."CSCOF" "CSCOF","H"."NCCOF" "NCCOF" FROM F362."HISMVC" "H" WHERE "H"."NCCOF"=11) "H" WHERE "H"."RK"=1 AND "H"."CTMVC"<>'N' AND "H"."CTMVC"<>'D' GROUP BY "H"."DATRA","H"."DAVAL","H"."NUBIX","H"."NUFDP","H"."CDCOF","H"."NCCOF") "H" WHERE "X"."NCCOF"=11 AND "P"."COMAR"="X"."COMAR" AND "D"."CODEV"="X"."CDCOF" AND "S"."CTCOF"="X"."CTCOF" AND "S"."COLAN"='A' AND "H"."DATRA"(+)="X"."DATRA" AND "H"."DAVAL"(+)="X"."DAVAL" AND "H"."NUBIX"(+)="X"."NUBIX" AND "H"."NUFDP"(+)="X"."NUFDP" AND "H"."CDCOF"(+)="X"."CDCOF" AND "H"."NCCOF"(+)="X"."NCCOF" AND "H"."NCCOF"(+)=11 GROUP BY "X"."COMAR","X"."DATRA","X"."DAVAL","X"."NUBIX","X"."NUFDP","X"."CDCOF","H"."QTCOF"
    Query block (0xc2b0b998) unchanged
    Query block (0xe5798858) before join elimination:
    SQL:******* UNPARSED QUERY IS *******
    SELECT "H"."COINL" "COINL","Z"."COINF" "COINF","Z"."COINN" "COINN","Z"."COOPE" "COOPE","I"."COINT" "COINC","C"."NUCPT" "NUCPT","P"."COPOR" "COPOR","H"."IDETY" "IDETY","H"."COINI" "COINI","H"."COMAR" "COMAR","Z"."CNACT" "CNACT","H"."CTSTR" "CTSTR","H"."CNACE" "CNACE","V"."DATRA" "DATRA","Z"."DATOP" "DATOP","V"."DAVAL" "DAVAL","Z"."CSENS" "CSENS","V"."QTCOF" "QTCOF","Z"."MTNEG" "MTNEG","Z"."MVNEG" "MVNEG","Z"."CSOPT" "CSOPT","Z"."CMECH" "CMECH","Z"."CAECH" "CAECH","Z"."MTSNA" "MTSNA","Z"."NUVER" "NUVER","Z"."NUINS" "NUINS","Z"."NUCON" "NUCON","H"."NUBIX" "NUBIX","H"."NUFDP" "NUFDP","H"."NUORD" "NUORD","H"."NUTIC" "NUTIC","H"."CTFNE" "CTFNE","H"."CTFDE" "CTFDE","H"."CTFIC" "CTFIC","Z"."COGLO" "COGLO","H"."CNAOP" "CNAOP","H"."CNTRA" "CNTRA","V"."CODEV" "CODEV","V"."MTHEX" "MTHEX","V"."MTVEX" "MTVEX","V"."MTHGX" "MTHGX","V"."MTVGX" "MTVGX","V"."MTTGL" "MTTGL","H"."COINV" "COINV","H"."COINA" "COINA","H"."COUTI" "COUTI","Z"."COINK" "COINK","V"."ISCHC" "ISCHC","Z"."DAECB" "DAECB","Z"."TYTRN" "TYTRN","Z"."CORIG" "CORIG","Z"."MTREF" "MTREF","Z"."DAECA" "DAECA","I"."COTAX" "COTAX","J"."COTAX" "COTAF","K"."COTAX" "COTAN","L"."COTAX" "COTAI","M"."COTAX" "COTAV","N"."COTAX" "COTAK","O"."COTAX" "COTAA","H"."INFO1" "INFO1","H"."GUREF" "GUREF","R"."RGCOD" "RGCOF","R"."RGCID" "RGCIF" FROM F362."IHSDEP" "H",F362."INTERV" "I",F362."COMPTE" "C",F362."COMPTE" "A",F362."PORTEF" "P",F362."HISNEG" "Z", (SELECT "X"."COMAR" "COMAR","X"."DATRA" "DATRA","X"."DAVAL" "DAVAL","X"."NUBIX" "NUBIX","X"."NUFDP" "NUFDP","X"."CDCOF" "CODEV","H"."QTCOF" "QTCOF",SUM(DECODE("S"."IDFEE",'O',ROUND("X"."MTCOF"*"X"."QTCOF"*"X"."CSCOF",DECODE("R"."NUSPF",825,0,2)),DECODE("P"."ARRCO",0,ROUND("X"."MTCOF"*"X"."QTCOF"*"X"."CSCOF","D"."FACAR"),TRUNC("X"."MTCOF"*"X"."QTCOF"*"X"."CSCOF","D"."FACAR")))) "MTHEX",SUM(DECODE("S"."IDFEE",'O',ROUND("X"."MTCOF"*"X"."QTCOF"*"X"."CSCOF"*(NVL("X"."TTCOF",0)/100),DECODE("R"."NUSPF",825,0,2)),DECODE("P"."ARRTV",0,ROUND("X"."MTCOF"*"X"."QTCOF"*"X"."CSCOF"*(NVL("X"."TTCOF",0)/100),"D"."FACAR"),TRUNC("X"."MTCOF"*"X"."QTCOF"*"X"."CSCOF"*(NVL("X"."TTCOF",0)/100),"D"."FACAR")))) "MTVEX",0 "MTHGX",0 "MTVGX",SUM(DECODE("S"."IDFEE",'O',ROUND("X"."MTCOF"*"X"."QTCOF"*"X"."CSCOF",DECODE("R"."NUSPF",825,0,2)),DECODE("P"."ARRCO",0,ROUND("X"."MTCOF"*"X"."QTCOF"*"X"."CSCOF","D"."FACAR"),TRUNC("X"."MTCOF"*"X"."QTCOF"*"X"."CSCOF","D"."FACAR"))))+SUM(DECODE("S"."IDFEE",'O',ROUND("X"."MTCOF"*"X"."QTCOF"*"X"."CSCOF"*(NVL("X"."TTCOF",0)/100),DECODE("R"."NUSPF",825,0,2)),DECODE("P"."ARRTV",0,ROUND("X"."MTCOF"*"X"."QTCOF"*"X"."CSCOF"*(NVL("X"."TTCOF",0)/100),"D"."FACAR"),TRUNC("X"."MTCOF"*"X"."QTCOF"*"X"."CSCOF"*(NVL("X"."TTCOF",0)/100),"D"."FACAR")))) "MTTGL",MIN(DECODE("X"."DATRA","X"."DAORI",1,DECODE("X"."CTMVC",'N',0,'D',0,1))) "ISCHC" FROM F362."HISMVC" "X",F362."PARMAR" "P","COMMON"."DEVISE" "D",F362."TYPSCR" "S",F362."RENGEN" "R", (SELECT "H"."DATRA" "DATRA","H"."DAVAL" "DAVAL","H"."NUBIX" "NUBIX","H"."NUFDP" "NUFDP","H"."CDCOF" "CDCOF","H"."NCCOF" "NCCOF",ABS(SUM(NVL(DECODE("H"."CTMVC",'N',0,'D',0,"H"."QTCOF"*"H"."CSCOF"),0))) "QTCOF" FROM  (SELECT "H"."DATRA" "DATRA","H"."DAVAL" "DAVAL","H"."NUBIX" "NUBIX","H"."NUFDP" "NUFDP","H"."CDCOF" "CDCOF",ROW_NUMBER() OVER ( PARTITION BY "H"."DATRA","H"."DAVAL","H"."NUBIX","H"."NUFDP","H"."CDCOF","H"."NTUBX" ORDER BY "H"."CTCOF") "RK","H"."CTMVC" "CTMVC","H"."QTCOF" "QTCOF","H"."CSCOF" "CSCOF","H"."NCCOF" "NCCOF" FROM F362."HISMVC" "H" WHERE "H"."NCCOF"=11) "H" WHERE "H"."RK"=1 AND "H"."CTMVC"<>'N' AND "H"."CTMVC"<>'D' GROUP BY "H"."DATRA","H"."DAVAL","H"."NUBIX","H"."NUFDP","H"."CDCOF","H"."NCCOF") "H" WHERE "X"."NCCOF"=11 AND "P"."COMAR"="X"."COMAR" AND "D"."CODEV"="X"."CDCOF" AND "S"."CTCOF"="X"."CTCOF" AND "S"."COLAN"='A' AND "H"."DATRA"(+)="X"."DATRA" AND "H"."DAVAL"(+)="X"."DAVAL" AND "H"."NUBIX"(+)="X"."NUBIX" AND "H"."NUFDP"(+)="X"."NUFDP" AND "H"."CDCOF"(+)="X"."CDCOF" AND "H"."NCCOF"(+)="X"."NCCOF" AND "H"."NCCOF"(+)=11 GROUP BY "X"."COMAR","X"."DATRA","X"."DAVAL","X"."NUBIX","X"."NUFDP","X"."CDCOF","H"."QTCOF") "V",F362."INTERV" "I",F362."INTERV" "J",F362."INTERV" "K",F362."INTERV" "L",F362."INTERV" "M",F362."INTERV" "N",F362."INTERV" "O",F362."RGCCAL" "R" WHERE ("H"."CTFDE"='N' OR "H"."CTFDE"='L' OR "H"."CTFDE"='A' OR "H"."CTFDE"='S' OR "H"."CTFDE"='E') AND "H"."COINL" IS NOT NULL AND "Z"."NUBIX"="H"."NUBIX" AND "Z"."DATRA"="H"."DATRA" AND "V"."NUBIX"="H"."NUBIX" AND "V"."NUFDP"="H"."NUFDP" AND "I"."COINT"(+)="I"."COINT" AND "J"."COINT"(+)="Z"."COINF" AND "K"."COINT"(+)="Z"."COINN" AND "L"."COINT"(+)="H"."COINI" AND "M"."COINT"(+)="H"."COINV" AND "N"."COINT"(+)="Z"."COINK" AND "O"."COINT"(+)="H"."COINA" AND "R"."COINT"(+)="H"."COINL" AND "R"."COMAR"(+)="H"."COMAR" AND "I"."IDINT"(+)="H"."ID_COINC" AND "C"."ID_NUCPT"(+)="H"."ID_NUCPT" AND "A"."ID_NUCPT"="H"."ID_NUCPI" AND "P"."ID_COPOR"(+)="H"."ID_COPOR" AND "Z"."DATOP">=TO_DATE('02112009 00:00:00','DDMMYYYY HH24:MI:SS') AND "Z"."DATOP"<=TO_DATE('08112009 23:59:59','DDMMYYYY HH24:MI:SS') AND "H"."COMAR"='ICE' AND "H"."NUORD" LIKE '%NY%' AND ("V"."MTHEX"<>0 OR "H"."CTFDE"='N') AND "R"."COMAR"(+)='ICE'
    Query block (0xe5798858) unchanged
    Query block (0xd199d030) before join elimination:
    SQL:******* UNPARSED QUERY IS *******
    SELECT '02/11/09' "'02/11/09'",'08/11/09' "'08/11/09'","X"."COINL" "COINL","X"."CNACT" "CNACT",SUM("X"."QTCOF"*"X"."ISCHC") "SUM(X.QTFDP*X.ISCHC)","X"."CODEV" "CODEV",SUM("X"."MTHEX") "SUM(X.MTHEX)",SUM("X"."MTVEX") "SUM(X.MTVEX)",SUM("X"."MTTGL") "SUM(X.MTTGL)",0 "0" FROM  (SELECT "H"."COINL" "COINL","Z"."COINF" "COINF","Z"."COINN" "COINN","Z"."COOPE" "COOPE","I"."COINT" "COINC","C"."NUCPT" "NUCPT","P"."COPOR" "COPOR","H"."IDETY" "IDETY","H"."COINI" "COINI","H"."COMAR" "COMAR","Z"."CNACT" "CNACT","H"."CTSTR" "CTSTR","H"."CNACE" "CNACE","V"."DATRA" "DATRA","Z"."DATOP" "DATOP","V"."DAVAL" "DAVAL","Z"."CSENS" "CSENS","V"."QTCOF" "QTCOF","Z"."MTNEG" "MTNEG","Z"."MVNEG" "MVNEG","Z"."CSOPT" "CSOPT","Z"."CMECH" "CMECH","Z"."CAECH" "CAECH","Z"."MTSNA" "MTSNA","Z"."NUVER" "NUVER","Z"."NUINS" "NUINS","Z"."NUCON" "NUCON","H"."NUBIX" "NUBIX","H"."NUFDP" "NUFDP","H"."NUORD" "NUORD","H"."NUTIC" "NUTIC","H"."CTFNE" "CTFNE","H"."CTFDE" "CTFDE","H"."CTFIC" "CTFIC","Z"."COGLO" "COGLO","H"."CNAOP" "CNAOP","H"."CNTRA" "CNTRA","V"."CODEV" "CODEV","V"."MTHEX" "MTHEX","V"."MTVEX" "MTVEX","V"."MTHGX" "MTHGX","V"."MTVGX" "MTVGX","V"."MTTGL" "MTTGL","H"."COINV" "COINV","H"."COINA" "COINA","H"."COUTI" "COUTI","Z"."COINK" "COINK","V"."ISCHC" "ISCHC","Z"."DAECB" "DAECB","Z"."TYTRN" "TYTRN","Z"."CORIG" "CORIG","Z"."MTREF" "MTREF","Z"."DAECA" "DAECA","I"."COTAX" "COTAX","J"."COTAX" "COTAF","K"."COTAX" "COTAN","L"."COTAX" "COTAI","M"."COTAX" "COTAV","N"."COTAX" "COTAK","O"."COTAX" "COTAA","H"."INFO1" "INFO1","H"."GUREF" "GUREF","R"."RGCOD" "RGCOF","R"."RGCID" "RGCIF" FROM F362."IHSDEP" "H",F362."INTERV" "I",F362."COMPTE" "C",F362."COMPTE" "A",F362."PORTEF" "P",F362."HISNEG" "Z", (SELECT "X"."COMAR" "COMAR","X"."DATRA" "DATRA","X"."DAVAL" "DAVAL","X"."NUBIX" "NUBIX","X"."NUFDP" "NUFDP","X"."CDCOF" "CODEV","H"."QTCOF" "QTCOF",SUM(DECODE("S"."IDFEE",'O',ROUND("X"."MTCOF"*"X"."QTCOF"*"X"."CSCOF",DECODE("R"."NUSPF",825,0,2)),DECODE("P"."ARRCO",0,ROUND("X"."MTCOF"*"X"."QTCOF"*"X"."CSCOF","D"."FACAR"),TRUNC("X"."MTCOF"*"X"."QTCOF"*"X"."CSCOF","D"."FACAR")))) "MTHEX",SUM(DECODE("S"."IDFEE",'O',ROUND("X"."MTCOF"*"X"."QTCOF"*"X"."CSCOF"*(NVL("X"."TTCOF",0)/100),DECODE("R"."NUSPF",825,0,2)),DECODE("P"."ARRTV",0,ROUND("X"."MTCOF"*"X"."QTCOF"*"X"."CSCOF"*(NVL("X"."TTCOF",0)/100),"D"."FACAR"),TRUNC("X"."MTCOF"*"X"."QTCOF"*"X"."CSCOF"*(NVL("X"."TTCOF",0)/100),"D"."FACAR")))) "MTVEX",0 "MTHGX",0 "MTVGX",SUM(DECODE("S"."IDFEE",'O',ROUND("X"."MTCOF"*"X"."QTCOF"*"X"."CSCOF",DECODE("R"."NUSPF",825,0,2)),DECODE("P"."ARRCO",0,ROUND("X"."MTCOF"*"X"."QTCOF"*"X"."CSCOF","D"."FACAR"),TRUNC("X"."MTCOF"*"X"."QTCOF"*"X"."CSCOF","D"."FACAR"))))+SUM(DECODE("S"."IDFEE",'O',ROUND("X"."MTCOF"*"X"."QTCOF"*"X"."CSCOF"*(NVL("X"."TTCOF",0)/100),DECODE("R"."NUSPF",825,0,2)),DECODE("P"."ARRTV",0,ROUND("X"."MTCOF"*"X"."QTCOF"*"X"."CSCOF"*(NVL("X"."TTCOF",0)/100),"D"."FACAR"),TRUNC("X"."MTCOF"*"X"."QTCOF"*"X"."CSCOF"*(NVL("X"."TTCOF",0)/100),"D"."FACAR")))) "MTTGL",MIN(DECODE("X"."DATRA","X"."DAORI",1,DECODE("X"."CTMVC",'N',0,'D',0,1))) "ISCHC" FROM F362."HISMVC" "X",F362."PARMAR" "P","COMMON"."DEVISE" "D",F362."TYPSCR" "S",F362."RENGEN" "R", (SELECT "H"."DATRA" "DATRA","H"."DAVAL" "DAVAL","H"."NUBIX" "NUBIX","H"."NUFDP" "NUFDP","H"."CDCOF" "CDCOF","H"."NCCOF" "NCCOF",ABS(SUM(NVL(DECODE("H"."CTMVC",'N',0,'D',0,"H"."QTCOF"*"H"."CSCOF"),0))) "QTCOF" FROM  (SELECT "H"."DATRA" "DATRA","H"."DAVAL" "DAVAL","H"."NUBIX" "NUBIX","H"."NUFDP" "NUFDP","H"."CDCOF" "CDCOF",ROW_NUMBER() OVER ( PARTITION BY "H"."DATRA","H"."DAVAL","H"."NUBIX","H"."NUFDP","H"."CDCOF","H"."NTUBX" ORDER BY "H"."CTCOF") "RK","H"."CTMVC" "CTMVC","H"."QTCOF" "QTCOF","H"."CSCOF" "CSCOF","H"."NCCOF" "NCCOF" FROM F362."HISMVC" "H" WHERE "H"."NCCOF"=11) "H" WHERE "H"."RK"=1 AND "H"."CTMVC"<>'N' AND "H"."CTMVC"<>'D' GROUP BY "H"."DATRA","H"."DAVAL","H"."NUBIX","H"."NUFDP","H"."CDCOF","H"."NCCOF") "H" WHERE "X"."NCCOF"=11 AND "P"."COMAR"="X"."COMAR" AND "D"."CODEV"="X"."CDCOF" AND "S"."CTCOF"="X"."CTCOF" AND "S"."COLAN"='A' AND "H"."DATRA"(+)="X"."DATRA" AND "H"."DAVAL"(+)="X"."DAVAL" AND "H"."NUBIX"(+)="X"."NUBIX" AND "H"."NUFDP"(+)="X"."NUFDP" AND "H"."CDCOF"(+)="X"."CDCOF" AND "H"."NCCOF"(+)="X"."NCCOF" AND "H"."NCCOF"(+)=11 GROUP BY "X"."COMAR","X"."DATRA","X"."DAVAL","X"."NUBIX","X"."NUFDP","X"."CDCOF","H"."QTCOF") "V",F362."INTERV" "I",F362."INTERV" "J",F362."INTERV" "K",F362."INTERV" "L",F362."INTERV" "M",F362."INTERV" "N",F362."INTERV" "O",F362."RGCCAL" "R" WHERE ("H"."CTFDE"='N' OR "H"."CTFDE"='L' OR "H"."CTFDE"='A' OR "H"."CTFDE"='S' OR "H"."CTFDE"='E') AND "H"."COINL" IS NOT NULL AND "Z"."NUBIX"="H"."NUBIX" AND "Z"."DATRA"="H"."DATRA" AND "V"."NUBIX"="H"."NUBIX" AND "V"."NUFDP"="H"."NUFDP" AND "I"."COINT"(+)="I"."COINT" AND "J"."COINT"(+)="Z"."COINF" AND "K"."COINT"(+)="Z"."COINN" AND "L"."COINT"(+)="H"."COINI" AND "M"."COINT"(+)="H"."COINV" AND "N"."COINT"(+)="Z"."COINK" AND "O"."COINT"(+)="H"."COINA" AND "R"."COINT"(+)="H"."COINL" AND "R"."COMAR"(+)="H"."COMAR" AND "I"."IDINT"(+)="H"."ID_COINC" AND "C"."ID_NUCPT"(+)="H"."ID_NUCPT" AND "A"."ID_NUCPT"="H"."ID_NUCPI" AND "P"."ID_COPOR"(+)="H"."ID_COPOR" AND "Z"."DATOP">=TO_DATE('02112009 00:00:00','DDMMYYYY HH24:MI:SS') AND "Z"."DATOP"<=TO_DATE('08112009 23:59:59','DDMMYYYY HH24:MI:SS') AND "H"."COMAR"='ICE' AND "H"."NUORD" LIKE '%NY%' AND ("V"."MTHEX"<>0 OR "H"."CTFDE"='N') AND "R"."COMAR"(+)='ICE') "X",F362."INTERV" "I","COMMON"."NATACF" "A","COMMON"."OBJCON" "O","COMMON"."MOIECH" "M",F362."COMPTE" "C",F362."SIEGES" "S" WHERE "I"."COINT"="X"."COINL" AND "A"."CNACT"="X"."CNACT" AND "O"."COBCN"="A"."COBCN" AND "M"."CMECH"(+)="X"."CMECH" AND "C"."COINT"(+)="X"."COINC" AND "C"."NUCPT"(+)="X"."NUCPT" AND "I"."COSIE"="S"."COSIE"(+) GROUP BY "X"."COINL","X"."CNACT","X"."CODEV" ORDER BY "X"."COINL","X"."CODEV","X"."CNACT"
    Query block (0xd199d030) unchanged
    **************************
    Predicate Move-Around (PM)
    **************************
    PM: Considering predicate move-around in SEL$F5BB74E1 (#1).
    PM:   Checking validity of predicate move-around in SEL$F5BB74E1 (#1).
    PM:     PM bypassed: View on the right side of semi, anti, or non-group outer join.
    PM:   Passed validity checks.
    PM:   Pulled up predicate "X"."DATOP">=TO_DATE('02112009 00:00:00','DDMMYYYY HH24:MI:SS')
            From SEL$07BDC5B4 (#2) to SEL$F5BB74E1 (#1).
    PM:   Pulled up predicate "X"."DATOP"<=TO_DATE('08112009 23:59:59','DDMMYYYY HH24:MI:SS')
            From SEL$07BDC5B4 (#2) to SEL$F5BB74E1 (#1).
    PM:   Pulled up predicate "X"."COMAR"='ICE'
            From SEL$07BDC5B4 (#2) to SEL$F5BB74E1 (#1).
  • 27. Re: I have to deactivate the "_OPTIMIZER_COST_BASED_TRANSFORMATION". WHY????
    Randolf Geist Oracle ACE Director
    Currently Being Moderated
    Farenheiit wrote:
    It's better but not better than the explain plan generated with query transformation turned off
    Since it looks like that the MERGE hint was ignored with the original statement and the VCRDAL/VCRDAL2 view, you could try the following now:

    - Use the PUSH_PRED(VCRDAL2) hint in the main query of the original statement to push the join predicates into the view - although the join push predicate transformation should also be part of the transformation tried/costed by the optimizer
    - Try the MERGE(VCRDAL3) and PUSH_PRED(VCRDAL3) hint with the rewritten version of the statement as suggested by Charles (the mentioned hints need to go into the surrounding query containing the view)

    For these experiments I suggest you put all views as inline views into one large statement rather than creating stored views with hints embedded. This way you can easily test the outcome of the different transformation hints applied - and it makes it simpler to put the hints at the correct place.

    I think that it should be possible to rewrite the statement and get rid of the now outer joined view (which used to be the nested subquery) completely, but I don't have the time to come up with a rewritten version - the challenge might be to get the aggregates right - probably one of the aggregates needs to be replaced with an analytical version of the aggregate.

    The statement will only be perform better if the early elimination of the rows from HISMVC is possible - otherwise it will always generate a lot of unnecessary work.

    Regards,
    Randolf

    Oracle related stuff blog:
    http://oracle-randolf.blogspot.com/

    Co-author of the forthcoming "OakTable Expert Oracle Practices" book:
    http://www.apress.com/book/view/1430226684
    http://www.amazon.com/Expert-Oracle-Practices-Database-Administration/dp/1430226684
1 2 Previous Next

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points