21.查询转换之-INLIST-FILTER

最新推荐文章于 2024-10-02 10:51:38 发布

戒掉贪嗔痴(薛双奇)

最新推荐文章于 2024-10-02 10:51:38 发布

阅读量452

点赞数 9

分类专栏：数据库运维-Oracle-性能调优SQL 文章标签： oracle

本文链接：https://blog.csdn.net/weixin_43346403/article/details/135948197

版权

数据库运维-Oracle-性能调优SQL 专栏收录该内容

28 篇文章 1 订阅

订阅专栏

1.什么是IN-LIST-FILTER?

IN-LIST-FILTER是针对IN后面是子查询的一种处理方法，优化器会把IN后面的子查询所对应的结果集
当作过滤条件，并且走FILTER 类型的执行计划。
IN后面是子查询，意味着IN后面是变量的集合:走的是FILTER类型的执行计划，意味着Oracle并没有对 
IN后面的子查询做子查询展开。

走IN-LIST-FILTER类型的执行计划需要满足两个条件:
(1)目标SQL的IN后面是子查询而不是常量集合。
(2)Oracle未对目标SQL的IN后面的子查询做子查询展开。

2.IN-LIST-FILTER执行计划的案例

(1)
select t1.id,t1.name from test01 t1 where 
t1.id in(select /*+no_unnest*/t2.id from test01 t2 
where t2.name in ('348xsq348','349xsq349','350xsq350','351xsq351','352xsq352'));

Execution Plan
----------------------------------------------------------
Plan hash value: 1831486660

---------------------------------------------------------------------------------------
| Id  | Operation		     | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT	     |	      |     1 |    21 |  3958K	(1)| 13:11:39 |
|*  1 |  FILTER 		     |	      |       |       | 	   |	      |
|   2 |   TABLE ACCESS FULL	     | TEST01 |  1000K|    20M|  1097	(1)| 00:00:14 |
|*  3 |   TABLE ACCESS BY INDEX ROWID| TEST01 |     1 |    21 |     4	(0)| 00:00:01 |
|*  4 |    INDEX RANGE SCAN	     | IDX_ID |     1 |       |     3	(0)| 00:00:01 |
---------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter( EXISTS (SELECT /*+ NO_UNNEST */ 0 FROM "TEST01" "T2" WHERE
	      "T2"."ID"=:B1 AND ("T2"."NAME"='348xsq348' OR "T2"."NAME"='349xsq349' OR
	      "T2"."NAME"='350xsq350' OR "T2"."NAME"='351xsq351' OR
	      "T2"."NAME"='352xsq352')))
   3 - filter("T2"."NAME"='348xsq348' OR "T2"."NAME"='349xsq349' OR
	      "T2"."NAME"='350xsq350' OR "T2"."NAME"='351xsq351' OR "T2"."NAME"='352xsq352')
   4 - access("T2"."ID"=:B1)
Statistics
----------------------------------------------------------
	  1  recursive calls
	  0  db block gets
    2064660  consistent gets
	  0  physical reads
	  0  redo size
	719  bytes sent via SQL*Net to client
	519  bytes received via SQL*Net from client
	  2  SQL*Net roundtrips to/from client
	  0  sorts (memory)
	  0  sorts (disk)
	  5  rows processed

(2)对比不加HINT,走的是NESTED LOOP类型的执行计划和BITMAP OR的执行计划。
做了子查询展开。
select t1.id,t1.name from test01 t1 where 
t1.id in(select t2.id from test01 t2 
where t2.name in ('348xsq348','349xsq349','350xsq350','351xsq351','352xsq352'));
Execution Plan
----------------------------------------------------------
Plan hash value: 2402577879
------------------------------------------------------------------------------------------------
| Id  | Operation			    | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT		    |	       |     5 |   210 |    26	 (4)| 00:00:01 |
|   1 |  NESTED LOOPS			    |	       |     5 |   210 |    26	 (4)| 00:00:01 |
|   2 |   NESTED LOOPS			    |	       |     5 |   210 |    26	 (4)| 00:00:01 |
|   3 |    SORT UNIQUE			    |	       |     5 |   105 |    16	 (0)| 00:00:01 |
|   4 |     TABLE ACCESS BY INDEX ROWID     | TEST01   |     5 |   105 |    16	 (0)| 00:00:01 |
|   5 |      BITMAP CONVERSION TO ROWIDS    |	       |       |       |	    |	       |
|   6 |       BITMAP OR 		    |	       |       |       |	    |	       |
|   7 |        BITMAP CONVERSION FROM ROWIDS|	       |       |       |	    |	       |
|*  8 | 	INDEX RANGE SCAN	    | IDX_NAME |       |       |     3	 (0)| 00:00:01 |
|   9 |        BITMAP CONVERSION FROM ROWIDS|	       |       |       |	    |	       |
|* 10 | 	INDEX RANGE SCAN	    | IDX_NAME |       |       |     3	 (0)| 00:00:01 |
|  11 |        BITMAP CONVERSION FROM ROWIDS|	       |       |       |	    |	       |
|* 12 | 	INDEX RANGE SCAN	    | IDX_NAME |       |       |     3	 (0)| 00:00:01 |
|  13 |        BITMAP CONVERSION FROM ROWIDS|	       |       |       |	    |	       |
|* 14 | 	INDEX RANGE SCAN	    | IDX_NAME |       |       |     3	 (0)| 00:00:01 |
|  15 |        BITMAP CONVERSION FROM ROWIDS|	       |       |       |	    |	       |
|* 16 | 	INDEX RANGE SCAN	    | IDX_NAME |       |       |     3	 (0)| 00:00:01 |
|* 17 |    INDEX RANGE SCAN		    | IDX_ID   |     1 |       |     2	 (0)| 00:00:01 |
|  18 |   TABLE ACCESS BY INDEX ROWID	    | TEST01   |     1 |    21 |     3	 (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   8 - access("T2"."NAME"='348xsq348')
  10 - access("T2"."NAME"='349xsq349')
  12 - access("T2"."NAME"='350xsq350')
  14 - access("T2"."NAME"='351xsq351')
  16 - access("T2"."NAME"='352xsq352')
  17 - access("T1"."ID"="T2"."ID")
Statistics
----------------------------------------------------------
	  1  recursive calls
	  0  db block gets
	 31  consistent gets
	  0  physical reads
	  0  redo size
	719  bytes sent via SQL*Net to client
	519  bytes received via SQL*Net from client
	  2  SQL*Net roundtrips to/from client
	  1  sorts (memory)
	  0  sorts (disk)
	  5  rows processed

3.总结
不做子查询展开的执行计划中出现FILTER;子查询最后一步被执行。
能做子查询展开的，优先执行子查询,并且该走索引的会走索引。