Oracle 连接因式分解（Join Factorization）

连接因式分解（Join Factorization）是优化器处理带union all的目标SQL的一种优化手段，它是指优化器在处理以union all连接的目标SQL的各个分支时，不再原封不动地分别重复执行每个分支，而是会把各个分支中公共的部分提出来作为一个单独的结果集，然后再和原union all中剩下的部分做表连接。连接因式分解在Oracle 11gr2中才被引入，它的好处是显而易见的。如果不把union all中公共的部分提出来，则意味着这些公共部分中所包含的表会在union all的各个分支中被重复访问；而连接因式分解则能够在最大程度上避免这种重复访问现象的产生，当union all的公共部分所包含的表的数据量很大时，减少一次对大表的重复访问，那也意味着执行效率的巨大提升。我们来看一个连接因式分解的实例。先来看用union all连接的例子：
select t2.prod_id as prod_id
from sales t2,customers t3
where t2.cust_id=t3.cust_id
and t3.cust_gender="MALE"
union all
select t2.prod_id as prod_id
from sales t2,customers t3
where t2.cust_id=t3.cust_id
and t3.cust_gender="FEMALE";
范例SQL的union all的各个分支中仅仅是针对表customers的列cust_gender的限制条件不一样，剩下的部分都是一模一样的，这也意味着表sales就是范例SQL的公共部分。如果这里Oracle不把sales表提出来，不对此SQL做连接因式分解，那就意味着要重复访问表sales两次。来验证一下。先在Oracle 10gr2中执行范例SQL：
SQL> select t2.prod_id as prod_id
2 from sales t2,customers t3
3 where t2.cust_id=t3.cust_id
4 and t3.cust_gender="MALE"
5 union all
6 select t2.prod_id as prod_id
7 from sales t2,customers t3
8 where t2.cust_id=t3.cust_id
9 and t3.cust_gender="FEMALE";no rows selected
Execution Plan
----------------------------------------------------------
Plan hash value: 4184572088------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost （%CPU）| Time | Pstart| Pstop |
------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1837K| 28M| 1098 （54）| 00:00:14 | | |
| 1 | UNION-ALL | | | | | | | |
|* 2 | HASH JOIN | | 918K| 14M| 549 （8）| 00:00:07 | | |
|* 3 | VIEW | index$_join$_002 | 27236 | 186K| 122 （4）| 00:00:02 | | |
|* 4 | HASH JOIN | | | | | | | |
| 5 | BITMAP CONVERSION TO ROWIDS| | 27236 | 186K| 2 （0）| 00:00:01 | | |
|* 6 | BITMAP INDEX SINGLE VALUE | CUSTOMERS_GENDER_BIX | | | | | | |
| 7 | INDEX FAST FULL SCAN | CUSTOMERS_PK | 27236 | 186K| 147 （2）| 00:00:02 | | |
| 8 | PARTITION RANGE ALL | | 918K| 8075K| 413 （6）| 00:00:05 | 1 | 28 |
| 9 | TABLE ACCESS FULL | SALES | 918K| 8075K| 413 （6）| 00:00:05 | 1 | 28 |
|* 10 | HASH JOIN | | 918K| 14M| 549 （8）| 00:00:07 | | |
|* 11 | VIEW | index$_join$_004 | 27750 | 189K| 122 （4）| 00:00:02 | | |
|* 12 | HASH JOIN | | | | | | | |
| 13 | BITMAP CONVERSION TO ROWIDS| | 27750 | 189K| 2 （0）| 00:00:01 | | |
|* 14 | BITMAP INDEX SINGLE VALUE | CUSTOMERS_GENDER_BIX | | | | | | |
| 15 | INDEX FAST FULL SCAN | CUSTOMERS_PK | 27750 | 189K| 147 （2）| 00:00:02 | | |
| 16 | PARTITION RANGE ALL | | 918K| 8075K| 413 （6）| 00:00:05 | 1 | 28 |
| 17 | TABLE ACCESS FULL | SALES | 918K| 8075K| 413 （6）| 00:00:05 | 1 | 28 |
------------------------------------------------------------------------------------------------------------------------Predicate Information （identified by operation id）:
--------------------------------------------------- 2 - access（"T2"."CUST_ID"="T3"."CUST_ID"）
3 - filter（"T3"."CUST_GENDER"="MALE"）
4 - access（ROWID=ROWID）
6 - access（"T3"."CUST_GENDER"="MALE"）
10 - access（"T2"."CUST_ID"="T3"."CUST_ID"）
11 - filter（"T3"."CUST_GENDER"="FEMALE"）
12 - access（ROWID=ROWID）
14 - access（"T3"."CUST_GENDER"="FEMALE"）
Statistics
----------------------------------------------------------
15453 recursive calls
0 db block gets
3297 consistent gets
96 physical reads
0 redo size
276 bytes sent via SQL*Net to client
389 bytes received via SQL*Net from client
1 SQL*Net roundtrips to/from client
159 sorts （memory）
0 sorts （disk）
0 rows processed
从上述显示内容可以看出，这里Oracle就是原封不动地分别重复执行了范例SQL的每个分支，最后对每个分支的执行结果做了一个union all。注意Id=9和Id=17的执行步骤均为“TABLE ACCESS FULL | SALES”，这说明Oracle对数据量接近100万的表sales重复执行了两次全表扫描，这是不好的。在Oracle 11gr2中再执行一次范例SQL,看看Oracle 11gr2会如何处理该SQL：
SQL> select t2.prod_id as prod_id
2 from sales t2,customers t3
3 where t2.cust_id=t3.cust_id
4 and t3.cust_gender="MALE"
5 union all
6 select t2.prod_id as prod_id
7 from sales t2,customers t3
8 where t2.cust_id=t3.cust_id
9 and t3.cust_gender="FEMALE";no rows selected
Execution Plan
----------------------------------------------------------
Plan hash value: 576876893-------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost （%CPU）| Time | Pstart| Pstop |
-------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1804K| 37M| 764 （2）| 00:00:10 | | |
|* 1 | HASH JOIN | | 1804K| 37M| 764 （2）| 00:00:10 | | |
| 2 | VIEW | VW_JF_SET$0F531EB5 | 17992 | 228K| 235 （1）| 00:00:03 | | |
| 3 | UNION-ALL | | | | | | | |
|* 4 | VIEW | index$_join$_002 | 8829 | 61803 | 118 （1）| 00:00:02 | | |
|* 5 | HASH JOIN | | | | | | | |
| 6 | BITMAP CONVERSION TO ROWIDS| | 8829 | 61803 | 1 （0）| 00:00:01 | | |
|* 7 | BITMAP INDEX SINGLE VALUE | CUSTOMERS_GENDER_BIX | | | | | | |
| 8 | INDEX FAST FULL SCAN | CUSTOMERS_PK | 8829 | 61803 | 145 （0）| 00:00:02 | | |
|* 9 | VIEW | index$_join$_004 | 9163 | 64141 | 118 （1）| 00:00:02 | | |
|* 10 | HASH JOIN | | | | | | | |
| 11 | BITMAP CONVERSION TO ROWIDS| | 9163 | 64141 | 1 （0）| 00:00:01 | | |
|* 12 | BITMAP INDEX SINGLE VALUE | CUSTOMERS_GENDER_BIX | | | | | | |
| 13 | INDEX FAST FULL SCAN | CUSTOMERS_PK | 9163 | 64141 | 145 （0）| 00:00:02 | | |
| 14 | PARTITION RANGE ALL | | 918K| 8075K| 523 （1）| 00:00:07 | 1 | 28 |
| 15 | TABLE ACCESS FULL | SALES | 918K| 8075K| 523 （1）| 00:00:07 | 1 | 28 |
-------------------------------------------------------------------------------------------------------------------------Predicate Information （identified by operation id）:
--------------------------------------------------- 1 - access（"T2"."CUST_ID"="ITEM_1"）
4 - filter（"T3"."CUST_GENDER"="MALE"）
5 - access（ROWID=ROWID）
7 - access（"T3"."CUST_GENDER"="MALE"）
9 - filter（"T3"."CUST_GENDER"="FEMALE"）
10 - access（ROWID=ROWID）
12 - access（"T3"."CUST_GENDER"="FEMALE"）
Statistics
----------------------------------------------------------
47 recursive calls
0 db block gets
111 consistent gets
0 physical reads
0 redo size
287 bytes sent via SQL*Net to client
404 bytes received via SQL*Net from client
1 SQL*Net roundtrips to/from client
8 sorts （memory）
0 sorts （disk）
0 rows processed
从上述显示内容可以看出，对表sales的全表扫描只在Id=15的执行步骤处出现了一次，这说明同样的SQL在Oracle 11gR2中只需要全表扫描sales一次，另外，Id=2的执行步骤的Operation列的值是“VIEW”，对应的 Name列的值是“VW_JF_SET$0F531EB5”（这里VW_JF_SET$0F531EB5中的关键字是JF是Join Factorization的缩写），同时Id=1的执行步骤的Operation列的值是”HASH JOIN”。种种迹象都说明Oracle已经把范例SQL中union all的公共部分表sales提了出来，然后和union all 剩下的部分所形成的内嵌视图VW_JF_SET$0F531EB5做了一个哈希连接，即Oracle已经对范例SQL做了连接因式分解。连接因式分解除了能够避免对公共部分中所包含的表做重复访问之外，还可以提供更多的执行路径给优化器做选择，这就增加了走出更高效执行计划的可能性。创建一个视图view_jf，其视图定义SQL语句就是范例SQL：
SQL> create or replace view view_jf as
2 select t2.prod_id as prod_id
3 from sales t2,customers t3
4 where t2.cust_id=t3.cust_id
5 and t3.cust_gender="MALE"
6 union all
7 select t2.prod_id as prod_id
8 from sales t2,customers t3
9 where t2.cust_id=t3.cust_id
10 and t3.cust_gender="FEMALE";View created.
然后我们来看如下形式的SQL1：
select t1.prod_id,t1.prod_name
from products t1,view_jf
where t1.prod_id=view_jf.prod_id
and t1.prod_list_price>1000;在范例SQL1中，表products和视图view_jf做了表连接，而view_jf又是表sales和customers做表连接且做union all的视图。因为view_jf中包含了集合运算符union all，所以Oracle不能对它做视图合并，即Oracle必须将view_jf的定义SQL语句当作一个整体来单独执行，这也就意味着sales必须和customers做表连接，然后才能和表products再做表连接。在Oracle10gr2中执行范例SQL1：
SQL> select t1.prod_id,t1.prod_name
2 from products t1,view_jf
3 where t1.prod_id=view_jf.prod_id
4 and t1.prod_list_price>1000;no rows selected
Execution Plan
----------------------------------------------------------
Plan hash value: 3095468170--------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost （%CPU）| Time | Pstart| Pstop |
--------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 426K| 19M| 1128 （10）| 00:00:14 | | |
|* 1 | HASH JOIN | | 426K| 19M| 1128 （10）| 00:00:14 | | |
|* 2 | TABLE ACCESS FULL | PRODUCTS | 17 | 595 | 3 （0）| 00:00:01 | | |
| 3 | VIEW | VIEW_JF | 1837K| 22M| 1098 （8）| 00:00:14 | | |
| 4 | UNION-ALL | | | | | | | |
|* 5 | HASH JOIN | | 918K| 14M| 549 （8）| 00:00:07 | | |
|* 6 | VIEW | index$_join$_004 | 27236 | 186K| 122 （4）| 00:00:02 | | |
|* 7 | HASH JOIN | | | | | | | |
| 8 | BITMAP CONVERSION TO ROWIDS| | 27236 | 186K| 2 （0）| 00:00:01 | | |
|* 9 | BITMAP INDEX SINGLE VALUE | CUSTOMERS_GENDER_BIX | | | | | | |
| 10 | INDEX FAST FULL SCAN | CUSTOMERS_PK | 27236 | 186K| 147 （2）| 00:00:02 | | |
| 11 | PARTITION RANGE ALL | | 918K| 8075K| 413 （6）| 00:00:05 | 1 | 28 |
| 12 | TABLE ACCESS FULL | SALES | 918K| 8075K| 413 （6）| 00:00:05 | 1 | 28 |
|* 13 | HASH JOIN | | 918K| 14M| 549 （8）| 00:00:07 | | |
|* 14 | VIEW | index$_join$_006 | 27750 | 189K| 122 （4）| 00:00:02 | | |
|* 15 | HASH JOIN | | | | | | | |
| 16 | BITMAP CONVERSION TO ROWIDS| | 27750 | 189K| 2 （0）| 00:00:01 | | |
|* 17 | BITMAP INDEX SINGLE VALUE | CUSTOMERS_GENDER_BIX | | | | | | |
| 18 | INDEX FAST FULL SCAN | CUSTOMERS_PK | 27750 | 189K| 147 （2）| 00:00:02 | | |
| 19 | PARTITION RANGE ALL | | 918K| 8075K| 413 （6）| 00:00:05 | 1 | 28 |
| 20 | TABLE ACCESS FULL | SALES | 918K| 8075K| 413 （6）| 00:00:05 | 1 | 28 |
--------------------------------------------------------------------------------------------------------------------------Predicate Information （identified by operation id）:
--------------------------------------------------- 1 - access（"T1"."PROD_ID"="VIEW_JF"."PROD_ID"）
2 - filter（"T1"."PROD_LIST_PRICE">1000）
5 - access（"T2"."CUST_ID"="T3"."CUST_ID"）
6 - filter（"T3"."CUST_GENDER"="MALE"）
7 - access（ROWID=ROWID）
9 - access（"T3"."CUST_GENDER"="MALE"）
13 - access（"T2"."CUST_ID"="T3"."CUST_ID"）
14 - filter（"T3"."CUST_GENDER"="FEMALE"）
15 - access（ROWID=ROWID）
17 - access（"T3"."CUST_GENDER"="FEMALE"）
Statistics
----------------------------------------------------------
457 recursive calls
0 db block gets
126 consistent gets
6 physical reads
0 redo size
335 bytes sent via SQL*Net to client
389 bytes received via SQL*Net from client
1 SQL*Net roundtrips to/from client
18 sorts （memory）
0 sorts （disk）
0 rows processed
注意到Id=3的执行步骤的Operation列的值为“VIEW”，对应Name列的值为“VIEW_JF”，这说明Oracle确实没有对范例SQL1中的视图VIEW_JF做视图合并，即Oracle现在还是将VIEW_JF的定义SQL语句当作一个整体来单独执行，所以此时表sales和customers分别做了两次哈希连接，然后Oracle将这两次哈希连接的连接结果做了union all合并后，才和视图VIEW_JF外部的表products再做一次哈希连接。在Oracle 11gr2中再执行一次范例SQL1,看看在Oracle 11gr2中会如何处理该SQL：
SQL> select t1.prod_id,t1.prod_name
2 from products t1,view_jf
3 where t1.prod_id=view_jf.prod_id
4 and t1.prod_list_price>1000;no rows selected
Execution Plan
----------------------------------------------------------
Plan hash value: 408077510----------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost （%CPU）| Time | Pstart| Pstop |
----------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 37586 | 2092K| 687 （1）| 00:00:09 | | |
|* 1 | HASH JOIN | | 37586 | 2092K| 687 （1）| 00:00:09 | | |
| 2 | VIEW | VW_JF_SET$7C24F767 | 17992 | 228K| 235 （1）| 00:00:03 | | |
| 3 | UNION-ALL | | | | | | | |
|* 4 | VIEW | index$_join$_004 | 8829 | 61803 | 118 （1）| 00:00:02 | | |
|* 5 | HASH JOIN | | | | | | | |
| 6 | BITMAP CONVERSION TO ROWIDS | | 8829 | 61803 | 1 （0）| 00:00:01 | | |
|* 7 | BITMAP INDEX SINGLE VALUE | CUSTOMERS_GENDER_BIX | | | | | | |
| 8 | INDEX FAST FULL SCAN | CUSTOMERS_PK | 8829 | 61803 | 145 （0）| 00:00:02 | | |
|* 9 | VIEW | index$_join$_006 | 9163 | 64141 | 118 （1）| 00:00:02 | | |
|* 10 | HASH JOIN | | | | | | | |
| 11 | BITMAP CONVERSION TO ROWIDS | | 9163 | 64141 | 1 （0）| 00:00:01 | | |
|* 12 | BITMAP INDEX SINGLE VALUE | CUSTOMERS_GENDER_BIX | | | | | | |
| 13 | INDEX FAST FULL SCAN | CUSTOMERS_PK | 9163 | 64141 | 145 （0）| 00:00:02 | | |
| 14 | NESTED LOOPS | | 19142 | 822K| 452 （0）| 00:00:06 | | |
| 15 | NESTED LOOPS | | 19142 | 822K| 452 （0）| 00:00:06 | | |
|* 16 | TABLE ACCESS FULL | PRODUCTS | 1 | 35 | 3 （0）| 00:00:01 | | |
| 17 | PARTITION RANGE ALL | | | | | | 1 | 28 |
| 18 | BITMAP CONVERSION TO ROWIDS | | | | | | | |
|* 19 | BITMAP INDEX SINGLE VALUE | SALES_PROD_BIX | | | | | 1 | 28 |
| 20 | TABLE ACCESS BY LOCAL INDEX ROWID| SALES | 12762 | 112K| 452 （0）| 00:00:06 | 1 | 1 |
----------------------------------------------------------------------------------------------------------------------------Predicate Information （identified by operation id）:
--------------------------------------------------- 1 - access（"T2"."CUST_ID"="ITEM_1"）
4 - filter（"T3"."CUST_GENDER"="MALE"）
5 - access（ROWID=ROWID）
7 - access（"T3"."CUST_GENDER"="MALE"）
9 - filter（"T3"."CUST_GENDER"="FEMALE"）
10 - access（ROWID=ROWID）
12 - access（"T3"."CUST_GENDER"="FEMALE"）
16 - filter（"T1"."PROD_LIST_PRICE">1000）
19 - access（"T1"."PROD_ID"="T2"."PROD_ID"）
Statistics
----------------------------------------------------------
12 recursive calls
0 db block gets
14 consistent gets
0 physical reads
0 redo size
350 bytes sent via SQL*Net to client
404 bytes received via SQL*Net from client
1 SQL*Net roundtrips to/from client
4 sorts （memory）
0 sorts （disk）
0 rows processed
注意到Id=2的执行步骤的Operation列的值是“VIEW”，其对应的Name列的值是“VW_JF_SET$7C24F767”，和范例SQL一样，
这说明Oracle已经把视图view_jf的定义SQL语句中union all的公共部分表sales提了出来，然后用union all剩下的部分形成了
一个内嵌视图VW_JF_SET$7C24F767，即Oracle已经对范例SQL1做了连接因式分解。有一条SQL在测试环境中能做连接因式分解，测试环境与生产环境完全全相同，同事说在生产环境中发现不能进行连接因式分解，说帮忙看看原因是因为生产环境中与该SQL相关的三张表原来的数据被清空了，而新导入的数据没有收集统计信息赞成的。下面来模拟测试一下这个问题。下面的SQL在表sales,customers没有统计信息时不能进行连接因式分解
SQL> select t2.prod_id as prod_id
2 from sales t2,customers t3
3 where t2.cust_id=t3.cust_id
4 and t3.cust_gender="MALE"
5 union all
6 select t2.prod_id as prod_id
7 from sales t2,customers t3
8 where t2.cust_id=t3.cust_id
9 and t3.cust_gender="FEMALE";no rows selected
Execution Plan
----------------------------------------------------------
Plan hash value: 4184572088------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost （%CPU）| Time | Pstart| Pstop |
------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1837K| 28M| 1290 （2）| 00:00:16 | | |
| 1 | UNION-ALL | | | | | | | |
|* 2 | HASH JOIN | | 918K| 14M| 645 （2）| 00:00:08 | | |
|* 3 | VIEW | index$_join$_002 | 26739 | 182K| 119 （1）| 00:00:02 | | |
|* 4 | HASH JOIN | | | | | | | |
| 5 | BITMAP CONVERSION TO ROWIDS| | 26739 | 182K| 2 （0）| 00:00:01 | | |
|* 6 | BITMAP INDEX SINGLE VALUE | CUSTOMERS_GENDER_BIX | | | | | | |
| 7 | INDEX FAST FULL SCAN | CUSTOMERS_PK | 26739 | 182K| 145 （0）| 00:00:02 | | |
| 8 | PARTITION RANGE ALL | | 918K| 8075K| 523 （1）| 00:00:07 | 1 | 28 |
| 9 | TABLE ACCESS FULL | SALES | 918K| 8075K| 523 （1）| 00:00:07 | 1 | 28 |
|* 10 | HASH JOIN | | 918K| 14M| 645 （2）| 00:00:08 | | |
|* 11 | VIEW | index$_join$_004 | 27750 | 189K| 119 （1）| 00:00:02 | | |
|* 12 | HASH JOIN | | | | | | | |
| 13 | BITMAP CONVERSION TO ROWIDS| | 27750 | 189K| 2 （0）| 00:00:01 | | |
|* 14 | BITMAP INDEX SINGLE VALUE | CUSTOMERS_GENDER_BIX | | | | | | |
| 15 | INDEX FAST FULL SCAN | CUSTOMERS_PK | 27750 | 189K| 145 （0）| 00:00:02 | | |
| 16 | PARTITION RANGE ALL | | 918K| 8075K| 523 （1）| 00:00:07 | 1 | 28 |
| 17 | TABLE ACCESS FULL | SALES | 918K| 8075K| 523 （1）| 00:00:07 | 1 | 28 |
------------------------------------------------------------------------------------------------------------------------Predicate Information （identified by operation id）:
--------------------------------------------------- 2 - access（"T2"."CUST_ID"="T3"."CUST_ID"）
3 - filter（"T3"."CUST_GENDER"="MALE"）
4 - access（ROWID=ROWID）
6 - access（"T3"."CUST_GENDER"="MALE"）
10 - access（"T2"."CUST_ID"="T3"."CUST_ID"）
11 - filter（"T3"."CUST_GENDER"="FEMALE"）
12 - access（ROWID=ROWID）
14 - access（"T3"."CUST_GENDER"="FEMALE"）
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
0 consistent gets
0 physical reads
0 redo size
287 bytes sent via SQL*Net to client
404 bytes received via SQL*Net from client
1 SQL*Net roundtrips to/from client
0 sorts （memory）
0 sorts （disk）
0 rows processed 查询sales,customers表的统计信息发现没有
SQL> select a.table_name,a.num_rows,a.blocks,a.empty_blocks,a.avg_row_len,a.last_analyzed
2 from dba_tables a where a.owner="SH" and a.table_name="SALES";TABLE_NAME NUM_ROWS BLOCKS EMPTY_BLOCKS AVG_ROW_LEN LAST_ANALYZE
------------------------------ ---------- ---------- ------------ ----------- ------------
SALES SQL> select a.table_name,a.num_rows,a.blocks,a.empty_blocks,a.avg_row_len,a.last_analyzed
2 from dba_tables a where a.owner="SH" and a.table_name="CUSTOMERS";TABLE_NAME NUM_ROWS BLOCKS EMPTY_BLOCKS AVG_ROW_LEN LAST_ANALYZE
------------------------------ ---------- ---------- ------------ ----------- ------------
CUSTOMERS 查询sales,customers表的修改记录这里是2014-11-28是最后的修改日期
SQL> select object_name,created,last_ddl_time from dba_objects where owner="SH" and object_name="SALES";OBJECT_NAME CREATED LAST_DDL_TIM
-------------------------------------------------------------------------------------------------------------------------------- ------------ ------------
SALES 28-NOV-14 28-NOV-14 SQL> select object_name,created,last_ddl_time from dba_objects where owner="SH" and object_name="CUSTOMERS";OBJECT_NAME CREATED LAST_DDL_TIM
-------------------------------------------------------------------------------------------------------------------------------- ------------ ------------
CUSTOMERS 28-NOV-14 28-NOV-14
表sales,customers的统计信息收集时间是2014-11-28
SQL> select * from dba_tab_stats_history where owner="SH" and table_name="SALES";OWNER TABLE_NAME PARTITION_NAME SUBPARTITION_NAME STATS_UPDATE_TIME
------------------------------ ------------------------------ ------------------------------ ------------------------------ ---------------------------------------------------------------------------
SH SALES 28-NOV-14 01.07.30.055660 PM +08:00
SQL> select * from dba_tab_stats_history where owner="SH" and table_name="CUSTOMERS";OWNER TABLE_NAME PARTITION_NAME SUBPARTITION_NAME STATS_UPDATE_TIME
------------------------------ ------------------------------ ------------------------------ ------------------------------ ---------------------------------------------------------------------------
SH CUSTOMERS 28-NOV-14 01.07.37.664982 PM +08:00 在重新导入数据后对表sales,customers收集统计信息
SQL> exec dbms_stats.gather_table_stats（ownname => "SH",tabname => "SALES",method_opt => "for all columns size auto"）;PL/SQL procedure successfully completed.SQL> exec dbms_stats.gather_table_stats（ownname => "SH",tabname => "SALES",estimate_percent => 100,method_opt => "for all columns size auto"）;PL/SQL procedure successfully completed.SQL> exec dbms_stats.gather_table_stats（ownname => "SH",tabname => "CUSTOMERS",estimate_percent => 100,method_opt => "for all columns size auto"）;PL/SQL procedure successfully completed.SQL> select a.table_name,a.num_rows,a.blocks,a.empty_blocks,a.avg_row_len,a.last_analyzed from dba_tables a where a.owner="SH" and a.table_name="CUSTOMERS";TABLE_NAME NUM_ROWS BLOCKS EMPTY_BLOCKS AVG_ROW_LEN LAST_ANALYZE
------------------------------ ---------- ---------- ------------ ----------- ------------
CUSTOMERS 55500 1486 0 181 12-DEC-14SQL> select a.table_name,a.num_rows,a.blocks,a.empty_blocks,a.avg_row_len,a.last_analyzed from dba_tables a where a.owner="SH" and a.table_name="SALES";TABLE_NAME NUM_ROWS BLOCKS EMPTY_BLOCKS AVG_ROW_LEN LAST_ANALYZE
------------------------------ ---------- ---------- ------------ ----------- ------------
SALES 918843 1907 0 29 12-DEC-14 收集统计信息后能够正常进行连接因式分解：
SQL> select t2.prod_id as prod_id
2 from sales t2,customers t3
3 where t2.cust_id=t3.cust_id
4 and t3.cust_gender="MALE"
5 union all
6 select t2.prod_id as prod_id
7 from sales t2,customers t3
8 where t2.cust_id=t3.cust_id
9 and t3.cust_gender="FEMALE";no rows selected
Execution Plan
----------------------------------------------------------
Plan hash value: 576876893-------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost （%CPU）| Time | Pstart| Pstop |
-------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1804K| 37M| 764 （2）| 00:00:10 | | |
|* 1 | HASH JOIN | | 1804K| 37M| 764 （2）| 00:00:10 | | |
| 2 | VIEW | VW_JF_SET$0F531EB5 | 17992 | 228K| 235 （1）| 00:00:03 | | |
| 3 | UNION-ALL | | | | | | | |
|* 4 | VIEW | index$_join$_002 | 8829 | 61803 | 118 （1）| 00:00:02 | | |
|* 5 | HASH JOIN | | | | | | | |
| 6 | BITMAP CONVERSION TO ROWIDS| | 8829 | 61803 | 1 （0）| 00:00:01 | | |
|* 7 | BITMAP INDEX SINGLE VALUE | CUSTOMERS_GENDER_BIX | | | | | | |
| 8 | INDEX FAST FULL SCAN | CUSTOMERS_PK | 8829 | 61803 | 145 （0）| 00:00:02 | | |
|* 9 | VIEW | index$_join$_004 | 9163 | 64141 | 118 （1）| 00:00:02 | | |
|* 10 | HASH JOIN | | | | | | | |
| 11 | BITMAP CONVERSION TO ROWIDS| | 9163 | 64141 | 1 （0）| 00:00:01 | | |
|* 12 | BITMAP INDEX SINGLE VALUE | CUSTOMERS_GENDER_BIX | | | | | | |
| 13 | INDEX FAST FULL SCAN | CUSTOMERS_PK | 9163 | 64141 | 145 （0）| 00:00:02 | | |
| 14 | PARTITION RANGE ALL | | 918K| 8075K| 523 （1）| 00:00:07 | 1 | 28 |
| 15 | TABLE ACCESS FULL | SALES | 918K| 8075K| 523 （1）| 00:00:07 | 1 | 28 |
-------------------------------------------------------------------------------------------------------------------------Predicate Information （identified by operation id）:
--------------------------------------------------- 1 - access（"T2"."CUST_ID"="ITEM_1"）
4 - filter（"T3"."CUST_GENDER"="MALE"）
5 - access（ROWID=ROWID）
7 - access（"T3"."CUST_GENDER"="MALE"）
9 - filter（"T3"."CUST_GENDER"="FEMALE"）
10 - access（ROWID=ROWID）
12 - access（"T3"."CUST_GENDER"="FEMALE"）
Statistics
----------------------------------------------------------
47 recursive calls
0 db block gets
111 consistent gets
0 physical reads
0 redo size
287 bytes sent via SQL*Net to client
404 bytes received via SQL*Net from client
1 SQL*Net roundtrips to/from client
8 sorts （memory）
0 sorts （disk）
0 rows processed在Oracle 11gr2及其后续的版本中，即使由于在视图定义SQL语句中包含了集合运算符union all而导致 Oracle不能对其做视图合并，Oracle也不一定会把该视图的定义SQL语句当作一个整体来单独执行，因为此时Oracle还可能会对其做连接因式分解。这里需要注意的是，Oracle对包含union all的目标SQL做连接因式分解的前提条件是，连接因式分解后的等价改写SQL和原SQL在语义上完全等价，如果不能满足这一点，那么Oracle就不会做连接因式分解。参考:
https://blogs.oracle.com/optimizer/entry/optimizer_transformations_join_factorization更多Oracle相关信息见Oracle 专题页面 http://www.linuxidc.com/topicnews.aspx？tid=12本文永久更新链接地址