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,
sum(C100030007) C100030007,
sum(C100030008) C100030008,
sum(C100030009) C100030009,
sum(C100030010) C100030010,
sum(C100030011) C100030011,
sum(C100030012) C100030012,
sum(C100030013) C100030013,
sum(C100030014) C100030014,
sum(C100030015) C100030015,
sum(C100030016) C100030016,
sum(C100030017) C100030017,
sum(C100030018) C100030018,
sum(C100030019) C100030019,
sum(C100030020) C100030020,
sum(C100030021) C100030021,
sum(C100030022) C100030022,
sum(C100030023) C100030023,
sum(C100030024) C100030024,
sum(C100030025) C100030025,
sum(C100030026) C100030026,
sum(C100030027) C100030027,
sum(C100030028) C100030028,
sum(C100030029) C100030029,
sum(C100030030) C100030030,
sum(C100030031) C100030031,
sum(C100030032) C100030032,
sum(C100030033) C100030033,
sum(C100030034) C100030034,
sum(C100030035) C100030035,
sum(C100030036) C100030036,
sum(C100030037) C100030037,
sum(C100030038) C100030038,
sum(C100030039) C100030039,
sum(C100030040) C100030040,
sum(C100030041) C100030041,
sum(C100030042) C100030042,
sum(C100030043) C100030043,
sum(C100030044) C100030044,
sum(C100030045) C100030045,
sum(C100030046) C100030046,
sum(C100030047) C100030047,
sum(C100030048) C100030048,
sum(C100030049) C100030049,
sum(C100030050) C100030050,
sum(C100030051) C100030051,
sum(C100030052) C100030052,
sum(C100030053) C100030053,
sum(C100030054) C100030054,
sum(C100030055) C100030055,
sum(C100030056) C100030056,
sum(C100030057) C100030057,
sum(C100030058) C100030058,
sum(C100030059) C100030059,
sum(C100030060) C100030060,
sum(C100030061) C100030061,
sum(C100030062) C100030062,
sum(C100030063) C100030063,
sum(C100030064) C100030064,
sum(C100030065) C100030065,
sum(C100030066) C100030066,
sum(C100030067) C100030067,
sum(C100030068) C100030068,
sum(C100030069) C100030069,
sum(C100030070) C100030070,
sum(C100030071) C100030071,
sum(C100030072) C100030072,
sum(C100030073) C100030073,
sum(C100030074) C100030074,
sum(C100030075) C100030075,
sum(C100030076) C100030076,
sum(C100030077) C100030077,
sum(C100030078) C100030078,
sum(C100030079) C100030079,
sum(C100030080) C100030080,
sum(C100030081) C100030081,
sum(C100030082) C100030082,
sum(C100030083) C100030083,
sum(C100030084) C100030084,
sum(C100030085) C100030085,
sum(C100030086) C100030086,
sum(C100030087) C100030087,
sum(C100030088) C100030088,
sum(C100030089) C100030089,
sum(C100030090) C100030090,
sum(C100030091) C100030091,
sum(C100030092) C100030092,
sum(C100030093) C100030093,
sum(C100030094) C100030094,
sum(C100030095) C100030095,
sum(C100030096) C100030096,
sum(C100030097) C100030097,
sum(C100030098) C100030098,
sum(C100030099) C100030099,
sum(C100030100) C100030100,
sum(C100030101) C100030101,
sum(C100030102) C100030102,
sum(C100030103) C100030103,
sum(C100030104) C100030104,
sum(C100030105) C100030105,
sum(C100030106) C100030106,
sum(C100030107) C100030107,
sum(C100030108) C100030108,
sum(C100030109) C100030109,
sum(C100030110) C100030110,
sum(C100030111) C100030111,
sum(C100030112) C100030112,
sum(C100030113) C100030113,
sum(C100030114) C100030114,
sum(C100030115) C100030115,
sum(C100030116) C100030116,
sum(C100030117) C100030117,
sum(C100030118) C100030118,
sum(C100030119) C100030119,
sum(C100030120) C100030120,
sum(C100030121) C100030121,
sum(C100030122) C100030122,
sum(C100030123) C100030123,
sum(C100030124) C100030124,
sum(C100030125) C100030125,
sum(C100030126) C100030126,
sum(C100030127) C100030127,
sum(C100030128) C100030128,
sum(C100030129) C100030129,
sum(C100030130) C100030130,
sum(C100030131) C100030131,
sum(C100030132) C100030132,
sum(C100030133) C100030133,
sum(C100030134) C100030134,
sum(C100030135) C100030135,
sum(C100030136) C100030136,
sum(C100030137) C100030137,
sum(C100030138) C100030138,
sum(C100030139) C100030139,
sum(C100030140) C100030140,
sum(C100030141) C100030141,
sum(C100030142) C100030142,
sum(C100030143) C100030143,
sum(C100030144) C100030144,
sum(C100030145) C100030145,
sum(C100030146) C100030146,
sum(C100030147) C100030147,
sum(C100030148) C100030148,
sum(C100030149) C100030149,
sum(C100030150) C100030150,
sum(C100030151) C100030151,
sum(C100030152) C100030152,
sum(C100030153) C100030153,
sum(C100030154) C100030154,
sum(C100030155) C100030155,
sum(C100030156) C100030156,
sum(C100030157) C100030157,
sum(C100030158) C100030158,
sum(C100030159) C100030159,
sum(C100030160) C100030160,
sum(C100030161) C100030161,
sum(C100030162) C100030162,
sum(C100030163) C100030163,
sum(C100030164) C100030164,
sum(C100030165) C100030165,
sum(C100030166) C100030166,
sum(C100030167) C100030167,
sum(C100030168) C100030168,
sum(C100030169) C100030169,
sum(C100030170) C100030170,
sum(C100030171) C100030171,
sum(C100030172) C100030172,
sum(C100030173) C100030173,
sum(C100030174) C100030174,
sum(C100030175) C100030175,
sum(C100030176) C100030176,
sum(C100030177) C100030177,
sum(C100030178) C100030178,
sum(C100030179) C100030179,
sum(C100030180) C100030180,
sum(C100030181) C100030181,
sum(C100030182) C100030182,
sum(C100030183) C100030183,
sum(C100030184) C100030184,
sum(C100030185) C100030185,
sum(C100030186) C100030186,
sum(C100030187) C100030187,
sum(C100030188) C100030188,
sum(C100030189) C100030189,
sum(C100030190) C100030190,
sum(C100030191) C100030191,
sum(C100030192) C100030192,
sum(C100030193) C100030193,
sum(C100030194) C100030194,
sum(C100030195) C100030195,
sum(C100030196) C100030196,
sum(C100030197) C100030197,
sum(C100030198) C100030198,
sum(C100030199) C100030199,
sum(C100030200) C100030200,
sum(C100030201) C100030201,
sum(C100030202) C100030202,
sum(C100030203) C100030203,
sum(C100030204) C100030204,
sum(C100030205) C100030205,
sum(C100030206) C100030206,
sum(C100030207) C100030207,
sum(C100030208) C100030208,
sum(C100030209) C100030209,
sum(C100030210) C100030210,
sum(C100030211) C100030211,
sum(C100030212) C100030212,
sum(C100030213) C100030213,
sum(C100030214) C100030214,
sum(C100030215) C100030215,
sum(C100030216) C100030216,
sum(C100030217) C100030217,
sum(C100030218) C100030218,
sum(C100030219) C100030219,
sum(C100030220) C100030220,
sum(C100030221) C100030221,
sum(C100030222) C100030222,
sum(C100030223) C100030223,
sum(C100030224) C100030224,
sum(C100030225) C100030225,
sum(C100030226) C100030226,
sum(C100030227) C100030227,
sum(C100030228) C100030228,
sum(C100030229) C100030229,
sum(C100030230) C100030230,
sum(C100030231) C100030231,
sum(C100030232) C100030232,
sum(C100030233) C100030233,
sum(C100030234) C100030234,
sum(C100030235) C100030235,
sum(C100030236) C100030236,
sum(C100030237) C100030237,
sum(C100030238) C100030238,
sum(C100030239) C100030239,
sum(C100030240) C100030240,
sum(C100030241) C100030241,
sum(C100030242) C100030242,
sum(C100030243) C100030243,
sum(C100030244) C100030244,
sum(C100030245) C100030245
FROM A_BASIC_CS_RADIO
WHERE COLLECTTIME between V_TIME_BEGIN and
V_TIME_BEGIN + 59 / 1440 --one day = 1440 minute
GROUP BY TRUNC(COLLECTTIME, 'HH24'), BSCID, SITEID, BTSID;
COMMIT;
EXCEPTION
WHEN DUP_VAL_ON_INDEX THEN
DBMS_OUTPUT.PUT_LINE('A_BASIC_CS_RADIO_H unique index break');
END;
V_TIME_BEGIN := V_TIME_BEGIN + 1 / 24; --one day = 24 hour
END IF;
END LOOP;
DBMS_OUTPUT.PUT_LINE('SELECT * FROM A_BASIC_CS_RADIO_H--4IN1');
END;
