A query that performs slowly as a single query but in fractions of a second when done in two steps

Question

I have two views in an ORACLE DB:

The view USERS with columns:
UserId | GroupId | Details

where there's 1 record for each User, total 40.000, and there c.a. 1-30 users in each Group.

and

The view SUMMARY with columns:
UserId | Summary 

where there's 1 record for each User

The SUMMARY view is very complex but is still quite fast when i query it based on user. That is:

select * from SUMMARY where UserId='some_user_id'

performs in 0,1 sec

The USERS view is pretty simple (it's a union all of USERS_TYPE1 and USERS_TYPE2 tables) and a query of the type:

select * from USERS where GroupId='some_group_id'

performs in 0,02 sec

Now here's the catch, when I do:

select * from USERS JOIN SUMMARY 
ON USERS.UserId = SUMMARY.UserId
WHERE USERS.GroupId = 'some_group_id'

I get AWFUL performance of 90seconds - even though there are only 3 users in the group.

This should take only a fraction of a second if the user ID's are found first and then the SUMMARY table is queried with those user ID's. Is there a way I can hint the DB to do that?

I have indexes based on UserId, GroupId and (GroupId,UserId) on all underlying tables.

((This is pretty hard to reproduce In a simple manner since the complex view has a lot of underlying tables. I have a couple of versions of the SUMMARY view. In some cases (depending on how SUMMARY is built) it stops being awful when i materialize the Users view, but in other cases it does not.))

Answer 1

You could try

  SELECT * 
  FROM
    (select * from USERS
    WHERE USERS.GroupId = 'some_group_id') u,
    summary
  WHERE USERS.UserId = SUMMARY.UserId

Also specify exactly what columns you want, rather than select * (you obviously don't need both versions of the UserId)

It MAY be worth trying

  SELECT * 
  FROM
    (select users.*, rownum rn from USERS
    WHERE USERS.GroupId = 'some_group_id') u,
    summary
  WHERE USERS.UserId = SUMMARY.UserId

which will force it to evaluate the inline view first.