LOCALUSAGE_200408,
PARTITION LOCALUSAGE_200412
)
TABLESPACE wacos
STORAGE(
INITIAL 6553600
NEXT 6553600
MAXEXTENTS unlimited
PCTINCREASE 0)
PCTFREE 5
NOLOGGING;
Index created.
Elapsed: 00:06:27.90 (由于数据量比较大，耗时6分钟）
8.再次查看执行计划：
SQL>Select nvl(sum(LOCALCHARGE),0),nvl(sum(usage),0) from LOCALUSAGE where to_char(ENDTIME,''''YYYYMMDD'''')=20041016
and LOCALCHARGE>0 and caller like ''''0543886%'''';
NVL(SUM(LOCALCHARGE),0) NVL(SUM(USAGE),0)
----------------------- -----------------
0 0
Elapsed: 00:00:03.00
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=22 Card=1 Bytes=53)
1 0 SORT (AGGREGATE)
2 1 PARTITION RANGE (ALL)
3 2 TABLE ACCESS (BY LOCAL INDEX ROWID) OF ''''LOCALUSAGE'''' (Cost=22 Card=181 Bytes=9593)
4 3 INDEX (RANGE SCAN) OF ''''I_LOCALUSAGE_CALLER'''' (NON-UNIQUE) (Cost=14 Card=65063)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
16813 consistent gets
569 physical reads
0 redo size
580 bytes sent via SQL*Net to client
651 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
这次走了索引后速度明显快多了，用了3秒钟就返回了结果。
SQL>select nvl(sum(LOCALCHARGE),0) from LOCALUSAGE where to_char(ENDTIME,''''YYYYMMDD'''')=20040816 and caller like ''''0543888%'''';
NVL(SUM(LOCALCHARGE),0)
-----------------------
27.6
Elapsed: 00:00:24.73
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=22 Card=1 Bytes=40)
1 0 SORT (AGGREGATE)
2 1 PARTITION RANGE (ALL)
3 2 TABLE ACCESS (BY LOCAL INDEX ROWID) OF ''''LOCALUSAGE'''' (Cost=22 Card=3615 Bytes=144600)
4 3 INDEX (RANGE SCAN) OF ''''I_LOCALUSAGE_CALLER'''' (NON-UNIQUE) (Cost=14 Card=65063)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
129336 consistent gets
7241 physical reads
0 redo size
507 bytes sent via SQL*Net to client
651 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
这个SQL语句走了索引，用了24秒钟返回结果，性能明显提高了很多。
上一页 [1] [2] [3] | 
注：本站部分文章源于互联网，版权归原作者所有！如有侵权，请原作者与本站联系，本站将立即删除！ 本站文章除特别注明外均可转载，但需注明出处！ [MinTao学以致用网] 