ITPub博客

首页 > 数据库 > Oracle > exist 改写为 left join

exist 改写为 left join

原创 Oracle 作者:墨雪-guoyan 时间:2017-12-06 15:50:41 0 删除 编辑

--3vm7pkp5cb69g

select * from gv$sql where sql_id = '3vm7pkp5cb69g';
explain plan for
SELECT GS.ORG_ID,
       GS.ORGANIZATION_ID,
       GS.SAMPLE_ID,
       GS.SAMPLE_NO,
       GS.SAMPLE_DESC,
       GS.LOT_NUMBER,
       GS.INVENTORY_ITEM_ID,
       GS.DATE_DRAWN,
       MSI.SEGMENT1 ITEM_NUM,
       MSI.DESCRIPTION ITEM_NAME,
       P1.LAST_NAME SAMPLER_USER,
       GH.MEANING HEADER_STATUS,
       GR.RESULT_ID,
       GR.TEST_ID,
       GR.SEQ,
       GQT.TEST_CODE,
       GQT.TEST_DESC,
       GST.MIN_VALUE_NUM,
       GST.TARGET_VALUE_NUM,
       GST.MAX_VALUE_NUM,
       GR.RESULT_VALUE_NUM,
       GR.RESULT_DATE,
       P2.LAST_NAME TESTER_USER,
       GL.MEANING END_RESULT,
       COUNT(1) OVER(PARTITION BY GS.SAMPLE_ID) SL
  FROM GMD_SAMPLES GS,
       GMD_RESULTS GR,
       GEM_LOOKUPS GL,
       GEM_LOOKUPS GH,
       GMD_SAMPLE_SPEC_DISP SSD,
       GMD_EVENT_SPEC_DISP GES,
       GMD_SAMPLING_EVENTS SE,
       GMD_SPEC_TESTS GST,
       GMD_SPECIFICATIONS GSP,
       GMD_SPEC_RESULTS GSR,
       GMD_QC_TESTS GQT,
       MTL_SYSTEM_ITEMS_VL MSI,
       (SELECT P.LAST_NAME, F.USER_ID, F.USER_NAME
          FROM PER_ALL_PEOPLE_F P, FND_USER F
         WHERE P.PERSON_ID = F.EMPLOYEE_ID
           AND SYSDATE BETWEEN P.EFFECTIVE_START_DATE AND
               NVL(P.EFFECTIVE_END_DATE, SYSDATE + 1)) P1,
       (SELECT P.LAST_NAME, F.USER_ID, F.USER_NAME
          FROM PER_ALL_PEOPLE_F P, FND_USER F
         WHERE P.PERSON_ID = F.EMPLOYEE_ID
           AND SYSDATE BETWEEN P.EFFECTIVE_START_DATE AND
               NVL(P.EFFECTIVE_END_DATE, SYSDATE + 1)) P2
 WHERE GR.SAMPLE_ID = GS.SAMPLE_ID
   AND (GS.SAMPLE_TYPE = 'I')
   AND GS.SAMPLING_EVENT_ID = GES.SAMPLING_EVENT_ID
   AND GES.SPEC_ID = GST.SPEC_ID
   AND GR.TEST_ID = GST.TEST_ID
   AND GR.TEST_ID = GQT.TEST_ID
   AND GS.ORGANIZATION_ID = MSI.ORGANIZATION_ID
   AND GS.INVENTORY_ITEM_ID = MSI.INVENTORY_ITEM_ID
   AND GL.LOOKUP_TYPE = 'GMD_QC_EVALUATION'
   AND GL.LOOKUP_CODE = GSR.EVALUATION_IND
   AND GH.LOOKUP_CODE = SSD.DISPOSITION
   AND GH.LOOKUP_TYPE = 'GMD_QC_SAMPLE_DISP'
   AND GSR.RESULT_ID = GR.RESULT_ID
   AND GS.SAMPLER_ID = P1.USER_ID
   AND GR.TESTER_ID = P2.USER_ID
   AND GES.EVENT_SPEC_DISP_ID = SSD.EVENT_SPEC_DISP_ID
   AND SSD.SAMPLE_ID = GS.SAMPLE_ID
   AND GES.SPEC_USED_FOR_LOT_ATTRIB_IND = 'Y'
   AND SE.SAMPLING_EVENT_ID = GES.SAMPLING_EVENT_ID
   AND GST.TEST_ID = GR.TEST_ID
   AND GES.SPEC_ID = GSP.SPEC_ID
   AND GES.SPEC_ID = GST.SPEC_ID
   AND EXISTS (SELECT 1
          FROM MTL_ITEM_CATEGORIES MIC,
               MTL_CATEGORY_SETS_V MCS,
               MTL_CATEGORIES      MC
         WHERE MIC.ORGANIZATION_ID = GS.ORGANIZATION_ID
           AND MIC.INVENTORY_ITEM_ID = GS.INVENTORY_ITEM_ID
           AND MCS.STRUCTURE_ID = 101
           AND MIC.CATEGORY_SET_ID = MCS.CATEGORY_SET_ID
           AND MCS.STRUCTURE_ID = MC.STRUCTURE_ID
           AND MIC.CATEGORY_ID = MC.CATEGORY_ID
           AND MC.SEGMENT1 = '15')
   AND GS.ORGANIZATION_ID = 1083
   AND GS.DATE_DRAWN BETWEEN to_date('09/01/2017 00:00:00','MM/DD/YYYY HH24:MI:SS')
   AND to_date('09/11/2017 00:00:00','MM/DD/YYYY HH24:MI:SS')
   AND GS.INVENTORY_ITEM_ID = NVL(null, GS.INVENTORY_ITEM_ID)
   AND GS.LOT_NUMBER = NVL(null, GS.LOT_NUMBER)
   AND GS.SAMPLE_NO = NVL(null, GS.SAMPLE_NO);
  
   select * from table(dbms_xplan.display);
  
  select  name,WAS_CAPTURED,DATATYPE_STRING,VALUE_STRING,INST_ID from
   gv$sql_bind_capture where sql_id='3vm7pkp5cb69g';

 

Plan hash value: 4065178589
 
