How can I Optimize this Query Taking 30 Seconds for 1746 Rows?

Question

Query:

SELECT A.USER_ID, A.ROLE_ID, C.SUBGROUP, MAX(A.STATUS_ID)
                 FROM USER_ROLE A, USER B, ROLE C
                WHERE A.ROLE_ID = C.ROLE_ID
                  AND C.GROUP_ID = 3
                  AND A.USER_ID = B.USER_ID
                  AND B.TEMPLATE_IND = 'N'
                  AND B.ONAP_PARTCODE IS NULL
                  AND A.PARTCODE ='005'
                GROUP BY A.PARTCODE,
                         A.USER_ID,
                         A.ROLE_ID,
                         C.SUBGROUP;

Explain plan:

--------------------------------------------------------------------------------
| Id  | Operation                      | Name               | Rows  | Bytes | Co
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT               |                    |     1 |    74 |
|   1 |  HASH GROUP BY                 |                    |     1 |    74 |
|   2 |   NESTED LOOPS                 |                    |     1 |    74 |
|   3 |    NESTED LOOPS                |                    |    56 |  3024 |
|   4 |     TABLE ACCESS BY INDEX ROWID| ROLE               |     8 |   240 |
|*  5 |      INDEX RANGE SCAN          | N_ROLE_IDX2        |     8 |       |
|   6 |     TABLE ACCESS BY INDEX ROWID| USER_ROLE          |     7 |   168 |
|*  7 |      INDEX RANGE SCAN          | N_USER_ROLE_IDX6   |     7 |       |
|   8 |    REMOTE                      | MV_PT_USER         |     1 |    20 |
--------------------------------------------------------------------------------

Is the `USER` table in the same database as `USER_ROLE` and `ROLE`? — Jeffrey Kemp, Jul 12 '10 at 05:13
Which version of the database? How fresh are the statitsics used in this plan? — APC, Jul 12 '10 at 09:44
Following from Jeffrey's comment, it looks like USER_ROLE is a remote table with a materialized view (MV_PT_USER) sitting on top of it. Is that correct? — Nick Pierpoint, Jul 12 '10 at 11:40

score 4 · Accepted Answer · answered Jul 12 '10 at 01:11

4

I re-wrote your query to use ANSI-92 syntax:

  SELECT A.USER_ID, A.ROLE_ID, C.SUBGROUP, MAX(A.STATUS_ID)
    FROM USER_ROLE a
    JOIN USER b ON b.user_id = a.user_id
               AND b.template_ind = 'N'
               AND b.onap_partcode IS NULL
    JOIN ROLE c ON c.role_id = a.role_id
               AND c.group_id = 3
   WHERE a.PARTCODE ='005'
GROUP BY a.USER_ID, a.ROLE_ID, c.SUBGROUP, a.PARTCODE;

It's not any faster, just more clear for me to suggest the following covering indexes:

CREATE INDEX ur_idx ON USER_ROLE (user_id, role_id, partcode) COMPUTE STATISTICS;
CREATE INDEX u_idx ON USER (user_id, template_ind) COMPUTE STATISTICS;
CREATE INDEX r_idx ON ROLE (role_id, group_id) COMPUTE STATISTICS;

answered Jul 12 '10 at 01:11

OMG Ponies

325,700
82
523
502

Note: I didn't specify `USER.onap_partcode` because IIRC an index is useless for nulls... – OMG Ponies Jul 12 '10 at 01:24
1

If the values of all columns in the index are null, it isn't indexed. However if any column is not null, then the row will be represented in the index. So you could add onap_partcode to the index on USER – Gary Myers Jul 12 '10 at 02:15
@Gary: Thx - hopefully I'll remember next time :) – OMG Ponies Jul 12 '10 at 02:20
The original syntax is ISO/ANSI SQL-92 syntax too! – onedaywhen Jul 12 '10 at 08:20
@onedaywhen: No, the original query provided by the OP uses ANSI-89 join syntax. It's supported for backwards compatibility in ANSI-92, but doesn't provide outer join syntax. – OMG Ponies Jul 12 '10 at 15:24
"It's supported for backwards compatibility in ANSI-92" -- if it's not out then it's in. The old outer joins were kicked out. This syntax will never be deprecated in SQL (Standard, Oracle, SQL Server, etc). If you work with other people you will encounter this syntax, so you may as well learn it, make peace with it :) – onedaywhen Jul 13 '10 at 08:49

score 2 · Answer 2 · answered Jul 12 '10 at 02:19

The ROWS estimate in the plan ends with 1, not anywhere near 1700. The 'remote' operation on MV_PT_USER is interesting. It appears that the optimizer is assuming one (or maybe zero) rows will be returned by that operation, even though there is no filtering.

Gathering some stats on that object may tell oracle how many rows it is likely to return, and suggest a different plan to the optimizer.

score 0 · Answer 3 · answered Sep 27 '12 at 03:40

0

I think you better study what is inner join , right join and left join

If all table has the common field and not null , then you may consider inner join which deal with the least number of rows

answered Sep 27 '12 at 03:40

Jeff Bootsholz

2,971
15
70
141

score 0 · Answer 4 · answered Jul 12 '10 at 01:10

0

Without knowing details about your table schema and indices, it would be hard to give decent advice.

It's probably won't help much, but I do see that the A.PARTCODE in your GROUP BY clause isn't needed.

answered Jul 12 '10 at 01:10

Aheho

12,622
13
54
83

I admit I'm curious about the group by, but it won't return an error. – OMG Ponies Jul 12 '10 at 01:13

score 0 · Answer 5 · answered Jul 12 '10 at 09:26

Your query takes 30 seconds. Now we need to know where those 30 seconds are spent on. The provided information gives close to nothing, so it's time to trace a run of the query.

Here is some quick information on how to gather the needed information: http://forums.oracle.com/forums/thread.jspa?messageID=1812597

Regards, Rob.

How can I Optimize this Query Taking 30 Seconds for 1746 Rows?

5 Answers5