Sometimes a column and a range of values are compared. The easiest way is to use subqueries in the WHERE clause. Two formats can be used in the WHERE clause. The first format is to use IN operators: ... WHERE column in (Select * from ... where ...); second format is using an exist operator: ... WHERE EXISTS (SELECT 'X' from " ... WHERE ...); I believe most people will use the first format because it is more likely to write, and the second format is much higher than the first format. In Oracle, you can rewrite almost all IN operator sub-queries to use the subquery using Exists. In the second format, the subquery starts with 'SELECT' X '. Use the EXISTS clause No, no matter what data extracted from the table, it only views the WHERE clause. Such an optimizer does not have to traverse the entire table, and the work can be done based on the index (here it assumes an index in the column used in the WHERE statement). Relative to the in clause, exists uses a linked sub-query to construct some difficulties than in sub child. By using the exist, the Oracle system will first check the main query, then run the sub-query until it finds the first match, which saves time. When the IN subquery is executed, the Oracle System first performs sub-queries and stores the result list in a temporary table added in an index. Before executing subqueries, the system will hang the primary query first. When the sub-query is executed, the primary query is stored in the temporary table. This is why using Exists is much faster than using IN usually queries. At the same time, NOT EXISTS should be used as much as possible to replace Not in, although both NOTs (can not use indexes), NOT EXISTS is higher than NOT inquiry efficiency.
copy from http://www.chinaunix.net Author: gunguymadman007
