7 Replies Latest reply: Dec 23, 2012 10:43 AM by EdStevens RSS

    sql query is hung, doesn't comes back

    981047
      Hello Every one,

      Two node Rac on Linux 5 64-bit, 11.2.0.1.0

      In our data ware housing environment, i have a sql query which doesn't comes back at all, when i run, it is just hung for a long time with no output. I did following basic testing

      1. All the indexes are valid and stats are up to date(count(*) matches num rows)
      2. There is a union clause in the query, i divided the query in two parts - the first part prior to union and second part after union, then i ran those two parts separately - the first part comes back quickly with no rows selected but the second part has a main problem(its just hung).

      Please note that one table in the query has a partitioned table. following is the query and the tables involved in it.

      Please let me know if you need any further information, it will be of great help if i get through this. Thanks in advance.
      OWNER                          TABLE_NAME                     STATUS   LAST_ANAL   NUM_ROWS
      ------------------------------ ------------------------------ -------- --------- ----------
      MAINTENANCE_DM                 D_VEHICLE                      VALID    21-NOV-12     752590
      PTL_HOLD                       R_COMP_CODES                   VALID    21-NOV-12        108
      PTL_HOLD                       USER_GEO                       VALID    20-DEC-12        300
      PTL_EDW                        D_CUSTOMER                     VALID    20-DEC-12     219498
      PTL_EDW                        D_LOCN                         VALID    20-DEC-12      19456
      PTL_EDW                        D_VEHICLE                      VALID    20-DEC-12    3973271
      PTL_EDW                        F_VEH_LOCN                     VALID    20-DEC-12    3131138
      PTL_EDW                        F_RO_DETL                      VALID    21-NOV-12   29234052
      
      8 rows selected.
      
      (
      SELECT
        '../../opendoc/',
        '&hideSave=true&hideEdit=true&hideRefresh=true&hideUserPromptInput=true',
        'Data As Of:',
        'Run Date:',
        ptl_hold.get_stgextr_time('FACT_FPNA_RO'),
        'RO Date',
        PTL_EDW.F_RO_DETL.RO_TYP,
        PTL_EDW.D_CUSTOMER.CUST_NUM,
        (PTL_EDW.D_CUSTOMER.CUST_NUM )||' - '||( PTL_EDW.D_CUSTOMER.CUST_PRIME_NM),
        PTL_EDW.D_CUSTOMER.BILL_GRP,
        CASE WHEN PTL_EDW.D_LOCN.CORP_CD='HPTL' THEN 'US' ELSE CASE WHEN  PTL_EDW.D_LOCN.CORP_CD='2000' THEN 'CANADA' ELSE 'UNKNOWN' END END,
        PTL_EDW.D_LOCN.REGN_NUM,
        PTL_EDW.D_LOCN.REGN_NUM ||' - '||PTL_EDW.D_LOCN.REGN_NM,
        PTL_EDW.D_LOCN.AREA_NUM,
        PTL_EDW.D_LOCN.AREA_NUM||' - '||PTL_EDW.D_LOCN.AREA_NM,
        PTL_EDW.D_LOCN.RDIST_NUM,
        PTL_EDW.D_LOCN.RDIST_NUM||' - '||PTL_EDW.D_LOCN.RDIST_NM,
        PTL_EDW.D_LOCN.DIST_NUM,
        PTL_EDW.D_LOCN.DIST_NUM||' - '||PTL_EDW.D_LOCN.DIST_NM,
        CASE WHEN TRIM(PTL_EDW.D_VEHICLE.VEH_CAP_STAT) IN ('ACTIVE','ACCRUED') THEN 'Active' ELSE 'Inactive' END,
        DECODE(PTL_EDW.D_VEHICLE.CONTR_GRP, 'X' ,'NON-CONTRACT',PTL_EDW.D_VEHICLE.VEH_CAT),
        PTL_EDW.D_VEHICLE.VEH_UNIT_TYP,
        PTL_EDW.D_VEHICLE.VEH_MODL_YR,
        PTL_EDW.D_VEHICLE.VEH_MAKE,
        PTL_EDW.D_VEHICLE.VEH_MODEL,
        CASE WHEN ( PTL_HOLD.R_COMP_CODES.COMP_CD ) IN ('057','061') THEN 'Accident/Incident' ELSE 'Non-Accident/Incident' END,
        PTL_HOLD.R_COMP_CODES.COMP_CD,
        PTL_HOLD.R_COMP_CODES.COMP_DESC,
        PTL_HOLD.R_COMP_CODES.COMP_CD||' - '||PTL_HOLD.R_COMP_CODES.COMP_DESC,
        PTL_EDW.D_VEHICLE.UNIT_NUM,
        PTL_EDW_D_RO_LOCN.DIST_NUM || PTL_EDW_D_RO_LOCN.DIST_SUFX ||  PTL_EDW.F_RO_DETL.ORIGINAL_RO_NUM,
        PTL_EDW_D_RO_LOCN.DIST_NUM || PTL_EDW_D_RO_LOCN.DIST_SUFX || PTL_EDW.F_RO_DETL.ORIGINAL_RO_NUM || PTL_EDW.F_RO_DETL.RO_SEQ_NUM
      ,
        PTL_EDW.F_RO_DETL.RO_SRC,
        PTL_EDW.F_RO_DETL.TOTAL_LABOR_HRS,
        PTL_EDW.F_RO_DETL.TOTAL_LABOR_COST,
        PTL_EDW.F_RO_DETL.OUT_COST,
        PTL_EDW.F_RO_DETL.PARTS_COST,
        PTL_EDW.F_RO_DETL.RO_COST,
        TO_CHAR(PTL_EDW_D_RO_LOCN.LOCN_KEY),
        PTL_EDW.F_RO_DETL.RO_NUM,
        PTL_EDW.F_RO_DETL.RO_SEQ_NUM,
        PTL_EDW.F_RO_DETL.ORIGINAL_RO_NUM
      FROM
        PTL_EDW.F_RO_DETL,
        PTL_EDW.D_CUSTOMER,
        PTL_EDW.D_LOCN,
        PTL_EDW.D_VEHICLE,
        PTL_HOLD.R_COMP_CODES,
        PTL_EDW.D_LOCN  PTL_EDW_D_RO_LOCN,
        PTL_EDW.D_LOCN  PTL_EDW_D_CHRG_LOCN,
        PTL_EDW.D_TIME,
        PTL_EDW.D_LOCN  CURRENT_VEH_LOCN,
        PTL_EDW.F_VEH_LOCN,
        PTL_EDW.D_VEHICLE  CURRENT_VEH
      WHERE
        ( PTL_EDW.F_RO_DETL.CUST_KEY=PTL_EDW.D_CUSTOMER.CUST_KEY  )
        AND  ( PTL_EDW.F_RO_DETL.LOCN_KEY=PTL_EDW.D_LOCN.LOCN_KEY  )
        AND  ( PTL_EDW.D_TIME.TIME_KEY=PTL_EDW.F_RO_DETL.RO_TIME_KEY  )
        AND  ( PTL_EDW.D_VEHICLE.VEH_KEY=PTL_EDW.F_RO_DETL.VEH_KEY  )
        AND  ( PTL_EDW_D_RO_LOCN.LOCN_KEY=PTL_EDW.F_RO_DETL.RO_LOCN_KEY  )
        AND  ( PTL_EDW.F_RO_DETL.CHRG_LOCN_KEY=PTL_EDW_D_CHRG_LOCN.LOCN_KEY  )
        AND  ( CURRENT_VEH_LOCN.LOCN_KEY=PTL_EDW.F_VEH_LOCN.LOCN_KEY  )
        AND  ( CURRENT_VEH.SCD_FLG='A'  )
        AND  ( CURRENT_VEH.CONTR_GRP='P'  )
        AND  ( CURRENT_VEH.CORP_CD=PTL_EDW.D_VEHICLE.CORP_CD and CURRENT_VEH.UNIT_NUM=PTL_EDW.D_VEHICLE.UNIT_NUM  )
        AND  ( CURRENT_VEH.VEH_KEY=PTL_EDW.F_VEH_LOCN.VEH_KEY  )
        AND  ( PTL_HOLD.R_COMP_CODES.COMP_CD=PTL_EDW.F_RO_DETL.COMP_CD  )
        AND  
        (
         ( 'Accounting Month' = 'RO Date'  )
         AND
         ( '2500' = 'NO-MIN'
      OR    EXISTS(SELECT RO_TOTAL_COST.RO_LOCN_KEY, RO_TOTAL_COST.ORIGINAL_RO_NUM  
      FROM      PTL_EDW.F_RO_DETL RO_TOTAL_COST,
          PTL_EDW.D_TIME DTIME
      WHERE 
          ('I' = 'I'
          OR ('I' = 'E'
              AND RO_TOTAL_COST.RO_TYP  <>  'W'))
          AND RO_TOTAL_COST.RO_LOCN_KEY = PTL_EDW.F_RO_DETL.RO_LOCN_KEY 
          AND RO_TOTAL_COST.ORIGINAL_RO_NUM = PTL_EDW.F_RO_DETL.ORIGINAL_RO_NUM
          AND RO_TOTAL_COST.RO_TIME_KEY = DTIME.TIME_KEY
          AND DTIME.CALENDAR_DT BETWEEN to_date('2011-JAN-01') AND to_date('2012-SEP-24')
      GROUP BY
          RO_TOTAL_COST.RO_LOCN_KEY,
          RO_TOTAL_COST.ORIGINAL_RO_NUM
      HAVING 
      Sum(RO_TOTAL_COST.RO_COST) >= TO_NUMBER(translate('2500','NO-MIN',' ')))
        )
         AND
         PTL_EDW_D_CHRG_LOCN.DIST_NUM_SUFX  IN  ('014810')
         AND
         ( PTL_EDW.D_TIME.CALENDAR_DT  BETWEEN  to_date('2011-JAN-01') AND to_date('2012-SEP-24')  )
         AND
         ( (
         ( 'I'  = 'I' 
         )
        OR
        ( 'I' = 'E' 
           And ( ( PTL_EDW.F_RO_DETL.RO_TYP ) <> 'W'
                  )
        )   
      )  )
         AND
         ( EXISTS(SELECT *  FROM PTL_HOLD.USER_GEO WHERE USERID='600125729' AND PTL_HOLD.USER_GEO.GEO IN (CURRENT_VEH_LOCN.CORP_CD, CURRENT_VEH_LOCN.REGN_NUM, CURRENT_VEH_LOCN.AREA_NUM, CURRENT_VEH_LOCN.RDIST_NUM, CURRENT_VEH_LOCN.DIST_NUM))  )
        )
      UNION  
      SELECT
        '../../opendoc/',
        '&hideSave=true&hideEdit=true&hideRefresh=true&hideUserPromptInput=true',
        'Data As Of:',
        'Run Date:',
        ptl_hold.get_stgextr_time('FACT_FPNA_RO'),
        'Accounting Month',
        PTL_EDW.F_RO_DETL.RO_TYP,
        PTL_EDW.D_CUSTOMER.CUST_NUM,
        (PTL_EDW.D_CUSTOMER.CUST_NUM )||' - '||( PTL_EDW.D_CUSTOMER.CUST_PRIME_NM),
        PTL_EDW.D_CUSTOMER.BILL_GRP,
        CASE WHEN PTL_EDW.D_LOCN.CORP_CD='HPTL' THEN 'US' ELSE CASE WHEN  PTL_EDW.D_LOCN.CORP_CD='2000' THEN 'CANADA' ELSE 'UNKNOWN' END END,
        PTL_EDW.D_LOCN.REGN_NUM,
        PTL_EDW.D_LOCN.REGN_NUM ||' - '||PTL_EDW.D_LOCN.REGN_NM,
        PTL_EDW.D_LOCN.AREA_NUM,
        PTL_EDW.D_LOCN.AREA_NUM||' - '||PTL_EDW.D_LOCN.AREA_NM,
        PTL_EDW.D_LOCN.RDIST_NUM,
        PTL_EDW.D_LOCN.RDIST_NUM||' - '||PTL_EDW.D_LOCN.RDIST_NM,
        PTL_EDW.D_LOCN.DIST_NUM,
        PTL_EDW.D_LOCN.DIST_NUM||' - '||PTL_EDW.D_LOCN.DIST_NM,
        CASE WHEN TRIM(PTL_EDW.D_VEHICLE.VEH_CAP_STAT) IN ('ACTIVE','ACCRUED') THEN 'Active' ELSE 'Inactive' END,
        DECODE(PTL_EDW.D_VEHICLE.CONTR_GRP, 'X' ,'NON-CONTRACT',PTL_EDW.D_VEHICLE.VEH_CAT),
        PTL_EDW.D_VEHICLE.VEH_UNIT_TYP,
        PTL_EDW.D_VEHICLE.VEH_MODL_YR,
        PTL_EDW.D_VEHICLE.VEH_MAKE,
        PTL_EDW.D_VEHICLE.VEH_MODEL,
        CASE WHEN ( PTL_HOLD.R_COMP_CODES.COMP_CD ) IN ('057','061') THEN 'Accident/Incident' ELSE 'Non-Accident/Incident' END,
        PTL_HOLD.R_COMP_CODES.COMP_CD,
        PTL_HOLD.R_COMP_CODES.COMP_DESC,
        PTL_HOLD.R_COMP_CODES.COMP_CD||' - '||PTL_HOLD.R_COMP_CODES.COMP_DESC,
        PTL_EDW.D_VEHICLE.UNIT_NUM,
        PTL_EDW_D_RO_LOCN.DIST_NUM || PTL_EDW_D_RO_LOCN.DIST_SUFX ||  PTL_EDW.F_RO_DETL.ORIGINAL_RO_NUM,
        PTL_EDW_D_RO_LOCN.DIST_NUM || PTL_EDW_D_RO_LOCN.DIST_SUFX || PTL_EDW.F_RO_DETL.ORIGINAL_RO_NUM || PTL_EDW.F_RO_DETL.RO_SEQ_NUM
      ,
        PTL_EDW.F_RO_DETL.RO_SRC,
        PTL_EDW.F_RO_DETL.TOTAL_LABOR_HRS,
        PTL_EDW.F_RO_DETL.TOTAL_LABOR_COST,
        PTL_EDW.F_RO_DETL.OUT_COST,
        PTL_EDW.F_RO_DETL.PARTS_COST,
        PTL_EDW.F_RO_DETL.RO_COST,
        TO_CHAR(PTL_EDW_D_RO_LOCN.LOCN_KEY),
        PTL_EDW.F_RO_DETL.RO_NUM,
        PTL_EDW.F_RO_DETL.RO_SEQ_NUM,
        PTL_EDW.F_RO_DETL.ORIGINAL_RO_NUM
      FROM
        PTL_EDW.F_RO_DETL,
        PTL_EDW.D_CUSTOMER,
        PTL_EDW.D_LOCN,
        PTL_EDW.D_VEHICLE,
        PTL_HOLD.R_COMP_CODES,
        PTL_EDW.D_LOCN  PTL_EDW_D_RO_LOCN,
        PTL_EDW.D_LOCN  PTL_EDW_D_CHRG_LOCN,
        PTL_EDW.D_LOCN  CURRENT_VEH_LOCN,
        PTL_EDW.F_VEH_LOCN,
        PTL_EDW.D_VEHICLE  CURRENT_VEH
      WHERE
        ( PTL_EDW.F_RO_DETL.CUST_KEY=PTL_EDW.D_CUSTOMER.CUST_KEY  )
        AND  ( PTL_EDW.F_RO_DETL.LOCN_KEY=PTL_EDW.D_LOCN.LOCN_KEY  )
        AND  ( PTL_EDW.D_VEHICLE.VEH_KEY=PTL_EDW.F_RO_DETL.VEH_KEY  )
        AND  ( PTL_EDW_D_RO_LOCN.LOCN_KEY=PTL_EDW.F_RO_DETL.RO_LOCN_KEY  )
        AND  ( PTL_EDW.F_RO_DETL.CHRG_LOCN_KEY=PTL_EDW_D_CHRG_LOCN.LOCN_KEY  )
        AND  ( CURRENT_VEH_LOCN.LOCN_KEY=PTL_EDW.F_VEH_LOCN.LOCN_KEY  )
        AND  ( CURRENT_VEH.SCD_FLG='A'  )
        AND  ( CURRENT_VEH.CONTR_GRP='P'  )
        AND  ( CURRENT_VEH.CORP_CD=PTL_EDW.D_VEHICLE.CORP_CD and CURRENT_VEH.UNIT_NUM=PTL_EDW.D_VEHICLE.UNIT_NUM  )
        AND  ( CURRENT_VEH.VEH_KEY=PTL_EDW.F_VEH_LOCN.VEH_KEY  )
        AND  ( PTL_HOLD.R_COMP_CODES.COMP_CD=PTL_EDW.F_RO_DETL.COMP_CD  )
        AND  
        (
         ( 'Accounting Month' = 'Accounting Month'  )
         AND
         ( '2500' = 'NO-MIN'
      OR           EXISTS(SELECT RO_TOTAL_COST.RO_LOCN_KEY, RO_TOTAL_COST.ORIGINAL_RO_NUM
      FROM      PTL_EDW.F_RO_DETL RO_TOTAL_COST
      WHERE 
          ('I' = 'I'
          OR ('I' = 'E'
              AND RO_TOTAL_COST.RO_TYP  <>  'W'))
          AND RO_TOTAL_COST.RO_LOCN_KEY = PTL_EDW.F_RO_DETL.RO_LOCN_KEY 
          AND RO_TOTAL_COST.ORIGINAL_RO_NUM = PTL_EDW.F_RO_DETL.ORIGINAL_RO_NUM
          AND RO_TOTAL_COST.PARTITION_KEY BETWEEN  
             to_number(to_char(to_date('2011-JAN-01','YYYY-MON-DD'),'yyyymm'))  
             AND  to_number(to_char(to_date('2012-SEP-24','YYYY-MON-DD'),'yyyymm'))
      GROUP BY
          RO_TOTAL_COST.RO_LOCN_KEY,
          RO_TOTAL_COST.ORIGINAL_RO_NUM
      HAVING 
      Sum(RO_TOTAL_COST.RO_COST) >= TO_NUMBER(translate('2500','NO-MIN',' ')))  )
         AND
         PTL_EDW_D_CHRG_LOCN.DIST_NUM_SUFX  IN  ('014810')
         AND
         ( (PTL_EDW.F_RO_DETL.PARTITION_KEY BETWEEN  
             to_number(to_char(to_date('2011-JAN-01','YYYY-MON-DD'),'yyyymm'))  
             AND  to_number(to_char(to_date('2012-SEP-24','YYYY-MON-DD'),'yyyymm'))
      )
        )
         AND
         ( (
         ( 'I'  = 'I' 
         )
        OR
        ( 'I' = 'E' 
           And ( ( PTL_EDW.F_RO_DETL.RO_TYP ) <> 'W'
                  )
        )   
      )  )
         AND
         ( EXISTS(SELECT *  FROM PTL_HOLD.USER_GEO WHERE USERID='600125729' AND PTL_HOLD.USER_GEO.GEO IN (CURRENT_VEH_LOCN.CORP_CD, CURRENT_VEH_LOCN.REGN_NUM, CURRENT_VEH_LOCN.AREA_NUM, CURRENT_VEH_LOCN.RDIST_NUM, CURRENT_VEH_LOCN.DIST_NUM))  )
        )
      );
      Thank you.
        • 1. Re: sql query is hung, doesn't comes back
          Hoek
          Welcome to the forum.

          First of all, it is important to read these two SQL and PL/SQL FAQ's:
          {message:id=9360002}
          {message:id=9360003}
          (both links contain valuable tips and templates for your current and future questions, so they are well worth the effort of reading and understanding)

          Regarding your question:
          2. There is a union clause in the query, i divided the query in two parts - the first part prior to union and second part after union, then i ran those two parts separately - the first part comes back quickly with no rows selected but the second part has a main problem(its just hung).
          Can you or your DBA provide the execution plans of the queries separatly and UNIONed? Or, even better: a tkprof report with wait events?
          That way we know the amount of data the Optimizer expects to process, whether partitions are pruned, which indexes are used and so on.
          Secondly, I was wondering if using UNION ALL instead of UNION would make any difference, but since your second query appears to 'get stuck', I don't think it will make any difference.
          You might want to rebuild the second query, start off with the 'base table' and add the rest of the tables and columns one-by-one until you get to the point the querty doesn't respond anymore. Take 'small baby steps' or 'dismantle and rebuild', so to speak.
          • 2. Re: sql query is hung, doesn't comes back
            981047
            Thanks for the reply.

            This query is generated by BO tool and i don't think we can make a lot changes to this, infact trying to find the root cause and see what best we can do to get the output. Also, union all doesn't meet there business requirement so we will have to stick with union.

            Following are the explain plan's you requested and i am working on to generate tkproof output. Please let me know if you need any further details.

            *UNIONED EXPLAIN PLAN --*
            PLAN_TABLE_OUTPUT
            --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
            Plan hash value: 3585472528
            
            -----------------------------------------------------------------------------------------------------------------------------------------------------
            | Id  | Operation                                        | Name                     | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     | Pstart| Pstop |
            -----------------------------------------------------------------------------------------------------------------------------------------------------
            |   0 | SELECT STATEMENT                                 |                          |     2 |   685 |       | 71337 (100)| 00:16:39 |    |        |
            |   1 |  SORT UNIQUE                                     |                          |     2 |   685 |       | 71337 (100)| 00:16:39 |    |        |
            |   2 |   UNION-ALL                                      |                          |       |       |       |            |          |    |        |
            |*  3 |    FILTER                                        |                          |       |       |       |            |          |    |        |
            |*  4 |     FILTER                                       |                          |       |       |       |            |          |    |        |
            |*  5 |      HASH JOIN                                   |                          |    61 | 20496 |       |   902   (1)| 00:00:13 |    |        |
            |   6 |       NESTED LOOPS                               |                          |    61 | 18666 |       |   782   (1)| 00:00:11 |    |        |
            |*  7 |        HASH JOIN                                 |                          |    61 | 18056 |       |   660   (2)| 00:00:10 |    |        |
            |*  8 |         HASH JOIN                                |                          |    61 | 17263 |       |   579   (1)| 00:00:09 |    |        |
            |   9 |          NESTED LOOPS                            |                          |       |       |       |            |          |    |        |
            |  10 |           NESTED LOOPS                           |                          |    61 | 13298 |       |   458   (1)| 00:00:07 |    |        |
            |  11 |            NESTED LOOPS                          |                          |    71 | 13845 |       |   245   (1)| 00:00:04 |    |        |
            |  12 |             NESTED LOOPS                         |                          |    71 |  9372 |       |   103   (1)| 00:00:02 |    |        |
            |* 13 |              HASH JOIN                           |                          |    71 |  6674 |       |    32   (4)| 00:00:01 |    |        |
            |  14 |               PARTITION RANGE ALL                |                          |    71 |  5403 |       |    28   (0)| 00:00:01 |  1 |     38 |
            |  15 |                TABLE ACCESS BY LOCAL INDEX ROWID | F_RO_DETL                |    71 |  5403 |       |    28   (0)| 00:00:01 |  1 |     38 |
            |  16 |                 BITMAP CONVERSION TO ROWIDS      |                          |       |       |       |            |          |    |        |
            |  17 |                  BITMAP AND                      |                          |       |       |       |            |          |    |        |
            |  18 |                   BITMAP MERGE                   |                          |       |       |       |            |          |    |        |
            |  19 |                    BITMAP KEY ITERATION          |                          |       |       |       |            |          |    |        |
            |  20 |                     BUFFER SORT                  |                          |       |       |       |            |          |    |        |
            |  21 |                      TABLE ACCESS BY INDEX ROWID | D_LOCN                   |     1 |    12 |       |     2   (0)| 00:00:01 |    |        |
            |* 22 |                       INDEX RANGE SCAN           | D_LOCN_DIM_KEY4          |     1 |       |       |     1   (0)| 00:00:01 |    |        |
            |* 23 |                     BITMAP INDEX RANGE SCAN      | BIMAP_FRODETL_CHRGLOCKEY |       |       |       |            |          |  1 |     38 |
            |  24 |                   BITMAP MERGE                   |                          |       |       |       |            |          |    |        |
            |  25 |                    BITMAP KEY ITERATION          |                          |       |       |       |            |          |    |        |
            |  26 |                     BUFFER SORT                  |                          |       |       |       |            |          |    |        |
            |* 27 |                      FILTER                      |                          |       |       |       |            |          |    |        |
            |  28 |                       TABLE ACCESS BY INDEX ROWID| D_TIME                   |    24 |   528 |       |     3   (0)| 00:00:01 |    |        |
            |* 29 |                        INDEX RANGE SCAN          | D_TIME_UQIND2_CALDT      |    43 |       |       |     2   (0)| 00:00:01 |    |        |
            |* 30 |                     BITMAP INDEX RANGE SCAN      | BIMAP_FRODETL_TIMKEY     |       |       |       |            |          |  1 |     38 |
            |  31 |               TABLE ACCESS FULL                  | R_COMP_CODES             |   108 |  1944 |       |     3   (0)| 00:00:01 |    |        |
            |  32 |              TABLE ACCESS BY INDEX ROWID         | D_CUSTOMER               |     1 |    38 |       |     1   (0)| 00:00:01 |    |        |
            |* 33 |               INDEX UNIQUE SCAN                  | CUST_DIM_KEY_UQIND1      |     1 |       |       |     0   (0)| 00:00:01 |    |        |
            |  34 |             TABLE ACCESS BY INDEX ROWID          | D_VEHICLE                |     1 |    63 |       |     2   (0)| 00:00:01 |    |        |
            |* 35 |              INDEX UNIQUE SCAN                   | VEHCILE_PK               |     1 |       |       |     1   (0)| 00:00:01 |    |        |
            |* 36 |            INDEX RANGE SCAN                      | VEH_DIM_KEY_IND1         |     1 |       |       |     2   (0)| 00:00:01 |    |        |
            |* 37 |           TABLE ACCESS BY INDEX ROWID            | D_VEHICLE                |     1 |    23 |       |     3   (0)| 00:00:01 |    |        |
            |  38 |          TABLE ACCESS FULL                       | D_LOCN                   | 19456 |  1235K|       |   119   (1)| 00:00:02 |    |        |
            |  39 |         VIEW                                     | index$_join$_006         | 19456 |   247K|       |    80   (3)| 00:00:02 |    |        |
            |* 40 |          HASH JOIN                               |                          |       |       |       |            |          |    |        |
            |  41 |           INDEX FAST FULL SCAN                   | LOCN_PK                  | 19456 |   247K|       |    38   (0)| 00:00:01 |    |        |
            |  42 |           INDEX FAST FULL SCAN                   | LOCN_DIM_KEY1            | 19456 |   247K|       |    61   (2)| 00:00:01 |    |        |
            |* 43 |        INDEX RANGE SCAN                          | F_VEH_LOCN_IND3          |     1 |    10 |       |     2   (0)| 00:00:01 |    |        |
            |  44 |       TABLE ACCESS FULL                          | D_LOCN                   | 19456 |   570K|       |   119   (1)| 00:00:02 |    |        |
            |* 45 |     FILTER                                       |                          |       |       |       |            |          |    |        |
            |  46 |      SORT GROUP BY NOSORT                        |                          |     1 |    36 |       |  1855   (1)| 00:00:26 |    |        |
            |* 47 |       FILTER                                     |                          |       |       |       |            |          |    |        |
            |  48 |        NESTED LOOPS                              |                          |       |       |       |            |          |    |        |
            |  49 |         NESTED LOOPS                             |                          |     1 |    36 |       |  1855   (1)| 00:00:26 |    |        |
            |  50 |          TABLE ACCESS BY INDEX ROWID             | D_TIME                   |    24 |   312 |       |     3   (0)| 00:00:01 |    |        |
            |* 51 |           INDEX RANGE SCAN                       | D_TIME_UQIND2_CALDT      |    43 |       |       |     2   (0)| 00:00:01 |    |        |
            |  52 |          PARTITION RANGE ALL                     |                          |       |       |       |            |          |  1 |     38 |
            |  53 |           BITMAP CONVERSION TO ROWIDS            |                          |       |       |       |            |          |    |        |
            |  54 |            BITMAP AND                            |                          |       |       |       |            |          |    |        |
            |* 55 |             BITMAP INDEX SINGLE VALUE            | BIMAP_FRODETL_TIMKEY     |       |       |       |            |          |  1 |     38 |
            |* 56 |             BITMAP INDEX SINGLE VALUE            | BIMAP_FRODETL_ROLOCKEY   |       |       |       |            |          |  1 |     38 |
            |* 57 |         TABLE ACCESS BY LOCAL INDEX ROWID        | F_RO_DETL                |     1 |    23 |       |  1855   (1)| 00:00:26 |  1 |      1 |
            |* 58 |     INDEX RANGE SCAN                             | I2_USERGEO               |     1 |    15 |       |     1   (0)| 00:00:01 |    |        |
            |* 59 |    FILTER                                        |                          |       |       |       |            |          |    |        |
            |* 60 |     HASH JOIN                                    |                          | 15092 |  5143K|       | 54570   (3)| 00:12:44 |    |        |
            |  61 |      TABLE ACCESS FULL                           | D_LOCN                   | 19456 |   570K|       |   119   (1)| 00:00:02 |    |        |
            |* 62 |      HASH JOIN                                   |                          | 15092 |  4701K|       | 54450   (3)| 00:12:43 |    |        |
            |  63 |       TABLE ACCESS FULL                          | D_LOCN                   | 19456 |  1235K|       |   119   (1)| 00:00:02 |    |        |
            |* 64 |       HASH JOIN                                  |                          | 15092 |  3743K|  3376K| 54329   (3)| 00:12:41 |    |        |
            |* 65 |        HASH JOIN                                 |                          | 15092 |  3183K|  3216K| 52903   (3)| 00:12:21 |    |        |
            |* 66 |         HASH JOIN                                |                          | 15092 |  3036K|  3376K| 47363   (3)| 00:11:04 |    |        |
            |* 67 |          HASH JOIN                               |                          | 17676 |  3158K|       | 24439   (2)| 00:05:43 |    |        |
            |  68 |           TABLE ACCESS FULL                      | R_COMP_CODES             |   108 |  1944 |       |     3   (0)| 00:00:01 |    |        |
            |* 69 |           HASH JOIN                              |                          | 17692 |  2850K|       | 24435   (2)| 00:05:43 |    |        |
            |* 70 |            HASH JOIN                             |                          | 17692 |  1762K|       |  3709   (2)| 00:00:52 |    |        |
            |  71 |             VIEW                                 | index$_join$_020         | 19456 |   247K|       |    80   (3)| 00:00:02 |    |        |
            |* 72 |              HASH JOIN                           |                          |       |       |       |            |          |    |        |
            |  73 |               INDEX FAST FULL SCAN               | LOCN_PK                  | 19456 |   247K|       |    38   (0)| 00:00:01 |    |        |
            |  74 |               INDEX FAST FULL SCAN               | LOCN_DIM_KEY1            | 19456 |   247K|       |    61   (2)| 00:00:01 |    |        |
            |  75 |             NESTED LOOPS                         |                          |       |       |       |            |          |    |        |
            |  76 |              NESTED LOOPS                        |                          | 17692 |  1537K|       |  3628   (2)| 00:00:51 |    |        |
            |  77 |               TABLE ACCESS BY INDEX ROWID        | D_LOCN                   |     1 |    12 |       |     2   (0)| 00:00:01 |    |        |
            |* 78 |                INDEX RANGE SCAN                  | D_LOCN_DIM_KEY4          |     1 |       |       |     1   (0)| 00:00:01 |    |        |
            |  79 |               PARTITION RANGE ITERATOR           |                          |       |       |       |            |          | 14 |     34 |
            |  80 |                BITMAP CONVERSION TO ROWIDS       |                          |       |       |       |            |          |    |        |
            |  81 |                 BITMAP AND                       |                          |       |       |       |            |          |    |        |
            |* 82 |                  BITMAP INDEX SINGLE VALUE       | BIMAP_FRODETL_CHRGLOCKEY |       |       |       |            |          | 14 |     34 |
            |  83 |                  BITMAP MERGE                    |                          |       |       |       |            |          |    |        |
            |* 84 |                   BITMAP INDEX RANGE SCAN        | BIMAP_FRODETL_PARTKEY    |       |       |       |            |          | 14 |     34 |
            |  85 |              TABLE ACCESS BY LOCAL INDEX ROWID   | F_RO_DETL                | 16980 |  1276K|       |  3628   (2)| 00:00:51 |  1 |      1 |
            |  86 |            TABLE ACCESS FULL                     | D_VEHICLE                |  3973K|   238M|       | 20670   (2)| 00:04:50 |    |        |
            |* 87 |          TABLE ACCESS FULL                       | D_VEHICLE                |  1289K|    28M|       | 20906   (3)| 00:04:53 |    |        |
            |  88 |         INDEX FAST FULL SCAN                     | F_VEH_LOCN_IND3          |  3131K|    29M|       |  2509   (3)| 00:00:36 |    |        |
            |  89 |        TABLE ACCESS FULL                         | D_CUSTOMER               |   219K|  8145K|       |   831   (2)| 00:00:12 |    |        |
            |* 90 |     FILTER                                       |                          |       |       |       |            |          |    |        |
            |  91 |      SORT GROUP BY NOSORT                        |                          |     1 |    24 |       |  4174   (2)| 00:00:59 |    |        |
            |  92 |       PARTITION RANGE ITERATOR                   |                          |     1 |    24 |       |  4174   (2)| 00:00:59 | 14 |     34 |
            |* 93 |        TABLE ACCESS BY LOCAL INDEX ROWID         | F_RO_DETL                |     1 |    24 |       |  4174   (2)| 00:00:59 | 14 |     34 |
            |  94 |         BITMAP CONVERSION TO ROWIDS              |                          |       |       |       |            |          |    |        |
            |  95 |          BITMAP AND                              |                          |       |       |       |            |          |    |        |
            |* 96 |           BITMAP INDEX SINGLE VALUE              | BIMAP_FRODETL_ROLOCKEY   |       |       |       |            |          | 14 |     34 |
            |  97 |           BITMAP MERGE                           |                          |       |       |       |            |          |    |        |
            |* 98 |            BITMAP INDEX RANGE SCAN               | BIMAP_FRODETL_PARTKEY    |       |       |       |            |          | 14 |     34 |
            |* 99 |     INDEX RANGE SCAN                             | I2_USERGEO               |     1 |    15 |       |     1   (0)| 00:00:01 |    |        |
            -----------------------------------------------------------------------------------------------------------------------------------------------------
            
            Predicate Information (identified by operation id):
            ---------------------------------------------------
            
               3 - filter( EXISTS (SELECT 0 FROM "PTL_EDW"."D_TIME" "DTIME","PTL_EDW"."F_RO_DETL" "RO_TOTAL_COST" WHERE
                          TO_DATE('2011-JAN-01')<=TO_DATE('2012-SEP-24') AND "RO_TOTAL_COST"."RO_TIME_KEY"="DTIME"."TIME_KEY" AND "RO_TOTAL_COST"."RO_LOCN_KEY"=:B1
                          AND "RO_TOTAL_COST"."ORIGINAL_RO_NUM"=:B2 AND "DTIME"."CALENDAR_DT"<=TO_DATE('2012-SEP-24') AND
                          "DTIME"."CALENDAR_DT">=TO_DATE('2011-JAN-01') GROUP BY "RO_TOTAL_COST"."RO_LOCN_KEY","RO_TOTAL_COST"."ORIGINAL_RO_NUM" HAVING
                          SUM("RO_TOTAL_COST"."RO_COST")>=2500) AND  EXISTS (SELECT 0 FROM "PTL_HOLD"."USER_GEO" "USER_GEO" WHERE "USERID"='600125729' AND
                          ("USER_GEO"."GEO"=:B3 OR "USER_GEO"."GEO"=:B4 OR "USER_GEO"."GEO"=:B5 OR "USER_GEO"."GEO"=:B6 OR "USER_GEO"."GEO"=:B7)))
               4 - filter(NULL IS NOT NULL)
               5 - access("CURRENT_VEH_LOCN"."LOCN_KEY"="F_VEH_LOCN"."LOCN_KEY")
               7 - access("PTL_EDW_D_RO_LOCN"."LOCN_KEY"="F_RO_DETL"."RO_LOCN_KEY")
               8 - access("F_RO_DETL"."LOCN_KEY"="D_LOCN"."LOCN_KEY")
              13 - access("R_COMP_CODES"."COMP_CD"="F_RO_DETL"."COMP_CD")
              22 - access("PTL_EDW_D_CHRG_LOCN"."DIST_NUM_SUFX"='014810')
              23 - access("F_RO_DETL"."CHRG_LOCN_KEY"="PTL_EDW_D_CHRG_LOCN"."LOCN_KEY")
              27 - filter(TO_DATE('2012-SEP-24')>=TO_DATE('2011-JAN-01'))
              29 - access("D_TIME"."CALENDAR_DT">=TO_DATE('2011-JAN-01') AND "D_TIME"."CALENDAR_DT"<=TO_DATE('2012-SEP-24'))
              30 - access("F_RO_DETL"."RO_TIME_KEY"="D_TIME"."TIME_KEY")
              33 - access("F_RO_DETL"."CUST_KEY"="D_CUSTOMER"."CUST_KEY")
              35 - access("D_VEHICLE"."VEH_KEY"="F_RO_DETL"."VEH_KEY")
              36 - access("CURRENT_VEH"."CORP_CD"="D_VEHICLE"."CORP_CD" AND "CURRENT_VEH"."UNIT_NUM"="D_VEHICLE"."UNIT_NUM")
              37 - filter("CURRENT_VEH"."SCD_FLG"='A' AND "CURRENT_VEH"."CONTR_GRP"='P')
              40 - access(ROWID=ROWID)
              43 - access("CURRENT_VEH"."VEH_KEY"="F_VEH_LOCN"."VEH_KEY")
              45 - filter(SUM("RO_TOTAL_COST"."RO_COST")>=2500)
              47 - filter(TO_DATE('2011-JAN-01')<=TO_DATE('2012-SEP-24'))
              51 - access("DTIME"."CALENDAR_DT">=TO_DATE('2011-JAN-01') AND "DTIME"."CALENDAR_DT"<=TO_DATE('2012-SEP-24'))
              55 - access("RO_TOTAL_COST"."RO_TIME_KEY"="DTIME"."TIME_KEY")
              56 - access("RO_TOTAL_COST"."RO_LOCN_KEY"=:B1)
              57 - filter("RO_TOTAL_COST"."ORIGINAL_RO_NUM"=:B1)
              58 - access("USERID"='600125729')
                   filter("USER_GEO"."GEO"=:B1 OR "USER_GEO"."GEO"=:B2 OR "USER_GEO"."GEO"=:B3 OR "USER_GEO"."GEO"=:B4 OR "USER_GEO"."GEO"=:B5)
              59 - filter( EXISTS (SELECT 0 FROM "PTL_EDW"."F_RO_DETL" "RO_TOTAL_COST" WHERE "RO_TOTAL_COST"."RO_LOCN_KEY"=:B1 AND
                          "RO_TOTAL_COST"."PARTITION_KEY"<=201209 AND "RO_TOTAL_COST"."PARTITION_KEY">=201101 AND "RO_TOTAL_COST"."ORIGINAL_RO_NUM"=:B2 GROUP BY
                          "RO_TOTAL_COST"."RO_LOCN_KEY","RO_TOTAL_COST"."ORIGINAL_RO_NUM" HAVING SUM("RO_TOTAL_COST"."RO_COST")>=2500) AND  EXISTS (SELECT 0 FROM
                          "PTL_HOLD"."USER_GEO" "USER_GEO" WHERE "USERID"='600125729' AND ("USER_GEO"."GEO"=:B3 OR "USER_GEO"."GEO"=:B4 OR "USER_GEO"."GEO"=:B5 OR
                          "USER_GEO"."GEO"=:B6 OR "USER_GEO"."GEO"=:B7)))
              60 - access("CURRENT_VEH_LOCN"."LOCN_KEY"="F_VEH_LOCN"."LOCN_KEY")
              62 - access("F_RO_DETL"."LOCN_KEY"="D_LOCN"."LOCN_KEY")
              64 - access("F_RO_DETL"."CUST_KEY"="D_CUSTOMER"."CUST_KEY")
              65 - access("CURRENT_VEH"."VEH_KEY"="F_VEH_LOCN"."VEH_KEY")
              66 - access("CURRENT_VEH"."CORP_CD"="D_VEHICLE"."CORP_CD" AND "CURRENT_VEH"."UNIT_NUM"="D_VEHICLE"."UNIT_NUM")
              67 - access("R_COMP_CODES"."COMP_CD"="F_RO_DETL"."COMP_CD")
              69 - access("D_VEHICLE"."VEH_KEY"="F_RO_DETL"."VEH_KEY")
              70 - access("PTL_EDW_D_RO_LOCN"."LOCN_KEY"="F_RO_DETL"."RO_LOCN_KEY")
              72 - access(ROWID=ROWID)
              78 - access("PTL_EDW_D_CHRG_LOCN"."DIST_NUM_SUFX"='014810')
              82 - access("F_RO_DETL"."CHRG_LOCN_KEY"="PTL_EDW_D_CHRG_LOCN"."LOCN_KEY")
              84 - access("F_RO_DETL"."PARTITION_KEY">=201101 AND "F_RO_DETL"."PARTITION_KEY"<=201209)
              87 - filter("CURRENT_VEH"."SCD_FLG"='A' AND "CURRENT_VEH"."CONTR_GRP"='P')
              90 - filter(SUM("RO_TOTAL_COST"."RO_COST")>=2500)
              93 - filter("RO_TOTAL_COST"."ORIGINAL_RO_NUM"=:B1)
              96 - access("RO_TOTAL_COST"."RO_LOCN_KEY"=:B1)
              98 - access("RO_TOTAL_COST"."PARTITION_KEY">=201101 AND "RO_TOTAL_COST"."PARTITION_KEY"<=201209)
              99 - access("USERID"='600125729')
                   filter("USER_GEO"."GEO"=:B1 OR "USER_GEO"."GEO"=:B2 OR "USER_GEO"."GEO"=:B3 OR "USER_GEO"."GEO"=:B4 OR "USER_GEO"."GEO"=:B5)
            
            Note
            -----
               - star transformation used for this statement
            
            168 rows selected.
            will continue explain plan of other two part in next reply...

            Thank you.
            • 3. Re: sql query is hung, doesn't comes back
              981047
              continuing the explain plan for last reply....
              *EXPLAIN PLAN OF FIRST PART --*
              
              PLAN_TABLE_OUTPUT
              --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
              Plan hash value: 3787263749
              
              -------------------------------------------------------------------------------------------------------------------------------------------
              | Id  | Operation                                      | Name                     | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
              -------------------------------------------------------------------------------------------------------------------------------------------
              |   0 | SELECT STATEMENT                               |                          |     1 |   361 |     0   (0)|          |       |    |
              |*  1 |  FILTER                                        |                          |       |       |            |          |       |    |
              |*  2 |   FILTER                                       |                          |       |       |            |          |       |    |
              |   3 |    NESTED LOOPS                                |                          |       |       |            |          |       |    |
              |   4 |     NESTED LOOPS                               |                          |    55 | 19855 |  8034   (1)| 00:01:53 |       |    |
              |   5 |      NESTED LOOPS                              |                          |    55 | 18205 |  7979   (1)| 00:01:52 |       |    |
              |   6 |       NESTED LOOPS                             |                          |    55 | 17490 |  7923   (1)| 00:01:51 |       |    |
              |   7 |        NESTED LOOPS                            |                          |    55 | 13915 |  7868   (1)| 00:01:51 |       |    |
              |   8 |         NESTED LOOPS                           |                          |    55 | 11825 |  7813   (1)| 00:01:50 |       |    |
              |   9 |          NESTED LOOPS                          |                          |    55 | 11275 |  7703   (1)| 00:01:48 |       |    |
              |  10 |           NESTED LOOPS                         |                          |    64 | 11648 |  7511   (1)| 00:01:46 |       |    |
              |* 11 |            HASH JOIN                           |                          |    64 |  7616 |  7383   (1)| 00:01:44 |       |    |
              |* 12 |             HASH JOIN                          |                          |    65 |  6565 |  7379   (1)| 00:01:44 |       |    |
              |  13 |              TABLE ACCESS BY INDEX ROWID       | D_TIME                   |    24 |   312 |     3   (0)| 00:00:01 |       |    |
              |* 14 |               INDEX RANGE SCAN                 | D_TIME_UQIND2_CALDT      |    43 |       |     2   (0)| 00:00:01 |       |    |
              |  15 |              NESTED LOOPS                      |                          |       |       |            |          |       |    |
              |  16 |               NESTED LOOPS                     |                          | 29312 |  2519K|  7375   (1)| 00:01:44 |       |    |
              |  17 |                TABLE ACCESS BY INDEX ROWID     | D_LOCN                   |     1 |    12 |     2   (0)| 00:00:01 |       |    |
              |* 18 |                 INDEX RANGE SCAN               | D_LOCN_DIM_KEY4          |     1 |       |     1   (0)| 00:00:01 |       |    |
              |  19 |                PARTITION RANGE ALL             |                          |       |       |            |          |     1 |    38 |
              |  20 |                 BITMAP CONVERSION TO ROWIDS    |                          |       |       |            |          |       |    |
              |* 21 |                  BITMAP INDEX SINGLE VALUE     | BIMAP_FRODETL_CHRGLOCKEY |       |       |            |          |     1 |    38 |
              |  22 |               TABLE ACCESS BY LOCAL INDEX ROWID| F_RO_DETL                | 28137 |  2088K|  7375   (1)| 00:01:44 |     1 |  1 |
              |  23 |             TABLE ACCESS FULL                  | R_COMP_CODES             |   108 |  1944 |     3   (0)| 00:00:01 |       |    |
              |  24 |            TABLE ACCESS BY INDEX ROWID         | D_VEHICLE                |     1 |    63 |     2   (0)| 00:00:01 |       |    |
              |* 25 |             INDEX UNIQUE SCAN                  | VEHCILE_PK               |     1 |       |     1   (0)| 00:00:01 |       |    |
              |* 26 |           TABLE ACCESS BY INDEX ROWID          | D_VEHICLE                |     1 |    23 |     3   (0)| 00:00:01 |       |    |
              |* 27 |            INDEX RANGE SCAN                    | VEH_DIM_KEY_IND2         |     1 |       |     2   (0)| 00:00:01 |       |    |
              |* 28 |          INDEX RANGE SCAN                      | F_VEH_LOCN_IND3          |     1 |    10 |     2   (0)| 00:00:01 |       |    |
              |  29 |         TABLE ACCESS BY INDEX ROWID            | D_CUSTOMER               |     1 |    38 |     1   (0)| 00:00:01 |       |    |
              |* 30 |          INDEX UNIQUE SCAN                     | CUST_DIM_KEY_UQIND1      |     1 |       |     0   (0)| 00:00:01 |       |    |
              |  31 |        TABLE ACCESS BY INDEX ROWID             | D_LOCN                   |     1 |    65 |     1   (0)| 00:00:01 |       |    |
              |* 32 |         INDEX UNIQUE SCAN                      | LOCN_PK                  |     1 |       |     0   (0)| 00:00:01 |       |    |
              |  33 |       TABLE ACCESS BY INDEX ROWID              | D_LOCN                   |     1 |    13 |     1   (0)| 00:00:01 |       |    |
              |* 34 |        INDEX UNIQUE SCAN                       | LOCN_PK                  |     1 |       |     0   (0)| 00:00:01 |       |    |
              |* 35 |      INDEX UNIQUE SCAN                         | LOCN_PK                  |     1 |       |     0   (0)| 00:00:01 |       |    |
              |  36 |     TABLE ACCESS BY INDEX ROWID                | D_LOCN                   |     1 |    30 |     1   (0)| 00:00:01 |       |    |
              |* 37 |   FILTER                                       |                          |       |       |            |          |       |    |
              |  38 |    SORT GROUP BY NOSORT                        |                          |     1 |    36 |  1855   (1)| 00:00:26 |       |    |
              |* 39 |     FILTER                                     |                          |       |       |            |          |       |    |
              |  40 |      NESTED LOOPS                              |                          |       |       |            |          |       |    |
              |  41 |       NESTED LOOPS                             |                          |     1 |    36 |  1855   (1)| 00:00:26 |       |    |
              |  42 |        TABLE ACCESS BY INDEX ROWID             | D_TIME                   |    24 |   312 |     3   (0)| 00:00:01 |       |    |
              |* 43 |         INDEX RANGE SCAN                       | D_TIME_UQIND2_CALDT      |    43 |       |     2   (0)| 00:00:01 |       |    |
              |  44 |        PARTITION RANGE ALL                     |                          |       |       |            |          |     1 |    38 |
              |  45 |         BITMAP CONVERSION TO ROWIDS            |                          |       |       |            |          |       |    |
              |  46 |          BITMAP AND                            |                          |       |       |            |          |       |    |
              |* 47 |           BITMAP INDEX SINGLE VALUE            | BIMAP_FRODETL_TIMKEY     |       |       |            |          |     1 |    38 |
              |* 48 |           BITMAP INDEX SINGLE VALUE            | BIMAP_FRODETL_ROLOCKEY   |       |       |            |          |     1 |    38 |
              |* 49 |       TABLE ACCESS BY LOCAL INDEX ROWID        | F_RO_DETL                |     1 |    23 |  1855   (1)| 00:00:26 |     1 |  1 |
              |* 50 |   INDEX RANGE SCAN                             | I2_USERGEO               |     1 |    15 |     1   (0)| 00:00:01 |       |    |
              -------------------------------------------------------------------------------------------------------------------------------------------
              
              Predicate Information (identified by operation id):
              ---------------------------------------------------
              
                 1 - filter( EXISTS (SELECT 0 FROM "PTL_EDW"."D_TIME" "DTIME","PTL_EDW"."F_RO_DETL" "RO_TOTAL_COST" WHERE
                            TO_DATE('2011-JAN-01')<=TO_DATE('2012-FEB-01') AND "RO_TOTAL_COST"."RO_TIME_KEY"="DTIME"."TIME_KEY" AND
                            "RO_TOTAL_COST"."RO_LOCN_KEY"=:B1 AND "RO_TOTAL_COST"."ORIGINAL_RO_NUM"=:B2 AND "DTIME"."CALENDAR_DT"<=TO_DATE('2012-FEB-01') AND
                            "DTIME"."CALENDAR_DT">=TO_DATE('2011-JAN-01') GROUP BY "RO_TOTAL_COST"."RO_LOCN_KEY","RO_TOTAL_COST"."ORIGINAL_RO_NUM" HAVING
                            SUM("RO_TOTAL_COST"."RO_COST")>=2500) AND  EXISTS (SELECT 0 FROM "PTL_HOLD"."USER_GEO" "USER_GEO" WHERE "USERID"='600125729' AND
                            ("USER_GEO"."GEO"=:B3 OR "USER_GEO"."GEO"=:B4 OR "USER_GEO"."GEO"=:B5 OR "USER_GEO"."GEO"=:B6 OR "USER_GEO"."GEO"=:B7)))
                 2 - filter(NULL IS NOT NULL AND TO_DATE('2011-JAN-01')<=TO_DATE('2012-FEB-01'))
                11 - access("R_COMP_CODES"."COMP_CD"="F_RO_DETL"."COMP_CD")
                12 - access("D_TIME"."TIME_KEY"="F_RO_DETL"."RO_TIME_KEY")
                14 - access("D_TIME"."CALENDAR_DT">=TO_DATE('2011-JAN-01') AND "D_TIME"."CALENDAR_DT"<=TO_DATE('2012-FEB-01'))
                18 - access("PTL_EDW_D_CHRG_LOCN"."DIST_NUM_SUFX"='014810')
                21 - access("F_RO_DETL"."CHRG_LOCN_KEY"="PTL_EDW_D_CHRG_LOCN"."LOCN_KEY")
                25 - access("D_VEHICLE"."VEH_KEY"="F_RO_DETL"."VEH_KEY")
                26 - filter("CURRENT_VEH"."SCD_FLG"='A' AND "CURRENT_VEH"."CONTR_GRP"='P')
                27 - access("CURRENT_VEH"."CORP_CD"="D_VEHICLE"."CORP_CD" AND "CURRENT_VEH"."UNIT_NUM"="D_VEHICLE"."UNIT_NUM")
                28 - access("CURRENT_VEH"."VEH_KEY"="F_VEH_LOCN"."VEH_KEY")
                30 - access("F_RO_DETL"."CUST_KEY"="D_CUSTOMER"."CUST_KEY")
                32 - access("F_RO_DETL"."LOCN_KEY"="D_LOCN"."LOCN_KEY")
                34 - access("PTL_EDW_D_RO_LOCN"."LOCN_KEY"="F_RO_DETL"."RO_LOCN_KEY")
                35 - access("CURRENT_VEH_LOCN"."LOCN_KEY"="F_VEH_LOCN"."LOCN_KEY")
                37 - filter(SUM("RO_TOTAL_COST"."RO_COST")>=2500)
                39 - filter(TO_DATE('2011-JAN-01')<=TO_DATE('2012-FEB-01'))
                43 - access("DTIME"."CALENDAR_DT">=TO_DATE('2011-JAN-01') AND "DTIME"."CALENDAR_DT"<=TO_DATE('2012-FEB-01'))
                47 - access("RO_TOTAL_COST"."RO_TIME_KEY"="DTIME"."TIME_KEY")
                48 - access("RO_TOTAL_COST"."RO_LOCN_KEY"=:B1)
                49 - filter("RO_TOTAL_COST"."ORIGINAL_RO_NUM"=:B1)
                50 - access("USERID"='600125729')
                     filter("USER_GEO"."GEO"=:B1 OR "USER_GEO"."GEO"=:B2 OR "USER_GEO"."GEO"=:B3 OR "USER_GEO"."GEO"=:B4 OR "USER_GEO"."GEO"=:B5)
              
              89 rows selected.
              
              
              *EXPLAIN PLAN OF SECOND PART --*
              
              PLAN_TABLE_OUTPUT
              --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
              Plan hash value: 1394756200
              
              ------------------------------------------------------------------------------------------------------------------------------------------------
              | Id  | Operation                                   | Name                     | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     | Pstart| Pstop |
              ------------------------------------------------------------------------------------------------------------------------------------------------
              |   0 | SELECT STATEMENT                            |                          |     1 |   349 |       | 63571   (3)| 00:14:50 |       |       |
              |*  1 |  FILTER                                     |                          |       |       |       |            |          |       |       |
              |*  2 |   HASH JOIN                                 |                          | 11479 |  3912K|       | 52220   (3)| 00:12:12 |       |       |
              |   3 |    TABLE ACCESS FULL                        | D_LOCN                   | 19458 |   570K|       |   119   (1)| 00:00:02 |       |       |
              |*  4 |    HASH JOIN                                |                          | 11479 |  3575K|       | 52100   (3)| 00:12:10 |       |       |
              |   5 |     TABLE ACCESS FULL                       | D_LOCN                   | 19458 |  1235K|       |   119   (1)| 00:00:02 |       |       |
              |*  6 |     HASH JOIN                               |                          | 11479 |  2847K|       | 51979   (3)| 00:12:08 |       |       |
              |   7 |      TABLE ACCESS FULL                      | R_COMP_CODES             |   108 |  1944 |       |     3   (0)| 00:00:01 |       |       |
              |*  8 |      HASH JOIN                              |                          | 11489 |  2647K|  2672K| 51975   (3)| 00:12:08 |       |       |
              |*  9 |       HASH JOIN                             |                          | 11489 |  2535K|  2816K| 46619   (3)| 00:10:53 |       |       |
              |* 10 |        HASH JOIN                            |                          | 13378 |  2652K|       | 23719   (2)| 00:05:33 |       |       |
              |* 11 |         HASH JOIN                           |                          | 13378 |  1829K|       |  2993   (3)| 00:00:42 |       |       |
              |* 12 |          HASH JOIN                          |                          | 13378 |  1332K|       |  2159   (3)| 00:00:31 |       |       |
              |  13 |           VIEW                              | index$_join$_006         | 19458 |   247K|       |    80   (3)| 00:00:02 |       |       |
              |* 14 |            HASH JOIN                        |                          |       |       |       |            |          |       |       |
              |  15 |             INDEX FAST FULL SCAN            | LOCN_PK                  | 19458 |   247K|       |    38   (0)| 00:00:01 |       |       |
              |  16 |             INDEX FAST FULL SCAN            | LOCN_DIM_KEY1            | 19458 |   247K|       |    61   (2)| 00:00:01 |       |       |
              |  17 |           NESTED LOOPS                      |                          |       |       |       |            |          |       |       |
              |  18 |            NESTED LOOPS                     |                          | 13378 |  1162K|       |  2077   (3)| 00:00:30 |       |       |
              |  19 |             TABLE ACCESS BY INDEX ROWID     | D_LOCN                   |     1 |    12 |       |     2   (0)| 00:00:01 |       |       |
              |* 20 |              INDEX RANGE SCAN               | D_LOCN_DIM_KEY4          |     1 |       |       |     1   (0)| 00:00:01 |       |       |
              |  21 |             PARTITION RANGE ITERATOR        |                          |       |       |       |            |          |    14 |    27 |
              |  22 |              BITMAP CONVERSION TO ROWIDS    |                          |       |       |       |            |          |       |       |
              |  23 |               BITMAP AND                    |                          |       |       |       |            |          |       |       |
              |* 24 |                BITMAP INDEX SINGLE VALUE    | BIMAP_FRODETL_CHRGLOCKEY |       |       |       |            |          |    14 |    27 |
              |  25 |                BITMAP MERGE                 |                          |       |       |       |            |          |       |       |
              |* 26 |                 BITMAP INDEX RANGE SCAN     | BIMAP_FRODETL_PARTKEY    |       |       |       |            |          |    14 |    27 |
              |  27 |            TABLE ACCESS BY LOCAL INDEX ROWID| F_RO_DETL                | 12841 |   965K|       |  2077   (3)| 00:00:30 |     1 |     1 |
              |  28 |          TABLE ACCESS FULL                  | D_CUSTOMER               |   219K|  8147K|       |   831   (2)| 00:00:12 |       |       |
              |  29 |         TABLE ACCESS FULL                   | D_VEHICLE                |  3973K|   238M|       | 20670   (2)| 00:04:50 |       |       |
              |* 30 |        TABLE ACCESS FULL                    | D_VEHICLE                |  1288K|    28M|       | 20907   (3)| 00:04:53 |       |       |
              |  31 |       INDEX FAST FULL SCAN                  | F_VEH_LOCN_IND3          |  3131K|    29M|       |  2348   (3)| 00:00:33 |       |       |
              |* 32 |   FILTER                                    |                          |       |       |       |            |          |       |       |
              |  33 |    SORT GROUP BY NOSORT                     |                          |     1 |    24 |       |  2378   (3)| 00:00:34 |       |       |
              |  34 |     PARTITION RANGE ITERATOR                |                          |     1 |    24 |       |  2378   (3)| 00:00:34 |    14 |    27 |
              |* 35 |      TABLE ACCESS BY LOCAL INDEX ROWID      | F_RO_DETL                |     1 |    24 |       |  2378   (3)| 00:00:34 |    14 |    27 |
              |  36 |       BITMAP CONVERSION TO ROWIDS           |                          |       |       |       |            |          |       |       |
              |  37 |        BITMAP AND                           |                          |       |       |       |            |          |       |       |
              |* 38 |         BITMAP INDEX SINGLE VALUE           | BIMAP_FRODETL_ROLOCKEY   |       |       |       |            |          |    14 |    27 |
              |  39 |         BITMAP MERGE                        |                          |       |       |       |            |          |       |       |
              |* 40 |          BITMAP INDEX RANGE SCAN            | BIMAP_FRODETL_PARTKEY    |       |       |       |            |          |    14 |    27 |
              |* 41 |   INDEX RANGE SCAN                          | I2_USERGEO               |     1 |    15 |       |     1   (0)| 00:00:01 |       |       |
              ------------------------------------------------------------------------------------------------------------------------------------------------
              
              Predicate Information (identified by operation id):
              ---------------------------------------------------
              
                 1 - filter( EXISTS (SELECT 0 FROM "PTL_EDW"."F_RO_DETL" "RO_TOTAL_COST" WHERE "RO_TOTAL_COST"."RO_LOCN_KEY"=:B1 AND
                            "RO_TOTAL_COST"."PARTITION_KEY"<=201202 AND "RO_TOTAL_COST"."PARTITION_KEY">=201101 AND "RO_TOTAL_COST"."ORIGINAL_RO_NUM"=:B2 GROUP BY
                            "RO_TOTAL_COST"."RO_LOCN_KEY","RO_TOTAL_COST"."ORIGINAL_RO_NUM" HAVING SUM("RO_TOTAL_COST"."RO_COST")>=2500) AND  EXISTS (SELECT 0 FROM
                            "PTL_HOLD"."USER_GEO" "USER_GEO" WHERE "USERID"='600125729' AND ("USER_GEO"."GEO"=:B3 OR "USER_GEO"."GEO"=:B4 OR "USER_GEO"."GEO"=:B5
                            OR "USER_GEO"."GEO"=:B6 OR "USER_GEO"."GEO"=:B7)))
                 2 - access("CURRENT_VEH_LOCN"."LOCN_KEY"="F_VEH_LOCN"."LOCN_KEY")
                 4 - access("F_RO_DETL"."LOCN_KEY"="D_LOCN"."LOCN_KEY")
                 6 - access("R_COMP_CODES"."COMP_CD"="F_RO_DETL"."COMP_CD")
                 8 - access("CURRENT_VEH"."VEH_KEY"="F_VEH_LOCN"."VEH_KEY")
                 9 - access("CURRENT_VEH"."CORP_CD"="D_VEHICLE"."CORP_CD" AND "CURRENT_VEH"."UNIT_NUM"="D_VEHICLE"."UNIT_NUM")
                10 - access("D_VEHICLE"."VEH_KEY"="F_RO_DETL"."VEH_KEY")
                11 - access("F_RO_DETL"."CUST_KEY"="D_CUSTOMER"."CUST_KEY")
                12 - access("PTL_EDW_D_RO_LOCN"."LOCN_KEY"="F_RO_DETL"."RO_LOCN_KEY")
                14 - access(ROWID=ROWID)
                20 - access("PTL_EDW_D_CHRG_LOCN"."DIST_NUM_SUFX"='014810')
                24 - access("F_RO_DETL"."CHRG_LOCN_KEY"="PTL_EDW_D_CHRG_LOCN"."LOCN_KEY")
                26 - access("F_RO_DETL"."PARTITION_KEY">=201101 AND "F_RO_DETL"."PARTITION_KEY"<=201202)
                30 - filter("CURRENT_VEH"."SCD_FLG"='A' AND "CURRENT_VEH"."CONTR_GRP"='P')
                32 - filter(SUM("RO_TOTAL_COST"."RO_COST")>=2500)
                35 - filter("RO_TOTAL_COST"."ORIGINAL_RO_NUM"=:B1)
                38 - access("RO_TOTAL_COST"."RO_LOCN_KEY"=:B1)
                40 - access("RO_TOTAL_COST"."PARTITION_KEY">=201101 AND "RO_TOTAL_COST"."PARTITION_KEY"<=201202)
                41 - access("USERID"='600125729')
                     filter("USER_GEO"."GEO"=:B1 OR "USER_GEO"."GEO"=:B2 OR "USER_GEO"."GEO"=:B3 OR "USER_GEO"."GEO"=:B4 OR "USER_GEO"."GEO"=:B5)
              
              76 rows selected.
              
              Elapsed: 00:00:00.08
              Thank you
              • 4. Re: sql query is hung, doesn't comes back
                SomeoneElse
                Please use code tags. It's not rocket science.
                Place one of these at the beginning and one at the end of all your code (or your explain plan).                                                                                                                                                                                                                                                                                                                        
                • 5. Re: sql query is hung, doesn't comes back
                  EdStevens
                  For your own sanity, you should learn to format your code. For the sake of the forum members, you should preserve the formatting by using the \
                   tags.  
                  
                  I've done that for you here, and some other questions popped right out.  Look for my embedded comments near the bottom
                  SELECT
                  '../../opendoc/',
                  '&hideSave=true&hideEdit=true&hideRefresh=true&hideUserPromptInput=true'
                  ,
                  'Data As Of:' ,
                  'Run Date:' ,
                  ptl_hold.get_stgextr_time('FACT_FPNA_RO'),
                  'RO Date' ,
                  PTL_EDW.F_RO_DETL.RO_TYP ,
                  PTL_EDW.D_CUSTOMER.CUST_NUM ,
                  (PTL_EDW.D_CUSTOMER.CUST_NUM )
                  ||' - '
                  ||( PTL_EDW.D_CUSTOMER.CUST_PRIME_NM),
                  PTL_EDW.D_CUSTOMER.BILL_GRP ,
                  CASE
                  WHEN PTL_EDW.D_LOCN.CORP_CD='HPTL'
                  THEN 'US'
                  ELSE
                  CASE
                  WHEN PTL_EDW.D_LOCN.CORP_CD='2000'
                  THEN 'CANADA'
                  ELSE 'UNKNOWN'
                  END
                  END ,
                  PTL_EDW.D_LOCN.REGN_NUM,
                  PTL_EDW.D_LOCN.REGN_NUM
                  ||' - '
                  ||PTL_EDW.D_LOCN.REGN_NM,
                  PTL_EDW.D_LOCN.AREA_NUM ,
                  PTL_EDW.D_LOCN.AREA_NUM
                  ||' - '
                  ||PTL_EDW.D_LOCN.AREA_NM,
                  PTL_EDW.D_LOCN.RDIST_NUM,
                  PTL_EDW.D_LOCN.RDIST_NUM
                  ||' - '
                  ||PTL_EDW.D_LOCN.RDIST_NM,
                  PTL_EDW.D_LOCN.DIST_NUM ,
                  PTL_EDW.D_LOCN.DIST_NUM
                  ||' - '
                  ||PTL_EDW.D_LOCN.DIST_NM,
                  CASE
                  WHEN TRIM(PTL_EDW.D_VEHICLE.VEH_CAP_STAT) IN
                  ('ACTIVE','ACCRUED')
                  THEN 'Active'
                  ELSE 'Inactive'
                  END,
                  DECODE(PTL_EDW.D_VEHICLE.CONTR_GRP, 'X' ,
                  'NON-CONTRACT',PTL_EDW.D_VEHICLE.VEH_CAT),
                  PTL_EDW.D_VEHICLE.VEH_UNIT_TYP ,
                  PTL_EDW.D_VEHICLE.VEH_MODL_YR ,
                  PTL_EDW.D_VEHICLE.VEH_MAKE ,
                  PTL_EDW.D_VEHICLE.VEH_MODEL ,
                  CASE
                  WHEN
                  (
                  PTL_HOLD.R_COMP_CODES.COMP_CD
                  )
                  IN ('057','061')
                  THEN 'Accident/Incident'
                  ELSE 'Non-Accident/Incident'
                  END ,
                  PTL_HOLD.R_COMP_CODES.COMP_CD ,
                  PTL_HOLD.R_COMP_CODES.COMP_DESC,
                  PTL_HOLD.R_COMP_CODES.COMP_CD
                  ||' - '
                  ||PTL_HOLD.R_COMP_CODES.COMP_DESC,
                  PTL_EDW.D_VEHICLE.UNIT_NUM ,
                  PTL_EDW_D_RO_LOCN.DIST_NUM
                  || PTL_EDW_D_RO_LOCN.DIST_SUFX
                  || PTL_EDW.F_RO_DETL.ORIGINAL_RO_NUM,
                  PTL_EDW_D_RO_LOCN.DIST_NUM
                  || PTL_EDW_D_RO_LOCN.DIST_SUFX
                  || PTL_EDW.F_RO_DETL.ORIGINAL_RO_NUM
                  || PTL_EDW.F_RO_DETL.RO_SEQ_NUM ,
                  PTL_EDW.F_RO_DETL.RO_SRC ,
                  PTL_EDW.F_RO_DETL.TOTAL_LABOR_HRS ,
                  PTL_EDW.F_RO_DETL.TOTAL_LABOR_COST ,
                  PTL_EDW.F_RO_DETL.OUT_COST ,
                  PTL_EDW.F_RO_DETL.PARTS_COST ,
                  PTL_EDW.F_RO_DETL.RO_COST ,
                  TO_CHAR(PTL_EDW_D_RO_LOCN.LOCN_KEY),
                  PTL_EDW.F_RO_DETL.RO_NUM ,
                  PTL_EDW.F_RO_DETL.RO_SEQ_NUM ,
                  PTL_EDW.F_RO_DETL.ORIGINAL_RO_NUM
                  FROM
                  PTL_EDW.F_RO_DETL ,
                  PTL_EDW.D_CUSTOMER ,
                  PTL_EDW.D_LOCN ,
                  PTL_EDW.D_VEHICLE ,
                  PTL_HOLD.R_COMP_CODES ,
                  PTL_EDW.D_LOCN PTL_EDW_D_RO_LOCN ,
                  PTL_EDW.D_LOCN PTL_EDW_D_CHRG_LOCN,
                  PTL_EDW.D_TIME ,
                  PTL_EDW.D_LOCN CURRENT_VEH_LOCN ,
                  PTL_EDW.F_VEH_LOCN ,
                  PTL_EDW.D_VEHICLE CURRENT_VEH
                  WHERE
                  (
                  PTL_EDW.F_RO_DETL.CUST_KEY =
                  PTL_EDW.D_CUSTOMER.CUST_KEY
                  )
                  AND
                  (
                  PTL_EDW.F_RO_DETL.LOCN_KEY =
                  PTL_EDW.D_LOCN.LOCN_KEY
                  )
                  AND
                  (
                  PTL_EDW.D_TIME.TIME_KEY =
                  PTL_EDW.F_RO_DETL.RO_TIME_KEY
                  )
                  AND
                  (
                  PTL_EDW.D_VEHICLE.VEH_KEY =
                  PTL_EDW.F_RO_DETL.VEH_KEY
                  )
                  AND
                  (
                  PTL_EDW_D_RO_LOCN.LOCN_KEY =
                  PTL_EDW.F_RO_DETL.RO_LOCN_KEY
                  )
                  AND
                  (
                  PTL_EDW.F_RO_DETL.CHRG_LOCN_KEY=
                  PTL_EDW_D_CHRG_LOCN.LOCN_KEY
                  )
                  AND
                  (
                  CURRENT_VEH_LOCN.LOCN_KEY =
                  PTL_EDW.F_VEH_LOCN.LOCN_KEY
                  )
                  AND
                  (
                  CURRENT_VEH.SCD_FLG ='A'
                  )
                  AND
                  (
                  CURRENT_VEH.CONTR_GRP ='P'
                  )
                  AND
                  (
                  CURRENT_VEH.CORP_CD =
                  PTL_EDW.D_VEHICLE.CORP_CD
                  AND CURRENT_VEH.UNIT_NUM =
                  PTL_EDW.D_VEHICLE.UNIT_NUM
                  )
                  AND
                  (
                  CURRENT_VEH.VEH_KEY =
                  PTL_EDW.F_VEH_LOCN.VEH_KEY
                  )
                  AND
                  (
                  PTL_HOLD.R_COMP_CODES.COMP_CD =
                  PTL_EDW.F_RO_DETL.COMP_CD
                  )
                  AND
                  (
                  (
                  'Accounting Month' = 'RO Date'
                  )
                  AND
                  (
                  '2500' = 'NO-MIN'
                  OR EXISTS
                  (
                  SELECT
                  RO_TOTAL_COST.RO_LOCN_KEY,
                  RO_TOTAL_COST.ORIGINAL_RO_NUM
                  FROM
                  PTL_EDW.F_RO_DETL RO_TOTAL_COST,
                  PTL_EDW.D_TIME DTIME
                  WHERE
                  (
                  'I' = 'I'
                  OR
                  (
                  'I' = 'E'
                  AND RO_TOTAL_COST.RO_TYP 'W'
                  )
                  )
                  AND RO_TOTAL_COST.RO_LOCN_KEY =
                  PTL_EDW.F_RO_DETL.RO_LOCN_KEY
                  AND RO_TOTAL_COST.ORIGINAL_RO_NUM =
                  PTL_EDW.F_RO_DETL.ORIGINAL_RO_NUM
                  AND RO_TOTAL_COST.RO_TIME_KEY =
                  DTIME.TIME_KEY
                  AND DTIME.CALENDAR_DT BETWEEN to_date(
                  '2011-JAN-01') AND to_date(
                  '2012-SEP-24')
                  GROUP BY
                  RO_TOTAL_COST.RO_LOCN_KEY,
                  RO_TOTAL_COST.ORIGINAL_RO_NUM
                  HAVING
                  SUM(RO_TOTAL_COST.RO_COST) >= TO_NUMBER
                  (TRANSLATE('2500','NO-MIN',' '))
                  )
                  )
                  AND PTL_EDW_D_CHRG_LOCN.DIST_NUM_SUFX IN (
                  '014810')
                  AND
                  (
                  PTL_EDW.D_TIME.CALENDAR_DT BETWEEN to_date(
                  '2011-JAN-01') AND to_date('2012-SEP-24')
                  )
                  AND
                  (
                  (
                  (
                  'I' = 'I'
                  )
                  OR
                  (
                  'I' = 'E'
                  AND
                  (
                  (
                  PTL_EDW.F_RO_DETL.RO_TYP
                  )
                  'W'
                  )
                  )
                  )
                  )
                  AND
                  (
                  EXISTS
                  (
                  SELECT
                  *
                  FROM
                  PTL_HOLD.USER_GEO
                  WHERE
                  USERID ='600125729'
                  AND PTL_HOLD.USER_GEO.GEO IN (
                  CURRENT_VEH_LOCN.CORP_CD,
                  CURRENT_VEH_LOCN.REGN_NUM,
                  CURRENT_VEH_LOCN.AREA_NUM,
                  CURRENT_VEH_LOCN.RDIST_NUM,
                  CURRENT_VEH_LOCN.DIST_NUM)
                  )
                  )
                  )
                  UNION
                  SELECT
                  '../../opendoc/',
                  '&hideSave=true&hideEdit=true&hideRefresh=true&hideUserPromptInput=true'
                  ,
                  'Data As Of:' ,
                  'Run Date:' ,
                  ptl_hold.get_stgextr_time('FACT_FPNA_RO'),
                  'Accounting Month' ,
                  PTL_EDW.F_RO_DETL.RO_TYP ,
                  PTL_EDW.D_CUSTOMER.CUST_NUM ,
                  (PTL_EDW.D_CUSTOMER.CUST_NUM )
                  ||' - '
                  ||( PTL_EDW.D_CUSTOMER.CUST_PRIME_NM),
                  PTL_EDW.D_CUSTOMER.BILL_GRP ,
                  CASE
                  WHEN PTL_EDW.D_LOCN.CORP_CD='HPTL'
                  THEN 'US'
                  ELSE
                  CASE
                  WHEN PTL_EDW.D_LOCN.CORP_CD='2000'
                  THEN 'CANADA'
                  ELSE 'UNKNOWN'
                  END
                  END ,
                  PTL_EDW.D_LOCN.REGN_NUM,
                  PTL_EDW.D_LOCN.REGN_NUM
                  ||' - '
                  ||PTL_EDW.D_LOCN.REGN_NM,
                  PTL_EDW.D_LOCN.AREA_NUM ,
                  PTL_EDW.D_LOCN.AREA_NUM
                  ||' - '
                  ||PTL_EDW.D_LOCN.AREA_NM,
                  PTL_EDW.D_LOCN.RDIST_NUM,
                  PTL_EDW.D_LOCN.RDIST_NUM
                  ||' - '
                  ||PTL_EDW.D_LOCN.RDIST_NM,
                  PTL_EDW.D_LOCN.DIST_NUM ,
                  PTL_EDW.D_LOCN.DIST_NUM
                  ||' - '
                  ||PTL_EDW.D_LOCN.DIST_NM,
                  CASE
                  WHEN TRIM(PTL_EDW.D_VEHICLE.VEH_CAP_STAT) IN
                  ('ACTIVE','ACCRUED')
                  THEN 'Active'
                  ELSE 'Inactive'
                  END,
                  DECODE(PTL_EDW.D_VEHICLE.CONTR_GRP, 'X' ,
                  'NON-CONTRACT',PTL_EDW.D_VEHICLE.VEH_CAT),
                  PTL_EDW.D_VEHICLE.VEH_UNIT_TYP ,
                  PTL_EDW.D_VEHICLE.VEH_MODL_YR ,
                  PTL_EDW.D_VEHICLE.VEH_MAKE ,
                  PTL_EDW.D_VEHICLE.VEH_MODEL ,
                  CASE
                  WHEN
                  (
                  PTL_HOLD.R_COMP_CODES.COMP_CD
                  )
                  IN ('057','061')
                  THEN 'Accident/Incident'
                  ELSE 'Non-Accident/Incident'
                  END ,
                  PTL_HOLD.R_COMP_CODES.COMP_CD ,
                  PTL_HOLD.R_COMP_CODES.COMP_DESC,
                  PTL_HOLD.R_COMP_CODES.COMP_CD
                  ||' - '
                  ||PTL_HOLD.R_COMP_CODES.COMP_DESC,
                  PTL_EDW.D_VEHICLE.UNIT_NUM ,
                  PTL_EDW_D_RO_LOCN.DIST_NUM
                  || PTL_EDW_D_RO_LOCN.DIST_SUFX
                  || PTL_EDW.F_RO_DETL.ORIGINAL_RO_NUM,
                  PTL_EDW_D_RO_LOCN.DIST_NUM
                  || PTL_EDW_D_RO_LOCN.DIST_SUFX
                  || PTL_EDW.F_RO_DETL.ORIGINAL_RO_NUM
                  || PTL_EDW.F_RO_DETL.RO_SEQ_NUM ,
                  PTL_EDW.F_RO_DETL.RO_SRC ,
                  PTL_EDW.F_RO_DETL.TOTAL_LABOR_HRS ,
                  PTL_EDW.F_RO_DETL.TOTAL_LABOR_COST ,
                  PTL_EDW.F_RO_DETL.OUT_COST ,
                  PTL_EDW.F_RO_DETL.PARTS_COST ,
                  PTL_EDW.F_RO_DETL.RO_COST ,
                  TO_CHAR(PTL_EDW_D_RO_LOCN.LOCN_KEY),
                  PTL_EDW.F_RO_DETL.RO_NUM ,
                  PTL_EDW.F_RO_DETL.RO_SEQ_NUM ,
                  PTL_EDW.F_RO_DETL.ORIGINAL_RO_NUM
                  FROM
                  PTL_EDW.F_RO_DETL ,
                  PTL_EDW.D_CUSTOMER ,
                  PTL_EDW.D_LOCN ,
                  PTL_EDW.D_VEHICLE ,
                  PTL_HOLD.R_COMP_CODES ,
                  PTL_EDW.D_LOCN PTL_EDW_D_RO_LOCN ,
                  PTL_EDW.D_LOCN PTL_EDW_D_CHRG_LOCN,
                  PTL_EDW.D_LOCN CURRENT_VEH_LOCN ,
                  PTL_EDW.F_VEH_LOCN ,
                  PTL_EDW.D_VEHICLE CURRENT_VEH
                  WHERE
                  (
                  PTL_EDW.F_RO_DETL.CUST_KEY =
                  PTL_EDW.D_CUSTOMER.CUST_KEY
                  )
                  AND
                  (
                  PTL_EDW.F_RO_DETL.LOCN_KEY =
                  PTL_EDW.D_LOCN.LOCN_KEY
                  )
                  AND
                  (
                  PTL_EDW.D_VEHICLE.VEH_KEY =
                  PTL_EDW.F_RO_DETL.VEH_KEY
                  )
                  AND
                  (
                  PTL_EDW_D_RO_LOCN.LOCN_KEY =
                  PTL_EDW.F_RO_DETL.RO_LOCN_KEY
                  )
                  AND
                  (
                  PTL_EDW.F_RO_DETL.CHRG_LOCN_KEY=
                  PTL_EDW_D_CHRG_LOCN.LOCN_KEY
                  )
                  AND
                  (
                  CURRENT_VEH_LOCN.LOCN_KEY =
                  PTL_EDW.F_VEH_LOCN.LOCN_KEY
                  )
                  AND
                  (
                  CURRENT_VEH.SCD_FLG ='A'
                  )
                  AND
                  (
                  CURRENT_VEH.CONTR_GRP ='P'
                  )
                  AND
                  (
                  CURRENT_VEH.CORP_CD =
                  PTL_EDW.D_VEHICLE.CORP_CD
                  AND CURRENT_VEH.UNIT_NUM =
                  PTL_EDW.D_VEHICLE.UNIT_NUM
                  )
                  AND
                  (
                  CURRENT_VEH.VEH_KEY =
                  PTL_EDW.F_VEH_LOCN.VEH_KEY
                  )
                  AND
                  (
                  PTL_HOLD.R_COMP_CODES.COMP_CD =
                  PTL_EDW.F_RO_DETL.COMP_CD
                  )
                  AND
                  (
                  (
                  'Accounting Month' = 'Accounting Month'
                  )
                  AND
                  (
                  '2500' = 'NO-MIN'
                  OR EXISTS
                  (
                  SELECT
                  RO_TOTAL_COST.RO_LOCN_KEY,
                  RO_TOTAL_COST.ORIGINAL_RO_NUM
                  FROM
                  PTL_EDW.F_RO_DETL RO_TOTAL_COST
                  WHERE
                  (
                  'I' = 'I'
                  /* what's the point? A literal always equals itself. /*
                  OR
                  (
                  'I' = 'E'
                  /* again, what's the point? This will never be true */
                  AND RO_TOTAL_COST.RO_TYP 'W'
                  )
                  )
                  AND RO_TOTAL_COST.RO_LOCN_KEY =
                  PTL_EDW.F_RO_DETL.RO_LOCN_KEY
                  AND RO_TOTAL_COST.ORIGINAL_RO_NUM =
                  PTL_EDW.F_RO_DETL.ORIGINAL_RO_NUM
                  AND RO_TOTAL_COST.PARTITION_KEY BETWEEN
                  to_number(TO_CHAR(to_date('2011-JAN-01','YYYY-MON-DD'),'yyyymm')) AND
                  to_number(TO_CHAR(to_date('2012-SEP-24','YYYY-MON-DD'),'yyyymm'))
                  /* what is the data type of PARTITION_KEY? This manipulation strongly suggests that it should be DATE and isn't.
                  GROUP BY
                  RO_TOTAL_COST.RO_LOCN_KEY,
                  RO_TOTAL_COST.ORIGINAL_RO_NUM
                  HAVING
                  SUM(RO_TOTAL_COST.RO_COST) >= TO_NUMBER(TRANSLATE('2500','NO-MIN',' '))
                  )
                  )
                  AND PTL_EDW_D_CHRG_LOCN.DIST_NUM_SUFX IN (
                  '014810')
                  AND
                  (
                  (
                  PTL_EDW.F_RO_DETL.PARTITION_KEY BETWEEN
                  to_number(TO_CHAR(to_date('2011-JAN-01',
                  'YYYY-MON-DD'),'yyyymm')) AND to_number(
                  TO_CHAR(to_date('2012-SEP-24',
                  'YYYY-MON-DD'),'yyyymm'))
                  )
                  )
                  AND
                  (
                  (
                  (
                  'I' = 'I'
                  )
                  OR
                  (
                  'I' = 'E'
                  AND
                  (
                  (
                  PTL_EDW.F_RO_DETL.RO_TYP
                  )
                  'W'
                  )
                  )
                  )
                  )
                  AND
                  (
                  EXISTS
                  (
                  SELECT
                  *
                  FROM
                  PTL_HOLD.USER_GEO
                  WHERE
                  USERID ='600125729'
                  AND PTL_HOLD.USER_GEO.GEO IN (
                  CURRENT_VEH_LOCN.CORP_CD,
                  CURRENT_VEH_LOCN.REGN_NUM,
                  CURRENT_VEH_LOCN.AREA_NUM,
                  CURRENT_VEH_LOCN.RDIST_NUM,
                  CURRENT_VEH_LOCN.DIST_NUM)
                  )
                  )
                  )
                  );
                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                      
                  • 6. Re: sql query is hung, doesn't comes back
                    981047
                    Thank you, i will make sure to use code tags going forward. I don't have any experience on query tuning, this is the first one i am trying to work on. i will check with the developer for the questions regarding literals, but i checked that partition_key is a number datatype.

                    PARTITION_KEY - NUMBER

                    following is the tkprof output for the unioned query.
                    call     count       cpu    elapsed       disk      query    current        rows
                    ------- ------  -------- ---------- ---------- ---------- ----------  ----------
                    Parse        1      1.93       1.98          0          0          0           0
                    Execute      1      0.00       0.00          0          0          0           0
                    Fetch        1    698.12    4349.55    2072906   16739180          0           0
                    ------- ------  -------- ---------- ---------- ---------- ----------  ----------
                    total        3    700.05    4351.54    2072906   16739180          0           0
                    
                    Misses in library cache during parse: 1
                    Optimizer mode: ALL_ROWS
                    Parsing user id: SYS
                    
                    Rows     Row Source Operation
                    -------  ---------------------------------------------------
                          0  SORT UNIQUE (cr=0 pr=0 pw=0 time=0 us cost=63573 size=685 card=2)
                          2   UNION-ALL  (cr=4468122 pr=261431 pw=0 time=443420000 us)
                          0    FILTER  (cr=0 pr=0 pw=0 time=0 us)
                          0     FILTER  (cr=0 pr=0 pw=0 time=0 us)
                          0      HASH JOIN  (cr=0 pr=0 pw=0 time=0 us cost=902 size=20496 card=61)
                          0       HASH JOIN  (cr=0 pr=0 pw=0 time=0 us cost=782 size=18666 card=61)
                          0        NESTED LOOPS  (cr=0 pr=0 pw=0 time=0 us cost=701 size=17873 card=61)
                          0         HASH JOIN  (cr=0 pr=0 pw=0 time=0 us cost=579 size=17263 card=61)
                          0          NESTED LOOPS  (cr=0 pr=0 pw=0 time=0 us)
                          0           NESTED LOOPS  (cr=0 pr=0 pw=0 time=0 us cost=458 size=13298 card=61)
                          0            NESTED LOOPS  (cr=0 pr=0 pw=0 time=0 us cost=245 size=13845 card=71)
                          0             NESTED LOOPS  (cr=0 pr=0 pw=0 time=0 us cost=103 size=9372 card=71)
                          0              HASH JOIN  (cr=0 pr=0 pw=0 time=0 us cost=32 size=6674 card=71)
                          0               PARTITION RANGE ALL PARTITION: 1 38 (cr=0 pr=0 pw=0 time=0 us cost=28 size=5403 card=71)
                          0                TABLE ACCESS BY LOCAL INDEX ROWID F_RO_DETL PARTITION: 1 38 (cr=0 pr=0 pw=0 time=0 us cost=28 size=5403 card=71)
                          0                 BITMAP CONVERSION TO ROWIDS (cr=0 pr=0 pw=0 time=0 us)
                          0                  BITMAP AND  (cr=0 pr=0 pw=0 time=0 us)
                          0                   BITMAP MERGE  (cr=0 pr=0 pw=0 time=0 us)
                          0                    BITMAP KEY ITERATION  (cr=0 pr=0 pw=0 time=0 us)
                          0                     BUFFER SORT (cr=0 pr=0 pw=0 time=0 us)
                          0                      TABLE ACCESS BY INDEX ROWID D_LOCN (cr=0 pr=0 pw=0 time=0 us cost=2 size=12 card=1)
                          0                       INDEX RANGE SCAN D_LOCN_DIM_KEY4 (cr=0 pr=0 pw=0 time=0 us cost=1 size=0 card=1)(object id 67088)
                          0                     BITMAP INDEX RANGE SCAN BIMAP_FRODETL_CHRGLOCKEY PARTITION: 1 38 (cr=0 pr=0 pw=0 time=0 us)(object id 239065)
                          0                   BITMAP MERGE  (cr=0 pr=0 pw=0 time=0 us)
                          0                    BITMAP KEY ITERATION  (cr=0 pr=0 pw=0 time=0 us)
                          0                     BUFFER SORT (cr=0 pr=0 pw=0 time=0 us)
                          0                      FILTER  (cr=0 pr=0 pw=0 time=0 us)
                          0                       TABLE ACCESS BY INDEX ROWID D_TIME (cr=0 pr=0 pw=0 time=0 us cost=3 size=528 card=24)
                          0                        INDEX RANGE SCAN D_TIME_UQIND2_CALDT (cr=0 pr=0 pw=0 time=0 us cost=2 size=0 card=43)(object id 67041)
                          0                     BITMAP INDEX RANGE SCAN BIMAP_FRODETL_TIMKEY PARTITION: 1 38 (cr=0 pr=0 pw=0 time=0 us)(object id 239143)
                          0               TABLE ACCESS FULL R_COMP_CODES (cr=0 pr=0 pw=0 time=0 us cost=3 size=1944 card=108)
                          0              TABLE ACCESS BY INDEX ROWID D_CUSTOMER (cr=0 pr=0 pw=0 time=0 us cost=1 size=38 card=1)
                          0               INDEX UNIQUE SCAN CUST_DIM_KEY_UQIND1 (cr=0 pr=0 pw=0 time=0 us cost=0 size=0 card=1)(object id 67094)
                          0             TABLE ACCESS BY INDEX ROWID D_VEHICLE (cr=0 pr=0 pw=0 time=0 us cost=2 size=63 card=1)
                          0              INDEX UNIQUE SCAN VEHCILE_PK (cr=0 pr=0 pw=0 time=0 us cost=1 size=0 card=1)(object id 67100)
                          0            INDEX RANGE SCAN VEH_DIM_KEY_IND2 (cr=0 pr=0 pw=0 time=0 us cost=2 size=0 card=1)(object id 67098)
                          0           TABLE ACCESS BY INDEX ROWID D_VEHICLE (cr=0 pr=0 pw=0 time=0 us cost=3 size=23 card=1)
                          0          TABLE ACCESS FULL D_LOCN (cr=0 pr=0 pw=0 time=0 us cost=119 size=1264770 card=19458)
                          0         INDEX RANGE SCAN F_VEH_LOCN_IND3 (cr=0 pr=0 pw=0 time=0 us cost=2 size=10 card=1)(object id 67080)
                          0        VIEW  index$_join$_006 (cr=0 pr=0 pw=0 time=0 us cost=80 size=252954 card=19458)
                          0         HASH JOIN  (cr=0 pr=0 pw=0 time=0 us)
                          0          INDEX FAST FULL SCAN LOCN_PK (cr=0 pr=0 pw=0 time=0 us cost=38 size=252954 card=19458)(object id 67089)
                          0          INDEX FAST FULL SCAN LOCN_DIM_KEY1 (cr=0 pr=0 pw=0 time=0 us cost=61 size=252954 card=19458)(object id 67091)
                          0       TABLE ACCESS FULL D_LOCN (cr=0 pr=0 pw=0 time=0 us cost=119 size=583740 card=19458)
                          0     FILTER  (cr=0 pr=0 pw=0 time=0 us)
                          0      SORT GROUP BY NOSORT (cr=0 pr=0 pw=0 time=0 us cost=1855 size=36 card=1)
                          0       FILTER  (cr=0 pr=0 pw=0 time=0 us)
                          0        NESTED LOOPS  (cr=0 pr=0 pw=0 time=0 us)
                          0         NESTED LOOPS  (cr=0 pr=0 pw=0 time=0 us cost=1855 size=36 card=1)
                          0          TABLE ACCESS BY INDEX ROWID D_TIME (cr=0 pr=0 pw=0 time=0 us cost=3 size=312 card=24)
                          0           INDEX RANGE SCAN D_TIME_UQIND2_CALDT (cr=0 pr=0 pw=0 time=0 us cost=2 size=0 card=43)(object id 67041)
                          0          PARTITION RANGE ALL PARTITION: 1 38 (cr=0 pr=0 pw=0 time=0 us)
                          0           BITMAP CONVERSION TO ROWIDS (cr=0 pr=0 pw=0 time=0 us)
                          0            BITMAP AND  (cr=0 pr=0 pw=0 time=0 us)
                          0             BITMAP INDEX SINGLE VALUE BIMAP_FRODETL_TIMKEY PARTITION: 1 38 (cr=0 pr=0 pw=0 time=0 us)(object id 239143)
                          0             BITMAP INDEX SINGLE VALUE BIMAP_FRODETL_ROLOCKEY PARTITION: 1 38 (cr=0 pr=0 pw=0 time=0 us)(object id 239104)
                          0         TABLE ACCESS BY LOCAL INDEX ROWID F_RO_DETL PARTITION: 1 1 (cr=0 pr=0 pw=0 time=0 us cost=1855 size=23 card=1)
                          0     INDEX RANGE SCAN I2_USERGEO (cr=0 pr=0 pw=0 time=0 us cost=1 size=15 card=1)(object id 67190)
                          2    FILTER  (cr=4468080 pr=261425 pw=0 time=443408096 us)
                       1288     HASH JOIN  (cr=139667 pr=126008 pw=0 time=4000581 us cost=52220 size=4006171 card=11479)
                      19458      TABLE ACCESS FULL D_LOCN (cr=256 pr=253 pw=0 time=16277 us cost=119 size=583740 card=19458)
                       1288      HASH JOIN  (cr=139411 pr=125755 pw=0 time=4373998 us cost=52100 size=3661801 card=11479)
                      19458       TABLE ACCESS FULL D_LOCN (cr=256 pr=0 pw=0 time=16404 us cost=119 size=1264770 card=19458)
                       1288       HASH JOIN  (cr=139155 pr=125755 pw=0 time=4366662 us cost=51979 size=2915666 card=11479)
                        108        TABLE ACCESS FULL R_COMP_CODES (cr=3 pr=2 pw=0 time=107 us cost=3 size=1944 card=108)
                       1288        HASH JOIN  (cr=139152 pr=125753 pw=0 time=4358039 us cost=51975 size=2711404 card=11489)
                      39375         HASH JOIN  (cr=135056 pr=121348 pw=0 time=1102216 us cost=46619 size=2596514 card=11489)
                      39894          HASH JOIN  (cr=80852 pr=67151 pw=0 time=13045906 us cost=23719 size=2715734 card=13378)
                      39894           HASH JOIN  (cr=26648 pr=12953 pw=0 time=59328 us cost=2993 size=1872920 card=13378)
                      39894            HASH JOIN  (cr=24518 pr=10829 pw=0 time=40040424 us cost=2159 size=1364556 card=13378)
                      19458             VIEW  index$_join$_020 (cr=93 pr=83 pw=0 time=121701 us cost=80 size=252954 card=19458)
                      19458              HASH JOIN  (cr=93 pr=83 pw=0 time=58371 us)
                      19458               INDEX FAST FULL SCAN LOCN_PK (cr=36 pr=32 pw=0 time=9919 us cost=38 size=252954 card=19458)(object id 67089)
                      19458               INDEX FAST FULL SCAN LOCN_DIM_KEY1 (cr=57 pr=51 pw=0 time=15641 us cost=61 size=252954 card=19458)(object id 67091)
                      39894             NESTED LOOPS  (cr=24425 pr=10746 pw=0 time=39905660 us)
                      39894              NESTED LOOPS  (cr=260 pr=39 pw=0 time=99732 us cost=2077 size=1190642 card=13378)
                          1               TABLE ACCESS BY INDEX ROWID D_LOCN (cr=3 pr=2 pw=0 time=0 us cost=2 size=12 card=1)
                          1                INDEX RANGE SCAN D_LOCN_DIM_KEY4 (cr=2 pr=2 pw=0 time=0 us cost=1 size=0 card=1)(object id 67088)
                      39894               PARTITION RANGE ITERATOR PARTITION: 14 27 (cr=257 pr=37 pw=0 time=67127 us)
                      39894                BITMAP CONVERSION TO ROWIDS (cr=257 pr=37 pw=0 time=33499 us)
                         14                 BITMAP AND  (cr=257 pr=37 pw=0 time=0 us)
                         15                  BITMAP INDEX SINGLE VALUE BIMAP_FRODETL_CHRGLOCKEY PARTITION: 14 27 (cr=43 pr=37 pw=0 time=8 us)(object id 239065)
                         93                  BITMAP MERGE  (cr=214 pr=0 pw=0 time=180402 us)
                        395                   BITMAP INDEX RANGE SCAN BIMAP_FRODETL_PARTKEY PARTITION: 14 27 (cr=214 pr=0 pw=0 time=3877 us)(object id 239182)
                      39894              TABLE ACCESS BY LOCAL INDEX ROWID F_RO_DETL PARTITION: 1 1 (cr=24165 pr=10707 pw=0 time=0 us cost=2077 size=988757 card=12841)
                     219546            TABLE ACCESS FULL D_CUSTOMER (cr=2130 pr=2124 pw=0 time=184361 us cost=831 size=8342710 card=219545)
                    3973804           TABLE ACCESS FULL D_VEHICLE (cr=54204 pr=54198 pw=0 time=5266703 us cost=20670 size=250345242 card=3973734)
                     952649          TABLE ACCESS FULL D_VEHICLE (cr=54204 pr=54197 pw=0 time=1851923 us cost=20907 size=29642492 card=1288804)
                    2084950         INDEX FAST FULL SCAN F_VEH_LOCN_IND3 (cr=4096 pr=4405 pw=0 time=1064298 us cost=2348 size=31313190 card=3131319)(object id 67080)
                          2     FILTER  (cr=16598268 pr=1945847 pw=0 time=0 us)
                        801      SORT GROUP BY NOSORT (cr=16598268 pr=1945847 pw=0 time=0 us cost=2378 size=24 card=1)
                       6399       PARTITION RANGE ITERATOR PARTITION: 14 27 (cr=16599181 pr=1946620 pw=0 time=197448384 us cost=2378 size=24 card=1)
                       6399        TABLE ACCESS BY LOCAL INDEX ROWID F_RO_DETL PARTITION: 14 27 (cr=16599507 pr=1946895 pw=0 time=164950400 us cost=2378 size=24 card=1)
                    35495868         BITMAP CONVERSION TO ROWIDS (cr=206223 pr=1634 pw=0 time=25491372 us)
                      11213          BITMAP AND  (cr=206223 pr=1634 pw=0 time=0 us)
                      11864           BITMAP INDEX SINGLE VALUE BIMAP_FRODETL_ROLOCKEY PARTITION: 14 27 (cr=34891 pr=1635 pw=0 time=852501 us)(object id 239104)
                      74288           BITMAP MERGE  (cr=171334 pr=0 pw=0 time=144184416 us)
                     316170            BITMAP INDEX RANGE SCAN BIMAP_FRODETL_PARTKEY PARTITION: 14 27 (cr=171336 pr=0 pw=0 time=2059378 us)(object id 239182)
                          2     INDEX RANGE SCAN I2_USERGEO (cr=2 pr=2 pw=0 time=0 us cost=1 size=15 card=1)(object id 67190)
                    
                    ********************************************************************************
                    • 7. Re: sql query is hung, doesn't comes back
                      EdStevens
                      978044 wrote:
                      Thank you, i will make sure to use code tags going forward. I don't have any experience on query tuning, this is the first one i am trying to work on. i will check with the developer for the questions regarding literals, but i checked that partition_key is a number datatype.
                      <snip>

                      If the partition_key is a number, then the fact that a date is being manipulated to compare it to, that suggests a fundamental design flaw. DATEs should be stored as DATES, not NUMBER and not VARCHAR or CHAR.