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-14 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='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
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/26015009/viewspace-1366323/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- Oracle(+)連線與Join連線Oracle
- Oracle 的 hash join連線方式Oracle
- Oracle表連線操作——Hash Join（雜湊連線）下Oracle
- Oracle表連線操作——Hash Join（雜湊連線）上Oracle
- Oracle 內外連線 join 總結Oracle
- LEFT JOIN 和JOIN 多表連線
- Oracle表連線操作——Merge Sort Join（合併排序連線）Oracle排序
- 連線查詢簡析 join 、 left join 、 right join
- 外連線(outer join)示例
- Hibernate連線查詢join
- Oracle優化案例-用left join代替反連線 not in not exists（十）Oracle優化
- 聊聊CBO的連線排列（Join Permutation）
- MySql的join（連線）查詢 （三表 left join 寫法）MySql
- 表的連線方式:NESTED LOOP、HASH JOIN、SORT MERGE JOIN(轉)OOP
- LINQ系列：LINQ to SQL Join連線SQL
- 表連線 join和(+)、union和uion allUI
- sql 連線查詢例項（left join）三表連線查詢SQL
- Oracle左連線,右連線Oracle
- 多表連線的三種方式詳解 hash join、merge join、 nested loopOOP
- SQL語句中不同的連線JOIN及SQL中join的各種用法SQL
- [資料庫][SQL]圖解各種連線join資料庫SQL圖解
- mysql常用連線查詢join，left,right,crossMySqlROS
- Apache Spark SQL的高階Join連線技術ApacheSparkSQL
- 排序合併連線(sort merge join)的原理排序
- 通過swap_join_inputs來控制多表連線hash join中的build tableUI
- ORACLE 半連線與反連線Oracle
- Oracle的左連線和右連線Oracle
- 【SQL 學習】表連線--natural join 的一個bugSQL
- 兩種連線的表達 ：left(right) join 和 (+)
- PHP 連線 OraclePHPOracle
- Servlet連線OracleServletOracle
- PHP 連線oraclePHPOracle
- jdbc連線oracleJDBCOracle
- sqlserver連線oracleSQLServerOracle
- mysql 連線oracleMySqlOracle
- navicat 連線 oracleOracle
- Oracle 表連線Oracle
- SQLSERVER 連線 ORACLESQLServerOracle