Re: Performance Difference Between != and <>?

Might it be bind variable peeking in action? The certain effect of writing != instead of <> is to force a re-parse. If at the first execution the values for :id were different and if you have an histogram on claws_doc_id it could be a reason.
And if you tell me that claws_doc_id is the primary key, then I'll ask you what is the purpose of counting, in particular when the query in the EXISTS clause is uncorrelated with the outer query and will return the same result whatever :id is.
Looks like a polling query. The code surrounding it must be interesting.

HTH Stéphane Faroult
RoughSea Ltd

Scott Canaan wrote:
>
> Does anyone know why there’s a big performance difference when using
> != vs. <>? I was just looking at a query that was written both ways
> and there is a big difference in how long it takes to return data. The
> query is:
>
> Select count(*) from claws_doc_table where claws_doc_id = :id and
> exists (select 1 from claws_person_id where status != 0);
>
> If you use !=, it returns sub-second. If you use <>, it takes 7
> seconds to return. Both return the right answer. I’ve looked in the
> Oracle documentation and can’t find anything that would explain this.
> The documentation says that they are interchangeable.
>
> We are running Oracle 10.2.0.2 on Solaris 10, 64-bit.
>
> Thank you,
>
> Scott Canaan '88 (Scott.Canaan_at_rit.edu)
>
> (585) 475-7886
>
> "Life is like a sewer, what you get out of it depends on what you put
> into it." - Tom Lehrer.
>

--
http://www.freelists.org/webpage/oracle-l