Hi,
Appreciations for valuable inputs.
SELECT distinct dt.POID_ID0 as tid,decode(act.status,10100,'Active',10102,'Inactive',10103,'Closed')
as dstatus,nvl(check_no, 'N/A') as check_no ,nvl(bank_name,'N/A') as bank_name,nvl(receipt_no,'N/A')
as receipt_no,DECODE(payment_type,1001,'Cash',1002,'Cheque','N/A') as payment_type , nvl(dt.trans_id,'-')
as collsystid, DECODE(e.prov_tag,'pool=dcdealer','DC','pool=idcdealer','IDC',NULL,'DC') AS sales_mode,
case when dt.status not in (0,2,4) then to_char(s2diq(dt.mod_t)) else 'N/A' end as Setdate,act.ACCOUNT_NO
as dacno,dt.ACCOUNT_NO as acno,dt.LOGIN as login,dt.AMOUNT as amt,dt.STATUS as stat,
b.CITY as dcity,b.FIRST_NAME || ' ' || b.MIDDLE_NAME || ' ' || b.LAST_NAME as dname,
to_char(S2DIQ(dt.CREATED_T),'dd-Mon-yyyy') as cdate ,pt.name as plan, dt.ap_flag, c.status as ap_status,
nvl(decode(dt.ACCOUNT_STATUS,10100,'Inservice',10102,'Suspend',10103,'Closed'),'N/A')
as status_at_pay,case when to_char(s2diq(dt.LAST_STATUS_T),'dd-Mon-yyyy')='01-Jan-1970'
then 'N/A' else to_char(s2diq(dt.LAST_STATUS_T),'dd-Mon-yyyy') end as spdate,
decode(dt.RENEWAL_TYPE,201,'Prepaid',2011,'Prepaid',2012,'Prepaid',202,'Suvidha',2021,'Suvidha',2022,
'Suvidha',207,'Suvidha',2071,'Suvidha',2072,'Suvidha') as cust_type,f.LOGIN as dlogin,nvl(f.AAC_VENDOR,'N/A')
as cp_fos_name,dt.created_t,dt.credit_status
FROM account_nameinfo_t b, plan_t pt,iqara_online_dealer_renewals_t dt,account_t act,
iqara_ap_renewals_t c,IQ_PROV_TAGS_T E,ACCOUNT_PRODUCTS_T D,service_t f
WHERE dt.CREATED_T BETWEEN D2S('19-jun-09') AND D2S('23-jun-09')
and dt.PLAN_POID_ID0=pt.POID_ID0
and act.business_type < 4
and b.OBJ_ID0 = dt.PARENT_OBJ_ID0 and dt.PARENT_OBJ_ID0 = act.POID_ID0 and dt.PARENT_SERVICE_OBJ_ID0=f.poid_id0
and dt.poid_id0 = c.renewal_obj_id0(+) AND D.PLAN_OBJ_ID0 = E.PLAN_POID_ID0(+) AND act.POID_ID0 = D.OBJ_ID0
AND D.SERVICE_OBJ_ID0 != 0 AND rc_tag='DEALER'
order by dt.created_t;
tkprof output:
TKPROF: Release 8.1.7.0.0 - Production on Tue Jun 23 15:34:23 2009
(c) Copyright 2000 Oracle Corporation. All rights reserved.
Trace file: pindb_ora_9737.trc
Sort options: prsela exeela fchela fchela
********************************************************************************
count = number of times OCI procedure was executed
cpu = cpu time in seconds executing
elapsed = elapsed time in seconds executing
disk = number of physical reads of buffers from disk
query = number of buffers gotten for consistent read
current = number of buffers gotten in current mode (usually for update)
rows = number of rows processed by the fetch or execute call
********************************************************************************
ALTER SESSION SET SQL_TRACE = TRUE
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 0 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 1 0.00 0.00 0 0 0 0
Misses in library cache during parse: 0
Misses in library cache during execute: 1
Optimizer goal: CHOOSE
Parsing user id: 28
********************************************************************************
SELECT DISTINCT A.ACCOUNT_NO,B.CITY,D.DESCR,
CASE WHEN A.PRIOR_PAYMENT_STATUS=10100 THEN 0
WHEN A.PRIOR_PAYMENT_STATUS=10102 THEN ROUND(S2DIQ(A.CREATED_T) - S2DIQ(A.LAST_STATUS_T),0)
ELSE 0
END AS SUSP_DAYS,
F.FIRST_NAME || ' ' || F.LAST_NAME AS DEALER_NAME,I.NAME AS PLAN_NAME,H.RC_TAG,
TO_CHAR(S2DIQ(A.CREATED_T),'DD-MON-YY HH24:MI') AS COLLECTION_DT,
TO_CHAR(S2DIQ(C.CREATED_T),'DD-MON-YY HH24:MI') AS CREATION_DT,
TO_CHAR(S2DIQ(A.LAST_STATUS_T),'DD-MON-YY HH24:MI') AS STS_CHG_DT,A.AMOUNT,
DECODE(C.BILL_TYPE,10000,'PREPAID',10001,'SUVIDHA') AS CURRENT_BILL_TYPE,
DECODE(A.PRIOR_PAYMENT_STATUS,10100,'ACTIVE',10102,'SUSPEND',10103,'CLOSED') AS PRIOR_STATUS,
DECODE(A.AFTER_PAYMENT_STATUS,10100,'ACTIVE',10102,'SUSPEND',10103,'CLOSED') AS AFTER_STATUS,
DECODE(C.STATUS,10100,'ACTIVE',10102,'SUSPEND',10103,'CLOSED') AS CURRENT_STATUS,
DECODE(A.STATUS,0,'SUCCESS',3,'FAIL') AS SYS_STATUS,K.NAME AS PLAN_AT_PAYMENT,J.RC_TAG AS RC_TAG_AT_PAYMENT,
A.CREATED_T,A.POID_ID0,A.TRANS_ID
FROM IQARA_ONLINE_PAYMENTS_T A,
ACCOUNT_NAMEINFO_T B,
ACCOUNT_T C,
YOU_ACTIVITY_MASTER_T D,
IQARA_ONLINE_DEALER_RENEWALS_T E,
ACCOUNT_NAMEINFO_T F,
(SELECT * FROM ACCOUNT_PRODUCTS_T WHERE PLAN_OBJ_ID0>0) G,
IQ_PROV_TAGS_T H,
PLAN_T I,
IQ_PROV_TAGS_T J,
PLAN_T K
WHERE A.ACCOUNT_OBJ_ID0 = B.OBJ_ID0
AND A.ACCOUNT_NO = C.ACCOUNT_NO
AND E.POID_ID0(+)=A.TRANS_OBJ_ID0
AND E.PARENT_OBJ_ID0=F.OBJ_ID0(+)
AND C.POID_ID0=G.OBJ_ID0(+)
AND G.PLAN_OBJ_ID0=H.PLAN_POID_ID0(+)
AND H.PLAN_POID_ID0=I.POID_ID0(+)
AND A.PAYMENT_MODE=D.TYPE
AND A.PLAN_OBJ_ID0=J.PLAN_POID_ID0
AND J.PLAN_POID_ID0=K.POID_ID0
AND J.RC_TAG LIKE 'PP%'
AND D.PROGRAM_NAME='PAYMENT_MODE'
AND D.TABLE_NAME='IQARA_ONLINE_PAYMENTS_T'
AND A.STATUS IN (0,3)
AND A.CREATED_T>=d2siq('01-jun-2009')
AND A.CREATED_T<d2siq('23-jun-2009')
ORDER BY CREATED_T
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 2 0.00 0.00 0 0 0 0
Fetch 1424 0.00 0.00 53889 1225962 64 21341
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 1427 0.00 0.00 53889 1225962 64 21341
Misses in library cache during parse: 0
Optimizer goal: CHOOSE
Parsing user id: 28
Rows Row Source Operation
------- ---------------------------------------------------
21341 SORT UNIQUE
48295 HASH JOIN
2308 TABLE ACCESS FULL PLAN_T
48295 HASH JOIN
810 TABLE ACCESS FULL IQ_PROV_TAGS_T
122604 HASH JOIN OUTER
122604 HASH JOIN OUTER
122604 HASH JOIN OUTER
122604 HASH JOIN OUTER
122604 NESTED LOOPS OUTER
69318 NESTED LOOPS
69318 NESTED LOOPS
69318 HASH JOIN
30 TABLE ACCESS FULL YOU_ACTIVITY_MASTER_T
69317 TABLE ACCESS BY INDEX ROWID IQARA_ONLINE_PAYMENTS_T
72619 INDEX RANGE SCAN (object id 28005)
138634 TABLE ACCESS BY INDEX ROWID ACCOUNT_NAMEINFO_T
138634 INDEX RANGE SCAN (object id 22709)
138634 TABLE ACCESS BY INDEX ROWID ACCOUNT_T
138634 INDEX UNIQUE SCAN (object id 22717)
122487 TABLE ACCESS BY INDEX ROWID ACCOUNT_PRODUCTS_T
260539 INDEX RANGE SCAN (object id 22714)
2290 TABLE ACCESS FULL IQ_PROV_TAGS_T
2308 TABLE ACCESS FULL PLAN_T
921583 TABLE ACCESS FULL IQARA_ONLINE_DEALER_RENEWALS_T
365232 TABLE ACCESS FULL ACCOUNT_NAMEINFO_T
********************************************************************************
set autotrace traceonly explain::
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=28791015 Card=100196
360 Bytes=30960675240)
1 0 SORT (UNIQUE) (Cost=14565538 Card=100196360 Bytes=30960675
240)
2 1 HASH JOIN (Cost=340062 Card=100196360 Bytes=30960675240)
3 2 TABLE ACCESS (FULL) OF 'PLAN_T' (Cost=16 Card=2308 Byt
es=103860)
4 2 HASH JOIN (Cost=290507 Card=100196360 Bytes=2645183904
0)
5 4 TABLE ACCESS (FULL) OF 'IQ_PROV_TAGS_T' (Cost=14 Car
d=810 Bytes=9720)
6 4 HASH JOIN (OUTER) (Cost=289605 Card=12369921 Bytes=3
117220092)
7 6 HASH JOIN (OUTER) (Cost=11341 Card=12369921 Bytes=
2845081830)
8 7 HASH JOIN (OUTER) (Cost=2813 Card=1344 Bytes=292
992)
9 8 HASH JOIN (OUTER) (Cost=2787 Card=1344 Bytes=2
32512)
10 9 NESTED LOOPS (OUTER) (Cost=2764 Card=1344 By
tes=216384)
11 10 NESTED LOOPS (Cost=1693 Card=892 Bytes=133
800)
12 11 NESTED LOOPS (Cost=1158 Card=892 Bytes=1
10608)
13 12 HASH JOIN (Cost=88 Card=892 Bytes=9812
0)
14 13 TABLE ACCESS (FULL) OF 'YOU_ACTIVITY
_MASTER_T' (Cost=1 Card=3 Bytes=96)
15 13 TABLE ACCESS (BY INDEX ROWID) OF 'IQ
ARA_ONLINE_PAYMENTS_T' (Cost=86 Card=3153 Bytes=245934)
16 15 INDEX (RANGE SCAN) OF 'I_IQARA_O_P
AYMENTS__CREATED_T' (NON-UNIQUE) (Cost=34 Card=3153)
17 12 TABLE ACCESS (BY INDEX ROWID) OF 'ACCO
UNT_NAMEINFO_T' (Cost=2 Card=364977 Bytes=5109678)
18 17 INDEX (RANGE SCAN) OF 'I_ACCOUNT_NAM
EINFO__ID' (UNIQUE) (Cost=3 Card=364977)
19 11 TABLE ACCESS (BY INDEX ROWID) OF 'ACCOUN
T_T' (Cost=1 Card=364977 Bytes=9489402)
20 19 INDEX (UNIQUE SCAN) OF 'I_ACCOUNT_NO__
ID' (UNIQUE) (Cost=1 Card=364977)
21 10 TABLE ACCESS (BY INDEX ROWID) OF 'ACCOUNT_
PRODUCTS_T' (Cost=2 Card=692063 Bytes=7612693)
22 21 INDEX (RANGE SCAN) OF 'I_ACCOUNT_PRODUCT
S__ID' (UNIQUE) (Cost=3 Card=692063)
23 9 TABLE ACCESS (FULL) OF 'IQ_PROV_TAGS_T' (Cos
t=14 Card=2290 Bytes=27480)
24 8 TABLE ACCESS (FULL) OF 'PLAN_T' (Cost=16 Card=
2308 Bytes=103860)
25 7 TABLE ACCESS (FULL) OF 'IQARA_ONLINE_DEALER_RENE
WALS_T' (Cost=8425 Card=920381 Bytes=11044572)
26 6 TABLE ACCESS (FULL) OF 'ACCOUNT_NAMEINFO_T' (Cost=
2706 Card=364977 Bytes=8029494)
Thanks
prakash