CODEWITHSUNDEEP
sqlplsqloracle11goracle-sqldeveloper
Esteban Rincon
Esteban Rincon

Reputation: 2110

Solving query performance issues

I've been having trouble knowing/understanding how to optimize the following query, I'm pretty new to performance hints so maybe someone could give a fiew pointers:

btw, I need to keep all info from left table (event_user) thus the left join but the cost is just outrageous! So when I call this query in SQL-Dev it just stays there 'loading' and takes ages and i don't know how to aproach this.

Query

SELECT event_user.*,
       dw_attendee.*
FROM event_user
    LEFT JOIN dw_attendee ON event_user.event_user_id = dw_attendee.event_user_id
    AND event_user.event_id = dw_attendee.event_id
    AND dw_attendee.session_id = 1
    AND event_user.event_id = :eventid;

Plan

-------------------------------------------------------------------------------------------------------------
| Id  | Operation             | Name        | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     | Pstart| Pstop |
-------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |             |   130M|    44G|       |  2754K  (2)| 10:42:44 |       |       |
|*  1 |  HASH JOIN RIGHT OUTER|             |   130M|    44G|  4513M|  2754K  (2)| 10:42:44 |       |       |
|*  2 |   TABLE ACCESS FULL   | DW_ATTENDEE |    57M|  3861M|       |   106K  (8)| 00:24:45 |       |       |
|   3 |   PARTITION RANGE ALL |             |   130M|    35G|       |   831K  (5)| 03:14:08 |     1 |  6044 |
|   4 |    TABLE ACCESS FULL  | EVENT_USER  |   130M|    35G|       |   831K  (5)| 03:14:08 |     1 |  6044 |
-------------------------------------------------------------------------------------------------------------

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

   1 - access("EVENT_USER"."EVENT_ID"="DW_ATTENDEE"."EVENT_ID"(+) AND 
              "EVENT_USER"."EVENT_USER_ID"="DW_ATTENDEE"."EVENT_USER_ID"(+) AND "EVENT_USER"."EVENT_ID"=CASE  WHEN 
              ("DW_ATTENDEE"."EVENT_USER_ID"(+) IS NOT NULL) THEN 2002317 ELSE 2002317 END )
   2 - filter("DW_ATTENDEE"."SESSION_ID"(+)=1)

My initial thought was to perform a query like the following, since each query performed separately is extreemely fast (cost 0)

with reg as (select * from event_user where event_id = :eventid),
atd as (select * from dw_attendee where event_id = :eventid)
select distinct reg.*, atd.*
from reg left join atd on reg.event_id = atd.event_id;

The above query is great in terms of cost (172) but my resultset from this query is too extensive, it is literaly repeating each row by 20.

My left table query:

select * from event_user where event_id = 2002317;

returns 30 rows, the with reg as... query returns 600! Each row*20, any pointers ? thank you

Upvotes: 0

Views: 37

Answers (1)

Steven
Steven

Reputation: 15318

in your query, the fact that you put the event_user.event_id = :eventid in the join condition, it means that you are not filtering event_user table. Just put it in the where and it will be faster. Otherwitse, you bring back the whole event_user table but try to join only for one particular event_id.

SELECT event_user.*,
       dw_attendee.*
FROM event_user
LEFT JOIN dw_attendee ON event_user.event_user_id = dw_attendee.event_user_id
                      AND event_user.event_id = dw_attendee.event_id
                      AND dw_attendee.session_id = 1
where event_user.event_id = :eventid;

Upvotes: 2

Related Questions