Optimizing Oracle Query

Question

Running explain plan on this query I am getting Full table Access.

Two tables used are:

user_role:   803507 rows
cmp_role:    27 rows

Query:

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 
                                    )) 
                             )

How can I better write this query so that it returns results in a decent time. Following are the indexes:

idx 1 = role_id
idx 2 = last_updt_user_id
idx 3 = actv_id, participant_code, effective_from_Date, effective_to_date
idx 4 = user_id, role_id, effective_from_Date, effective_to_date
idx 5 = participant_code, user_id, roke_id, actv_cd

Explain plan:

Q_PLAN
--------------------------------------------------------------------------------
  SELECT STATEMENT
    FILTER
      HASH GROUP BY
        HASH JOIN
          TABLE ACCESS BY INDEX ROWID ROLE
            INDEX RANGE SCAN N_ROLE_IDX2
          TABLE ACCESS FULL USER_ROLE
      TABLE ACCESS BY INDEX ROWID USER_ROLE
        INDEX UNIQUE SCAN U_USER_ROLE_IDX1
    FILTER
      HASH GROUP BY
        HASH JOIN
          TABLE ACCESS BY INDEX ROWID ROLE
            INDEX RANGE SCAN N_ROLE_IDX2
          TABLE ACCESS FULL USER_ROLE
      TABLE ACCESS BY INDEX ROWID USER_ROLE
        INDEX UNIQUE SCAN U_USER_ROLE_IDX1

I do not have enough priveleges to run stats on the table

Tried the following changes but it shaves off 1 or 2 seconds only:

WITH CTE AS (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 
       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 * from CTE)

Answer 1

1. Collect statistics for the tables
2. explain plan for the query and show the results.

Answer 2

Firstly you have the subquery

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 )
      )

There is no way that you can do anything other than a full table scan to get that result. You may be missing a join, but not knowing what you expect your query to do, there's no way for us to tell.

Secondly, depending on the proportion of cmp_role records with a group_id of 3, and the proportion of user_role than match those roles, it may be better off doing the full scan there. If, say, 3 out of the 27 cmp_role records are in group 3, and 100,000 of the user_role records match those cmp_role records, then it can be more efficient doing a single scan of the table than doing 100,000 index lookups.

Answer 3

I think the following approach will work.I would have thought the subquery will be evaluated only once since it is not correlated - this doesnt seem to be the case.I tried a similar query (simple) against sales table in sh demo schema. I modified it to use a Materialized CTE approach and it ran in 1 second as opposed to 18 sec. See below for the approach.This was 10 times faster

with cte as (
select /*+materialize*/  max(amount_sold) from sales)
select prod_id,sum(amount_sold) from
sales
group by prod_id
having max(amount_sold) in(
select * from cte)
/

So in you case you materialize the subquery as

with CTE as (
    SELECT /*+ materialize */ 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 
                                        )) 
                                 )
)

and select FROM CTE in main query

Answer 4

So you have a query that currently takes 16,5 seconds and you want it to run faster. To do that, you need to know where those 16,5 seconds are spent on. The Oracle database is extremely well instrumented, so you can see in great detail what it is doing. You can check it this thread that I wrote on OTN Forums:

http://forums.oracle.com/forums/thread.jspa?messageID=1812597

Without knowing where your time is being spent, all efforts are just guesses ...

Regards, Rob.