在SQL优化中，经常会有人问IN,NOT IN,NOT EXIST, EXIST的区别，会问IN 和 EXIST, NOT IN 和 NOT EXIST做比较哪些会更快些。 以前在RBO时代也许他们还是有区别的，但是现在oracle都是用CBO来处理SQL了，应该不会有区别。 但这只是猜想，还是要试试看才行。
首先，作比较的话 应该是这样比较 IN 和 EXIST一对儿作比较， NOT IN , NOT EXIST 比较 ，我们先看看IN , EXIST。 其他的文章再讨论 NOT IN 和 NOT EXIST
IN && EXIST
用in 和 exist的情况一般都是下面这样。
SELECT * FROM t1 WHERE x IN ( SELECT y FROM t2);
SELECT * FROM t1 WHERE EXISTS ( SELECT NULL FROM t2 WHERE y=t1.x);
根据ASK TOM 上的回答 （http://asktom.oracle.com/pls/asktom/f?p=100:11:::::P11_QUESTION_ID:953229842074）这两者的区别如下。
SELECT * FROM t1 WHERE x IN ( SELECT y FROM t2);
的执行模式应该是：
select * from t1, ( select distinct y from t2 ) t2 where t1.x = t2.y;
The subquery is evaluated, distinct'ed, indexed (or hashed or sorted) and then joined to
the original table -- typically.
SELECT * FROM t1 WHERE EXISTS ( SELECT NULL FROM t2 WHERE y=t1.x);
的执行模式应该是：
for x in ( select * from t1 ) loop if ( exists ( select null from t2 where y = x.x ) then OUTPUT THE RECORD end if end loop
It always results in a full scan of T1 whereas the first query can make use of an index on T1(x).
那么是不是这样的呢？ 因为TOM 用的是8i，他当时的环境可能是RBO 也可能是CBO。 我们都测试一下。
首先测试RBO模式
1. 设置使用RBO来优化SQL
SQL> alter session set optimizer_mode='RULE'; Session altered.
2. 执行语句查看指向计划 IN的执行计划
SQL> select /*+ gather_plan_statistics */ * from emp where deptno in ( select DEPTNO from dept where DNAME='ACCOUNTING'); EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO ---------- -------- ---------- ---------- ------------------- ---------- ---------- ---------- 7782 CLARK MANAGER 7839 1981-06-09 00:00:00 2450 10 7839 KING PRESIDENT 1981-11-17 00:00:00 5000 10 7934 MILLER CLERK 7782 1982-01-23 00:00:00 9999 10 SQL> select * from TABLE(dbms_xplan.display_cursor(null,null,'iostats last')); ----------------------------------------------------------------------------------- | Id | Operation | Name | Starts | A-Rows | A-Time | Buffers | ----------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 3 |00:00:00.01 | 6 | | 1 | MERGE JOIN | | 1 | 3 |00:00:00.01 | 6 | | 2 | SORT JOIN | | 1 | 4 |00:00:00.01 | 3 | | 3 | TABLE ACCESS FULL | EMP | 1 | 14 |00:00:00.01 | 3 | |* 4 | SORT JOIN | | 4 | 3 |00:00:00.01 | 3 | | 5 | VIEW | VW_NSO_1 | 1 | 1 |00:00:00.01 | 3 | | 6 | SORT UNIQUE | | 1 | 1 |00:00:00.01 | 3 | |* 7 | TABLE ACCESS FULL| DEPT | 1 | 1 |00:00:00.01 | 3 | ----------------------------------------------------------------------------------- 4 - access("DEPTNO"="$nso_col_1") filter("DEPTNO"="$nso_col_1") 7 - filter("DNAME"='ACCOUNTING') Note ----- - rule based optimizer used (consider using cbo)
完全符合TOM的说法（如下）
select * from t1, ( select distinct y from t2 ) t2 where t1.x = t2.y;
The subquery is evaluated, distinct'ed, indexed (or hashed or sorted) and then joined to the original table -- typically.
IN后面的子查询被看成一个view，也就是上面执行计划的5,6,7步骤。 然后这个VIEW和原来的表做连接。
3. 再看一下EXIST的执行计划
SQL> select /*+ gather_plan_statistics */ * from emp where exists (select 8 from dept where dname='ACCOUNTING' and deptno=emp.deptno); EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO ---------- -------- ---------- ---------- ------------------- ---------- ---------- ---------- 7782 CLARK MANAGER 7839 1981-06-09 00:00:00 2450 10 7839 KING PRESIDENT 1981-11-17 00:00:00 5000 10 7934 MILLER CLERK 7782 1982-01-23 00:00:00 9999 10 SQL> select * from TABLE(dbms_xplan.display_cursor(null,null,'iostats last')); ---------------------------------------------------------------------------- | Id | Operation | Name | Starts | A-Rows | A-Time | Buffers | ---------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 3 |00:00:00.01 | 13 | |* 1 | FILTER | | 1 | 3 |00:00:00.01 | 13 | | 2 | TABLE ACCESS FULL| EMP | 1 | 14 |00:00:00.01 | 4 | |* 3 | TABLE ACCESS FULL| DEPT | 3 | 1 |00:00:00.01 | 9 | ---------------------------------------------------------------------------- 1 - filter( IS NOT NULL) 3 - filter(("DEPTNO"=:B1 AND "DNAME"='ACCOUNTING')) Note ----- - rule based optimizer used (consider using cbo) 26 rows selected.
这里面至关重要的是理解FILTER这个操作。 在ORACLE性能优化艺术一书中，它被归结为相关联合性操作，如果不理解这是什么意思也没关系，你只需要知道FILTER在这里的意思是，让执行计划的第二行来驱动第三行，也就是第二行返回的结果，每一行都会驱动第三行跑一次。 这正是tom所说的情况（如下）。
for x in ( select * from t1 ) loop if ( exists ( select null from t2 where y = x.x ) then OUTPUT THE RECORD end if end loop It always results in a full scan of T1 whereas the first query can make use of an index on T1(x).
但是，不要忘了，上面的还是在RBO下做的，RBO现在已经是历史了，所以我们要在CBO下再测试一次。
下面这一段是in 模式
SQL> alter session set optimizer_mode='ALL_ROWS'; Session altered. SQL> select /*+ gather_plan_statistics */ * from emp where deptno in ( select DEPTNO from dept where DNAME='ACCOUNTING'); EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO ---------- -------- ---------- ---------- ------------------- ---------- ---------- ---------- 7934 MILLER CLERK 7782 1982-01-23 00:00:00 9999 10 7839 KING PRESIDENT 1981-11-17 00:00:00 5000 10 7782 CLARK MANAGER 7839 1981-06-09 00:00:00 2450 10 SQL> select * from TABLE(dbms_xplan.display_cursor(null,null,'iostats last')); ------------------------------------------------------------------------------------------ | Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | ------------------------------------------------------------------------------------------ | 0 | SELECT STATEMENT | | 1 | | 3 |00:00:00.01 | 7 | |* 1 | HASH JOIN SEMI | | 1 | 5 | 3 |00:00:00.01 | 7 | | 2 | TABLE ACCESS FULL | EMP | 1 | 14 | 14 |00:00:00.01 | 3 | | 3 | VIEW | VW_NSO_1 | 1 | 1 | 1 |00:00:00.01 | 4 | |* 4 | TABLE ACCESS FULL| DEPT | 1 | 1 | 1 |00:00:00.01 | 4 | ------------------------------------------------------------------------------------------ 1 - access("DEPTNO"="$nso_col_1") 4 - filter("DNAME"='ACCOUNTING') 23 rows selected.
下面这一段是 exist模式
SQL> select /*+ gather_plan_statistics */ * from emp where exists (select 8 from dept where dname='ACCOUNTING' and deptno=emp.deptno); EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO ---------- -------- ---------- ---------- ------------------- ---------- ---------- ---------- 7934 MILLER CLERK 7782 1982-01-23 00:00:00 9999 10 7839 KING PRESIDENT 1981-11-17 00:00:00 5000 10 7782 CLARK MANAGER 7839 1981-06-09 00:00:00 2450 10 SQL> select * from TABLE(dbms_xplan.display_cursor(null,null,'iostats last')); ----------------------------------------------------------------------------------------- | Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | ----------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | | 3 |00:00:00.01 | 7 | |* 1 | HASH JOIN SEMI | | 1 | 5 | 3 |00:00:00.01 | 7 | | 2 | TABLE ACCESS FULL | EMP | 1 | 14 | 14 |00:00:00.01 | 3 | | 3 | VIEW | VW_SQ_1 | 1 | 1 | 1 |00:00:00.01 | 4 | |* 4 | TABLE ACCESS FULL| DEPT | 1 | 1 | 1 |00:00:00.01 | 4 | ----------------------------------------------------------------------------------------- 1 - access("ITEM_1"="EMP"."DEPTNO") 4 - filter("DNAME"='ACCOUNTING') 23 rows selected.
可以看到 IN 与 EXIST 在CBO模式执行计划 都是一样的。 所以，我暂时得出结论--以前强调 IN , EXIST的用法是因为优化器是RBO，现在 用不用太在意 用IN 还是 EXIST。但是保守起见又去看了一下10gR2的官方文档。
在看10g R2的文档（Performance Tuning Guide）的时候，发现下面这种说法。
In certain circumstances, it is better to use IN rather than EXISTS. In general, if the selective predicate is in the subquery, then use IN. If the selective predicate is in the parent query, then use EXISTS.
Sometimes, Oracle can rewrite a subquery when used with an IN clause to take advantage of selectivity specified in the subquery. This is most beneficial when the most selective filter appears in the subquery and there are indexes on the join columns. Conversely, using EXISTS is beneficial when the most selective filter is in the parent query. This allows the selective predicates in the parent query to be applied before filtering the rows against the EXISTS criteria.
"Example 1: Using IN - Selective Filters in the Subquery" and "Example 2: Using EXISTS - Selective Predicate in the Parent" are two examples that demonstrate the benefits of IN and EXISTS. Both examples use the same schema with the following characteristics:
-
There is a unique index on the
employees.employee_idfield. -
There is an index on the
orders.customer_idfield. -
There is an index on the
employees.department_idfield. -
The
employeestable has 27,000 rows. -
The
orderstable has 10,000 rows. -
The
OEandHRschemas, which own these segments, were both analyzed withCOMPUTE.
Example 1: Using IN - Selective Filters in the Subquery
This example demonstrates how rewriting a query to use IN can improve performance. This query identifies all employees who have placed orders on behalf of customer 144.
The following SQL statement uses EXISTS:
SELECT /* EXISTS example */
e.employee_id, e.first_name, e.last_name, e.salary
FROM employees e
WHERE EXISTS (SELECT 1 FROM orders o /* Note 1 */
WHERE e.employee_id = o.sales_rep_id /* Note 2 */
AND o.customer_id = 144); /* Note 3 */
Notes:
-
Note 1: This shows the line containing
EXISTS. -
Note 2: This shows the line that makes the subquery a correlated subquery.
-
Note 3: This shows the line where the correlated subqueries include the highly selective predicate
customer_id=number.
The following plan output is the execution plan (from V$SQL_PLAN) for the preceding statement. The plan requires a full table scan of the employees table, returning many rows. Each of these rows is then filtered against the orders table (through an index).
ID OPERATION OPTIONS OBJECT_NAME OPT COST ---- -------------------- --------------- ---------------------- --- ---------- 0 SELECT STATEMENT CHO 1 FILTER 2 TABLE ACCESS FULL EMPLOYEES ANA 155 3 TABLE ACCESS BY INDEX ROWID ORDERS ANA 3 4 INDEX RANGE SCAN ORD_CUSTOMER_IX ANA 1
Rewriting the statement using IN results in significantly fewer resources used.
The SQL statement using IN:
SELECT /* IN example */
e.employee_id, e.first_name, e.last_name, e.salary
FROM employees e
WHERE e.employee_id IN (SELECT o.sales_rep_id /* Note 4 */
FROM orders o
WHERE o.customer_id = 144); /* Note 3 */
Note:
-
Note 3: This shows the line where the correlated subqueries include the highly selective predicate
customer_id=number -
Note 4: This indicates that an
INis being used. The subquery is no longer correlated, because theINclause replaces the join in the subquery.
The following plan output is the execution plan (from V$SQL_PLAN) for the preceding statement. The optimizer rewrites the subquery into a view, which is then joined through a unique index to the employees table. This results in a significantly better plan, because the view (that is, subquery) has a selective predicate, thus returning only a few employee_ids. These employee_ids are then used to access the employees table through the unique index.
ID OPERATION OPTIONS OBJECT_NAME OPT COST ---- -------------------- --------------- ---------------------- --- ---------- 0 SELECT STATEMENT CHO 1 NESTED LOOPS 5 2 VIEW 3 3 SORT UNIQUE 3 4 TABLE ACCESS FULL ORDERS ANA 1 5 TABLE ACCESS BY INDEX ROWID EMPLOYEES ANA 1 6 INDEX UNIQUE SCAN EMP_EMP_ID_PK ANA
官方文档的意思是这样的。如果父查询中有高效的过滤条件，父查询能返回较小的结果集 那么就应该用EXIST。 如果 子查询有更佳的过滤条件，能够返回较小的结果集，就用IN。但这一般是相对OLAP系统，并且不绝对，因为统计信息等因素影响很大。
对应这官方文档中的例子，我们自己做一下实验。
首先是搭建环境。创建一个外层查询表 obj。 创建一个内层查询表sy (synonym)。建立相应的索引。
in和 exist 的查询语句为
select /*+ gather_plan_statistics */ object_name from obj where object_name in ( select synonym_name from sy where owner='CITOSADMIN');
select /*+ gather_plan_statistics */ object_name from obj where exists ( select 1 from sy where sy.synonym_name = obj.object_name and sy.owner='CITOSADMIN');
这两个语句的共同点就是子查询有一个限定条件 sy.owner=' CITOSADMIN' 可以高效的返回一个很小的结果集。 按照官方文档的理论，两者不但执行方式不同，而且效率也不同。OK 开始实验
1 SQL> create table obj as select * from dba_objects; 2 3 Table created. 4 5 SQL> create table sy as select * from dba_synonyms; 6 7 Table created. 8 9 SQL> create index sy_ind_syname on sy(synonym_name); 10 11 Index created. 12 13 SQL> create index obj_ind_objname on obj(object_name); 14 15 Index created. 16 17 SQL> create index sy_ind_own on sy(owner); 18 19 Index created.
运行in方式的查询，并查看执行计划
1 SQL> select /*+ gather_plan_statistics */ object_name from obj where object_name in ( select synonym_name from sy where owner='CITOSADMIN'); 2 3 OBJECT_NAME 4 ------------------------------------------------ 5 /eb4470db_YARD_CLUSTER_DISCHAR 6 WHERF_RESTRICTION 7 8 SQL> select * from TABLE(dbms_xplan.display_cursor(null,null,'iostats last')); 9 10 PLAN_TABLE_OUTPUT 11 ------------------------------------------------------------------------------ 12 SQL_ID bkrqp62y2z7fd, child number 0 13 ------------------------------------- 14 select /*+ gather_plan_statistics */ object_name from obj where object_name in ( select 15 synonym_name from sy where owner='CITOSADMIN') 16 17 Plan hash value: 2488893939 18 19 ------------------------------------------------------------------------------------------------------------ 20 | Id | Operation | Name | Starts | A-Rows | A-Time | Buffers | Reads | 21 ------------------------------------------------------------------------------------------------------------ 22 | 1 | NESTED LOOPS | | 1 | 2 |00:00:00.01 | 8 | 3 | 23 | 2 | VIEW | VW_NSO_1 | 1 | 2 |00:00:00.01 | 3 | 1 | 24 | 3 | SORT UNIQUE | | 1 | 2 |00:00:00.01 | 3 | 1 | 25 | 4 | TABLE ACCESS BY INDEX ROWID| SY | 1 | 2 |00:00:00.01 | 3 | 1 | 26 |* 5 | INDEX RANGE SCAN | SY_IND_OWN | 1 | 2 |00:00:00.01 | 2 | 1 | 27 |* 6 | INDEX RANGE SCAN | OBJ_IND_OBJNAME | 2 | 2 |00:00:00.01 | 5 | 2 | 28 ------------------------------------------------------------------------------------------------------------ 29 30 Predicate Information (identified by operation id): 31 --------------------------------------------------- 32 33 5 - access("OWNER"='CITOSADMIN') 34 6 - access("OBJECT_NAME"="$nso_col_1") 35 36 Note 37 ----- 38 - rule based optimizer used (consider using cbo) 39 40 41 28 rows selected.
这里居然像tom说的那样。in 走的是 sub-query 变成view 然后连接的模式。
再看看exist的模式
1 SQL> select /*+ gather_plan_statistics */ object_name from obj where exists ( select 1 from sy where sy.synonym_name = obj.object_name and sy.owner='CITOSADMIN'); 2 3 OBJECT_NAME 4 ------------------------------------------------ 5 /eb4470db_YARD_CLUSTER_DISCHAR 6 WHERF_RESTRICTION 7 8 SQL> select * from TABLE(dbms_xplan.display_cursor(null,null,'iostats last')); 9 10 PLAN_TABLE_OUTPUT 11 ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ 12 SQL_ID 902k3xnyr41w2, child number 0 13 ------------------------------------- 14 select /*+ gather_plan_statistics */ object_name from obj where exists ( select 1 15 from sy where sy.synonym_name = obj.object_name and sy.owner='CITOSADMIN') 16 17 Plan hash value: 482598837 18 19 ---------------------------------------------------------------------------------------------- 20 | Id | Operation | Name | Starts | A-Rows | A-Time | Buffers | Reads | 21 ---------------------------------------------------------------------------------------------- 22 |* 1 | FILTER | | 1 | 2 |00:00:01.54 | 217K| 108 | 23 | 2 | TABLE ACCESS FULL| OBJ | 1 | 56697 |00:00:00.06 | 787 | 0 | 24 | 3 | AND-EQUAL | | 54946 | 2 |00:00:01.36 | 217K| 108 | 25 |* 4 | INDEX RANGE SCAN| SY_IND_SYNAME | 54946 | 41304 |00:00:00.63 | 134K| 108 | 26 |* 5 | INDEX RANGE SCAN| SY_IND_OWN | 54946 | 24488 |00:00:00.40 | 82599 | 0 | 27 ---------------------------------------------------------------------------------------------- 28 29 Predicate Information (identified by operation id): 30 --------------------------------------------------- 31 32 1 - filter( IS NOT NULL) 33 4 - access("SY"."SYNONYM_NAME"=:B1) 34 5 - access("SY"."OWNER"='CITOSADMIN') 35 36 Note 37 ----- 38 - rule based optimizer used (consider using cbo) 39 40 41 28 rows selected.
好吧我败了。 前面的结论推翻了。 IN 和 EXIST在写法的选择上还是有讲究的。 这个实验跟官方文档中的说法完全一样，和tom的说法也一样。 子查询有高效的where条件的时候，IN 更快些。
再看官方文档中关于exists 优于in的情况
This example demonstrates how rewriting a query to use EXISTS can improve performance. This query identifies all employees from department 80 who are sales reps who have placed orders.
The following SQL statement uses IN:
SELECT /* IN example */
e.employee_id, e.first_name, e.last_name, e.department_id, e.salary
FROM employees e
WHERE e.department_id = 80 /* Note 5 */
AND e.job_id = 'SA_REP' /* Note 6 */
AND e.employee_id IN (SELECT o.sales_rep_id FROM orders o); /* Note 4 */
Note:
-
Note 4: This indicates that an
INis being used. The subquery is no longer correlated, because theINclause replaces the join in the subquery. -
Note 5 and 6: These are the selective predicates in the parent SQL.
The following plan output is the execution plan (from V$SQL_PLAN) for the preceding statement. The SQL statement was rewritten by the optimizer to use a view on the orders table, which requires sorting the data to return all unique employee_ids existing in the orders table. Because there is no predicate, many employee_ids are returned. The large list of resulting employee_ids are then used to access the employees table through the unique index.
ID OPERATION OPTIONS OBJECT_NAME OPT COST ---- -------------------- --------------- ---------------------- --- ---------- 0 SELECT STATEMENT CHO 1 NESTED LOOPS 125 2 VIEW 116 3 SORT UNIQUE 116 4 TABLE ACCESS FULL ORDERS ANA 40 5 TABLE ACCESS BY INDEX ROWID EMPLOYEES ANA 1 6 INDEX UNIQUE SCAN EMP_EMP_ID_PK ANA
The following SQL statement uses EXISTS:
SELECT /* EXISTS example */
e.employee_id, e.first_name, e.last_name, e.salary
FROM employees e
WHERE e.department_id = 80 /* Note 5 */
AND e.job_id = 'SA_REP' /* Note 6 */
AND EXISTS (SELECT 1 /* Note 1 */
FROM orders o
WHERE e.employee_id = o.sales_rep_id); /* Note 2 */
Note:
-
Note 1: This shows the line containing
EXISTS. -
Note 2: This shows the line that makes the subquery a correlated subquery.
-
Note 5 & 6:These are the selective predicates in the parent SQL.
The following plan output is the execution plan (from V$SQL_PLAN) for the preceding statement. The cost of the plan is reduced by rewriting the SQL statement to use an EXISTS. This plan is more effective, because two indexes are used to satisfy the predicates in the parent query, thus returning only a few employee_ids. The employee_ids are then used to access the orders table through an index.
ID OPERATION OPTIONS OBJECT_NAME OPT COST ---- -------------------- --------------- ---------------------- --- ---------- 0 SELECT STATEMENT CHO 1 FILTER 2 TABLE ACCESS BY INDEX ROWID EMPLOYEES ANA 98 3 AND-EQUAL 4 INDEX RANGE SCAN EMP_JOB_IX ANA 5 INDEX RANGE SCAN EMP_DEPARTMENT_IX ANA 6 INDEX RANGE SCAN ORD_SALES_REP_IX ANA 8
Note:
An even more efficient approach is to have a concatenated index ondepartment_id and job_id. This eliminates the need to access two indexes and reduces the resources used.SQL> select /*+ gather_plan_statistics */ object_name from obj where owner='CITOSADMIN' and object_type='SYNONYM' and exists 2 (select null from sy where obj.object_name=sy.synonym_name); OBJECT_NAME ------------------------------------------------ /eb4470db_YARD_CLUSTER_DISCHAR WHERF_RESTRICTION Elapsed: 00:00:00.03 SQL> SQL> select * from TABLE(dbms_xplan.display_cursor(null,null,'iostats last')); PLAN_TABLE_OUTPUT -------------------------------------------------------------------------------------------------------------------------------- SQL_ID 0k96pgrx3k1u5, child number 0 ------------------------------------- select /*+ gather_plan_statistics */ object_name from obj where owner='CITOSADMIN' and object_type='SYNONYM' and exists (select null from sy where obj.object_name=sy.synonym_name) Plan hash value: 2625485413 -------------------------------------------------------------------------------------------------- | Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | -------------------------------------------------------------------------------------------------- |* 1 | HASH JOIN SEMI | | 1 | 55 | 2 |00:00:00.02 | 876 | |* 2 | TABLE ACCESS FULL | OBJ | 1 | 55 | 2 |00:00:00.01 | 786 | | 3 | VIEW | VW_SQ_1 | 1 | 21343 | 16254 |00:00:00.01 | 90 | | 4 | INDEX FAST FULL SCAN| SY_IND_SYNAME | 1 | 21343 | 16254 |00:00:00.01 | 90 | -------------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 1 - access("OBJ"."OBJECT_NAME"="SYNONYM_NAME") 2 - filter(("OWNER"='CITOSADMIN' AND "OBJECT_TYPE"='SYNONYM')) 23 rows selected. Elapsed: 00:00:00.03 SQL> SQL> SQL> SQL> SQL> SQL> SQL> SQL> SQL> SQL> SQL> SQL> select /*+ gather_plan_statistics */ object_name from obj where owner='CITOSADMIN' and object_type='SYNONYM' and object_name in 2 (select synonym_name from sy); OBJECT_NAME ------------------------------------------------ /eb4470db_YARD_CLUSTER_DISCHAR WHERF_RESTRICTION Elapsed: 00:00:00.04 SQL> select * from TABLE(dbms_xplan.display_cursor(null,null,'iostats last')); PLAN_TABLE_OUTPUT -------------------------------------------------------------------------------------------------------------------------------- SQL_ID dzfagp8vyh1xh, child number 0 ------------------------------------- select /*+ gather_plan_statistics */ object_name from obj where owner='CITOSADMIN' and object_type='SYNONYM' and object_name in (select synonym_name from sy) Plan hash value: 3101896092 -------------------------------------------------------------------------------------------------- | Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | -------------------------------------------------------------------------------------------------- |* 1 | HASH JOIN SEMI | | 1 | 55 | 2 |00:00:00.02 | 876 | |* 2 | TABLE ACCESS FULL | OBJ | 1 | 55 | 2 |00:00:00.01 | 786 | | 3 | VIEW | VW_NSO_1 | 1 | 21343 | 16254 |00:00:00.01 | 90 | | 4 | INDEX FAST FULL SCAN| SY_IND_SYNAME | 1 | 21343 | 16254 |00:00:00.01 | 90 | -------------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 1 - access("OBJECT_NAME"="$nso_col_1") 2 - filter(("OWNER"='CITOSADMIN' AND "OBJECT_TYPE"='SYNONYM')) 22 rows selected. Elapsed: 00:00:00.04