views:

55

answers:

1

I asked this question before but from comments I realized that the question was not enough information in the question to get help so here I am providing the whole query + explain plan:

Query:

WITH gtt_1 AS  
  (SELECT 
   r.user_id, r.role_id, r.participant_code, MAX(status_id) 
  FROM 
    user_role r, 
    cmp_role c 
  WHERE 
    r.role_id = c.role_id 
    AND r.participant_code IS NOT NULL 
    AND c.group_id = 3 
    GROUP BY 
    r.user_id, r.role_id, r.participant_code 
    HAVING MAX(status_id)  IN (SELECT b.status_id FROM USER_ROLE b 
                              WHERE (b.ACTIVE = 1 OR ( b.ACTIVE IN ( 0,3 )  
                                     AND SYSDATE BETWEEN b.effective_from_date AND b.effective_to_date 
                                    )) 
                             ) 
  ) 
SELECT c.role_id, 
       c.subgroup, 
       c.subgroup_description, 
       COUNT(a.USER_ID) user_count 
FROM    
    (SELECT b.user_id, b.role_id FROM gtt_1 b, user e 
        WHERE  e.user_id = RTRIM(b.user_id) 
       ) a 
RIGHT OUTER JOIN CMP_ROLE c ON a.role_id = c.role_id 
WHERE c.group_id = 3
GROUP BY c.role_id,c.subgroup,c.subgroup_description 
ORDER BY c.subgroup; 

Tables used:

SQL> select count(*) from user_role;

  COUNT(*)
----------
    803513

SQL> select count(*) from cmp_role;

  COUNT(*)
----------
        27

SQL> select count(*) from user;

  COUNT(*)
----------
     73893

Explain plan:

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 269840545

--------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                           | Name               | Rows  | Bytes | Cost (%CPU)| Time     | Inst   |IN-OUT|
--------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                    |                    |   256 | 18944 |  1683   (4)| 00:00:21 |        |      |
|   1 |  SORT GROUP BY                      |                    |   256 | 18944 |  1683   (4)| 00:00:21 |        |      |
|*  2 |   HASH JOIN OUTER                   |                    |  2776 |   200K|  1682   (4)| 00:00:21 |        |      |
|   3 |    TABLE ACCESS BY INDEX ROWID      | ROLE               |     8 |   504 |     2   (0)| 00:00:01 |        |      |
|*  4 |     INDEX RANGE SCAN                | N_ROLE_IDX2        |     8 |       |     1   (0)| 00:00:01 |        |      |
|   5 |    VIEW                             |                    |  9370 |   100K|  1683   (5)| 00:00:21 |        |      |

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------
|   6 |     NESTED LOOPS                    |                    |  9370 |   164K|  1683   (5)| 00:00:21 |        |      |
|   7 |      VIEW                           |                    |  9370 |   100K|  1677   (4)| 00:00:21 |        |      |
|*  8 |       FILTER                        |                    |       |       |            |          |        |      |
|   9 |        HASH GROUP BY                |                    |  9370 |   283K|  1677   (4)| 00:00:21 |        |      |
|* 10 |         HASH JOIN                   |                    |   232K|  7051K|  1654   (3)| 00:00:20 |        |      |
|  11 |          TABLE ACCESS BY INDEX ROWID| ROLE               |     8 |    56 |     2   (0)| 00:00:01 |        |      |
|* 12 |           INDEX RANGE SCAN          | N_ROLE_IDX2        |     8 |       |     1   (0)| 00:00:01 |        |      |
|* 13 |          TABLE ACCESS FULL          | USER_ROLE          |   786K|    17M|  1643   (2)| 00:00:20 |        |      |
|* 14 |        TABLE ACCESS BY INDEX ROWID  | USER_ROLE          |     1 |    30 |     3   (0)| 00:00:01 |        |      |
|* 15 |         INDEX UNIQUE SCAN           | U_USER_ROLE_IDX1   |     1 |       |     2   (0)| 00:00:01 |        |      |
|  16 |      REMOTE                         | MV_P113PT_USER_HUB |     1 |     7 |     0   (0)| 00:00:01 | HWVAU~ | R->S |



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

   2 - access("A"."ROLE_ID"(+)="ROLE"."ROLE_ID")
   4 - access("ROLE"."GRP_ID"=3)
   8 - filter( EXISTS (SELECT 0 FROM "P181"."USER_ROLE" "USER_ROLE" WHERE "USER_ROLE"."STTS_ID"=:B1 AND
              (TO_NUMBER("USER_ROLE"."ACTV_CD")=1 OR (TO_NUMBER("USER_ROLE"."ACTV_CD")=0 OR TO_NUMBER("USER_ROLE"."ACTV_CD")=3)
              AND "USER_ROLE"."EFCTV_TO_DTTM">=SYSDATE@! AND "USER_ROLE"."EFCTV_FROM_DTTM"<=SYSDATE@!)))
  10 - access("USER_ROLE"."ROLE_ID"="ROLE"."ROLE_ID")

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------
  12 - access("ROLE"."GRP_ID"=3)
  13 - filter("USER_ROLE"."PRTPT_CD" IS NOT NULL)
  14 - filter(TO_NUMBER("USER_ROLE"."ACTV_CD")=1 OR (TO_NUMBER("USER_ROLE"."ACTV_CD")=0 OR
              TO_NUMBER("USER_ROLE"."ACTV_CD")=3) AND "USER_ROLE"."EFCTV_TO_DTTM">=SYSDATE@! AND
              "USER_ROLE"."EFCTV_FROM_DTTM"<=SYSDATE@!)
  15 - access("USER_ROLE"."STTS_ID"=:B1)

I do not have enough priveleges for tkprof

A: 

Does following query produce the same results?

SELECT  c.role_id
        , c.subgroup
        , c.subgroup_description
        , COUNT(a.USER_ID) user_count 
FROM    cmp_role c
        LEFT OUTER JOIN (
          SELECT  r.user_id
                  , r.role_id 
          FROM    (
                    SELECT  r.user_id
                            , r.role_id
                            , r.participant_code
                            , MAX(r.status_id) 
                    FROM    user_role r
                            INNER JOIN cmp_role c ON c.role_id = r.role_id
                    WHERE   r.participant_code IS NOT NULL 
                            AND c.group_id = 3 
                    GROUP BY 
                            r.user_id
                            , r.role_id
                            , r.participant_code 
                    HAVING  MAX(r.status_id) IN (
                              SELECT  status_id 
                              FROM    user_role
                              WHERE   (Active = 1 
                                      OR (Active IN ( 0,3 ) 
                                          AND SYSDATE BETWEEN effective_from_date AND effective_to_date)
                                        )
                            ) 
                  ) r
                  INNER JOIN user e ON e.user_id = RTRIM(r.user_id)        
        ) a ON a.role_id = c.role_id         
WHERE   c.group_id = 3
GROUP BY 
        c.role_id
        , c.subgroup
        , c.subgroup_description 
ORDER BY 
        c.subgroup

It is easier to read (for me). If it produces the same results, you can already start with dropping r.participant_codeand MAX(r.status_id)from the inner select.

Lieven
results are off slightly but not by much though.
Mehur