select count(1) over() as tcnt,
rownum as rn,
O.CustomerID,
O.CustomerName,
O.CustomerType,
O.CertType,
O.CertID,
O.MFCustomerID,
O.MCCustomerID,
getManageUserID(O.CustomerID) as ManageUserID,
getManageOrgName(O.CustomerID) as ManageOrgName,
getManageUserName(O.CustomerID) as ManageUserName,
O.Private,
O.Isresure,
getcustomerbalance3(O.CustomerID) as Balance
from customer_info O
where 1 = 1
and (exists (Select 'X'
From IND_INFO II
Where O.CustomerID = II. CustomerID
and II.UpdateDate = '2019/06/26'
and II.UpdateUserID = '017634') or exists
(Select 'X'
From ENT_INFO EI
Where O.CustomerID = EI.CustomerID
and EI.UpdateDate = '2019/06/26'
and EI.UpdateUserID = '017634'))
and nvl(O.belongCorporaTion, '9999') = '9999'
and (EXISTS (Select 'X'
from CUSTOMER_BELONG CB
where CB.CustomerID = O.CUSTOMERID
and CB.USERID = '017634'
and CB.BELONGATTRIBUTE = '1') or Exists
(select 'X'
from BUSINESSRIGHT_APPLY ba
where ba.CustomerID = O.CustomerID
and ba.UserID = '017634'
and ba.ApplyStatus = '020'))
and EXISTS
(Select 'X'
from CUSTOMER_PREEVALUATE CP1
where CP1.serialno = (select max(CP.serialno)
from CUSTOMER_PREEVALUATE CP
where CP.INPUTUSERID = '017634'
and CP.CustomerId = O.CustomerId
and CP.Serialno like '2%')
and CP1.ApplyResult in ('1010', '1020', '1030', '3010')
and CP1.InputDate >=
to_char(add_months(sysdate, -1), 'yyyy/mm/dd'));
TLXD@xdxtdb> TLXD@xdxtdb> select * from table(dbms_xplan.display_cursor(null,null,'ALLSTATS LAST'));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID 22rnph35yxqtg, child number 0
-------------------------------------
select count(1) over() as tcnt, rownum as rn,
O.CustomerID, O.CustomerName, O.CustomerType,
O.CertType, O.CertID, O.MFCustomerID,
O.MCCustomerID, getManageUserID(O.CustomerID) as ManageUserID,
getManageOrgName(O.CustomerID) as ManageOrgName,
getManageUserName(O.CustomerID) as ManageUserName, O.Private,
O.Isresure, getcustomerbalance3(O.CustomerID) as Balance
from customer_info O where 1 = 1 and (exists (Select 'X'
From IND_INFO II Where O.CustomerID = II.
CustomerID and II.UpdateDate = '2019/06/26'
and II.UpdateUserID = '017634') or exists (Select 'X'
From ENT_INFO EI Where O.CustomerID =
EI.CustomerID and EI.UpdateDate = '2019/06/26'
and EI.UpdateUserID = '017634')) and nvl(O.belongCorporaTion,
'9999') = '9999' and (EXISTS (Select 'X'
Plan hash value: 3456449175
---------------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | Reads | OMem | 1Mem | Used-Mem |
---------------------------------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 2 |00:01:11.80 | 9608K| 138K| | | |
| 1 | WINDOW BUFFER | | 1 | 1 | 2 |00:01:11.80 | 9608K| 138K| 2048 | 2048 | 2048 (0)|
| 2 | COUNT | | 1 | | 2 |00:01:11.80 | 9608K| 138K| | | |
|* 3 | FILTER | | 1 | | 2 |00:01:11.80 | 9608K| 138K| | | |
|* 4 | TABLE ACCESS FULL | CUSTOMER_INFO | 1 | 3297K| 3298K|00:00:07.04 | 96769 | 36696 | | | |
|* 5 | TABLE ACCESS BY INDEX ROWID | CUSTOMER_PREEVALUATE | 3298K| 1 | 28 |00:01:02.54 | 9511K| 101K| | | |
|* 6 | INDEX UNIQUE SCAN | SYS_C0039976 | 3298K| 1 | 29 |00:01:00.91 | 9511K| 101K| | | |
| 7 | SORT AGGREGATE | | 3298K| 1 | 3298K|00:00:58.13 | 9511K| 101K| | | |
|* 8 | TABLE ACCESS BY INDEX ROWID BATCHED| CUSTOMER_PREEVALUATE | 3298K| 1 | 30 |00:00:55.58 | 9511K| 101K| | | |
|* 9 | INDEX RANGE SCAN | IDX1_CUSTOMER_PREEVALUATE | 3298K| 2 | 3129K|00:00:17.88 | 6394K| 10062 | | | |
|* 10 | TABLE ACCESS BY INDEX ROWID | IND_INFO | 28 | 1 | 2 |00:00:00.01 | 186 | 8 | | | |
|* 11 | INDEX UNIQUE SCAN | PK_IND_INFO | 28 | 1 | 27 |00:00:00.01 | 125 | 6 | | | |
|* 12 | TABLE ACCESS BY INDEX ROWID | ENT_INFO | 26 | 1 | 0 |00:00:00.01 | 54 | 3 | | | |
|* 13 | INDEX UNIQUE SCAN | PK_ENT_INFO | 26 | 1 | 1 |00:00:00.01 | 52 | 2 | | | |
|* 14 | TABLE ACCESS BY INDEX ROWID BATCHED | CUSTOMER_BELONG | 2 | 1 | 2 |00:00:00.01 | 31 | 1 | | | |
|* 15 | INDEX RANGE SCAN | PK_CUSTOMER_BELONG | 2 | 1 | 2 |00:00:00.01 | 27 | 1 | | | |
|* 16 | TABLE ACCESS FULL | BUSINESSRIGHT_APPLY | 0 | 1 | 0 |00:00:00.01 | 0 | 0 | | | |
---------------------------------------------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - filter(( IS NOT NULL AND ( IS NOT NULL OR IS NOT NULL) AND ( IS NOT NULL OR IS NOT NULL)))
4 - filter(NVL("O"."BELONGCORPORATION",'9999')='9999')
5 - filter(("CP1"."INPUTDATE">=TO_CHAR(ADD_MONTHS(SYSDATE@!,-1),'yyyy/mm/dd') AND INTERNAL_FUNCTION("CP1"."APPLYRESULT")))
6 - access("CP1"."SERIALNO"=)
8 - filter(("CP"."INPUTUSERID"='017634' AND "CP"."SERIALNO" LIKE '2%'))
9 - access("CP"."CUSTOMERID"=:B1)
10 - filter(("II"."UPDATEUSERID"='017634' AND "II"."UPDATEDATE"='2019/06/26'))
11 - access("II"."CUSTOMERID"=:B1)
12 - filter(("EI"."UPDATEUSERID"='017634' AND "EI"."UPDATEDATE"='2019/06/26'))
13 - access("EI"."CUSTOMERID"=:B1)
14 - filter("CB"."BELONGATTRIBUTE"='1')
15 - access("CB"."CUSTOMERID"=:B1 AND "CB"."USERID"='017634')
filter("CB"."USERID"='017634')
16 - filter(("BA"."CUSTOMERID"=:B1 AND "BA"."USERID"='017634' AND "BA"."APPLYSTATUS"='020'))