SQL conditions - use of NOT
I administer a multi terabyte ODS on 9i release 2. The database recieves a lot of AD HOC sql, and as a result I get numerous inquiries as to what a given process is doing. Often these long running queries have high costs associated with them. Somtimes it is obvious what drives the up the cost of the query, somtimes not so. Often I see high cost queries associated with SQL statements using NOT logic. When I see this condition, I suggest that the user attempt to eliminate the NOT logic and find some other way to perform their SQL. I have heard in the past that NOT, NOT EXISTS, NOT IN, != should be avoided at all costs as their use will always produce high cost, slow running queries. Is there any validity to this claim? and if so, why?