CODEWITHSUNDEEP
sqloracle-databasejoin
dangelsaurus
dangelsaurus

Reputation: 7532

Oracle SQL '+ 0' syntax for join?

I was using Toad's SQL optimizer and it came up with the following addition to my join statements..

instead of say

emp.emplid = dept.emplid

it suggested

emp.emplid = dept.emplid + 0

What does the '+ 0' do? I've searched for the past hour online and I cannot find anything. I know the (+) meaning, but I've never seen anything like this.

Upvotes: 2

Views: 4906

Answers (2)

josecomares
josecomares

Reputation: 11

What the ' + 0' does is to indicate to the optimizer that it should use another index. In other words, I'm pretty sure that one of these two fields (mp.emplid = dept.emplid) has, in addition to the foreign key, also another index specific field. As well, the + 0 cancels the index that takes by default optimizer (the foreign key) and tells it to choose another index.

Upvotes: 1

Sodved
Sodved

Reputation: 8588

The + 0 does what it looks like. It adds 0 to dept.emplid. But from a performance point of view this does make a difference. By turning that into an expression Oracle is not able to use any index on dept.emplid.

So if Oracle is choosing an index on dept.emplid but you would rather it used a different index/plan, then adding + 0 is a way to influence the optimiser, as there is not longer a match on that particular column. Any expression would have done the trick.

The other way to go about this would be to get into optimiser hints. Although this can be a bit of a pain for big queries.

Upvotes: 7

Related Questions