有一个Oracle脚本跑了很久不出结果，优化之后瞬间出结果。原语句如下：
SQL> explain plan for
2 select *
3 from crm_dg.tb_ba_channelstaff a,
4 crm_dg.tb_ba_subscription_hist b,
5 crm_dg.tb_cm_serv c
6 where a.subs_id = b.subs_id
7 and b.serv_id = c.serv_id
8 and a.create_date >= to_date('20150201', 'yyyymmdd')
9 and c.acc_nbr = '15322926784';
Explained.
Elapsed: 00:00:00.03
SQL> @getplan
'general,outline,starts'
Enter value for plan type:
PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 1257311340
---------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 562 | 12 (0)| 00:00:01 |
| 1 | NESTED LOOPS | | 1 | 562 | 12 (0)| 00:00:01 |
| 2 | MERGE JOIN CARTESIAN | | 2 | 716 | 8 (0)| 00:00:01 |
| 3 | TABLE ACCESS BY INDEX ROWID | PROD_INST | 1 | 273 | 4 (0)| 00:00:01 |
|* 4 | INDEX RANGE SCAN | IX_PROD_INST_NUM | 1 | | 3 (0)| 00:00:01 |
| 5 | BUFFER SORT | | 2 | 170 | 4 (0)| 00:00:01 |
| 6 | TABLE ACCESS BY INDEX ROWID| TB_BA_CHANNELSTAFF | 2 | 170 | 4 (0)| 00:00:01 |
|* 7 | INDEX RANGE SCAN | IDX_BA_CHANNELSTAFF_CRT_DATE | 2 | | 2 (0)| 00:00:01 |
|* 8 | TABLE ACCESS BY INDEX ROWID | ORDER_ITEM_HIST | 1 | 204 | 2 (0)| 00:00:01 |
|* 9 | INDEX UNIQUE SCAN | PKH_ORDER_ITEM | 1 | | 1 (0)| 00:00:01 |
---------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - access("ACC_NBR"='15322926784')
7 - access("A"."CREATE_DATE">=TO_DATE(' 2015-02-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))
8 - filter("SERV_ID"="PROD_INST_ID")
9 - access("A"."SUBS_ID"="ORDER_ITEM_ID")
......getting segment size......
OWNER SEGMENT_NAME SEGMENT_TYPE Size(Mb)
-------------------- ------------------------------ -------------------- ----------
CRM_DG IX_PROD_INST_NUM INDEX 602.0625
CRM_DG IDX_BA_CHANNELSTAFF_CRT_DATE INDEX 1799.5625
CRM_DG PKH_ORDER_ITEM INDEX 6199
CRM_DG PROD_INST TABLE 5126
CRM_DG TB_BA_CHANNELSTAFF TABLE 7390
CRM_DG ORDER_ITEM_HIST TABLE 48776
6 rows selected.
Elapsed: 00:00:01.26
......getting table infomation......