NOT IN performs indeed bad in situations where you get many items in the NOT IN list. If there are few, it's absolutely ok to use.

Replacing NOT IN or NOT EXISTS with an outer join is worse. Outer joins need more resources than exist/in relations, since they force an active join. Only if you need to join the outer table again for getting the IN or EXISTS list there might be a performance gain with using a well-formed outer join.

EXISTS and NOT EXISTS have the pro that they dismiss the results, and concentrate on the boolean value, which allows for many internal optimizations, and in particular for making use of otherwise unused, small indexes, if existing. However, the need to use a join in EXISTS or NOT EXISTS can render that more costly, if not well-formed and bad indexed.

0

Featured Post

Disaster Recovery as a Service is one go-to solution that revolutionizes DR planning. Implementing DRaaS could be an efficient process, easily accessible to non-DR experts. Learn about monitoring, testing, executing failovers and failbacks to ensure a "healthy" DR environment.

This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.

This video shows information on the Oracle Data Dictionary, starting with the Oracle documentation, explaining the different types of Data Dictionary views available by group and permissions as well as giving examples on how to retrieve data from th…