sql:
SELECT distinct P.trans_id,
P.cust_id,
P.acct_id,
P.sub_id,
P.batch_no,
P.trans_type,
P.channel_id,
P.tele_type,
ac.acct_code,
P.entry_date,
P.amount,
P.oper_id,
P.dept_id,
P.version,
sb.msisdn,
cu.cust_code
FROM BILLING.PAYMENT P,
BILLING.PAYMENT_DETAIL pd,
CCARE.INF_ACCT ac,
CCARE.INF_CUSTOMER_ALL cu,
CCARE.INF_SUBSCRIBER_ALL sb
WHERE ac.acct_id = P.acct_id
AND p.cust_id = cu.cust_id
AND p.sub_id = sb.sub_id(+)
AND P.amount > 0
AND P.status = 'V' AND
p.trans_type in ('PRE', 'DEP', 'POB','')
AND sb.partition_id(+) = MOD(p.sub_id, 100)
AND ac.Partition_Id = MOD(p.ACCT_ID, 100)
AND cu.partition_id = MOD(p.CUST_ID, 100)
AND P.entry_date >= to_date('2010-01-30', 'yyyy-mm-dd')
AND P.entry_date < to_date('2010-02-01', 'yyyy-mm-dd')
and P.dept_id = 17035
and p.cust_id = 120001000071320
-- and P.oper_id = ?
order by P.entry_date desc;
这张表是多余的 BILLING.PAYMENT_DETAIL pd
distinct是否有问题
SELECT distinct P.trans_id,
P.cust_id,
P.acct_id,
P.sub_id,
P.batch_no,
P.trans_type,
P.channel_id,
P.tele_type,
ac.acct_code,
P.entry_date,
P.amount,
P.oper_id,
P.dept_id,
P.version,
sb.msisdn,
cu.cust_code
FROM BILLING.PAYMENT P,
BILLING.PAYMENT_DETAIL pd,
CCARE.INF_ACCT ac,
CCARE.INF_CUSTOMER_ALL cu,
CCARE.INF_SUBSCRIBER_ALL sb
WHERE ac.acct_id = P.acct_id
AND p.cust_id = cu.cust_id
AND p.sub_id = sb.sub_id(+)
AND P.amount > 0
AND P.status = 'V' AND
p.trans_type in ('PRE', 'DEP', 'POB','')
AND sb.partition_id(+) = MOD(p.sub_id, 100)
AND ac.Partition_Id = MOD(p.ACCT_ID, 100)
AND cu.partition_id = MOD(p.CUST_ID, 100)
AND P.entry_date >= to_date('2010-01-30', 'yyyy-mm-dd')
AND P.entry_date < to_date('2010-02-01', 'yyyy-mm-dd')
and P.dept_id = 17035
and p.cust_id = 120001000071320
-- and P.oper_id = ?
order by P.entry_date desc;
这张表是多余的 BILLING.PAYMENT_DETAIL pd
distinct是否有问题
SELECT STATEMENT, GOAL = ALL_ROWS 108 1 154
SORT ORDER BY 108 1 154
HASH UNIQUE 107 1 154
MERGE JOIN CARTESIAN 106 1 154
NESTED LOOPS
NESTED LOOPS 21 1 154
NESTED LOOPS OUTER 19 1 123
NESTED LOOPS 17 1 101
TABLE ACCESS BY INDEX ROWID CCARE INF_CUSTOMER_ALL 3 1 28
INDEX UNIQUE SCAN CCARE PK_INF_CUSTOMER_ALL 2 1
TABLE ACCESS BY INDEX ROWID BILLING PAYMENT 14 1 73
INDEX RANGE SCAN BILLING IDX_ENTRY_DATE 1 95
TABLE ACCESS BY INDEX ROWID CCARE INF_SUBSCRIBER_ALL 2 1 22
INDEX UNIQUE SCAN CCARE PK_INF_SUBSCRIBER_ALL 1 1
INDEX UNIQUE SCAN CCARE PK_INF_ACCT 1 1
TABLE ACCESS BY INDEX ROWID CCARE INF_ACCT 2 1 31
BUFFER SORT 105 99999
INDEX FAST FULL SCAN BILLING PK_PAYMENT_DETAIL 85 99999
该表在sql中好像没有使用到,应去掉。
退一步来说,即使这样写不影响性能,但给用户或其他同事看见了,那不是显得你很不专业了(多写了一个无用的表)!!!!要养成好的习惯。
即使你是相同的表(个数,结构),在from子句后的顺序不同都会影响性能。