2

I have an oracle query that works like this:

SELECT * FROM VW_REQUIRED r JOIN VW_ACTUAL a ON a.person_id = r.person_id AND a.target_id = r.target_id

Required is a view detailing all required training materials, Actual is a view detailing the most recent taking of the given courses. Each of these queries by themselves take under 2 seconds to generate between 10k and 100k rows.

What I want to do is something like:

SELECT * FROM VW_REQUIRED r WHERE NOT EXISTS ( SELECT 1 FROM VW_ACTUAL a WHERE a.person_id = r.person_id AND a.target_id = r.target_id)

and it takes more than 20 seconds (I didn't let it finish, because that is obviously too long.

So then I decided to do something different, I made the original JOIN a left join so it will show me all required training, and the actual training only if it exists.

That worked, and was super fast still.

But I want a list of just the courses where there is no actual training attached (i.e. the people we need to kick into gear and get their training...)

When I try something like 

SELECT * FROM VW_REQUIRED r LEFT JOIN VW_ACTUAL a ON a.person_id = r.person_id AND a.target_id = r.target_id WHERE r.person_id = null

I get no rows back. I'm not sure I can filter out the rows where I have no actual results. Normally I'd use WHERE NOT EXISTS but the performance on it was super slow (and I don't think I can put an index on a view...)

I've managed to make some changes that works, but it seems hacky and I'm sure there's a better solution.

SELECT who, where_from, mand, target_id, grace_period, date_taken FROM ( SELECT r.person_id who, r.where_from where_from, r.mand mand, r.target_id target_id r.grace_period grace_period, nvl(a.date_taken, to_date('1980/01/01','yyyy/mm/dd')) date_taken FROM VW_REQUIRED r LEFT JOIN VW_ACTUAL a ON a.person_id = r.person_id AND a.target_id = r.target_id ) WHERE date_taken = to_date('1980/01/01','yyyy/mm/dd')
Improve this question
1
  • This is a terrible hack. The solution provided by@LajosVerdes is much cleaner and should even be faster. Are you 100% sure you tried the query as proposed by him (replacing the r.person_id with a.person_id and using IS NULL instead of = NULL) ? Commented May 13, 2014 at 9:11

2 Answers 2

Reset to default
3

I think you only mixed the table names. Can you change the last where to

a.person_id is null

?

So your query should like this:

SELECT * FROM VW_REQUIRED r LEFT JOIN VW_ACTUAL a ON a.person_id = r.person_id AND a.target_id = r.target_id WHERE a.person_id is null

Or maybe the "old" way?

SELECT * FROM VW_REQUIRED r, VW_ACTUAL a WHERE r.person_id = a.person_id(+) AND r.target_id = a.target_id(+) AND a.person_id is null
Improve this answer
Sign up to request clarification or add additional context in comments.

10 Comments

Either way, it displays nothing.
I think this is the problem. You want only the results where a.person_id is null (which means there is no related record in VW_ACTUAL)
i've managed to find a selection that works, but it still seems a bit hacky. I added it to my question.
This query looks correct. Although you say it displayed nothing, this has the key points: 1) left join 2) extra conditions on the joined table are in the join condition and not the where clause 3) the where clause has a test for null on a non-null column from the joined table. If you are getting nothing, check that you actually have data that should match and/or fine time the extra condition
@LajosVeres You forgot to mention that you fixed the second error, as well :-) (using = NULL instead of IS NULL)
|
0

Maybe your problem came from a bad plan in the non exist query.

could you please show us the plan for this query?

SELECT * FROM VW_REQUIRED r WHERE NOT EXISTS ( SELECT 1 FROM VW_ACTUAL a WHERE a.person_id = r.person_id AND a.target_id = r.target_id)

And try to change the join alorithm ( /+use_nl(a)/ or /+use_hash(a)/). I think it's a nested loop. Maybe you have to put a hash join in this example like this

SELECT * FROM VW_REQUIRED r WHERE NOT EXISTS ( SELECT /*+use_hash(a)*/ 1 FROM VW_ACTUAL a WHERE a.person_id = r.person_id AND a.target_id = r.target_id)
Improve this answer

3 Comments

You misspelled the hint (it's use_hash, not use_ash). I'd also be very hesitant in using hints unless absolutely necessary; they will come back to haunt you in the future.
You're right about mispell. I'll correct it. For your second assert (using or not hint), I know the official "rule" from Oralce inc (never use hint, Oracle do it better than you). But in real life, when it became to complex, it save our life many time;)
I want to explain why I want to do a hash join. corsiKa say's that the time response for the two view is less than 2 secondes. If it's true, the time response for the not exist query with a hash join must be somethin less than 4 seconds. With a nested loop, it could be instantaneous or infinite! If I'm right, then the problem change. We'll know a solution ( the hash join) but we have to understood why Oracle doesn't want to use it (or not if we are lazzy, just keep the hint)

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.