|
两条SQL的执行计划一样,为什么消耗的时间不一样?请看下面两条SQL,功能是一样的,只是写法不一样。第一条时间为Elapsed: 00:00:02.32,第二条为Elapsed: 00:00:04.55, 多次测试证明,第二条时间确实比第一条慢。
SQL> select a.projectid,b.itemname,
sum(decode(a.spiece,null,0,a.spiece)) as spiece,
sum(decode(a.sgweigth,null,0,a.sgweigth)) as weight,
0,0,
count(*) as ballot
from t_bill_base a,t_iteminfo b where
a.projectid= b.itemcode
and to_char(a.collectiontime, 'yyyy-MM-dd') ='2005-02-28'
group by a.projectid, b.itemname;
PROJECTID ITEMNAME SPIECE WEIGHT 0 0 BALLOT
---------- ---------- ---------- ---------- ---------- ---------- ----------
CHQC ???? 61 646.3 0 0 5
CQYY ???? 26 264 0 0 2
XNLD ???? 6 76.06 0 0 3
ZBYY ???? 1 3.7 0 0 1
ALKYY ????? 8 38.88 0 0 4
Elapsed: 00:00:02.32
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE
1 0 SORT (GROUP BY)
2 1 TABLE ACCESS (BY INDEX ROWID) OF 'T_BILL_BASE'
3 2 NESTED LOOPS
4 3 TABLE ACCESS (FULL) OF 'T_ITEMINFO'
5 3 INDEX (RANGE SCAN) OF 'I_T_ BILL_BASE_POD' (NON
-UNIQUE)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
1771 consistent gets
0 physical reads
0 redo size
1048 bytes sent via SQL*Net to client
655 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
5 rows processed
SQL>
select a.projectid,b.itemname,
sum(case when a.spiece is null then 0 else a.spiece end) as spiece,
sum(case when a.sgweigth is null then 0 else a.sgweigth end) as weight,0,0,
count(*) as ballot
from t_bill_base a inner join t_iteminfo b on a.projectid = b.itemcode
where 1 = 1 and to_char(a.collectiontime, 'yyyy-MM-dd HH24:mm:ss') >=
'2005-02-28 00:00:00' and
to_char(a.collectiontime, 'yyyy-MM-dd HH24:mm:ss') <=
'2005-02-28 23:59:59'
group by a.projectid, b.itemname;
PROJECTID ITEMNAME SPIECE WEIGHT VOL FEE BALLOT
---------- ---------- ---------- ---------- ---------- ---------- ----------
CHQC ???? 61 646.3 0 0 5
CQYY ???? 26 264 0 0 2
XNLD ???? 6 76.06 0 0 3
ZBYY ???? 1 3.7 0 0 1
ALKYY ????? 8 38.88 0 0 4
Elapsed: 00:00:04.55
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE
1 0 SORT (GROUP BY)
2 1 TABLE ACCESS (BY INDEX ROWID) OF 'T_BILL_BASE'
3 2 NESTED LOOPS
4 3 TABLE ACCESS (FULL) OF 'T_ITEMINFO'
5 3 INDEX (RANGE SCAN) OF 'I_T_BILL_BASE_POD' (NON
-UNIQUE)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
1771 consistent gets
0 physical reads
0 redo size
1052 bytes sent via SQL*Net to client
655 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
5 rows processed |
|