查询转换方式

最新推荐文章于 2022-09-14 19:43:29 发布

cindy_1215

最新推荐文章于 2022-09-14 19:43:29 发布

阅读量144

点赞数

查询转换方式有很多种：
1、视图合并( view merge ) 2、子查询展开( subquery unnesting ) 3、谓词推入 ( predicate pushdown )

1、视图合并：就是视图的基表和外部表做连接
创建一个视图
create view v1 as
select * from emp where deptno = 10;

select *
from v1, dept
where v1.deptno = dept.deptno
and v1.job = 'CLERK';
找到v1的基表emp，做了视图合并.emp和dept返回的记录很少，所以走嵌套循环和hash都很正常

不让他进行视图合并的方法：

《1》/*+ no_meger(view)*/
《2》查询块包含分析函数或聚合函数、集合运算（例如union、intersect、minus）,
order by字句或者使用了rownum，视图合并将会被禁止或者限制。

rownum也会阻止谓词推入

2、（1）、走子查询展开：就是where子句后边有in，not in，exists，not exists，<,<=,>,>=等，CBO认为对SQL进行等价改写以后能够更好的进行优化，等价改写的过程就是子查询展开

SQL> select ename from emp where deptno in (select deptno from dept where dname like 'S');

执行计划
----------------------------------------------------------
Plan hash value: 330698451

--------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 5 | 110 | 5 (20)| 00:00:01 |
|* 1 | HASH JOIN SEMI | | 5 | 110 | 5 (20)| 00:00:01 |
| 2 | TABLE ACCESS FULL | EMP | 14 | 126 | 2 (0)| 00:00:01 |
| 3 | VIEW | VW_NSO_1 | 1 | 13 | 2 (0)| 00:00:01 |
|* 4 | TABLE ACCESS FULL| DEPT | 1 | 12 | 2 (0)| 00:00:01 |
--------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

1 - access("DEPTNO"="$nso_col_1")
4 - filter("DNAME" LIKE 'S')

统计信息
----------------------------------------------------------
1 recursive calls
0 db block gets
7 consistent gets
0 physical reads
0 redo size
248 bytes sent via SQL*Net to client
339 bytes received via SQL*Net from client
1 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
0 rows processed

（2）、加一个Hint禁止走子查询展开，只要有filter exists一定没有走子查询展开，

filter是嵌套表有多少不重复数据就会返回多少条记录。

SQL> select ename from emp where deptno in (select /*+ no_unnest*/ deptno from dept where dname like 'S');

执行计划
----------------------------------------------------------
Plan hash value: 1499841400

---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 5 | 45 | 8 (0)| 00:00:01 |
|* 1 | FILTER | | | | | |
| 2 | TABLE ACCESS FULL| EMP | 14 | 126 | 2 (0)| 00:00:01 |
|* 3 | TABLE ACCESS FULL| DEPT | 1 | 12 | 2 (0)| 00:00:01 |
---------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

1 - filter( EXISTS (SELECT /*+ NO_UNNEST */ 0 FROM "DEPT" "DEPT"
WHERE "DEPTNO"=:B1 AND "DNAME" LIKE 'S'))
3 - filter("DEPTNO"=:B1 AND "DNAME" LIKE 'S')

统计信息
----------------------------------------------------------
1 recursive calls
0 db block gets
16 consistent gets
0 physical reads
0 redo size
248 bytes sent via SQL*Net to client
339 bytes received via SQL*Net from client
1 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
0 rows processed

or是阻止进行子查询展开的条件。

查询名字带S的用户或者empno>5000的人

（1）、
SQL> select ename from emp where deptno in (select deptno from dept where dname like 'S') or empno >= 5000 ;

执行计划
----------------------------------------------------------
Plan hash value: 1499841400

---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 14 | 182 | 2 (0)| 00:00:01 |
|* 1 | FILTER | | | | | |
| 2 | TABLE ACCESS FULL| EMP | 14 | 182 | 2 (0)| 00:00:01 |
|* 3 | TABLE ACCESS FULL| DEPT | 1 | 12 | 2 (0)| 00:00:01 |
---------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

1 - filter("EMPNO">=5000 OR EXISTS (SELECT 0 FROM "DEPT" "DEPT"
WHERE "DEPTNO"=:B1 AND "DNAME" LIKE 'S'))
3 - filter("DEPTNO"=:B1 AND "DNAME" LIKE 'S')

统计信息
----------------------------------------------------------
1 recursive calls
0 db block gets
5 consistent gets
0 physical reads
0 redo size
511 bytes sent via SQL*Net to client
350 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
15 rows processed

（2）、改写后的SQL语句：

SQL> select ename from emp where deptno in (select deptno from dept where dname like 'S')
2 union all
3 select ename from emp where empno >= 5000 ;

ENAME
--------------------
SMITH
ALLEN
WARD
JONES
MARTIN
BLAKE
CLARK
SCOTT
KING
TURNER
ADAMS
JAMES
FORD
MILLER
h

已选择15行。

执行计划
----------------------------------------------------------
Plan hash value: 527984613

---------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 19 | 250 | 7 (43)| 00:00:01 |
| 1 | UNION-ALL | | | | | |
|* 2 | HASH JOIN SEMI | | 5 | 110 | 5 (20)| 00:00:01 |
| 3 | TABLE ACCESS FULL | EMP | 14 | 126 | 2 (0)| 00:00:01 |
| 4 | VIEW | VW_NSO_1 | 1 | 13 | 2 (0)| 00:00:01 |
|* 5 | TABLE ACCESS FULL| DEPT | 1 | 12 | 2 (0)| 00:00:01 |
|* 6 | TABLE ACCESS FULL | EMP | 14 | 140 | 2 (0)| 00:00:01 |
---------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

2 - access("DEPTNO"="$nso_col_1")
5 - filter("DNAME" LIKE 'S')
6 - filter("EMPNO">=5000)

统计信息
----------------------------------------------------------
24 recursive calls
0 db block gets
14 consistent gets
0 physical reads
0 redo size
511 bytes sent via SQL*Net to client
350 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
15 rows processed

3、谓词推进（ predicate pushdown ）
不让他做视图合并，加一个hint/*+ no_merge(v1) */
select /*+ no_merge(v1) */ *
from v1, dept
where v1.deptno = dept.deptno
and v1.job = 'CLERK';