两个SCHEMA，开发反馈数据量相差无几，但一模一样的SQL，A Schema要跑6分钟，B Schema只5秒就出来了。SQL语句如下：
SELECT *
FROM (SELECT A.*, ROWNUM RN
FROM (select t3.check_show,
t1.*,
t2.storesid,
to_char(rdate, 'yyyy-mm-dd') as to_rdate,
to_char(submitdate, 'yyyy-mm-dd') as to_submitdate,
to_char(expected_shipdate, 'yyyy-mm-dd') as to_expected_shipdate,
to_char(expected_shipdate, 'yyyy-mm-dd') as to_expected_shipdate2,
to_char(billingdate, 'yyyy-mm-dd hh24:mi:ss') as to_billingdate,
to_char(dealtotal, 'fm9999999990.00') as to_dealtotal,
(nvl(cashreceiptssum, 0) + nvl(received, 0)) as to_cashreceiptssum,
to_char((nvl(debtsum, 0) - nvl(received, 0)),
'fm9999999990.00') to_debtsum,
nvl(amounttotal, 0) as to_amounttotal,
(select a.reduce_points
from SchemaB.m_mempoint_logs a
where a.billno = t1.billno
and a.billtype = 4) as reduce_points
from SchemaB.v0bill t1,
SchemaB.tbl_stores t2,
SchemaB.TBL_BILL_CHECKSTATE_SHOW t3
where ((posbillno is not null and BCOMPLETE = 1) or
posbillno is null)
and t1.StoreRoomID = t2.storesid
and t1.billsubcase = t3.billsubcase
and t1.check_status = t3.check_status
and (instoreroomid in
(select storesid
from SchemaB.tbl_user_stores
where employeeid = 4352) or
outstoreroomid in
(select storesid
from SchemaB.tbl_user_stores
where employeeid = 4352))
and servicStatus = 1
and rdate >= to_date('2015-06-04', 'yyyy-mm-dd')
and rdate <=
to_date('2015-06-11 23:59:59', 'yyyy-mm-dd hh24:mi:ss')
and (t1.billsubcase = '75' or t1.billsubcase = '711' or
(instoreroomid in
(select storesid
from SchemaB.tbl_stores
where areacode like '001%') or
outstoreroomid in
(select storesid
from SchemaB.tbl_stores
where areacode like '001%')))
ORDER by rdate desc, billingdate desc, billno desc) A
WHERE ROWNUM <= (1 * 20))
WHERE RN > ((1 - 1) * 20);
B Schema的执行计划：
-------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time | Pstart| Pstop |
-------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 20 | 152K| | 16718 (1)| 00:03:21 | | |
| 1 | TABLE ACCESS BY GLOBAL INDEX ROWID | TBL_BILL | 1 | 25 | | 3 (0)| 00:00:01 | ROWID | ROWID |
|* 2 | INDEX UNIQUE SCAN | XPK_BILL_P | 1 | | | 2 (0)| 00:00:01 | | |
|* 3 | TABLE ACCESS BY INDEX ROWID | M_MEMPOINT_LOGS | 1 | 24 | | 4 (0)| 00:00:01 | | |
|* 4 | INDEX RANGE SCAN | IDX_MEMPOINT_LOGS_BILLNO | 1 | | | 3 (0)| 00:00:01 | | |
|* 5 | VIEW | | 20 | 152K| | 16718 (1)| 00:03:21 | | |
|* 6 | COUNT STOPKEY | | | | | | | | |
| 7 | VIEW | | 917 | 6989K| | 16718 (1)| 00:03:21 | | |
|* 8 | SORT ORDER BY STOPKEY | | 917 | 522K| 57M| 16718 (1)| 00:03:21 | | |
|* 9 | FILTER | | | | | | | | |
|* 10 | HASH JOIN | | 96250 | 53M| | 4844 (1)| 00:00:59 | | |
| 11 | TABLE ACCESS FULL | TBL_BILL_CHECKSTATE_SHOW | 200 | 9600 | | 3 (0)| 00:00:01 | | |
|* 12 | HASH JOIN | | 57750 | 29M| | 4840 (1)| 00:00:59 | | |
| 13 | INDEX FULL SCAN | XPK_TBL_STORES | 85 | 340 | | 1 (0)| 00:00:01 | | |
|* 14 | HASH JOIN RIGHT OUTER | | 57750 | 29M| | 4838 (1)| 00:00:59 | | |
| 15 | TABLE ACCESS FULL | TBL_STORES | 85 | 2550 | | 4 (0)| 00:00:01 | | |
|* 16 | HASH JOIN RIGHT OUTER | | 57750 | 27M| | 4834 (1)| 00:00:59 | | |
| 17 | TABLE ACCESS FULL | TBL_STORES | 85 | 2550 | | 4 (0)| 00:00:01 | | |
|* 18 | HASH JOIN RIGHT OUTER | | 57750 | 25M| 4200K| 4829 (1)| 00:00:58 | | |
| 19 | TABLE ACCESS FULL | M_MEMDETAIL | 89469 | 3145K| | 380 (1)| 00:00:05 | | |
|* 20 | HASH JOIN RIGHT OUTER | | 57750 | 24M| | 3020 (1)| 00:00:37 | | |
| 21 | TABLE ACCESS FULL | TBL_BILLCASE | 40 | 840 | | 4 (0)| 00:00:01 | | |
|* 22 | HASH JOIN RIGHT OUTER | | 57750 | 22M| 3176K| 3015 (1)| 00:00:37 | | |
| 23 | TABLE ACCESS FULL | M_MEMDETAILCARD | 90213 | 2114K| | 310 (1)| 00:00:04 | | |
|* 24 | HASH JOIN R