Oracle 查询转换

IT那活儿 2025-05-13

点击上方“IT那活儿”公众号--专注于企业全栈运维技术分享，不管IT什么活儿，干就完了！！！

Oracle 里的查询转换(Query Transformation)，又称为査询改写(Query Rewrite)，它是 Oracle 在解析目标SOL 的过程中的重要一步，其含义是指 Oracle 在解析目标 SQL 时可能会对其做等价改写，目的是为了能更高效地执行目标 SQL，即 Oracle 可能会将目标 SQL改写成语义上完全等价但执行效率却更高的形式。

一

使用子查询展开

使用HASH半连接：

select t1.a,t2.b
from t1,t2
where t1.c=t2.c
and t1.c in (select c from t3 );
   
------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | |       | |   301 (100)| |
|* 1 |  HASH JOIN | |     1| 110 |   301   (1)| 00:00:04 |
|* 2 |   HASH JOIN RIGHT SEMI| |    10| 310 |   294   (1)| 00:00:04 |
| 3 |    TABLE ACCESS FULL | T3 |    10| 130 |     3   (0)| 00:00:01 |
| 4 |    TABLE ACCESS FULL | T1 |62548| 1011K|   290   (1)| 00:00:04 |
| 5 |   TABLE ACCESS FULL | T2 |1000| 79000 |     6   (0)| 00:00:01 |

当加入no_unnest的hint以filter的连接方式来执行子查询SQL：

select  t1.a,t2.b
from t1,t2
where t1.c=t2.c
and t1.c in (select/*+ no_unnest */c from t3 );

----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------
| 0 | SELECTSTATEMENT    | | | | 1796 (100)| |
|* 1 | FILTER | | | | | |
|* 2 | HASHJOIN         | | 119 | 96903 | 297   (1)| 00:00:04 |
| 3 | TABLEACCESSFULL| T2 | 1000 | 79000 | 6   (0)| 00:00:01 |
| 4 | TABLEACCESSFULL| T1 | 62548 | 1011K| 290   (1)| 00:00:04 |
|* 5 | TABLEACCESSFULL | T3 | 1 | 13 | 3   (0)| 00:00:01 |

二

谓词推入

创建index：

SQL> create index idx_t1_id on t1(c);

Index created.

SQL> create index idx_t2_id on t2(c);

Index created.

SQL> create index idx_t3_id on t3(c);

Index created.


select/*+ push_pred(t) no_merge(t) */ t3.a,t.c from
t3,view_1 t where t3.c=t.c(+) and t3.c=11
------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 3 (100)| |
| 1 | NESTED LOOPS OUTER | | 1 | 31 | 3   (0)| 00:00:01 |
| 2 | TABLE ACCESS BY INDEX ROWID| T3 | 1 | 18 | 1   (0)| 00:00:01 |
|* 3 | INDEX RANGE SCAN | IDX_T3_ID | 1 | | 1   (0)| 00:00:01 |
| 4 | VIEW PUSHED PREDICATE | view_1 | 1 | 13 | 2   (0)| 00:00:01 | 谓词推入关键字
|* 5 | FILTER | | | | | |
| 6 | MERGE JOIN CARTESIAN | | 1 | 26 | 2   (0)| 00:00:01 |
|* 7 | INDEX RANGE SCAN | IDX_T1_ID | 1 | 13 | 1   (0)| 00:00:01 |
| 8 | BUFFER SORT | | 1 | 13 | 1   (0)| 00:00:01 |
|* 9 | INDEX RANGE SCAN | IDX_T2_ID | 1 | 13 | 1   (0)| 00:00:01 |

三

应用谓词推入

先不让它进行谓词推入：

select /*+ no_push_pred(t) merge(t) */ t3.a,t.c from
t3,view_1 t where t3.c=t.c(+) and t3.c=11

------------------------------------------------------------------------------------------
| Id  | Operation | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------
| 0 | SELECTSTATEMENT             | | | | 4 (100)| |
|* 1 | HASHJOINOUTER             | | 1 | 31 | 4  (25)| 00:00:01 |
| 2 | TABLEACCESSBYINDEXROWID| T3 | 1 | 18 | 1   (0)| 00:00:01 |
|* 3 | INDEXRANGESCAN          | IDX_T3_ID | 1 | | 1   (0)| 00:00:01 |
| 4 | VIEW                       | view_1 | 1 | 13 | 2   (0)| 00:00:01 |
| 5 | MERGEJOIN CARTESIAN | | 1 | 26 | 2   (0)| 00:00:01 |
|* 6 | INDEXRANGESCAN         | IDX_T1_ID | 1 | 13 | 1   (0)| 00:00:01 |
| 7 | BUFFER SORT              | | 1 | 13 | 1   (0)| 00:00:01 |
|* 8 | INDEXRANGESCAN        | IDX_T2_ID | 1 | 13 | 1   (0)| 00:00:01 |
------------------------------------------------------------------------------------------

