`
DataBird
  • 浏览: 70607 次
  • 性别: Icon_minigender_1
  • 来自: 湖南长沙
社区版块
存档分类
最新评论

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秒。差别太大了。
分享到：
| 简单存储过程实例：P_GCELL_PEAK_ZTE
评论
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;
发表评论

您还没有登录,请您登录后再发表评论

相关推荐

    oracle trunc函数使用介绍

    oracle trunc函数使用介绍 1.TRUNC（for dates） TRUNC函数为指定元素而截去的日期值。 其具体的语法格式如下： TRUNC（date[,fmt]）

    trunc函数应用实例

    trunc函数在日期比较和事务执行中是经常要使用到的一个函数,以前使用比较多的是忽略参数fmt的应用,这时默认返回的是距离当前最近的上一个整数日期.比如语句select trunc(sysdate) from dual;就可以返回当天的整数...

    oracle的trunc函数使用实例

    详细介绍了 oracle 内置 trunc 函数的使用场景及实例,对日期格式和数字格式的数据进行操作

    TRUNC_保留小数位

    TRUNC_保留小数位TRUNC_保留小数位TRUNC_保留小数位TRUNC_保留小数位TRUNC_保留小数位TRUNC_保留小数位TRUNC_保留小数位TRUNC_保留小数位TRUNC_保留小数位TRUNC_保留小数位TRUNC_保留小数位TRUNC_保留小数位TRUNC_...

    Oracle中trunc()函数用法处理日期、数字类型数据

    Oracle中trunc()函数用法处理日期、数字类型数据

    b-trunc标准

    b-trunc总体技术要求；..。 国家b-trunc联盟技术要求；

    LTE宽带集群通信(B-TrunC)技术白皮书

    发布《LTE 宽带集群通信（B-TrunC）技术白皮书》，旨在与业界分享宽 带集群（B-TrunC）产业联盟在宽带集群通信技术上的研究成果。 随着全球公共安全、政务、交通、能源等行业的快速发展，行业用户在 传统的语音集群...

    oracle中函数 trunc(),round(),ceil(),floor的使用详解

    1.round函数(四舍五入） 描述 : 传回一个数值，该数值是按照指定的小数位元数进行四舍五入运算的结果 参数: number : 欲处理之数值 decimal_places : 四舍五入 , 小数取几位 ( 预设为 0 ) select round(123.456, ...

    TRUNC()函数用法

    TRUNC()函数用法。。。。。。。。。。。。。

    B-TrunC资源下载

    通信文档协议，LTE TrunC协议，包含多个文档资料，解压可用

    trunc处理日期

    oracl 用 trunc函数处理日期 eg:select trunc sysdate &quot;year&quot; from dual; 截取到年（本年的第一天）

    Django中日期时间型字段进行年月日时分秒分组统计

    数据结构如下：modification字段为修改数据时间字段，格式为 年，月，日，时，分，秒。 案例场景为，根据modification字段，统计每个统计粒子，产生数据的条数。如需要统计2020年10月29日 每个小时段产生数据的条数...

    oracle trunc()函数的使用方法

    oracle trunc()函数是最常用的函数之一，下面就为您介绍oracle trunc()函数的用法，供您参考，希望可以让您对oracle trunc()函数有更深的认识。 1.TRUNC(for dates) TRUNC函数为指定元素而截去的日期值。 其具体的...

    ora_trunc_table.sh

    linux 下 shell 连接 oracle 数据库...脚本产生的背景是生产服务器每天产生几万笔数据，使用的数据库是oralceXE，当数据文件超过4G，会提示错误ORA-12953。因此需要调整数据库中部分表，日志表，部分数据，或者数据文件

    日常收集整理oracle trunc 函数处理日期格式(很实用)

    本文给大家分享的oracle trunc 函数处理日期格式的相关知识，非常具有参考价值，具体请看下文说明吧。 代码如下: select to_char(sysdate,’yyyy-mm-dd hh24:mi:ss’) from dual; –显示当前时间 2011-12-29 16:...

    Oracle常用函数Trunc及Trunc函数用法讲解

    主要介绍了Oracle常用函数Trunc及Trunc函数用法讲解,需要的朋友可以参考下

    【delphi_文章】delphi的取整函数round、trunc、ceil和floor.doc

    【delphi_文章】delphi的取整函数round、trunc、ceil和floor.doc

    Oracle自我补充之trunc()函数的使用方法

    TRUNC函数为指定元素而截去的日期值。下面通过本文给大家介绍Oracle自我补充之trunc()函数的使用方法，感兴趣的朋友一起看看吧

    sqlserver数据库日志收缩工具最新版本trunc.exe

    sql日志收缩工具,sql日志收缩工具,在升级用友软件的时候，因为数据库创建的时候日志文件的大小设置太小，长期使用该数据库生成日志文件最终造成空间不够，而报日志文件空间已满的错误，通过此工具完美解决。...

Magicbox
Global site tag (gtag.js) - Google Analytics