Oracle slow join with view

I’m using the following query to find a monetary transaction for a person.

SELECT
  PERSON.NAME,
  MONETARY_TRANSACTION.DESCRIPTION
FROM PERSON
LEFT JOIN MONETARY_TRANSACTION
  ON PERSON.ID = MONETARY_TRANSACTION.PERSON_ID
WHERE PERSON.ID = 5
AND ROWNUM <= 1

1.5 min.

If I run them seperately:

SELECT NAME FROM PERSON WHERE PERSON.ID = 5 AND ROWNUM <=1;

0.158 sec.

SELECT DESCRIPTION FROM MONETARY_TRANSACTION WHERE PERSON_ID = 5 AND ROWNUM <= 1;

5.423 sec.

I’m guessing it’s taking so long because the join happens before the where clause and each join the view gets recalculated (2.8 million people rows).

The view is MONETARY_TRANSACTION

Answer

It’s difficult to say why this is happening without knowing how complex the SQL in the view is, but a few things to try would be:

a. Run an explain plan to conform your diagnosis.

b. Update statistics on the tables being queried.

c. Try using a subquery instead of a left join and see if this hints the optimizer to do what you want. Something like:

SELECT
  PERSON.NAME,
  (select MONETARY_TRANSACTION.DESCRIPTION from MONETARY_TRANSACTION where PERSON.ID = MONETARY_TRANSACTION.PERSON_ID)
FROM PERSON
WHERE PERSON.ID = 5
AND ROWNUM <= 1

d. Try using limit 1 instead of the rownum comparison. I believe Oracle supports that.