然后去掉HINT让它运行：

select t3.a,t.c from t3,view_1 t where
t3.c=t.c(+) and t3.c=11

------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | |       | |     3 (100)| |
| 1 |  NESTED LOOPS OUTER | |     1| 31 |     3   (0)| 00:00:01 |
| 2 |   TABLE ACCESS BY INDEX ROWID| T3 |     1| 18 |     1   (0)| 00:00:01 |
|* 3 |    INDEX RANGE SCAN | IDX_T3_ID |     1| |     1   (0)| 00:00:01 |
| 4 |   VIEW PUSHED PREDICATE | view_1 |     1| 13 |     2   (0)| 00:00:01 |
|* 5 |    FILTER | |       | |            | |
| 6 |     MERGE JOIN CARTESIAN | |     1| 26 |     2   (0)| 00:00:01 |
|* 7 |      INDEX RANGE SCAN | IDX_T1_ID |     1| 13 |     1   (0)| 00:00:01 |
| 8 |      BUFFER SORT | |     1| 13 |     1   (0)| 00:00:01 |
|* 9 |       INDEX RANGE SCAN | IDX_T2_ID |     1| 13 |     1   (0)| 00:00:01 |
------------------------------------------------------------------------------------------

比较可以看到谓词推入后的COST值，ORACLE计算的结果为3比上一条推入的4略小。

四

视图合并

创建视图view_1 ：

SQL> create view view_1 as 
2select  t1.c,t2.a
3from t1,t2
4where t1.c=t2.c;

View created.

SQL> create table t1 asselect * from dba_objects;

Table created.

SQL> create table t2 asselect * from dba_objects where rownum<100;

Table created.

SQL> create table t3 asselect * from dba_objects where rownum<10;

Table created.

视图view_1没有出现在name列：

select t3.c,t.a from t3,view_1 t where t3.c=t.c 
-----------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | OMem |1Mem | Used-Mem |
-----------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | |      1| |      9|00:00:02.03 |    1060| |       | |
|* 1 |  HASH JOIN | |      1| 1 |      9|00:00:02.03 |    1060| 1306K|1306K| 1128K (0)|
|* 2 |   HASH JOIN | |      1| 10 |     10|00:00:02.01 |    1043| 1517K|1517K| 1123K (0)|
| 3 |    TABLE ACCESS FULL| T3 |      1| 10 |     10|00:00:00.01 |       3| |       | |
| 4 |    TABLE ACCESS FULL| T1 |      1| 62548 |72668|00:00:00.55 |    1040| |       | |
| 5 |   TABLE ACCESS FULL | T2 |      1| 1000 |   1000|00:00:00.01 |      17| |       | |
-----------------------------------------------------------------------------------------------------------------

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

   1 - access("T1"."c"="T2"."c")
   2 - access("T3"."c"="T1"."c")

Note
-----
   - dynamic sampling used for this statement (level=2)

加入no_merge的hint后可以name列看到view_1的视图名称：

select  /*+ no_merge(t) */t3.c,t.a from t3,view_1 t where t3.c=t.c 
-------------------------------------------------------------------------------
| Id  | Operation | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------
| 0 | SELECTSTATEMENT     | | | | 300 (100)| |
|* 1 | HASHJOIN           | | 10 | 310 | 300   (1)| 00:00:04 |
| 2 | TABLEACCESSFULL  | T3 | 10 | 130 | 3   (0)| 00:00:01 |
| 3 | VIEW               | view_1 | 119 | 17982 | 297   (1)| 00:00:04 |
|* 4 | HASHJOIN         | | 119 | 30969 | 297   (1)| 00:00:04 |
| 5 | TABLEACCESSFULL| T2 | 1000 | 18000 | 6   (0)| 00:00:01 |
| 6 | TABLEACCESSFULL| T1 | 62548 | 794K| 290   (1)| 00:00:04 |
-------------------------------------------------------------------------------