这个存储过程奇慢？大家看看为什么？

Question

CREATE OR REPLACE PROCEDURE P_QUERY_METER_DATE(
V_NODE_ID IN VARCHAR2,
V_USER_NAME IN VARCHAR2,
V_NEW_USER_ID IN VARCHAR2,
V_USER_TYPE_CODE IN VARCHAR2,
V_PAY_ID IN VARCHAR2,
V_START_MON IN VARCHAR2,
V_END_MON IN VARCHAR2,
V_METER_TYPE IN VARCHAR2,
PAGE_INDEX IN INTEGER,
PAGE_SIZE IN INTEGER,
RECORD_CNT OUT INTEGER,
RET OUT CHARGE_FORWEB.RETURN_CURSOR
)
AS
BEGIN
  ---->找出来记录数
       SELECT COUNT(*) INTO RECORD_CNT
       FROM (
            SELECT D.ADDRESS_NAME AREA_NAME,A.CASH_ID,A.CUST_ID,A.NEW_USER_ID,A.USER_NAME,A.S_ADDRESS,E.ADDRESS_NAME BUILD_NAME,
                   B.USER_TYPE,C.PAY_TYPE_NAME,CASE H."TYPE" WHEN 1 THEN '电表' WHEN 2 THEN '水表' WHEN 3 THEN '气表' END M_TYPE,
                   SUM(NVL(J.NUMBERS,0)) P_NUM,SUM(NVL(J.NUMBER1,0)) J_NUM,SUM(NVL(J.NUMBER2,0)) F_NUM,SUM(NVL(J.NUMBER3,0)) G_NUM,
                   SUM(NVL(J.ADD_NUMBER,0)) ADD_NUMBER,SUM(NVL(J.FREE_NUM,0)) FREE_NUM,SUM(NVL(J.FREE_SUM,0)) FREE_SUM,
                   CASE IS_CHECK WHEN 0 THEN '未审核' WHEN 1 THEN '已审核' ELSE '' END IS_CHECK
            FROM ICCARD.V_STOREY_INFO A LEFT JOIN
                 ICCARD.USER_TYPE B ON A.USER_TYPE_CODE = B.USER_TYPE_CODE LEFT JOIN
                 ICCARD.PAY_TYPE C ON A.PAY_TYPE_ID = C.PAY_TYPE_ID LEFT JOIN
                 ICCARD.STOREY_INFO D ON D.NODE_ID = SUBSTR(A.NODE_ID,1,9) LEFT JOIN
                 ICCARD.STOREY_INFO E ON E.NODE_ID = SUBSTR(A.NODE_ID,1,12) LEFT JOIN
                 ICCARD.METER_ALLOT F ON A.ADDRESS_ID = F.ADDRESS_ID LEFT JOIN
                 ICCARD.METER_MARKET G ON F.METER_ID = G.METER_ID LEFT JOIN
                 ICCARD.METER_TYPE H ON G.M_TYPE_CODE = H.M_TYPE_CODE LEFT JOIN
                 ICCARD.V_READ_METER J ON F.ADDRESS_ID = J.ADDRESS_ID AND F.U_METER_ID = J.U_METER_ID
           WHERE F.USE_FLAG >= 0 
           GROUP BY D.ADDRESS_NAME,A.CASH_ID,A.CUST_ID,A.NEW_USER_ID,A.USER_NAME,A.S_ADDRESS,E.ADDRESS_NAME,E.ADDRESS_NAME,
                   B.USER_TYPE,C.PAY_TYPE_NAME,H."TYPE",IS_CHECK
           ) Z;  
   ---->其它的工作，在下面进行        
END ;           

Answer 1

关联F表时不要用左连接，用inner join吧

Answer 2

楼主，。我用oracle不是很久。、以前都是用mysql设计。。不过前几天有个DBA给我们讲了讲oracle10在sql标准语言方面优化的问题。。
以个人的微见
count的使用上有优化的可能。。当数据小时..表也少是count(*)把每个表每条数据都按where条件跑一便。。没啥大影响。。但是楼主的from后面表太多了啊。。能不能考虑groupby别用那么多啊。。还有一种是count(case..when..)这样是在第一便遍历表数据时就加上了条件控制。。数度成倍上升啊。。不过最好是把group能写少就写少。。毕竟数据取出来在group oracle很费时间。。看楼主的代码。。group少时。外联表也少了。。多好啊。还有group时还不带着null好像还得加判断
我就知道这些了。。剩下的在等狠人没弄了

