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]