r select count(*)
from (select quxiantodishi(ti.city_code) city_code, t.trade_money
from trade_history t, sp_info ti
where t.sp_id = ti.sp_id
and t.partition_id = 2
and t.start_time between
to_date('20160202 00:00:00', 'YYYYMMDD HH24:MI:SS') and
to_date('20160202 23:59:59', 'YYYYMMDD HH24:MI:SS')
and t.des_payorg_id = 'DX05'
and t.ap_id = '306'
and t.ability_type = '0101'
and t.result = '0') aa
where '0' || city_code = '0591'
-------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Pstart| Pstop |
-------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 66 | | 10269 (3)| | |
| 1 | SORT AGGREGATE | | 1 | 66 | | | | |
|* 2 | TABLE ACCESS BY LOCAL INDEX ROWID | TRADE_HISTORY | 1 | 46 | | 10269 (3)| 2 | 2 |
| 3 | NESTED LOOPS | | 21 | 1386 | | 10269 (3)| | |
|* 4 | TABLE ACCESS FULL | SP_INFO | 78 | 1560 | | 59 (7)| | |
| 5 | PARTITION RANGE SINGLE | | | | | | 2 | 2 |
| 6 | BITMAP CONVERSION TO ROWIDS | | | | | | | |
| 7 | BITMAP AND | | | | | | | |
| 8 | BITMAP CONVERSION FROM ROWIDS| | | | | | | |
| 9 | SORT ORDER BY | | | | | | | |
|* 10 | INDEX RANGE SCAN | IDX_TRADE_HISTORY_ID | 2878 | | | 6 (0)| 2 | 2 |
| 11 | BITMAP CONVERSION FROM ROWIDS| | | | | | | |
| 12 | SORT ORDER BY | | | | 19M| | | |
|* 13 | INDEX RANGE SCAN | IDX_START_TIME | 2878 | | | 38 (6)| 2 | 2 |
-------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("T"."DES_PAYORG_ID"='DX05' AND "T"."AP_ID"='306' AND "T"."ABILITY_TYPE"='0101' AND
"T"."RESULT"='0' AND "T"."PARTITION_ID"=2)
4 - filter('0'||"QUXIANTODISHI"("TI"."CITY_CODE")='0591')
10 - access("T"."SP_ID"="TI"."SP_ID")
filter("T"."SP_ID"="TI"."SP_ID")
13 - access("T"."START_TIME">=TO_DATE(' 2016-02-02 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND
"T"."START_TIME"<=TO_DATE(' 2016-02-02 23:59:59', 'syyyy-mm-dd hh24:mi:ss'))
Note
-----
- 'PLAN_TABLE' is old version