1 2 Previous Next 27 Replies Latest reply: Nov 25, 2009 2:26 PM by Randolf Geist Go to original post RSS
      • 15. Re: I have to deactivate the "_OPTIMIZER_COST_BASED_TRANSFORMATION". WHY????
        Charles Hooper
        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
          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
            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
              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????
                Charles Hooper
                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
                  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
                    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????
                      Charles Hooper
                      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
                        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????
                          Charles Hooper
                          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
                            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
                              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
                                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