Answer 3

 SELECT COUNT(*) INTO RECORD_CNT 
       FROM ( 
            SELECT 1 
            FROM ICCARD.V_STOREY_INFO A LEFT JOIN 
                 ICCARD.USER_TYPE B ON A.USER_TYPE_CODE = B.USER_TYPE_CODE LEFT JOIN 
                 ICCARD.PAY_TYPE C ON A.PAY_TYPE_ID = C.PAY_TYPE_ID LEFT JOIN 
                 ICCARD.STOREY_INFO D ON D.NODE_ID = SUBSTR(A.NODE_ID,1,9) LEFT JOIN 
                 ICCARD.STOREY_INFO E ON E.NODE_ID = SUBSTR(A.NODE_ID,1,12) LEFT JOIN 
                 ICCARD.METER_ALLOT F ON A.ADDRESS_ID = F.ADDRESS_ID LEFT JOIN 
                 ICCARD.METER_MARKET G ON F.METER_ID = G.METER_ID LEFT JOIN 
                 ICCARD.METER_TYPE H ON G.M_TYPE_CODE = H.M_TYPE_CODE LEFT JOIN 
                 ICCARD.V_READ_METER J ON F.ADDRESS_ID = J.ADDRESS_ID AND F.U_METER_ID = J.U_METER_ID 
           WHERE F.USE_FLAG >= 0 
           GROUP BY D.ADDRESS_NAME,A.CASH_ID,A.CUST_ID,A.NEW_USER_ID,A.USER_NAME,A.S_ADDRESS,E.ADDRESS_NAME,E.ADDRESS_NAME, 
                   B.USER_TYPE,C.PAY_TYPE_NAME,H."TYPE",IS_CHECK 
           ) Z; 这么写就可以满足你的要求了还有就是不知道你那个条件是限制什么的  如果能放在 f表on的后面做限制就不要放在where中，效果会好些

Answer 4

CREATE OR REPLACE PROCEDURE P_QUERY_METER_DATE( 
V_NODE_ID IN VARCHAR2, 
V_USER_NAME IN VARCHAR2, 
V_NEW_USER_ID IN VARCHAR2, 
V_USER_TYPE_CODE IN VARCHAR2, 
V_PAY_ID IN VARCHAR2, 
V_START_MON IN VARCHAR2, 
V_END_MON IN VARCHAR2, 
V_METER_TYPE IN VARCHAR2, 
PAGE_INDEX IN INTEGER, 
PAGE_SIZE IN INTEGER, 
RECORD_CNT OUT INTEGER, 
RET OUT CHARGE_FORWEB.RETURN_CURSOR 
) 
AS 
BEGIN 
  ---->找出来记录数 
       SELECT COUNT(*) INTO RECORD_CNT
       FROM (
            SELECT SUBSTR(A.NODE_ID,1,9) AREA_NAME,A.CASH_ID,A.CUST_ID,A.NEW_USER_ID,A.USER_NAME,A.S_ADDRESS,SUBSTR(A.NODE_ID,1,13) BUILD_NAME,
                   A.USER_TYPE_CODE,A.PAY_TYPE_ID,H."TYPE",
                   SUM(NVL(J.NUMBERS,0)) P_NUM,SUM(NVL(J.NUMBER1,0)) J_NUM,SUM(NVL(J.NUMBER2,0)) F_NUM,SUM(NVL(J.NUMBER3,0)) G_NUM,
                   SUM(NVL(J.ADD_NUMBER,0)) ADD_NUMBER,SUM(NVL(J.FREE_NUM,0)) FREE_NUM,SUM(NVL(J.FREE_SUM,0)) FREE_SUM,
                   IS_CHECK
            FROM ICCARD.V_STOREY_INFO A LEFT JOIN
                 ICCARD.METER_ALLOT F ON A.ADDRESS_ID = F.ADDRESS_ID LEFT JOIN
                 ICCARD.METER_MARKET G ON F.METER_ID = G.METER_ID LEFT JOIN
                 ICCARD.METER_TYPE H ON G.M_TYPE_CODE = H.M_TYPE_CODE LEFT JOIN
                 ICCARD.V_READ_METER J ON F.ADDRESS_ID = J.ADDRESS_ID AND F.U_METER_ID = J.U_METER_ID
           WHERE F.USE_FLAG >= 0 AND A.NODE_ID LIKE V_NODE_ID || '%' AND A.NEW_USER_ID LIKE V_NEW_USER_ID || '%' AND
                 A.USER_NAME LIKE V_USER_NAME || '%' AND A.USER_TYPE_CODE LIKE V_USER_TYPE_CODE || '%' AND
                 A.PAY_TYPE_ID LIKE V_PAY_ID || '%' AND J.START_DATE >= V_START_MON AND J.END_DATE <= V_END_MON AND
                 H."TYPE" LIKE V_METER_TYPE || '%'
           GROUP BY SUBSTR(A.NODE_ID,1,9),A.CASH_ID,A.CUST_ID,A.NEW_USER_ID,A.USER_NAME,A.S_ADDRESS,SUBSTR(A.NODE_ID,1,13),
                   A.USER_TYPE_CODE,A.PAY_TYPE_ID,H."TYPE",IS_CHECK
           ) Z; 
  ---->其它的工作，在下面进行        
