Understanding Rules-of-Thumb:- Part - 2 ----------------------------------------------- Use NOT EXISTS instead of NOT IN:- ------------------------------------------- Using NOT EXISTS instead of NOT IN adds a limiting condition to your queries that can reduce the number of full table scans necessary. The following example uses a NOT IN clause to find names and department IDs in the DEPARTMENT table where the department ID does not also exist in the EMPLOYEE table: - SELECT name, department_id FROM department WHERE department_id NOT IN (SELECT department_id FROM employee) Because NOT IN does not use a limiting condition, Oracle will perform a full table scan of DEPARTMENT. For each record in DEPARTMENT, the subquery will be executed. Since the subquery has no limiting WHERE clause, it will perform a full table scan for every record in the full table scan of DEPARTMENT. Instead, NOT EXISTS can be used so that nested index scans will be used in the subquery for each row in the DEPARTMENT table. The logic of the NOT EXISTS clause tells Oracle not to return the row if it finds a match in both tables. The only records that will be returned from DEPARTMENT are those that return no rows from the subquery, and no full table scans are performed by the subquery. The following statement, therefore, is more efficient than the previous example. SELECT name, department_id FROM department, WHERE NOT EXISTS (SELECT department_id FROM employee WHERE department.department_id=employee.department_id) Use NOT EXISTS or NOT IN with hints instead of MINUS:- ------------------------------------------------------------------ MINUS returns the set of rows from one query that is not present in the set of rows returned by a second query. Rewriting queries using NOT EXISTS or NOT IN can enable them to take advantage of indexes, reducing the number of full table scans a clause may require. In some cases, Oracle SQL Analyze might determine that because a hash anti-join (HASH_AJ) usually does not require a sort, it will produce better results than MINUS. The following query, for example, matches names and birthdates in the EMPLOYEE table with those in the STOCKHOLDER table, then returns the names and birthdates of employees who are not stockholders. Because MINUS does not use indexes, Oracle will use two full table scans and perform a sort on each table before the MINUS operation can be performed. SELECT birth_date, last_name, first_name FROM employee MINUS SELECT birth_date, last_name, first_name FROM stock_holder If the statement is re-written using NOT EXISTS, Oracle can use nested index scans in the subquery for rows in the primary statement. -------------- SELECT birth_date, last_name, first_name FROM employee WHERE NOT EXISTS (SELECT 1 FROM stock_holder WHERE stock_holder.birth_date = employee.birth_date AND stock_holder.first_name = employee.first_name) If Oracle SQL Analyze determines that a hash anti-join will produce better results, the example query could be rewritten to use two full table scans and an anti-join algorithm to join the rows, instead of performing sort and minus operations. ------------------------------------------ SELECT birth_date, last_name, first_name FROM employee WHERE (birth_date, last_name, first_name)NOT IN (SELECT /*+ hash_aj (stock_holder) */ birth_date, last_name, first_name FROM stock_holder)
Posted on: Fri, 23 Aug 2013 09:41:10 +0000
Trending Topics
Recently Viewed Topics
© 2015