---------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                                                    | Name                         | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                                             |                              |     1 |   576 | 14067   (3)| 00:00:01 |
|   1 |  WINDOW SORT                                                 |                              |     1 |   576 | 14067   (3)| 00:00:01 |
|   2 |   NESTED LOOPS                                               |                              |     1 |   576 | 14066   (3)| 00:00:01 |
|   3 |    NESTED LOOPS                                              |                              |     1 |   576 | 14066   (3)| 00:00:01 |
|*  4 |     HASH JOIN                                                |                              |     1 |   503 | 14063   (3)| 00:00:01 |
|   5 |      JOIN FILTER CREATE                                      | :BF0000                      |     1 |   494 |    94   (2)| 00:00:01 |
|   6 |       NESTED LOOPS                                           |                              |     1 |   494 |    94   (2)| 00:00:01 |
|   7 |        NESTED LOOPS                                          |                              |     1 |   494 |    94   (2)| 00:00:01 |
|   8 |         NESTED LOOPS                                         |                              |     1 |   421 |    91   (2)| 00:00:01 |
|   9 |          NESTED LOOPS                                        |                              |     1 |   395 |    90   (2)| 00:00:01 |
|  10 |           NESTED LOOPS                                       |                              |     1 |   364 |    89   (2)| 00:00:01 |
|  11 |            NESTED LOOPS                                      |                              |     1 |   333 |    86   (2)| 00:00:01 |
|  12 |             NESTED LOOPS                                     |                              |     1 |   323 |    85   (2)| 00:00:01 |
|  13 |              NESTED LOOPS                                    |                              |     1 |   309 |    84   (2)| 00:00:01 |
|  14 |               NESTED LOOPS                                   |                              |     1 |   291 |    82   (2)| 00:00:01 |
|  15 |                NESTED LOOPS                                  |                              |     1 |   256 |    79   (2)| 00:00:01 |
|  16 |                 NESTED LOOPS                                 |                              |     1 |   225 |    76   (2)| 00:00:01 |
|  17 |                  NESTED LOOPS                                |                              |     1 |   215 |    75   (2)| 00:00:01 |
|  18 |                   NESTED LOOPS                               |                              |     1 |   209 |    74   (2)| 00:00:01 |
|  19 |                    NESTED LOOPS                              |                              |     1 |   200 |    73   (2)| 00:00:01 |
|  20 |                     NESTED LOOPS                             |                              |     1 |   195 |    73   (2)| 00:00:01 |
|  21 |                      NESTED LOOPS                            |                              |     1 |   179 |    71   (2)| 00:00:01 |
|  22 |                       NESTED LOOPS                           |                              |     1 |   160 |    68   (2)| 00:00:01 |
|  23 |                        NESTED LOOPS                          |                              |     1 |   121 |    66   (2)| 00:00:01 |
|  24 |                         NESTED LOOPS                         |                              |     1 |    99 |    64   (2)| 00:00:01 |
|  25 |                          VIEW                                | VW_SQ_1                      |     1 |    10 |    20   (0)| 00:00:01 |
|  26 |                           HASH UNIQUE                        |                              |     1 |   251 |            |          |
|  27 |                            NESTED LOOPS SEMI                 |                              |     1 |   251 |    20   (0)| 00:00:01 |
|  28 |                             NESTED LOOPS SEMI                |                              |     1 |   226 |    20   (0)| 00:00:01 |
|  29 |                              NESTED LOOPS                    |                              |     1 |   200 |    20   (0)| 00:00:01 |
|  30 |                               MERGE JOIN CARTESIAN           |                              |     3 |   540 |     9   (0)| 00:00:01 |
|  31 |                                NESTED LOOPS SEMI             |                              |     1 |   150 |     4   (0)| 00:00:01 |
|  32 |                                 NESTED LOOPS                 |                              |     1 |    94 |     2   (0)| 00:00:01 |
|  33 |                                  NESTED LOOPS                |                              |     1 |    64 |     1   (0)| 00:00:01 |
|* 34 |                                   INDEX UNIQUE SCAN          | FND_ID_FLEX_STRUCTURES_U1    |     1 |    30 |     1   (0)| 00:00:01 |
|* 35 |                                   INDEX UNIQUE SCAN          | FND_ID_FLEX_STRUCTURES_TL_U1 |     1 |    34 |     0   (0)| 00:00:01 |
|* 36 |                                  TABLE ACCESS BY INDEX ROWID | MTL_CATEGORY_SETS_B          |     1 |    30 |     1   (0)| 00:00:01 |
|* 37 |                                   INDEX RANGE SCAN           | MTL_CATEGORY_SETS_B_N1       |     1 |       |     0   (0)| 00:00:01 |
|* 38 |                                 INDEX RANGE SCAN             | FND_LOOKUP_VALUES_U1         |     1 |    56 |     2   (0)| 00:00:01 |
|  39 |                                BUFFER SORT                   |                              |     9 |   270 |     7   (0)| 00:00:01 |
|* 40 | BATCHED                         TABLE ACCESS BY INDEX ROWID  | MTL_CATEGORIES_B             |     9 |   270 |     5   (0)| 00:00:01 |
|* 41 |                                  INDEX RANGE SCAN            | MTL_CATEGORIES_B_N1          |    57 |       |     1   (0)| 00:00:01 |
|  42 | TCHED                         TABLE ACCESS BY INDEX ROWID BA | MTL_ITEM_CATEGORIES          |     1 |    20 |     7   (0)| 00:00:01 |
|* 43 |                                INDEX RANGE SCAN              | MTL_ITEM_CATEGORIES_N3       |    19 |       |     2   (0)| 00:00:01 |
|* 44 |                              INDEX UNIQUE SCAN               | MTL_CATEGORY_SETS_TL_U1      |    17 |   442 |     0   (0)| 00:00:01 |
|* 45 |                             INDEX UNIQUE SCAN                | MTL_CATEGORIES_TL_U1         |  2100 | 52500 |     0   (0)| 00:00:01 |
|* 46 |                          TABLE ACCESS BY INDEX ROWID BATCHED | GMD_SAMPLES                  |     1 |    89 |    43   (0)| 00:00:01 |
|* 47 |                           INDEX RANGE SCAN                   | GMD_SAMPLES_N2               |    49 |       |     2   (0)| 00:00:01 |
|  48 |                         TABLE ACCESS BY INDEX ROWID          | MTL_SYSTEM_ITEMS_B           |     1 |    22 |     2   (0)| 00:00:01 |
|* 49 |                          INDEX UNIQUE SCAN                   | MTL_SYSTEM_ITEMS_B_U1        |     1 |       |     1   (0)| 00:00:01 |
|  50 |                        TABLE ACCESS BY INDEX ROWID           | MTL_SYSTEM_ITEMS_TL          |     1 |    39 |     2   (0)| 00:00:01 |
|* 51 |                         INDEX UNIQUE SCAN                    | MTL_SYSTEM_ITEMS_TL_U1       |     1 |       |     1   (0)| 00:00:01 |
|* 52 |                       TABLE ACCESS BY INDEX ROWID BATCHED    | GMD_EVENT_SPEC_DISP          |     1 |    19 |     3   (0)| 00:00:01 |
|* 53 |                        INDEX RANGE SCAN                      | GMD_EVENT_SPEC_DISP_N1       |     1 |       |     2   (0)| 00:00:01 |
|  54 |                      TABLE ACCESS BY INDEX ROWID             | GMD_SAMPLE_SPEC_DISP         |     1 |    16 |     2   (0)| 00:00:01 |
|* 55 |                       INDEX UNIQUE SCAN                      | GMD_SAMPLE_SPEC_DISP_PK      |     1 |       |     1   (0)| 00:00:01 |
|* 56 |                     INDEX UNIQUE SCAN                        | GMD_SPECIFICATIONS_B_PK      |     1 |     5 |     0   (0)| 00:00:01 |
|* 57 |                    INDEX UNIQUE SCAN                         | GMD_SPECIFICATIONS_TL_PK     |     1 |     9 |     1   (0)| 00:00:01 |
|* 58 |                   INDEX UNIQUE SCAN                          | GMD_SAMPLING_EVENTS_PK       |     1 |     6 |     1   (0)| 00:00:01 |
|  59 |                  TABLE ACCESS BY INDEX ROWID                 | FND_USER                     |     1 |    10 |     1   (0)| 00:00:01 |
|* 60 |                   INDEX UNIQUE SCAN                          | FND_USER_U1                  |     1 |       |     0   (0)| 00:00:01 |
|  61 |                 TABLE ACCESS BY INDEX ROWID BATCHED          | PER_ALL_PEOPLE_F             |     1 |    31 |     3   (0)| 00:00:01 |
|* 62 |                  INDEX RANGE SCAN                            | PER_PEOPLE_F_PK              |     1 |       |     2   (0)| 00:00:01 |
|* 63 |                TABLE ACCESS BY INDEX ROWID BATCHED           | GMD_RESULTS                  |     4 |   140 |     3   (0)| 00:00:01 |
|* 64 |                 INDEX RANGE SCAN                             | GMD_RESULTS_N1               |     5 |       |     2   (0)| 00:00:01 |
|  65 |               TABLE ACCESS BY INDEX ROWID                    | GMD_SPEC_TESTS_B             |     1 |    18 |     2   (0)| 00:00:01 |
|* 66 |                INDEX UNIQUE SCAN                             | GMD_SPEC_TESTS_B_PK          |     1 |       |     1   (0)| 00:00:01 |
|* 67 |              INDEX UNIQUE SCAN                               | GMD_SPEC_TESTS_TL_PK         |     1 |    14 |     1   (0)| 00:00:01 |
|  68 |             TABLE ACCESS BY INDEX ROWID                      | FND_USER                     |     1 |    10 |     1   (0)| 00:00:01 |
|* 69 |              INDEX UNIQUE SCAN                               | FND_USER_U1                  |     1 |       |     0   (0)| 00:00:01 |
|  70 |            TABLE ACCESS BY INDEX ROWID BATCHED               | PER_ALL_PEOPLE_F             |     1 |    31 |     3   (0)| 00:00:01 |
|* 71 |             INDEX RANGE SCAN                                 | PER_PEOPLE_F_PK              |     1 |       |     2   (0)| 00:00:01 |
|  72 |           TABLE ACCESS BY INDEX ROWID                        | GMD_QC_TESTS_B               |     1 |    31 |     1   (0)| 00:00:01 |
|* 73 |            INDEX UNIQUE SCAN                                 | GMD_QC_TESTS_B_PK            |     1 |       |     0   (0)| 00:00:01 |
|  74 |          TABLE ACCESS BY INDEX ROWID                         | GMD_QC_TESTS_TL              |     1 |    26 |     1   (0)| 00:00:01 |
|* 75 |           INDEX UNIQUE SCAN                                  | GMD_QC_TESTS_TL_PK           |     1 |       |     0   (0)| 00:00:01 |
|* 76 |         INDEX RANGE SCAN                                     | FND_LOOKUP_VALUES_U1         |     1 |       |     2   (0)| 00:00:01 |
|  77 |        TABLE ACCESS BY INDEX ROWID                           | FND_LOOKUP_VALUES            |     1 |    73 |     3   (0)| 00:00:01 |
|  78 |      JOIN FILTER USE                                         | :BF0000                      |  4965K|    42M| 13909   (2)| 00:00:01 |
|* 79 |       TABLE ACCESS STORAGE FULL                              | GMD_SPEC_RESULTS             |  4965K|    42M| 13909   (2)| 00:00:01 |
|* 80 |     INDEX RANGE SCAN                                         | FND_LOOKUP_VALUES_U1         |     1 |       |     2   (0)| 00:00:01 |
|  81 |    TABLE ACCESS BY INDEX ROWID                               | FND_LOOKUP_VALUES            |     1 |    73 |     3   (0)| 00:00:01 |
---------------------------------------------------------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   4 - access("GSR"."RESULT_ID"="GR"."RESULT_ID")
  34 - access("APPLICATION_ID"=401 AND "ID_FLEX_CODE"='MCAT' AND "ID_FLEX_NUM"=101 AND "ZD_EDITION_NAME"='V_20151118_1137')
  35 - access("APPLICATION_ID"=401 AND "ID_FLEX_CODE"='MCAT' AND "ID_FLEX_NUM"=101 AND "LANGUAGE"=USERENV('LANG') AND
              "ZD_EDITION_NAME"='V_20151118_1137')
  36 - filter("ZD_EDITION_NAME"='V_20151118_1137')
  37 - access("STRUCTURE_ID"=101)
  38 - access("LOOKUP_TYPE"='ITEM_CONTROL_LEVEL_GUI' AND "VIEW_APPLICATION_ID"=700 AND "SECURITY_GROUP_ID"=0 AND
              "LANGUAGE"=USERENV('LANG') AND "ZD_EDITION_NAME"='V_20151118_1137')
       filter("CONTROL_LEVEL"=TO_NUMBER("LOOKUP_CODE") AND "LANGUAGE"=USERENV('LANG') AND "SECURITY_GROUP_ID"=0 AND
              "ZD_EDITION_NAME"='V_20151118_1137')
  40 - filter("STRUCTURE_ID"=101 AND "ZD_EDITION_NAME"='V_20151118_1137')
  41 - access("SEGMENT1"='15')
  43 - access("MIC"."CATEGORY_SET_ID"="CATEGORY_SET_ID" AND "MIC"."CATEGORY_ID"="CATEGORY_ID" AND "MIC"."ORGANIZATION_ID"=1083)
  44 - access("CATEGORY_SET_ID"="CATEGORY_SET_ID" AND "LANGUAGE"=USERENV('LANG') AND "ZD_EDITION_NAME"='V_20151118_1137')
  45 - access("CATEGORY_ID"="CATEGORY_ID" AND "LANGUAGE"=USERENV('LANG') AND "ZD_EDITION_NAME"='V_20151118_1137')
  46 - filter("GS"."DATE_DRAWN">=TO_DATE(' 2017-09-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND "ITEM_1"="GS"."ORGANIZATION_ID" AND
              "GS"."ORGANIZATION_ID"=1083 AND "GS"."SAMPLE_TYPE"='I' AND "GS"."DATE_DRAWN"<=TO_DATE(' 2017-09-11 00:00:00', 'syyyy-mm-dd
              hh24:mi:ss'))
  47 - access("ITEM_2"="GS"."INVENTORY_ITEM_ID")
       filter("GS"."LOT_NUMBER" IS NOT NULL AND "GS"."INVENTORY_ITEM_ID" IS NOT NULL)
  49 - access("GS"."INVENTORY_ITEM_ID"="INVENTORY_ITEM_ID" AND "ORGANIZATION_ID"=1083)
  51 - access("INVENTORY_ITEM_ID"="T"."INVENTORY_ITEM_ID" AND "T"."ORGANIZATION_ID"=1083 AND "T"."LANGUAGE"=USERENV('LANG'))
  52 - filter("GES"."SPEC_USED_FOR_LOT_ATTRIB_IND"='Y')
  53 - access("GS"."SAMPLING_EVENT_ID"="GES"."SAMPLING_EVENT_ID")
  55 - access("GES"."EVENT_SPEC_DISP_ID"="SSD"."EVENT_SPEC_DISP_ID" AND "SSD"."SAMPLE_ID"="GS"."SAMPLE_ID")
  56 - access("GES"."SPEC_ID"="B"."SPEC_ID")
  57 - access("B"."SPEC_ID"="T"."SPEC_ID" AND "T"."LANGUAGE"=USERENV('LANG'))
  58 - access("SE"."SAMPLING_EVENT_ID"="GES"."SAMPLING_EVENT_ID")
  60 - access("GS"."SAMPLER_ID"="F"."USER_ID")
  62 - access("P"."PERSON_ID"="F"."EMPLOYEE_ID" AND "P"."EFFECTIVE_END_DATE">=SYSDATE@! AND "P"."EFFECTIVE_START_DATE"<=SYSDATE@!)
       filter("P"."EFFECTIVE_END_DATE">=SYSDATE@!)
  63 - filter("GR"."TESTER_ID" IS NOT NULL)
  64 - access("GR"."SAMPLE_ID"="GS"."SAMPLE_ID")
  66 - access("GES"."SPEC_ID"="B"."SPEC_ID" AND "GR"."TEST_ID"="B"."TEST_ID")
  67 - access("B"."SPEC_ID"="T"."SPEC_ID" AND "B"."TEST_ID"="T"."TEST_ID" AND "T"."LANGUAGE"=USERENV('LANG'))
  69 - access("GR"."TESTER_ID"="F"."USER_ID")
  71 - access("P"."PERSON_ID"="F"."EMPLOYEE_ID" AND "P"."EFFECTIVE_END_DATE">=SYSDATE@! AND "P"."EFFECTIVE_START_DATE"<=SYSDATE@!)
       filter("P"."EFFECTIVE_END_DATE">=SYSDATE@!)
  73 - access("GR"."TEST_ID"="B"."TEST_ID")
  75 - access("B"."TEST_ID"="T"."TEST_ID" AND "T"."LANGUAGE"=USERENV('LANG'))
  76 - access("LOOKUP_TYPE"='GMD_QC_SAMPLE_DISP' AND "VIEW_APPLICATION_ID">=550 AND "LOOKUP_CODE"="SSD"."DISPOSITION" AND
              "SECURITY_GROUP_ID"=0 AND "LANGUAGE"=USERENV('LANG') AND "ZD_EDITION_NAME"='V_20151118_1137' AND "VIEW_APPLICATION_ID"<=559)
       filter("LOOKUP_CODE"="SSD"."DISPOSITION" AND "LANGUAGE"=USERENV('LANG') AND "SECURITY_GROUP_ID"=0 AND
              "ZD_EDITION_NAME"='V_20151118_1137')
  79 - storage("GSR"."EVALUATION_IND" IS NOT NULL AND SYS_OP_BLOOM_FILTER(:BF0000,"GSR"."RESULT_ID"))
       filter("GSR"."EVALUATION_IND" IS NOT NULL AND SYS_OP_BLOOM_FILTER(:BF0000,"GSR"."RESULT_ID"))
  80 - access("LOOKUP_TYPE"='GMD_QC_EVALUATION' AND "VIEW_APPLICATION_ID">=550 AND "LOOKUP_CODE"="GSR"."EVALUATION_IND" AND
              "SECURITY_GROUP_ID"=0 AND "LANGUAGE"=USERENV('LANG') AND "ZD_EDITION_NAME"='V_20151118_1137' AND "VIEW_APPLICATION_ID"<=559)
       filter("LOOKUP_CODE"="GSR"."EVALUATION_IND" AND "LANGUAGE"=USERENV('LANG') AND "SECURITY_GROUP_ID"=0 AND
              "ZD_EDITION_NAME"='V_20151118_1137')


exsits 导致产生 笛卡尔积   改写成 left  join

消除了笛卡尔积,去null


Plan hash value: 1210765236
 
---------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                                                    | Name                         | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                                             |                              |     1 |   817 | 14062   (3)| 00:00:01 |
|   1 |  WINDOW SORT                                                 |                              |     1 |   817 | 14062   (3)| 00:00:01 |
|   2 |   NESTED LOOPS                                               |                              |     1 |   817 | 14061   (3)| 00:00:01 |
|   3 |    NESTED LOOPS                                              |                              |     1 |   761 | 14059   (3)| 00:00:01 |
|   4 |     NESTED LOOPS                                             |                              |     1 |   730 | 14056   (3)| 00:00:01 |
|   5 |      NESTED LOOPS                                            |                              |     1 |   720 | 14055   (3)| 00:00:01 |
|   6 |       NESTED LOOPS                                           |                              |     1 |   694 | 14054   (3)| 00:00:01 |
|   7 |        NESTED LOOPS                                          |                              |     1 |   663 | 14053   (3)| 00:00:01 |
|*  8 |         HASH JOIN                                            |                              |     1 |   628 | 14051   (3)| 00:00:01 |
|   9 |          JOIN FILTER CREATE                                  | :BF0000                      |     1 |   619 |    82   (0)| 00:00:01 |
|  10 |           NESTED LOOPS                                       |                              |     1 |   619 |    82   (0)| 00:00:01 |
|  11 |            NESTED LOOPS                                      |                              |     1 |   619 |    82   (0)| 00:00:01 |
|  12 |             NESTED LOOPS                                     |                              |     1 |   546 |    79   (0)| 00:00:01 |
|  13 |              NESTED LOOPS                                    |                              |     1 |   540 |    78   (0)| 00:00:01 |
|  14 |               NESTED LOOPS                                   |                              |     1 |   524 |    76   (0)| 00:00:01 |
|  15 |                NESTED LOOPS                                  |                              |     1 |   510 |    75   (0)| 00:00:01 |
|  16 |                 NESTED LOOPS                                 |                              |     1 |   492 |    72   (0)| 00:00:01 |
|  17 |                  NESTED LOOPS                                |                              |     1 |   483 |    71   (0)| 00:00:01 |
|  18 |                   NESTED LOOPS                               |                              |     1 |   478 |    71   (0)| 00:00:01 |
|  19 |                    NESTED LOOPS                              |                              |     1 |   459 |    68   (0)| 00:00:01 |
|  20 |                     NESTED LOOPS                             |                              |     1 |   428 |    65   (0)| 00:00:01 |
|  21 |                      NESTED LOOPS                            |                              |     1 |   418 |    64   (0)| 00:00:01 |
|  22 |                       NESTED LOOPS                           |                              |     1 |   379 |    62   (0)| 00:00:01 |
|  23 |                        NESTED LOOPS                          |                              |     1 |   357 |    60   (0)| 00:00:01 |
|  24 |                         NESTED LOOPS                         |                              |     1 |   268 |    17   (0)| 00:00:01 |
|  25 |                          NESTED LOOPS                        |                              |     1 |   248 |    10   (0)| 00:00:01 |
|  26 |                           NESTED LOOPS                       |                              |     1 |   223 |    10   (0)| 00:00:01 |
|  27 |                            MERGE JOIN CARTESIAN              |                              |     1 |   197 |    10   (0)| 00:00:01 |
|  28 |                             MERGE JOIN CARTESIAN             |                              |     1 |   167 |     5   (0)| 00:00:01 |
|  29 |                              NESTED LOOPS                    |                              |     1 |   137 |     4   (0)| 00:00:01 |
|  30 |                               NESTED LOOPS                   |                              |     1 |    64 |     1   (0)| 00:00:01 |
|* 31 |                                INDEX UNIQUE SCAN             | FND_ID_FLEX_STRUCTURES_U1    |     1 |    30 |     1   (0)| 00:00:01 |
|* 32 |                                INDEX UNIQUE SCAN             | FND_ID_FLEX_STRUCTURES_TL_U1 |     1 |    34 |     0   (0)| 00:00:01 |
|  33 |                               TABLE ACCESS BY INDEX ROWID    | FND_LOOKUP_VALUES            |     1 |    73 |     3   (0)| 00:00:01 |
|* 34 |                                INDEX RANGE SCAN              | FND_LOOKUP_VALUES_U1         |     1 |       |     2   (0)| 00:00:01 |
|  35 |                              BUFFER SORT                     |                              |     1 |    30 |     2   (0)| 00:00:01 |
|* 36 | TCHED                         TABLE ACCESS BY INDEX ROWID BA | MTL_CATEGORY_SETS_B          |     1 |    30 |     1   (0)| 00:00:01 |
|* 37 |                                INDEX RANGE SCAN              | MTL_CATEGORY_SETS_B_N1       |     1 |       |     0   (0)| 00:00:01 |
|  38 |                             BUFFER SORT                      |                              |     9 |   270 |     9   (0)| 00:00:01 |
|* 39 | CHED                         TABLE ACCESS BY INDEX ROWID BAT | MTL_CATEGORIES_B             |     9 |   270 |     5   (0)| 00:00:01 |
|* 40 |                               INDEX RANGE SCAN               | MTL_CATEGORIES_B_N1          |    57 |       |     1   (0)| 00:00:01 |
|* 41 |                            INDEX UNIQUE SCAN                 | MTL_CATEGORY_SETS_TL_U1      |     1 |    26 |     0   (0)| 00:00:01 |
|* 42 |                           INDEX UNIQUE SCAN                  | MTL_CATEGORIES_TL_U1         |     1 |    25 |     0   (0)| 00:00:01 |
|  43 |                          TABLE ACCESS BY INDEX ROWID BATCHED | MTL_ITEM_CATEGORIES          |     1 |    20 |     7   (0)| 00:00:01 |
|* 44 |                           INDEX RANGE SCAN                   | MTL_ITEM_CATEGORIES_N3       |    19 |       |     2   (0)| 00:00:01 |
|* 45 |                         TABLE ACCESS BY INDEX ROWID BATCHED  | GMD_SAMPLES                  |     1 |    89 |    43   (0)| 00:00:01 |
|* 46 |                          INDEX RANGE SCAN                    | GMD_SAMPLES_N2               |    49 |       |     2   (0)| 00:00:01 |
|  47 |                        TABLE ACCESS BY INDEX ROWID           | MTL_SYSTEM_ITEMS_B           |     1 |    22 |     2   (0)| 00:00:01 |
|* 48 |                         INDEX UNIQUE SCAN                    | MTL_SYSTEM_ITEMS_B_U1        |     1 |       |     1   (0)| 00:00:01 |
|  49 |                       TABLE ACCESS BY INDEX ROWID            | MTL_SYSTEM_ITEMS_TL          |     1 |    39 |     2   (0)| 00:00:01 |
|* 50 |                        INDEX UNIQUE SCAN                     | MTL_SYSTEM_ITEMS_TL_U1       |     1 |       |     1   (0)| 00:00:01 |
|  51 |                      TABLE ACCESS BY INDEX ROWID             | FND_USER                     |     1 |    10 |     1   (0)| 00:00:01 |
|* 52 |                       INDEX UNIQUE SCAN                      | FND_USER_U1                  |     1 |       |     0   (0)| 00:00:01 |
|  53 |                     TABLE ACCESS BY INDEX ROWID BATCHED      | PER_ALL_PEOPLE_F             |     1 |    31 |     3   (0)| 00:00:01 |
|* 54 |                      INDEX RANGE SCAN                        | PER_PEOPLE_F_PK              |     1 |       |     2   (0)| 00:00:01 |
|* 55 |                    TABLE ACCESS BY INDEX ROWID BATCHED       | GMD_EVENT_SPEC_DISP          |     1 |    19 |     3   (0)| 00:00:01 |
|* 56 |                     INDEX RANGE SCAN                         | GMD_EVENT_SPEC_DISP_N1       |     1 |       |     2   (0)| 00:00:01 |
|* 57 |                   INDEX UNIQUE SCAN                          | GMD_SPECIFICATIONS_B_PK      |     1 |     5 |     0   (0)| 00:00:01 |
|* 58 |                  INDEX UNIQUE SCAN                           | GMD_SPECIFICATIONS_TL_PK     |     1 |     9 |     1   (0)| 00:00:01 |
|  59 |                 TABLE ACCESS BY INDEX ROWID BATCHED          | GMD_SPEC_TESTS_B             |     5 |    90 |     3   (0)| 00:00:01 |
|* 60 |                  INDEX RANGE SCAN                            | GMD_SPEC_TESTS_B_PK          |     5 |       |     2   (0)| 00:00:01 |
|* 61 |                INDEX UNIQUE SCAN                             | GMD_SPEC_TESTS_TL_PK         |     1 |    14 |     1   (0)| 00:00:01 |
|  62 |               TABLE ACCESS BY INDEX ROWID                    | GMD_SAMPLE_SPEC_DISP         |     1 |    16 |     2   (0)| 00:00:01 |
|* 63 |                INDEX UNIQUE SCAN                             | GMD_SAMPLE_SPEC_DISP_PK      |     1 |       |     1   (0)| 00:00:01 |
|* 64 |              INDEX UNIQUE SCAN                               | GMD_SAMPLING_EVENTS_PK       |     1 |     6 |     1   (0)| 00:00:01 |
|* 65 |             INDEX RANGE SCAN                                 | FND_LOOKUP_VALUES_U1         |     1 |       |     2   (0)| 00:00:01 |
|  66 |            TABLE ACCESS BY INDEX ROWID                       | FND_LOOKUP_VALUES            |     1 |    73 |     3   (0)| 00:00:01 |
|  67 |          JOIN FILTER USE                                     | :BF0000                      |  4965K|    42M| 13909   (2)| 00:00:01 |
|* 68 |           TABLE ACCESS STORAGE FULL                          | GMD_SPEC_RESULTS             |  4965K|    42M| 13909   (2)| 00:00:01 |
|* 69 |         TABLE ACCESS BY INDEX ROWID                          | GMD_RESULTS                  |     1 |    35 |     2   (0)| 00:00:01 |
|* 70 |          INDEX UNIQUE SCAN                                   | GMD_RESULTS_PK               |     1 |       |     1   (0)| 00:00:01 |
|  71 |        TABLE ACCESS BY INDEX ROWID                           | GMD_QC_TESTS_B               |     1 |    31 |     1   (0)| 00:00:01 |
|* 72 |         INDEX UNIQUE SCAN                                    | GMD_QC_TESTS_B_PK            |     1 |       |     0   (0)| 00:00:01 |
|  73 |       TABLE ACCESS BY INDEX ROWID                            | GMD_QC_TESTS_TL              |     1 |    26 |     1   (0)| 00:00:01 |
|* 74 |        INDEX UNIQUE SCAN                                     | GMD_QC_TESTS_TL_PK           |     1 |       |     0   (0)| 00:00:01 |
|  75 |      TABLE ACCESS BY INDEX ROWID                             | FND_USER                     |     1 |    10 |     1   (0)| 00:00:01 |
|* 76 |       INDEX UNIQUE SCAN                                      | FND_USER_U1                  |     1 |       |     0   (0)| 00:00:01 |
|  77 |     TABLE ACCESS BY INDEX ROWID BATCHED                      | PER_ALL_PEOPLE_F             |     1 |    31 |     3   (0)| 00:00:01 |
|* 78 |      INDEX RANGE SCAN                                        | PER_PEOPLE_F_PK              |     1 |       |     2   (0)| 00:00:01 |
|* 79 |    INDEX RANGE SCAN                                          | FND_LOOKUP_VALUES_U1         |     1 |    56 |     2   (0)| 00:00:01 |
---------------------------------------------------------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   8 - access("LOOKUP_CODE"="GSR"."EVALUATION_IND")
  31 - access("APPLICATION_ID"=401 AND "ID_FLEX_CODE"='MCAT' AND "ID_FLEX_NUM"=101 AND "ZD_EDITION_NAME"='V_20151118_1137')
  32 - access("APPLICATION_ID"=401 AND "ID_FLEX_CODE"='MCAT' AND "ID_FLEX_NUM"=101 AND "LANGUAGE"=USERENV('LANG') AND
              "ZD_EDITION_NAME"='V_20151118_1137')
  34 - access("LOOKUP_TYPE"='GMD_QC_EVALUATION' AND "VIEW_APPLICATION_ID">=550 AND "SECURITY_GROUP_ID"=0 AND
              "LANGUAGE"=USERENV('LANG') AND "ZD_EDITION_NAME"='V_20151118_1137' AND "VIEW_APPLICATION_ID"<=559)
       filter("LANGUAGE"=USERENV('LANG') AND "SECURITY_GROUP_ID"=0 AND "ZD_EDITION_NAME"='V_20151118_1137')
  36 - filter("ZD_EDITION_NAME"='V_20151118_1137')
  37 - access("STRUCTURE_ID"=101)
  39 - filter("STRUCTURE_ID"=101 AND "ZD_EDITION_NAME"='V_20151118_1137')
  40 - access("SEGMENT1"='15')
  41 - access("CATEGORY_SET_ID"="CATEGORY_SET_ID" AND "LANGUAGE"=USERENV('LANG') AND "ZD_EDITION_NAME"='V_20151118_1137')
  42 - access("CATEGORY_ID"="CATEGORY_ID" AND "LANGUAGE"=USERENV('LANG') AND "ZD_EDITION_NAME"='V_20151118_1137')
  44 - access("MIC"."CATEGORY_SET_ID"="CATEGORY_SET_ID" AND "MIC"."CATEGORY_ID"="CATEGORY_ID" AND "MIC"."ORGANIZATION_ID"=1083)
  45 - filter("GS"."ORGANIZATION_ID"=1083 AND "GS"."DATE_DRAWN">=TO_DATE(' 2017-01-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND
              "GS"."SAMPLE_TYPE"='I' AND "GS"."DATE_DRAWN"<=TO_DATE(' 2017-09-11 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))
  46 - access("MIC"."INVENTORY_ITEM_ID"="GS"."INVENTORY_ITEM_ID")
       filter("GS"."LOT_NUMBER" IS NOT NULL AND "GS"."INVENTORY_ITEM_ID" IS NOT NULL)
  48 - access("GS"."INVENTORY_ITEM_ID"="INVENTORY_ITEM_ID" AND "ORGANIZATION_ID"=1083)
  50 - access("INVENTORY_ITEM_ID"="T"."INVENTORY_ITEM_ID" AND "T"."ORGANIZATION_ID"=1083 AND "T"."LANGUAGE"=USERENV('LANG'))
  52 - access("GS"."SAMPLER_ID"="F"."USER_ID")
  54 - access("P"."PERSON_ID"="F"."EMPLOYEE_ID" AND "P"."EFFECTIVE_END_DATE">=SYSDATE@! AND "P"."EFFECTIVE_START_DATE"<=SYSDATE@!)
       filter("P"."EFFECTIVE_END_DATE">=SYSDATE@!)
  55 - filter("GES"."SPEC_USED_FOR_LOT_ATTRIB_IND"='Y')
  56 - access("GS"."SAMPLING_EVENT_ID"="GES"."SAMPLING_EVENT_ID")
  57 - access("GES"."SPEC_ID"="B"."SPEC_ID")
  58 - access("B"."SPEC_ID"="T"."SPEC_ID" AND "T"."LANGUAGE"=USERENV('LANG'))
  60 - access("GES"."SPEC_ID"="B"."SPEC_ID")
  61 - access("B"."SPEC_ID"="T"."SPEC_ID" AND "B"."TEST_ID"="T"."TEST_ID" AND "T"."LANGUAGE"=USERENV('LANG'))
  63 - access("GES"."EVENT_SPEC_DISP_ID"="SSD"."EVENT_SPEC_DISP_ID" AND "SSD"."SAMPLE_ID"="GS"."SAMPLE_ID")
  64 - access("SE"."SAMPLING_EVENT_ID"="GES"."SAMPLING_EVENT_ID")
  65 - access("LOOKUP_TYPE"='GMD_QC_SAMPLE_DISP' AND "VIEW_APPLICATION_ID">=550 AND "LOOKUP_CODE"="SSD"."DISPOSITION" AND
              "SECURITY_GROUP_ID"=0 AND "LANGUAGE"=USERENV('LANG') AND "ZD_EDITION_NAME"='V_20151118_1137' AND "VIEW_APPLICATION_ID"<=559)
       filter("LOOKUP_CODE"="SSD"."DISPOSITION" AND "LANGUAGE"=USERENV('LANG') AND "SECURITY_GROUP_ID"=0 AND
              "ZD_EDITION_NAME"='V_20151118_1137')
  68 - storage("GSR"."EVALUATION_IND" IS NOT NULL AND SYS_OP_BLOOM_FILTER(:BF0000,"GSR"."EVALUATION_IND"))
       filter("GSR"."EVALUATION_IND" IS NOT NULL AND SYS_OP_BLOOM_FILTER(:BF0000,"GSR"."EVALUATION_IND"))
  69 - filter("GR"."SAMPLE_ID"="GS"."SAMPLE_ID" AND "GR"."TEST_ID"="B"."TEST_ID" AND "GR"."TESTER_ID" IS NOT NULL)
  70 - access("GSR"."RESULT_ID"="GR"."RESULT_ID")
  72 - access("GR"."TEST_ID"="B"."TEST_ID")
  74 - access("B"."TEST_ID"="T"."TEST_ID" AND "T"."LANGUAGE"=USERENV('LANG'))
  76 - access("GR"."TESTER_ID"="F"."USER_ID")
  78 - access("P"."PERSON_ID"="F"."EMPLOYEE_ID" AND "P"."EFFECTIVE_END_DATE">=SYSDATE@! AND "P"."EFFECTIVE_START_DATE"<=SYSDATE@!)
       filter("P"."EFFECTIVE_END_DATE">=SYSDATE@!)
  79 - access("LOOKUP_TYPE"='ITEM_CONTROL_LEVEL_GUI' AND "VIEW_APPLICATION_ID"=700 AND "SECURITY_GROUP_ID"=0 AND
              "LANGUAGE"=USERENV('LANG') AND "ZD_EDITION_NAME"='V_20151118_1137')
       filter("CONTROL_LEVEL"=TO_NUMBER("LOOKUP_CODE") AND "LANGUAGE"=USERENV('LANG') AND "SECURITY_GROUP_ID"=0 AND
              "ZD_EDITION_NAME"='V_20151118_1137')


exsits 改写成 left  join

消除了谓词推入和笛卡尔积,不去null


Plan hash value: 655267779
 
---------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                                                    | Name                         | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                                             |                              |     1 |   570 | 18359   (3)| 00:00:01 |
|   1 |  WINDOW SORT                                                 |                              |     1 |   570 | 18359   (3)| 00:00:01 |
|   2 |   NESTED LOOPS                                               |                              |     1 |   570 | 18358   (3)| 00:00:01 |
|   3 |    NESTED LOOPS                                              |                              |     1 |   570 | 18358   (3)| 00:00:01 |
|   4 |     NESTED LOOPS                                             |                              |     1 |   544 | 18357   (3)| 00:00:01 |
|   5 |      NESTED LOOPS                                            |                              |     1 |   513 | 18356   (3)| 00:00:01 |
|   6 |       NESTED LOOPS                                           |                              |     1 |   482 | 18353   (3)| 00:00:01 |
|   7 |        NESTED LOOPS                                          |                              |     1 |   472 | 18352   (3)| 00:00:01 |
|   8 |         NESTED LOOPS                                         |                              |     1 |   458 | 18351   (3)| 00:00:01 |
|   9 |          NESTED LOOPS                                        |                              |     1 |   440 | 18349   (3)| 00:00:01 |
|* 10 |           HASH JOIN                                          |                              |     1 |   367 | 18346   (3)| 00:00:01 |
|  11 |            JOIN FILTER CREATE                                | :BF0000                      |     1 |   358 |  4376   (2)| 00:00:01 |
|  12 |             NESTED LOOPS                                     |                              |     1 |   358 |  4376   (2)| 00:00:01 |
|  13 |              NESTED LOOPS                                    |                              |     5 |   358 |  4376   (2)| 00:00:01 |
|  14 |               NESTED LOOPS OUTER                             |                              |     1 |   323 |  4373   (2)| 00:00:01 |
|  15 |                NESTED LOOPS                                  |                              |     1 |   319 |  4366   (2)| 00:00:01 |
|  16 |                 NESTED LOOPS                                 |                              |     1 |   288 |  4363   (2)| 00:00:01 |
|  17 |                  NESTED LOOPS                                |                              |     1 |   278 |  4362   (2)| 00:00:01 |
|  18 |                   NESTED LOOPS                               |                              |     1 |   272 |  4361   (2)| 00:00:01 |
|  19 |                    NESTED LOOPS                              |                              |     1 |   263 |  4360   (2)| 00:00:01 |
|  20 |                     NESTED LOOPS                             |                              |     1 |   258 |  4360   (2)| 00:00:01 |
|  21 |                      NESTED LOOPS                            |                              |     1 |   219 |  4358   (2)| 00:00:01 |
|  22 |                       NESTED LOOPS                           |                              |     1 |   197 |  4356   (2)| 00:00:01 |
|* 23 |                        HASH JOIN                             |                              |     9 |  1602 |  4338   (2)| 00:00:01 |
|  24 |                         TABLE ACCESS BY INDEX ROWID BATCHED  | FND_LOOKUP_VALUES            |     1 |    73 |     4   (0)| 00:00:01 |
|* 25 |                          INDEX RANGE SCAN                    | FND_LOOKUP_VALUES_U1         |     1 |       |     3   (0)| 00:00:01 |
|* 26 |                         HASH JOIN                            |                              |  1461 |   149K|  4334   (2)| 00:00:01 |
|  27 |                          JOIN FILTER CREATE                  | :BF0001                      |  1461 |   126K|  1989   (1)| 00:00:01 |
|* 28 |                           TABLE ACCESS BY INDEX ROWID BATCHED| GMD_SAMPLES                  |  1461 |   126K|  1989   (1)| 00:00:01 |
|* 29 |                            INDEX RANGE SCAN                  | GMD_SAMPLES_U1               |  3947 |       |    21   (0)| 00:00:01 |
|  30 |                          JOIN FILTER USE                     | :BF0001                      |  1105K|    16M|  2332   (3)| 00:00:01 |
|* 31 |                           TABLE ACCESS STORAGE FULL          | GMD_SAMPLE_SPEC_DISP         |  1105K|    16M|  2332   (3)| 00:00:01 |
|* 32 |                        TABLE ACCESS BY INDEX ROWID           | GMD_EVENT_SPEC_DISP          |     1 |    19 |     2   (0)| 00:00:01 |
|* 33 |                         INDEX UNIQUE SCAN                    | GMD_EVENT_SPEC_DISP_PK       |     1 |       |     1   (0)| 00:00:01 |
|  34 |                       TABLE ACCESS BY INDEX ROWID            | MTL_SYSTEM_ITEMS_B           |     1 |    22 |     2   (0)| 00:00:01 |
|* 35 |                        INDEX UNIQUE SCAN                     | MTL_SYSTEM_ITEMS_B_U1        |     1 |       |     1   (0)| 00:00:01 |
|  36 |                      TABLE ACCESS BY INDEX ROWID             | MTL_SYSTEM_ITEMS_TL          |     1 |    39 |     2   (0)| 00:00:01 |
|* 37 |                       INDEX UNIQUE SCAN                      | MTL_SYSTEM_ITEMS_TL_U1       |     1 |       |     1   (0)| 00:00:01 |
|* 38 |                     INDEX UNIQUE SCAN                        | GMD_SPECIFICATIONS_B_PK      |     1 |     5 |     0   (0)| 00:00:01 |
|* 39 |                    INDEX UNIQUE SCAN                         | GMD_SPECIFICATIONS_TL_PK     |     1 |     9 |     1   (0)| 00:00:01 |
|* 40 |                   INDEX UNIQUE SCAN                          | GMD_SAMPLING_EVENTS_PK       |     1 |     6 |     1   (0)| 00:00:01 |
|  41 |                  TABLE ACCESS BY INDEX ROWID                 | FND_USER                     |     1 |    10 |     1   (0)| 00:00:01 |
|* 42 |                   INDEX UNIQUE SCAN                          | FND_USER_U1                  |     1 |       |     0   (0)| 00:00:01 |
|  43 |                 TABLE ACCESS BY INDEX ROWID BATCHED          | PER_ALL_PEOPLE_F             |     1 |    31 |     3   (0)| 00:00:01 |
|* 44 |                  INDEX RANGE SCAN                            | PER_PEOPLE_F_PK              |     1 |       |     2   (0)| 00:00:01 |
|  45 |                VIEW PUSHED PREDICATE                         |                              |     1 |     4 |     7   (0)| 00:00:01 |
|* 46 |                 FILTER                                       |                              |       |       |            |          |
|  47 |                  NESTED LOOPS                                |                              |     1 |   251 |     7   (0)| 00:00:01 |
|  48 |                   NESTED LOOPS                               |                              |     1 |   195 |     5   (0)| 00:00:01 |
|  49 |                    NESTED LOOPS                              |                              |     1 |   170 |     5   (0)| 00:00:01 |
|  50 |                     NESTED LOOPS                             |                              |     1 |   140 |     4   (0)| 00:00:01 |
|  51 |                      NESTED LOOPS                            |                              |     1 |   114 |     4   (0)| 00:00:01 |
|  52 |                       NESTED LOOPS                           |                              |     1 |    84 |     3   (0)| 00:00:01 |
|  53 |                        NESTED LOOPS                          |                              |     1 |    64 |     1   (0)| 00:00:01 |
|* 54 |                         INDEX UNIQUE SCAN                    | FND_ID_FLEX_STRUCTURES_U1    |     1 |    30 |     1   (0)| 00:00:01 |
|* 55 |                         INDEX UNIQUE SCAN                    | FND_ID_FLEX_STRUCTURES_TL_U1 |     1 |    34 |     0   (0)| 00:00:01 |
|* 56 |                        INDEX RANGE SCAN                      | MTL_ITEM_CATEGORIES_U1       |     1 |    20 |     2   (0)| 00:00:01 |
|* 57 |                       TABLE ACCESS BY INDEX ROWID            | MTL_CATEGORY_SETS_B          |     1 |    30 |     1   (0)| 00:00:01 |
|* 58 |                        INDEX UNIQUE SCAN                     | MTL_CATEGORY_SETS_B_U1       |     1 |       |     0   (0)| 00:00:01 |
|* 59 |                      INDEX UNIQUE SCAN                       | MTL_CATEGORY_SETS_TL_U1      |     1 |    26 |     0   (0)| 00:00:01 |
|* 60 |                     TABLE ACCESS BY INDEX ROWID              | MTL_CATEGORIES_B             |     1 |    30 |     1   (0)| 00:00:01 |
|* 61 |                      INDEX UNIQUE SCAN                       | MTL_CATEGORIES_B_U1          |     1 |       |     0   (0)| 00:00:01 |
|* 62 |                    INDEX UNIQUE SCAN                         | MTL_CATEGORIES_TL_U1         |     1 |    25 |     0   (0)| 00:00:01 |
|* 63 |                   INDEX RANGE SCAN                           | FND_LOOKUP_VALUES_U1         |     1 |    56 |     2   (0)| 00:00:01 |
|* 64 |               INDEX RANGE SCAN                               | GMD_RESULTS_N1               |     5 |       |     2   (0)| 00:00:01 |
|* 65 |              TABLE ACCESS BY INDEX ROWID                     | GMD_RESULTS                  |     4 |   140 |     3   (0)| 00:00:01 |
|  66 |            JOIN FILTER USE                                   | :BF0000                      |  4965K|    42M| 13909   (2)| 00:00:01 |
|* 67 |             TABLE ACCESS STORAGE FULL                        | GMD_SPEC_RESULTS             |  4965K|    42M| 13909   (2)| 00:00:01 |
|  68 |           TABLE ACCESS BY INDEX ROWID BATCHED                | FND_LOOKUP_VALUES            |     1 |    73 |     3   (0)| 00:00:01 |
|* 69 |            INDEX RANGE SCAN                                  | FND_LOOKUP_VALUES_U1         |     1 |       |     2   (0)| 00:00:01 |
|  70 |          TABLE ACCESS BY INDEX ROWID                         | GMD_SPEC_TESTS_B             |     1 |    18 |     2   (0)| 00:00:01 |
|* 71 |           INDEX UNIQUE SCAN                                  | GMD_SPEC_TESTS_B_PK          |     1 |       |     1   (0)| 00:00:01 |
|* 72 |         INDEX UNIQUE SCAN                                    | GMD_SPEC_TESTS_TL_PK         |     1 |    14 |     1   (0)| 00:00:01 |
|  73 |        TABLE ACCESS BY INDEX ROWID                           | FND_USER                     |     1 |    10 |     1   (0)| 00:00:01 |
|* 74 |         INDEX UNIQUE SCAN                                    | FND_USER_U1                  |     1 |       |     0   (0)| 00:00:01 |
|  75 |       TABLE ACCESS BY INDEX ROWID BATCHED                    | PER_ALL_PEOPLE_F             |     1 |    31 |     3   (0)| 00:00:01 |
|* 76 |        INDEX RANGE SCAN                                      | PER_PEOPLE_F_PK              |     1 |       |     2   (0)| 00:00:01 |
|  77 |      TABLE ACCESS BY INDEX ROWID                             | GMD_QC_TESTS_B               |     1 |    31 |     1   (0)| 00:00:01 |
|* 78 |       INDEX UNIQUE SCAN                                      | GMD_QC_TESTS_B_PK            |     1 |       |     0   (0)| 00:00:01 |
|* 79 |     INDEX UNIQUE SCAN                                        | GMD_QC_TESTS_TL_PK           |     1 |       |     0   (0)| 00:00:01 |
|  80 |    TABLE ACCESS BY INDEX ROWID                               | GMD_QC_TESTS_TL              |     1 |    26 |     1   (0)| 00:00:01 |
---------------------------------------------------------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
  10 - access("GSR"."RESULT_ID"="GR"."RESULT_ID")
  23 - access("LOOKUP_CODE"="SSD"."DISPOSITION")
  25 - access("LOOKUP_TYPE"='GMD_QC_SAMPLE_DISP' AND "VIEW_APPLICATION_ID">=550 AND "SECURITY_GROUP_ID"=0 AND
              "LANGUAGE"=USERENV('LANG') AND "ZD_EDITION_NAME"='V_20151118_1137' AND "VIEW_APPLICATION_ID"<=559)
       filter("LANGUAGE"=USERENV('LANG') AND "SECURITY_GROUP_ID"=0 AND "ZD_EDITION_NAME"='V_20151118_1137')
  26 - access("SSD"."SAMPLE_ID"="GS"."SAMPLE_ID")
  28 - filter("GS"."DATE_DRAWN">=TO_DATE(' 2017-01-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND "GS"."LOT_NUMBER" IS NOT NULL AND
              "GS"."INVENTORY_ITEM_ID" IS NOT NULL AND "GS"."SAMPLE_TYPE"='I' AND "GS"."DATE_DRAWN"<=TO_DATE(' 2017-09-11 00:00:00', 'syyyy-mm-dd
              hh24:mi:ss'))
  29 - access("GS"."ORGANIZATION_ID"=1083)
  31 - storage(SYS_OP_BLOOM_FILTER(:BF0001,"SSD"."SAMPLE_ID"))
       filter(SYS_OP_BLOOM_FILTER(:BF0001,"SSD"."SAMPLE_ID"))
  32 - filter("GS"."SAMPLING_EVENT_ID"="GES"."SAMPLING_EVENT_ID" AND "GES"."SPEC_USED_FOR_LOT_ATTRIB_IND"='Y')
  33 - access("GES"."EVENT_SPEC_DISP_ID"="SSD"."EVENT_SPEC_DISP_ID")
  35 - access("GS"."INVENTORY_ITEM_ID"="INVENTORY_ITEM_ID" AND "ORGANIZATION_ID"=1083)
  37 - access("INVENTORY_ITEM_ID"="T"."INVENTORY_ITEM_ID" AND "T"."ORGANIZATION_ID"=1083 AND "T"."LANGUAGE"=USERENV('LANG'))
  38 - access("GES"."SPEC_ID"="B"."SPEC_ID")
  39 - access("B"."SPEC_ID"="T"."SPEC_ID" AND "T"."LANGUAGE"=USERENV('LANG'))
  40 - access("SE"."SAMPLING_EVENT_ID"="GES"."SAMPLING_EVENT_ID")
  42 - access("GS"."SAMPLER_ID"="F"."USER_ID")
  44 - access("P"."PERSON_ID"="F"."EMPLOYEE_ID" AND "P"."EFFECTIVE_END_DATE">=SYSDATE@! AND "P"."EFFECTIVE_START_DATE"<=SYSDATE@!)
       filter("P"."EFFECTIVE_END_DATE">=SYSDATE@!)
  46 - filter("GS"."ORGANIZATION_ID"=1083)
  54 - access("APPLICATION_ID"=401 AND "ID_FLEX_CODE"='MCAT' AND "ID_FLEX_NUM"=101 AND "ZD_EDITION_NAME"='V_20151118_1137')
  55 - access("APPLICATION_ID"=401 AND "ID_FLEX_CODE"='MCAT' AND "ID_FLEX_NUM"=101 AND "LANGUAGE"=USERENV('LANG') AND
              "ZD_EDITION_NAME"='V_20151118_1137')
  56 - access("MIC"."ORGANIZATION_ID"=1083 AND "MIC"."INVENTORY_ITEM_ID"="GS"."INVENTORY_ITEM_ID")
  57 - filter("STRUCTURE_ID"=101)
  58 - access("MIC"."CATEGORY_SET_ID"="CATEGORY_SET_ID" AND "ZD_EDITION_NAME"='V_20151118_1137')
  59 - access("CATEGORY_SET_ID"="CATEGORY_SET_ID" AND "LANGUAGE"=USERENV('LANG') AND "ZD_EDITION_NAME"='V_20151118_1137')
  60 - filter("SEGMENT1"='15' AND "STRUCTURE_ID"=101)
  61 - access("MIC"."CATEGORY_ID"="CATEGORY_ID" AND "ZD_EDITION_NAME"='V_20151118_1137')
  62 - access("CATEGORY_ID"="CATEGORY_ID" AND "LANGUAGE"=USERENV('LANG') AND "ZD_EDITION_NAME"='V_20151118_1137')
  63 - access("LOOKUP_TYPE"='ITEM_CONTROL_LEVEL_GUI' AND "VIEW_APPLICATION_ID"=700 AND "SECURITY_GROUP_ID"=0 AND
              "LANGUAGE"=USERENV('LANG') AND "ZD_EDITION_NAME"='V_20151118_1137')
       filter("CONTROL_LEVEL"=TO_NUMBER("LOOKUP_CODE") AND "LANGUAGE"=USERENV('LANG') AND "SECURITY_GROUP_ID"=0 AND
              "ZD_EDITION_NAME"='V_20151118_1137')
  64 - access("GR"."SAMPLE_ID"="GS"."SAMPLE_ID")
  65 - filter("GR"."TESTER_ID" IS NOT NULL)
  67 - storage("GSR"."EVALUATION_IND" IS NOT NULL AND SYS_OP_BLOOM_FILTER(:BF0000,"GSR"."RESULT_ID"))
       filter("GSR"."EVALUATION_IND" IS NOT NULL AND SYS_OP_BLOOM_FILTER(:BF0000,"GSR"."RESULT_ID"))
  69 - access("LOOKUP_TYPE"='GMD_QC_EVALUATION' AND "VIEW_APPLICATION_ID">=550 AND "LOOKUP_CODE"="GSR"."EVALUATION_IND" AND
              "SECURITY_GROUP_ID"=0 AND "LANGUAGE"=USERENV('LANG') AND "ZD_EDITION_NAME"='V_20151118_1137' AND "VIEW_APPLICATION_ID"<=559)
       filter("LOOKUP_CODE"="GSR"."EVALUATION_IND" AND "LANGUAGE"=USERENV('LANG') AND "SECURITY_GROUP_ID"=0 AND
              "ZD_EDITION_NAME"='V_20151118_1137')
  71 - access("GES"."SPEC_ID"="B"."SPEC_ID" AND "GR"."TEST_ID"="B"."TEST_ID")
  72 - access("B"."SPEC_ID"="T"."SPEC_ID" AND "B"."TEST_ID"="T"."TEST_ID" AND "T"."LANGUAGE"=USERENV('LANG'))
  74 - access("GR"."TESTER_ID"="F"."USER_ID")
  76 - access("P"."PERSON_ID"="F"."EMPLOYEE_ID" AND "P"."EFFECTIVE_END_DATE">=SYSDATE@! AND "P"."EFFECTIVE_START_DATE"<=SYSDATE@!)
       filter("P"."EFFECTIVE_END_DATE">=SYSDATE@!)
  78 - access("GR"."TEST_ID"="B"."TEST_ID")
  79 - access("B"."TEST_ID"="T"."TEST_ID" AND "T"."LANGUAGE"=USERENV('LANG'))


--left join 之后 有 null,为了去掉null 保证数据一致性,写了外部查询再过滤一次
 sql


select * from gv$sql where sql_id = '3vm7pkp5cb69g';

 explain plan for
 with t_view as (SELECT  MIC.ORGANIZATION_ID,MIC.INVENTORY_ITEM_ID
          FROM MTL_ITEM_CATEGORIES MIC,
               MTL_CATEGORY_SETS_V MCS,
               MTL_CATEGORIES      MC
         WHERE 1=1
           AND MCS.STRUCTURE_ID = 101
           AND MIC.CATEGORY_SET_ID = MCS.CATEGORY_SET_ID
           AND MCS.STRUCTURE_ID = MC.STRUCTURE_ID
           AND MIC.CATEGORY_ID = MC.CATEGORY_ID
           AND MC.SEGMENT1 = '15')
           select t1.* from (
   SELECT t.ORGANIZATION_ID ORGANIZATION_ID_1,
          t.INVENTORY_ITEM_ID INVENTORY_ITEM_ID_1 ,
       GS.ORG_ID,
       GS.ORGANIZATION_ID,
       GS.SAMPLE_ID,
       GS.SAMPLE_NO,
       GS.SAMPLE_DESC,
       GS.LOT_NUMBER,
       GS.INVENTORY_ITEM_ID,
       GS.DATE_DRAWN,
       MSI.SEGMENT1 ITEM_NUM,
       MSI.DESCRIPTION ITEM_NAME,
       P1.LAST_NAME SAMPLER_USER,
       GH.MEANING HEADER_STATUS,
       GR.RESULT_ID,
       GR.TEST_ID,
       GR.SEQ,
       GQT.TEST_CODE,
       GQT.TEST_DESC,
       GST.MIN_VALUE_NUM,
       GST.TARGET_VALUE_NUM,
       GST.MAX_VALUE_NUM,
       GR.RESULT_VALUE_NUM,
       GR.RESULT_DATE,
       P2.LAST_NAME TESTER_USER,
       GL.MEANING END_RESULT,
       COUNT(1) OVER(PARTITION BY GS.SAMPLE_ID) SL
  FROM (GMD_SAMPLES GS left join t_view t on t.ORGANIZATION_ID = GS.ORGANIZATION_ID
           AND t.INVENTORY_ITEM_ID =GS.INVENTORY_ITEM_ID  --and t.ORGANIZATION_ID is not null
 -- and  t.INVENTORY_ITEM_ID is not null
            ),
       GMD_RESULTS GR,
       GEM_LOOKUPS GL,
       GEM_LOOKUPS GH,
       GMD_SAMPLE_SPEC_DISP SSD,
       GMD_EVENT_SPEC_DISP GES,
       GMD_SAMPLING_EVENTS SE,
       GMD_SPEC_TESTS GST,
       GMD_SPECIFICATIONS GSP,
       GMD_SPEC_RESULTS GSR,
       GMD_QC_TESTS GQT,
       MTL_SYSTEM_ITEMS_VL MSI,
       (SELECT P.LAST_NAME, F.USER_ID, F.USER_NAME
          FROM PER_ALL_PEOPLE_F P, FND_USER F
         WHERE P.PERSON_ID = F.EMPLOYEE_ID
           AND SYSDATE BETWEEN P.EFFECTIVE_START_DATE AND
               NVL(P.EFFECTIVE_END_DATE, SYSDATE + 1)) P1,
       (SELECT P.LAST_NAME, F.USER_ID, F.USER_NAME
          FROM PER_ALL_PEOPLE_F P, FND_USER F
         WHERE P.PERSON_ID = F.EMPLOYEE_ID
           AND SYSDATE BETWEEN P.EFFECTIVE_START_DATE AND
               NVL(P.EFFECTIVE_END_DATE, SYSDATE + 1)) P2
 WHERE GR.SAMPLE_ID = GS.SAMPLE_ID
--and t.ORGANIZATION_ID is not null
--and t.INVENTORY_ITEM_ID is not null
   AND (GS.SAMPLE_TYPE = 'I')
   AND GS.SAMPLING_EVENT_ID = GES.SAMPLING_EVENT_ID
   AND GES.SPEC_ID = GST.SPEC_ID
   AND GR.TEST_ID = GST.TEST_ID
   AND GR.TEST_ID = GQT.TEST_ID
   AND GS.ORGANIZATION_ID = MSI.ORGANIZATION_ID
   AND GS.INVENTORY_ITEM_ID = MSI.INVENTORY_ITEM_ID
   AND GL.LOOKUP_TYPE = 'GMD_QC_EVALUATION'
   AND GL.LOOKUP_CODE = GSR.EVALUATION_IND
   AND GH.LOOKUP_CODE = SSD.DISPOSITION
   AND GH.LOOKUP_TYPE = 'GMD_QC_SAMPLE_DISP'
   AND GSR.RESULT_ID = GR.RESULT_ID
   AND GS.SAMPLER_ID = P1.USER_ID
   AND GR.TESTER_ID = P2.USER_ID
   AND GES.EVENT_SPEC_DISP_ID = SSD.EVENT_SPEC_DISP_ID
   AND SSD.SAMPLE_ID = GS.SAMPLE_ID
   AND GES.SPEC_USED_FOR_LOT_ATTRIB_IND = 'Y'
   AND SE.SAMPLING_EVENT_ID = GES.SAMPLING_EVENT_ID
   AND GST.TEST_ID = GR.TEST_ID
   AND GES.SPEC_ID = GSP.SPEC_ID
   AND GES.SPEC_ID = GST.SPEC_ID
   /*AND EXISTS (SELECT 1
          FROM MTL_ITEM_CATEGORIES MIC,
               MTL_CATEGORY_SETS_V MCS,
               MTL_CATEGORIES      MC
         WHERE MIC.ORGANIZATION_ID = GS.ORGANIZATION_ID
           AND MIC.INVENTORY_ITEM_ID = GS.INVENTORY_ITEM_ID
           AND MCS.STRUCTURE_ID = 101
           AND MIC.CATEGORY_SET_ID = MCS.CATEGORY_SET_ID
           AND MCS.STRUCTURE_ID = MC.STRUCTURE_ID
           AND MIC.CATEGORY_ID = MC.CATEGORY_ID
           AND MC.SEGMENT1 = '15')*/
   AND GS.ORGANIZATION_ID = 1083
   AND GS.DATE_DRAWN BETWEEN to_date('01/01/2017 00:00:00','MM/DD/YYYY HH24:MI:SS')
   AND to_date('09/11/2017 00:00:00','MM/DD/YYYY HH24:MI:SS')
   AND GS.INVENTORY_ITEM_ID = NVL(null, GS.INVENTORY_ITEM_ID)
   AND GS.LOT_NUMBER = NVL(null, GS.LOT_NUMBER)
   AND GS.SAMPLE_NO = NVL(null, GS.SAMPLE_NO)) t1
   where t1.ORGANIZATION_ID_1  is not null
          and t1.INVENTORY_ITEM_ID_1 is not null;

 


Plan hash value: 1361232557
 
---------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                                                    | Name                         | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                                             |                              |     1 |   872 | 16692   (2)| 00:00:01 |
|*  1 |  VIEW                                                        |                              |     1 |   872 | 16692   (2)| 00:00:01 |
|   2 |   WINDOW SORT                                                |                              |     1 |   576 | 16692   (2)| 00:00:01 |
|   3 |    NESTED LOOPS                                              |                              |     1 |   576 | 16691   (2)| 00:00:01 |
|   4 |     NESTED LOOPS                                             |                              |     1 |   576 | 16691   (2)| 00:00:01 |
|*  5 |      HASH JOIN                                               |                              |     1 |   503 | 16688   (2)| 00:00:01 |
|   6 |       JOIN FILTER CREATE                                     | :BF0000                      |     1 |   494 |  2719   (1)| 00:00:01 |
|   7 |        NESTED LOOPS                                          |                              |     1 |   494 |  2719   (1)| 00:00:01 |
|*  8 |         HASH JOIN OUTER                                      |                              |     1 |   480 |  2718   (1)| 00:00:01 |
|   9 |          NESTED LOOPS                                        |                              |     1 |   470 |  2698   (1)| 00:00:01 |
|  10 |           NESTED LOOPS                                       |                              |     1 |   470 |  2698   (1)| 00:00:01 |
|  11 |            NESTED LOOPS                                      |                              |     1 |   444 |  2697   (1)| 00:00:01 |
|  12 |             NESTED LOOPS                                     |                              |     1 |   413 |  2696   (1)| 00:00:01 |
|  13 |              NESTED LOOPS                                    |                              |     1 |   382 |  2693   (1)| 00:00:01 |
|  14 |               NESTED LOOPS                                   |                              |     1 |   351 |  2690   (1)| 00:00:01 |
|  15 |                NESTED LOOPS                                  |                              |     1 |   341 |  2689   (1)| 00:00:01 |
|  16 |                 NESTED LOOPS                                 |                              |     1 |   323 |  2687   (1)| 00:00:01 |
|  17 |                  NESTED LOOPS                                |                              |     1 |   288 |  2684   (1)| 00:00:01 |
|  18 |                   NESTED LOOPS                               |                              |     1 |   279 |  2683   (1)| 00:00:01 |
|  19 |                    NESTED LOOPS                              |                              |     1 |   269 |  2682   (1)| 00:00:01 |
|  20 |                     NESTED LOOPS                             |                              |     1 |   263 |  2681   (1)| 00:00:01 |
|  21 |                      NESTED LOOPS                            |                              |     1 |   258 |  2681   (1)| 00:00:01 |
|* 22 |                       HASH JOIN                              |                              |     1 |   239 |  2679   (1)| 00:00:01 |
|  23 |                        TABLE ACCESS BY INDEX ROWID BATCHED   | FND_LOOKUP_VALUES            |     1 |    73 |     4   (0)| 00:00:01 |
|* 24 |                         INDEX RANGE SCAN                     | FND_LOOKUP_VALUES_U1         |     1 |       |     3   (0)| 00:00:01 |
|  25 |                        NESTED LOOPS                          |                              |    76 | 12616 |  2675   (1)| 00:00:01 |
|  26 |                         NESTED LOOPS                         |                              |    76 | 12616 |  2675   (1)| 00:00:01 |
|  27 |                          NESTED LOOPS                        |                              |    76 | 11400 |  2447   (1)| 00:00:01 |
|* 28 |                           HASH JOIN                          |                              |    76 |  8436 |  2295   (1)| 00:00:01 |
|  29 | ED                         TABLE ACCESS BY INDEX ROWID BATCH | MTL_SYSTEM_ITEMS_B           |  1163 | 25586 |   306   (1)| 00:00:01 |
|* 30 |                             INDEX SKIP SCAN                  | MTL_SYSTEM_ITEMS_B_N8        |  1163 |       |     6   (0)| 00:00:01 |
|* 31 | ED                         TABLE ACCESS BY INDEX ROWID BATCH | GMD_SAMPLES                  |  1461 |   126K|  1989   (1)| 00:00:01 |
|* 32 |                             INDEX RANGE SCAN                 | GMD_SAMPLES_U1               |  3947 |       |    21   (0)| 00:00:01 |
|  33 |                           TABLE ACCESS BY INDEX ROWID        | MTL_SYSTEM_ITEMS_TL          |     1 |    39 |     2   (0)| 00:00:01 |
|* 34 |                            INDEX UNIQUE SCAN                 | MTL_SYSTEM_ITEMS_TL_U1       |     1 |       |     1   (0)| 00:00:01 |
|* 35 |                          INDEX RANGE SCAN                    | GMD_SAMPLE_SPEC_DISP_N1      |     1 |       |     2   (0)| 00:00:01 |
|  36 |                         TABLE ACCESS BY INDEX ROWID          | GMD_SAMPLE_SPEC_DISP         |     1 |    16 |     3   (0)| 00:00:01 |
|* 37 |                       TABLE ACCESS BY INDEX ROWID            | GMD_EVENT_SPEC_DISP          |     1 |    19 |     2   (0)| 00:00:01 |
|* 38 |                        INDEX UNIQUE SCAN                     | GMD_EVENT_SPEC_DISP_PK       |     1 |       |     1   (0)| 00:00:01 |
|* 39 |                      INDEX UNIQUE SCAN                       | GMD_SPECIFICATIONS_B_PK      |     1 |     5 |     0   (0)| 00:00:01 |
|* 40 |                     INDEX UNIQUE SCAN                        | GMD_SAMPLING_EVENTS_PK       |     1 |     6 |     1   (0)| 00:00:01 |
|  41 |                    TABLE ACCESS BY INDEX ROWID               | FND_USER                     |     1 |    10 |     1   (0)| 00:00:01 |
|* 42 |                     INDEX UNIQUE SCAN                        | FND_USER_U1                  |     1 |       |     0   (0)| 00:00:01 |
|* 43 |                   INDEX UNIQUE SCAN                          | GMD_SPECIFICATIONS_TL_PK     |     1 |     9 |     1   (0)| 00:00:01 |
|* 44 |                  TABLE ACCESS BY INDEX ROWID BATCHED         | GMD_RESULTS                  |     4 |   140 |     3   (0)| 00:00:01 |
|* 45 |                   INDEX RANGE SCAN                           | GMD_RESULTS_N1               |     5 |       |     2   (0)| 00:00:01 |
|  46 |                 TABLE ACCESS BY INDEX ROWID                  | GMD_SPEC_TESTS_B             |     1 |    18 |     2   (0)| 00:00:01 |
|* 47 |                  INDEX UNIQUE SCAN                           | GMD_SPEC_TESTS_B_PK          |     1 |       |     1   (0)| 00:00:01 |
|  48 |                TABLE ACCESS BY INDEX ROWID                   | FND_USER                     |     1 |    10 |     1   (0)| 00:00:01 |
|* 49 |                 INDEX UNIQUE SCAN                            | FND_USER_U1                  |     1 |       |     0   (0)| 00:00:01 |
|  50 |               TABLE ACCESS BY INDEX ROWID BATCHED            | PER_ALL_PEOPLE_F             |     1 |    31 |     3   (0)| 00:00:01 |
|* 51 |                INDEX RANGE SCAN                              | PER_PEOPLE_F_PK              |     1 |       |     2   (0)| 00:00:01 |
|  52 |              TABLE ACCESS BY INDEX ROWID BATCHED             | PER_ALL_PEOPLE_F             |     1 |    31 |     3   (0)| 00:00:01 |
|* 53 |               INDEX RANGE SCAN                               | PER_PEOPLE_F_PK              |     1 |       |     2   (0)| 00:00:01 |
|  54 |             TABLE ACCESS BY INDEX ROWID                      | GMD_QC_TESTS_B               |     1 |    31 |     1   (0)| 00:00:01 |
|* 55 |              INDEX UNIQUE SCAN                               | GMD_QC_TESTS_B_PK            |     1 |       |     0   (0)| 00:00:01 |
|* 56 |            INDEX UNIQUE SCAN                                 | GMD_QC_TESTS_TL_PK           |     1 |       |     0   (0)| 00:00:01 |
|  57 |           TABLE ACCESS BY INDEX ROWID                        | GMD_QC_TESTS_TL              |     1 |    26 |     1   (0)| 00:00:01 |
|  58 |          VIEW                                                |                              |     1 |    10 |    20   (0)| 00:00:01 |
|  59 |           NESTED LOOPS                                       |                              |     1 |   251 |    20   (0)| 00:00:01 |
|  60 |            NESTED LOOPS                                      |                              |     1 |   226 |    20   (0)| 00:00:01 |
|  61 |             NESTED LOOPS                                     |                              |     1 |   200 |    20   (0)| 00:00:01 |
|  62 |              MERGE JOIN CARTESIAN                            |                              |     3 |   540 |     9   (0)| 00:00:01 |
|  63 |               NESTED LOOPS                                   |                              |     1 |   150 |     4   (0)| 00:00:01 |
|  64 |                NESTED LOOPS                                  |                              |     1 |   150 |     4   (0)| 00:00:01 |
|  65 |                 NESTED LOOPS                                 |                              |     1 |   120 |     3   (0)| 00:00:01 |
|  66 |                  NESTED LOOPS                                |                              |     1 |    64 |     1   (0)| 00:00:01 |
|* 67 |                   INDEX UNIQUE SCAN                          | FND_ID_FLEX_STRUCTURES_U1    |     1 |    30 |     1   (0)| 00:00:01 |
|* 68 |                   INDEX UNIQUE SCAN                          | FND_ID_FLEX_STRUCTURES_TL_U1 |     1 |    34 |     0   (0)| 00:00:01 |
|* 69 |                  INDEX RANGE SCAN                            | FND_LOOKUP_VALUES_U1         |     1 |    56 |     2   (0)| 00:00:01 |
|* 70 |                 INDEX RANGE SCAN                             | MTL_CATEGORY_SETS_B_N1       |     1 |       |     0   (0)| 00:00:01 |
|* 71 |                TABLE ACCESS BY INDEX ROWID                   | MTL_CATEGORY_SETS_B          |     1 |    30 |     1   (0)| 00:00:01 |
|  72 |               BUFFER SORT                                    |                              |     9 |   270 |     8   (0)| 00:00:01 |
|* 73 |                TABLE ACCESS BY INDEX ROWID BATCHED           | MTL_CATEGORIES_B             |     9 |   270 |     5   (0)| 00:00:01 |
|* 74 |                 INDEX RANGE SCAN                             | MTL_CATEGORIES_B_N1          |    57 |       |     1   (0)| 00:00:01 |
|  75 |              TABLE ACCESS BY INDEX ROWID BATCHED             | MTL_ITEM_CATEGORIES          |     1 |    20 |     7   (0)| 00:00:01 |
|* 76 |               INDEX RANGE SCAN                               | MTL_ITEM_CATEGORIES_N3       |    19 |       |     2   (0)| 00:00:01 |
|* 77 |             INDEX UNIQUE SCAN                                | MTL_CATEGORY_SETS_TL_U1      |     1 |    26 |     0   (0)| 00:00:01 |
|* 78 |            INDEX UNIQUE SCAN                                 | MTL_CATEGORIES_TL_U1         |     1 |    25 |     0   (0)| 00:00:01 |
|* 79 |         INDEX UNIQUE SCAN                                    | GMD_SPEC_TESTS_TL_PK         |     1 |    14 |     1   (0)| 00:00:01 |
|  80 |       JOIN FILTER USE                                        | :BF0000                      |  4965K|    42M| 13909   (2)| 00:00:01 |
|* 81 |        TABLE ACCESS STORAGE FULL                             | GMD_SPEC_RESULTS             |  4965K|    42M| 13909   (2)| 00:00:01 |
|* 82 |      INDEX RANGE SCAN                                        | FND_LOOKUP_VALUES_U1         |     1 |       |     2   (0)| 00:00:01 |
|  83 |     TABLE ACCESS BY INDEX ROWID                              | FND_LOOKUP_VALUES            |     1 |    73 |     3   (0)| 00:00:01 |
---------------------------------------------------------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   1 - filter("T1"."ORGANIZATION_ID_1" IS NOT NULL AND "T1"."INVENTORY_ITEM_ID_1" IS NOT NULL)
   5 - access("GSR"."RESULT_ID"="GR"."RESULT_ID")
   8 - access("T"."ORGANIZATION_ID"(+)="GS"."ORGANIZATION_ID" AND "T"."INVENTORY_ITEM_ID"(+)="GS"."INVENTORY_ITEM_ID")
  22 - access("LOOKUP_CODE"="SSD"."DISPOSITION")
  24 - access("LOOKUP_TYPE"='GMD_QC_SAMPLE_DISP' AND "VIEW_APPLICATION_ID">=550 AND "SECURITY_GROUP_ID"=0 AND
              "LANGUAGE"=USERENV('LANG') AND "ZD_EDITION_NAME"='V_20151118_1137' AND "VIEW_APPLICATION_ID"<=559)
       filter("LANGUAGE"=USERENV('LANG') AND "SECURITY_GROUP_ID"=0 AND "ZD_EDITION_NAME"='V_20151118_1137')
  28 - access("GS"."ORGANIZATION_ID"="ORGANIZATION_ID" AND "GS"."INVENTORY_ITEM_ID"="INVENTORY_ITEM_ID")
  30 - access("ORGANIZATION_ID"=1083)
       filter("ORGANIZATION_ID"=1083)
  31 - filter("GS"."DATE_DRAWN">=TO_DATE(' 2017-01-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND "GS"."LOT_NUMBER" IS NOT NULL AND
              "GS"."INVENTORY_ITEM_ID" IS NOT NULL AND "GS"."SAMPLE_TYPE"='I' AND "GS"."DATE_DRAWN"<=TO_DATE(' 2017-09-11 00:00:00', 'syyyy-mm-dd
              hh24:mi:ss'))
  32 - access("GS"."ORGANIZATION_ID"=1083)
  34 - access("INVENTORY_ITEM_ID"="T"."INVENTORY_ITEM_ID" AND "T"."ORGANIZATION_ID"=1083 AND "T"."LANGUAGE"=USERENV('LANG'))
  35 - access("SSD"."SAMPLE_ID"="GS"."SAMPLE_ID")
  37 - filter("GS"."SAMPLING_EVENT_ID"="GES"."SAMPLING_EVENT_ID" AND "GES"."SPEC_USED_FOR_LOT_ATTRIB_IND"='Y')
  38 - access("GES"."EVENT_SPEC_DISP_ID"="SSD"."EVENT_SPEC_DISP_ID")
  39 - access("GES"."SPEC_ID"="B"."SPEC_ID")
  40 - access("SE"."SAMPLING_EVENT_ID"="GES"."SAMPLING_EVENT_ID")
  42 - access("GS"."SAMPLER_ID"="F"."USER_ID")
  43 - access("B"."SPEC_ID"="T"."SPEC_ID" AND "T"."LANGUAGE"=USERENV('LANG'))
  44 - filter("GR"."TESTER_ID" IS NOT NULL)
  45 - access("GR"."SAMPLE_ID"="GS"."SAMPLE_ID")
  47 - access("GES"."SPEC_ID"="B"."SPEC_ID" AND "GR"."TEST_ID"="B"."TEST_ID")
  49 - access("GR"."TESTER_ID"="F"."USER_ID")
  51 - access("P"."PERSON_ID"="F"."EMPLOYEE_ID" AND "P"."EFFECTIVE_END_DATE">=SYSDATE@! AND "P"."EFFECTIVE_START_DATE"<=SYSDATE@!)
       filter("P"."EFFECTIVE_END_DATE">=SYSDATE@!)
  53 - access("P"."PERSON_ID"="F"."EMPLOYEE_ID" AND "P"."EFFECTIVE_END_DATE">=SYSDATE@! AND "P"."EFFECTIVE_START_DATE"<=SYSDATE@!)
       filter("P"."EFFECTIVE_END_DATE">=SYSDATE@!)
  55 - access("GR"."TEST_ID"="B"."TEST_ID")
  56 - access("B"."TEST_ID"="T"."TEST_ID" AND "T"."LANGUAGE"=USERENV('LANG'))
  67 - access("APPLICATION_ID"=401 AND "ID_FLEX_CODE"='MCAT' AND "ID_FLEX_NUM"=101 AND "ZD_EDITION_NAME"='V_20151118_1137')
  68 - access("APPLICATION_ID"=401 AND "ID_FLEX_CODE"='MCAT' AND "ID_FLEX_NUM"=101 AND "LANGUAGE"=USERENV('LANG') AND
              "ZD_EDITION_NAME"='V_20151118_1137')
  69 - access("LOOKUP_TYPE"='ITEM_CONTROL_LEVEL_GUI' AND "VIEW_APPLICATION_ID"=700 AND "SECURITY_GROUP_ID"=0 AND
              "LANGUAGE"=USERENV('LANG') AND "ZD_EDITION_NAME"='V_20151118_1137')
       filter("LANGUAGE"=USERENV('LANG') AND "SECURITY_GROUP_ID"=0 AND "ZD_EDITION_NAME"='V_20151118_1137')
  70 - access("STRUCTURE_ID"=101)
  71 - filter("CONTROL_LEVEL"=TO_NUMBER("LOOKUP_CODE") AND "ZD_EDITION_NAME"='V_20151118_1137')
  73 - filter("STRUCTURE_ID"=101 AND "ZD_EDITION_NAME"='V_20151118_1137')
  74 - access("SEGMENT1"='15')
  76 - access("MIC"."CATEGORY_SET_ID"="CATEGORY_SET_ID" AND "MIC"."CATEGORY_ID"="CATEGORY_ID" AND "MIC"."ORGANIZATION_ID"=1083)
  77 - access("CATEGORY_SET_ID"="CATEGORY_SET_ID" AND "LANGUAGE"=USERENV('LANG') AND "ZD_EDITION_NAME"='V_20151118_1137')
  78 - access("CATEGORY_ID"="CATEGORY_ID" AND "LANGUAGE"=USERENV('LANG') AND "ZD_EDITION_NAME"='V_20151118_1137')
  79 - access("B"."SPEC_ID"="T"."SPEC_ID" AND "B"."TEST_ID"="T"."TEST_ID" AND "T"."LANGUAGE"=USERENV('LANG'))
  81 - storage("GSR"."EVALUATION_IND" IS NOT NULL AND SYS_OP_BLOOM_FILTER(:BF0000,"GSR"."RESULT_ID"))
       filter("GSR"."EVALUATION_IND" IS NOT NULL AND SYS_OP_BLOOM_FILTER(:BF0000,"GSR"."RESULT_ID"))
  82 - access("LOOKUP_TYPE"='GMD_QC_EVALUATION' AND "VIEW_APPLICATION_ID">=550 AND "LOOKUP_CODE"="GSR"."EVALUATION_IND" AND
              "SECURITY_GROUP_ID"=0 AND "LANGUAGE"=USERENV('LANG') AND "ZD_EDITION_NAME"='V_20151118_1137' AND "VIEW_APPLICATION_ID"<=559)
       filter("LOOKUP_CODE"="GSR"."EVALUATION_IND" AND "LANGUAGE"=USERENV('LANG') AND "SECURITY_GROUP_ID"=0 AND
              "ZD_EDITION_NAME"='V_20151118_1137')


 

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/28380626/viewspace-2148358/,如需转载,请注明出处,否则将追究法律责任。

下一篇: 没有了~
请登录后发表评论 登录
全部评论

注册时间:2013-02-18

  • 博文量
    14
  • 访问量
    68357