END ;    我已经精简到这种地步了，还是速度提不上去，就是WHERE条件这一块出的问题，看大家谁还有办法，我试过了，我把那放到字符串中，动态执行是很快的。      

Answer 5

做一个stackpack
关注 db file sequential read ,db file scatter read 事件:db file sequential read 比较大，则说明索引等待比较长，需要优化索引
如果 db file scatter read比较大，则说明全表扫描比较多，需要优化查询，建立索引

Answer 6

在进行数据库架构的时候，要考虑一个数据库的基本问题，既第一范式
（原子性）id   name   hobbies
1    **      文学，艺术，电影
就违反了第一范式，我们在查询的时候就要考虑了，
比如查询所有 爱好是艺术 的人的姓名
select * from ** where hobbies like"";这样查询效果就不好了，（需要全表扫描）
正常的情况下我们应该这么做，
建立两个table
cust
id    namehobby
id    hobbies查询的时候是对两个用户进行union或join查询来解决问题。

Answer 7

       SELECT COUNT(1) INTO RECORD_CNT
            FROM ICCARD.V_STOREY_INFO A LEFT JOIN
                ICCARD.USER_TYPE B ON A.USER_TYPE_CODE = B.USER_TYPE_CODE LEFT JOIN
                ICCARD.PAY_TYPE C ON A.PAY_TYPE_ID = C.PAY_TYPE_ID LEFT JOIN
                ICCARD.STOREY_INFO D ON D.NODE_ID = SUBSTR(A.NODE_ID,1,9) LEFT JOIN
                ICCARD.STOREY_INFO E ON E.NODE_ID = SUBSTR(A.NODE_ID,1,12) LEFT JOIN
                ICCARD.METER_ALLOT F ON A.ADDRESS_ID = F.ADDRESS_ID LEFT JOIN
                ICCARD.METER_MARKET G ON F.METER_ID = G.METER_ID LEFT JOIN
                ICCARD.METER_TYPE H ON G.M_TYPE_CODE = H.M_TYPE_CODE LEFT JOIN
                ICCARD.V_READ_METER J ON F.ADDRESS_ID = J.ADDRESS_ID AND F.U_METER_ID = J.U_METER_ID
          WHERE F.USE_FLAG >= 0
          GROUP BY D.ADDRESS_NAME,A.CASH_ID,A.CUST_ID,A.NEW_USER_ID,A.USER_NAME,A.S_ADDRESS,E.ADDRESS_NAME,E.ADDRESS_NAME,
                  B.USER_TYPE,C.PAY_TYPE_NAME,H."TYPE",IS_CHECK;
这样就可以了