Oracle 高级查询. （小弟在线等)

Question

Oracle的高工们，小弟用下面这个Sql语句查询百万的记录，要用17秒的时间，那位大哥可以指教一下优化查询怎么搞啊，我没有建索引，不知道该建什么类型的索引.
select a.carea_id,(Select cname From tbarea Where carea_id=a.carea_id) as cname,
  a.con1 as nserial,a.fee1 as ncharge_fee,nvl(b.con2,0) as nbcancel_fee,
  nvl(b.fee2,0) as feemoney,nvl(a.fee1,0)-nvl(b.fee2,0) as realmoney
  from (select carea_id,count(nserial) con1,sum(ncharge_fee)/100 fee1
        from tbcharge_fee
 where dtcomplete>=to_date('2006-3-6 00:00:00','yyyy-MM-dd HH24:MI:SS')
     and dtcomplete<to_date('2006-3-30 23:59:59','yyyy-MM-dd HH24:MI:SS')
 and bsuccess=1
         group by carea_id) a,
 (select carea_id,count(nserial) con2,sum(ncharge_fee)/100 fee2
    from tbcharge_fee
 where dtcancel>=to_date('2006-3-6 00:00:00','yyyy-MM-dd HH24:MI:SS')
   and dtcancel<to_date('2006-3-30 23:59:59','yyyy-MM-dd HH24:MI:SS')
 and nbcancel_fee=1
 group by carea_id) b
 where a.carea_id=b.carea_id(+) Order By carea_id

Answer 1

先Join再Group By
两个Group By有点吓人, 把子查询改成Join要好一些

Answer 2

create index idx_tbcharge_free_0 ON tbcharge_free(dtcomplete)create index idx_tbcharge_free_1 ON tbcharge_free(dtcancel)再貼出執行計劃:
在sql/plus中
SQL>set autot traceonly
SQL>select ...... from.....
把顯示的結果給貼上來.

Answer 3

相关索引都建上select a.carea_id,t.cname,
a.con1 as nserial,a.fee1 as ncharge_fee,nvl(b.con2,0) as nbcancel_fee,
nvl(b.fee2,0) as feemoney,nvl(a.fee1,0)-nvl(b.fee2,0) as realmoney
from (select carea_id,count(nserial) con1,sum(ncharge_fee)/100 fee1
from tbcharge_fee
where dtcomplete>=to_date('2006-3-6 00:00:00','yyyy-MM-dd HH24:MI:SS')
and dtcomplete<to_date('2006-3-30 23:59:59','yyyy-MM-dd HH24:MI:SS')
and bsuccess=1
group by carea_id) a,
(select carea_id,count(nserial) con2,sum(ncharge_fee)/100 fee2
from tbcharge_fee
where dtcancel>=to_date('2006-3-6 00:00:00','yyyy-MM-dd HH24:MI:SS')
and dtcancel<to_date('2006-3-30 23:59:59','yyyy-MM-dd HH24:MI:SS')
and nbcancel_fee=1
group by carea_id) b,tbarea t
where a.carea_id=b.carea_id(+) 
and t.carea_id=a.carea_id
Order By carea_id

Answer 4

SELECT   a.carea_id, (SELECT cname
                        FROM tbarea
                       WHERE carea_id = a.carea_id) AS cname,
         a.con1 AS nserial, a.fee1 AS ncharge_fee,
         NVL (b.con2, 0) AS nbcancel_fee, NVL (b.fee2, 0) AS feemoney,
         NVL (a.fee1, 0) - NVL (b.fee2, 0) AS realmoney
    FROM (SELECT   carea_id, COUNT (nserial) con1,
                   SUM (ncharge_fee) / 100 fee1
              FROM tbcharge_fee
             WHERE dtcomplete >=
                        TO_DATE ('2006-3-6 00:00:00', 'yyyy-MM-dd HH24:MI:SS')
               AND dtcomplete <
                       TO_DATE ('2006-3-30 23:59:59', 'yyyy-MM-dd HH24:MI:SS')
               AND bsuccess = 1
          GROUP BY carea_id) a,
         (SELECT   carea_id, COUNT (nserial) con2,
                   SUM (ncharge_fee) / 100 fee2
              FROM tbcharge_fee
             WHERE dtcancel >=
                        TO_DATE ('2006-3-6 00:00:00', 'yyyy-MM-dd HH24:MI:SS')
               AND dtcancel <
                       TO_DATE ('2006-3-30 23:59:59', 'yyyy-MM-dd HH24:MI:SS')
               AND nbcancel_fee = 1
          GROUP BY carea_id) b
   WHERE a.carea_id = b.carea_id(+)
ORDER BY carea_id

Answer 5

skyinfo(tiger) 大哥：以下是我在pl/sql里面执行的结果，请你帮我看一下啊，谢谢了：SQL>   Select * From tbcharge_fee;1090518 rows selected.
Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=ALL_ROWS (Cost=6090 Card=1047447
          Bytes=192730248)   1    0   TABLE ACCESS (FULL) OF 'TBCHARGE_FEE' (TABLE) (Cost=6090 C
          ard=1047447 Bytes=192730248)Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
      99753  consistent gets
      28952  physical reads
          0  redo size
  215708550  bytes sent via SQL*Net to client
     800223  bytes received via SQL*Net from client
      72703  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
    1090518  rows processed

Answer 6

大哥，小弟忘了说一下，上面的是我没有建索引执行的结果，下面是我建索引执行的结果，你帮我看一下吧：小弟万分感激:
SQL> create index idx_tbcharge_fee_0 on tbcharge_fee(dtcomplete);Index created.SQL> create index idx_tbcharge_fee_1 on tbcharge_fee(dtcancel);Index created.SQL> set autot traceonly;
SQL> select * from tbcharge_fee;1090518 rows selected.
Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=ALL_ROWS (Cost=6090 Card=1047447
          Bytes=192730248)   1    0   TABLE ACCESS (FULL) OF 'TBCHARGE_FEE' (TABLE) (Cost=6090 C
          ard=1047447 Bytes=192730248)Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
      99753  consistent gets
      28965  physical reads
          0  redo size
  215708550  bytes sent via SQL*Net to client
     800223  bytes received via SQL*Net from client
      72703  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
    1090518  rows processed

Answer 7

應該是重新執行你原來的語句.而不是select * from tbcharge_fee;SQL>set autot on 或者 set autot traceonly
SQL>select a.carea_id,(Select cname From tbarea Where carea_id=a.carea_id) as cname,
a.con1 as nserial,a.fee1 as ncharge_fee,nvl(b.con2,0) as nbcancel_fee,
nvl(b.fee2,0) as feemoney,nvl(a.fee1,0)-nvl(b.fee2,0) as realmoney
from (select carea_id,count(nserial) con1,sum(ncharge_fee)/100 fee1
from tbcharge_fee
where dtcomplete>=to_date('2006-3-6 00:00:00','yyyy-MM-dd HH24:MI:SS')
and dtcomplete<to_date('2006-3-30 23:59:59','yyyy-MM-dd HH24:MI:SS')
and bsuccess=1
group by carea_id) a,
(select carea_id,count(nserial) con2,sum(ncharge_fee)/100 fee2
from tbcharge_fee
where dtcancel>=to_date('2006-3-6 00:00:00','yyyy-MM-dd HH24:MI:SS')
and dtcancel<to_date('2006-3-30 23:59:59','yyyy-MM-dd HH24:MI:SS')
and nbcancel_fee=1
group by carea_id) b
where a.carea_id=b.carea_id(+) Order By carea_id

Answer 8

执行"select * from tbcharge_fee;"这个计划没有什么意义.