Data

SQL Query:

    
/* Multiples left joins strategy
Select...
left join (....) AUTH_1 ON AUTH_1.RESOURCE_ID_ = RES.PROC_DEF_KEY_
left join (....) AUTH_2 ON AUTH_2.RESOURCE_ID_ = D.KEY_
left join (...)  AUTH_3 ON AUTH_3.RESOURCE_ID_ = '*'
WHERE ...
and ( (RES.CASE_EXECUTION_ID_ IS NOT NULL) OR (AUTH_1.RESOURCE_ID_ IS NOT NULL) OR (AUTH_2.RESOURCE_ID_ IS NOT NULL) OR (AUTH_3.RESOURCE_ID_ IS NOT NULL))
*/
EXPLAIN select count(distinct RES.ID_ )
    from ACT_RU_TASK RES
          left join
      ACT_RE_PROCDEF D on RES.PROC_DEF_ID_ = D.ID_
     left join 
    (
    SELECT A.*
    FROM ACT_RU_AUTHORIZATION A
    WHERE A.TYPE_ < 2     
    AND ( A.USER_ID_ in ( 'kermit', '*')           
      OR A.GROUP_ID_ IN  (  'Gerentes' , 'Atendentes' , 'AtendentesSAC' , 'Bko-formalizacao' ) 
    )
      AND (
         (  
          A.RESOURCE_TYPE_ = 7 AND A.PERMS_ & 2 = 2
         OR 
          A.RESOURCE_TYPE_ = 6 AND A.PERMS_ & 64 = 64
         ) 
      )
    ) AUTH_1 ON (AUTH_1.RESOURCE_ID_ = RES.ID_  )
     left join 
    (
    SELECT A.*
    FROM ACT_RU_AUTHORIZATION A
    WHERE A.TYPE_ < 2     
    AND ( A.USER_ID_ in ( 'kermit', '*')           
     
      OR A.GROUP_ID_ IN  (  'Gerentes' , 'Atendentes' , 'AtendentesSAC' , 'Bko-formalizacao' ) 
     
    )
      AND (
         (  
          A.RESOURCE_TYPE_ = 7 AND A.PERMS_ & 2 = 2
         OR 
          A.RESOURCE_TYPE_ = 6 AND A.PERMS_ & 64 = 64
         ) 
      )
    ) AUTH_2 ON (AUTH_2.RESOURCE_ID_ = D.KEY_  )
     left join 
    (
    SELECT A.*
    FROM ACT_RU_AUTHORIZATION A
    WHERE A.TYPE_ < 2     
    AND ( A.USER_ID_ in ( 'kermit', '*')           
      OR A.GROUP_ID_ IN  (  'Gerentes' , 'Atendentes' , 'AtendentesSAC' , 'Bko-formalizacao' ) 
    )
      AND (
         (  
          A.RESOURCE_TYPE_ = 7 AND A.PERMS_ & 2 = 2
         OR 
          A.RESOURCE_TYPE_ = 6 AND A.PERMS_ & 64 = 64
         ) 
      )
    ) AUTH_3 ON (AUTH_3.RESOURCE_ID_  = '*' )
and ( (RES.CASE_EXECUTION_ID_ IS NOT NULL) OR (AUTH_1.RESOURCE_ID_ IS NOT NULL) OR (AUTH_2.RESOURCE_ID_ IS NOT NULL) OR (AUTH_3.RESOURCE_ID_ IS NOT NULL))

id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	RES	NULL	ALL	NULL	NULL	NULL	NULL	10981	100.00	NULL
1	SIMPLE	D	NULL	ALL	PRIMARY	NULL	NULL	NULL	3	100.00	Using where; Using join buffer (Block Nested Loop)
1	SIMPLE	A	NULL	ref	ACT_UNIQ_AUTH_USER,ACT_UNIQ_AUTH_GROUP,ACT_IDX_AUTH_GROUP_ID,ACT_IDX_AUTH_RESOURCE_ID	ACT_IDX_AUTH_RESOURCE_ID	768	camunda.RES.ID_	1	100.00	Using where
1	SIMPLE	A	NULL	ref	ACT_UNIQ_AUTH_USER,ACT_UNIQ_AUTH_GROUP,ACT_IDX_AUTH_GROUP_ID,ACT_IDX_AUTH_RESOURCE_ID	ACT_IDX_AUTH_RESOURCE_ID	768	camunda.D.KEY_	1	100.00	Using where
1	SIMPLE	A	NULL	ref	ACT_UNIQ_AUTH_USER,ACT_UNIQ_AUTH_GROUP,ACT_IDX_AUTH_GROUP_ID,ACT_IDX_AUTH_RESOURCE_ID	ACT_IDX_AUTH_RESOURCE_ID	768	const	1	100.00	Using where