请帮忙优化一下sql语句

Question

SELECT folio.xcode,
       folio.type,
       count(folio.fcode),
       sum(folio.fbprc),
       sum(folio.fser)
  FROM folio
 WHERE (folio.fdt >= to_date('2005-02-21','yyyy-mm-dd'))
   AND (folio.fdt <= to_date('2005-03-20','yyyy-mm-dd'))
   AND (folio.xcode IN (SELECT temp_deparmt.xcode FROM temp_deparmt))
 GROUP BY folio.xcode, folio.type
 Order By folio.xcode Asc, folio.Type Asc;其中folio表中有500万条数据，上面的语句执行起来要花费1~2分钟，希望高手们帮忙优化一下。

Answer 1

执行计划贴出来看看。in改成exists

Answer 2

folio.type,
       count(folio.fcode),
       sum(folio.fbprc),
       sum(folio.fser)
  FROM folio inner join temp_deparmt on folio.xcode=temp_deparmt.xcode
 WHERE (folio.fdt >= to_date('2005-02-21','yyyy-mm-dd'))
   AND (folio.fdt <= to_date('2005-03-20','yyyy-mm-dd'))  
 GROUP BY folio.xcode, folio.type
 Order By folio.xcode Asc, folio.Type Asc;

Answer 3

pl/sql developer里面的右上角的new－>explain plan window窗口或者后台看执行计划
示例：SQL> @?/rdbms/admin/utlxplan.sql;Table created.
SQL> alter session set "_always_semi_join"=off;Session altered.SQL> explain plan for 
  2  select q.id as questionid, q.text as questiontext, q.is_deleted, q.create_date, q.created_by, q.last_update_date, q.last_updated_by, q.image_url, q.image_location, q.category_id, q.allow_html, q.site_id, qbq.question_bank_id, qbq.question_id as qstid
from ilearn.question q,ilearn.question_bank_question qbq,ilearn.response_type rt
where exists
      (select 'x'
       from ilearn.response_value
       where response_value.response_type_id = rt.id and response_value.is_deleted != 'y') and
       q.id = qbq.question_id and
       rt.question_id = q.id and
       rt.is_deleted != 'y' and
       q.is_deleted != 'y' and
       qbq.question_bank_id = :1  3    4    5    6    7    8    9   10   11   12  
 13  /Explained.SQL> @?/rdbms/admin/utlxpls.sql;PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                    |  Name                       | Rows  | Bytes | Cost  |
--------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |                             |     6 |   810 |   142 |
|*  1 |  FILTER                      |                             |       |       |       |
|*  2 |   HASH JOIN                  |                             |     6 |   810 |   130 |
|   3 |    NESTED LOOPS              |                             |   118 | 14278 |    86 |
|*  4 |     TABLE ACCESS FULL        | QUESTION                    | 51988 |  5533K|    86 |
|*  5 |     INDEX UNIQUE SCAN        | QUESTION_BANK_QUESTION_PK   |     1 |    12 |       |
|*  6 |    TABLE ACCESS FULL         | RESPONSE_TYPE               |  2599 | 36386 |    43 |
|*  7 |   TABLE ACCESS BY INDEX ROWID| RESPONSE_VALUE              |     4 |    32 |     2 |
|*  8 |    INDEX RANGE SCAN          | RESPONSE_VALUE_RTYPE_INDEX  |     4 |       |     1 |
--------------------------------------------------------------------------------------------Predicate Information (identified by operation id):
---------------------------------------------------   1 - filter( EXISTS (SELECT /*+ */ 0 FROM "ILEARN"."RESPONSE_VALUE" "RESPONSE_VALUE"
              WHERE "RESPONSE_VALUE"."RESPONSE_TYPE_ID"=:B1 AND "RESPONSE_VALUE"."IS_DELETED"<>'y'))
   2 - access("SYS_ALIAS_1"."QUESTION_ID"="Q"."ID")
   4 - filter("Q"."IS_DELETED"<>'y')
   5 - access("QBQ"."QUESTION_BANK_ID"=TO_NUMBER(:Z) AND "Q"."ID"="QBQ"."QUESTION_ID")
   6 - filter("SYS_ALIAS_1"."IS_DELETED"<>'y')
   7 - filter("RESPONSE_VALUE"."IS_DELETED"<>'y')
   8 - access("RESPONSE_VALUE"."RESPONSE_TYPE_ID"=:B1)Note: cpu costing is off28 rows selected.

Answer 4

举的例子不是好的sql，因为它的执行计划显示，它一上来就是全表扫描|*  4 |     TABLE ACCESS FULL        | QUESTION                    | 51988 |  5533K|    86

Answer 5

to start_again(重新来) 
你的方法我尝试过了，速度一样，没有优化

Answer 6

这个是题目所示的sql语句的执行计划select statement,goal = all_ro,  cost = 270 , cardinality = 22998 ,bytes=1425876;
sort Group BY    ,               cost = 270,  cardinality = 22998 ,bytes=1425876;
nested loops    ,                cost = 6  ,  cardinality = 22998 ,bytes=1425876;
table access by index ro` , object name = folio ,cost = 6  ,  cardinality = 22998 ,bytes=1425876;index range scan,object_name = foliodate cost =2, cardinality = 22998 
index unique scan,object_name=temp_deparmt_x,cardinality = 41 ,bytes=287;