Oracle的SQL优化一 - 数据库编程

IGHT OUTER | | 57750 | 21M| | 1450 (1)| 00:00:18 | | | | 25 | TABLE ACCESS FULL | TBL_CUSTOM | 537 | 17184 | | 6 (0)| 00:00:01 | | | |* 26 | HASH JOIN RIGHT OUTER | | 57750 | 19M| | 1443 (1)| 00:00:18 | | | | 27 | TABLE ACCESS FULL | TBL_BUSINESS_TYPE | 38 | 760 | | 3 (0)| 00:00:01 | | | | 28 | PARTITION RANGE ITERATOR| | 57750 | 18M| | 1439 (1)| 00:00:18 | 181 | 180 | |* 29 | TABLE ACCESS FULL | TBL_BILL | 57750 | 18M| | 1439 (1)| 00:00:18 | 181 | 180 | |* 30 | TABLE ACCESS FULL | TBL_USER_STORES | 1 | 8 | | 7 (0)| 00:00:01 | | | |* 31 | TABLE ACCESS FULL | TBL_USER_STORES | 1 | 8 | | 7 (0)| 00:00:01 | | | |* 32 | TABLE ACCESS BY INDEX ROWID | TBL_STORES | 1 | 12 | | 1 (0)| 00:00:01 | | | |* 33 | INDEX UNIQUE SCAN | XPK_TBL_STORES | 1 | | | 0 (0)| 00:00:01 | | | |* 34 | TABLE ACCESS BY INDEX ROWID | TBL_STORES | 1 | 12 | | 1 (0)| 00:00:01 | | | |* 35 | INDEX UNIQUE SCAN | XPK_TBL_STORES | 1 | | | 0 (0)| 00:00:01 | | | -------------------------------------------------------------------------------------------------------------------------------------------

在B schema执行该SQL耗费1万多次逻辑读。

A Schema的执行计划：

----------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time | Pstart| Pstop |
----------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 20 | 152K| | 50624 (1)| 00:10:08 | | |
| 1 | TABLE ACCESS BY GLOBAL INDEX ROWID | TBL_BILL | 1 | 31 | | 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 | 47 | | 1 (0)| 00:00:01 | | |
|* 4 | INDEX RANGE SCAN 




| IDX_MEMPOINT_LOGS_BILLNO | 1 | | | 1 (0)| 00:00:01 | | |
|* 5 | VIEW | | 20 | 152K| | 50624 (1)| 00:10:08 | | |
|* 6 | COUNT STOPKEY | | | | | | | | |
| 7 | VIEW | | 3600 | 26M| | 50624 (1)| 00:10:08 | | |
|* 8 | SORT ORDER BY STOPKEY | | 3600 | 2000K| 210M| 50624 (1)| 00:10:08 | | |
|* 9 | FILTER | | | | | | | | |
|* 10 | HASH JOIN | | 378K| 205M| | 5203 (1)| 00:01:03 | | |
| 11 | TABLE ACCESS FULL | TBL_BILL_CHECKSTATE_SHOW | 200 | 9600 | | 3 (0)| 00:00:01 | | |
|* 12 | HASH JOIN | | 151K| 75M| | 5199 (1)| 00:01:03 | | |
| 13 | INDEX FAST FULL SCAN | XPK_TBL_STORES | 616 | 3080 | | 2 (0)| 00:00:01 | | |
|* 14 | HASH JOIN RIGHT OUTER | | 151K| 74M| | 5196 (1)| 00:01:03 | | |
| 15 | TABLE ACCESS FULL | TBL_STORES | 616 | 20944 | | 8 (0)| 00:00:01 | | |
|* 16 | HASH JOIN RIGHT OUTER | | 151K| 69M| | 5187 (1)| 00:01:03 | | |
| 17 | TABLE ACCESS FULL | TBL_STORES | 616 | 20944 | | 8 (0)| 00:00:01 | | |
|* 18 | HASH JOIN RIGHT OUTER | | 151K| 64M| | 5178 (1)| 00:01:03 | | |
| 19 | TABLE ACCESS FULL | TBL_CUSTOM | 93 | 2139 | | 4 (0)| 00:00:01 | | |
|* 20 | HASH JOIN RIGHT OUTER | | 151K| 61M| | 5173 (1)| 00:01:03 | | |
| 21 | TABLE ACCESS FULL | TBL_BILLCASE | 40 | 800 | | 4 (0)| 00:00:01 | | |
|* 22 | HASH JOIN RIGHT OUTER | | 151K| 58M| | 5168 (1)| 00:01:03 | | |
| 23 | TABLE ACCESS FULL | TBL_BUSINESS_TYPE | 33 | 660 | | 3 (0)| 00:00:01 | | |
|* 24 | HASH JOIN RIGHT OUTER | | 151K| 55M| | 5164 (1)| 00:01:02 | | |
| 25 | TABLE ACCESS FULL | M_MEMDETAIL | 5 | 120 | | 4 (0)| 00:00:01 | | |
| 26 | NESTED LOOPS OUTER | | 151K| 52M| | 5159 (1)| 00:01:02 | | |
| 27 | PARTITION RANGE ITERATOR | | 151K| 50M| | 5158 (1)| 00:01:02 | 181 | 180 |
|* 28 | TABLE ACCESS FULL | TBL_BILL | 151K| 50M| | 5158 (1)| 00:01:02 | 181 | 180 |
| 29 | TABLE ACCESS BY INDEX ROWID| M_MEMDETAILCARD | 1 | 14 | | 1 (0)| 00:00:01 | | |
|* 30 | INDEX UNIQUE SCAN | XPK_TBL_MEMDETAIL | 1 | | | 0 (0)| 00:00:0

Oracle的SQL优化一(二)