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