日期:2014-05-16  浏览次数:20427 次

WHERE条件中使用TRUNC(时间字段)非常影响效率
--不要对时间字段进行函数处理,非常慢

低能--每提取一条记录都要对时间字段进行函数处理才能确定是否合适
SELECT COUNT(1)
  FROM A_BASIC_CS_RADIO_H
WHERE TRUNC(COLLECTTIME) = TRUNC(SYSDATE) - 1
一般耗时4-5秒。

高效--直接可以判断是否合适
SELECT COUNT(1)
  FROM A_BASIC_CS_RADIO_H
WHERE COLLECTTIME BETWEEN TRUNC(SYSDATE) - 1 AND
       TRUNC(SYSDATE) - 1 + 23 / 24
一般耗时0.05秒。差别太大了。
1 楼 DataBird 2010-12-05  
这个要牢记!非常影响效率!!!
CREATE OR REPLACE PROCEDURE P_ABASICCSRADIO(V_BEGIN IN VARCHAR,
                                            V_END   IN VARCHAR) AS
  V_EXIST      INT;
  V_TIME_BEGIN DATE := TO_DATE(V_BEGIN, 'YYYY-MM-DD HH24');
  V_TIME_END   DATE := TO_DATE(V_END, 'YYYY-MM-DD HH24');
  MY_CONTINUE EXCEPTION;

  TYPE T_VARRAY IS VARRAY(18) OF VARCHAR2(50);
  V_VAR T_VARRAY := T_VARRAY('PGZTECELLSUM0', 'PGZTECELLSUM1');
BEGIN
  FOR I IN 1 .. V_VAR.COUNT LOOP
    SELECT COUNT(1)
      INTO V_EXIST
      FROM USER_TABLES
     WHERE TABLE_NAME = V_VAR(I);
    IF V_EXIST > 0 THEN
      EXECUTE IMMEDIATE ('DROP TABLE ' || V_VAR(I));
    END IF;
  END LOOP;

  WHILE V_TIME_BEGIN <= V_TIME_END LOOP
    --
    DBMS_OUTPUT.PUT_LINE(TO_CHAR(V_TIME_BEGIN, 'YYYY-MM-DD HH24'));
    SELECT COUNT(1)
      INTO V_EXIST
      FROM A_BASIC_CS_RADIO
     WHERE COLLECTTIME = V_TIME_BEGIN;
 
    IF V_EXIST = 0 THEN
      DBMS_OUTPUT.PUT_LINE('');
      V_TIME_BEGIN := V_TIME_BEGIN + 1 / 24;
    ELSE
      DBMS_OUTPUT.PUT_LINE('A_BASIC_CS_RADIO ' || V_EXIST);
      --
      BEGIN
        --
        INSERT INTO A_BASIC_CS_RADIO_H
          SELECT TRUNC(COLLECTTIME, 'HH24') COLLECTTIME,
                 BSCID,
                 SITEID,
                 BTSID,
                 sum(C100030001) C100030001,
                 sum(C100030002) C100030002,
                 sum(C100030003) C100030003,
                 sum(C100030004) C100030004,
                 sum(C100030005) C100030005,
                 sum(C100030006) C100030006,
              &nbs