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):
---------------------------------------------------