`
DataBird
  • 浏览: 71724 次
  • 性别: 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;
发表评论

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

相关推荐

    sql时间段查询

    这里使用了`EXTRACT`函数来获取`datetime_col`字段中的小时部分，然后通过逻辑运算符`AND`和`OR`组合起来进行条件判断。 **2. Oracle 示例** Oracle 提供了一些额外的函数和语法支持，使得这样的查询更为简便： `...

    高手详解SQL性能优化十条经验

    SQL性能优化是数据库管理中的关键任务，它直接影响到应用程序的响应时间和资源消耗。本文将深入探讨高手们在实践中总结的十条SQL性能优化经验。 1. **避免模糊匹配**：使用LIKE '%parm1%'可能导致索引失效，降低...

    mysql时间比较

    MySQL 中使用 `DATE_FORMAT()` 函数来格式化日期时间，并且可以非常灵活地指定输出格式。 **示例：** - 获取当前日期不包含时间的部分（即只保留年月日）： ```sql SELECT DATE_FORMAT(NOW(), '%Y%m%d'); ``` - ...

    oracle日期操作举例

    以上示例展示了如何使用`DBMS_UTILITY.GET_TIME`函数来测量执行一系列操作所需的时间。 通过上述示例，可以看出Oracle提供了丰富的日期操作功能，能够满足各种日期处理的需求。这些功能对于日常的数据分析和应用...

    SQL语句,查询当月，当年数据

    - `t.create_time` 是表中存储时间戳的字段名。 #### 查询当年的数据 查询当前年份的数据，可以使用如下SQL语句： ```sql SELECT * FROM table t WHERE t.create_time &gt;= TRUNC(SYSDATE, 'YYYY') AND t.create_...

    sql优化技巧

    - **顺序的影响**：WHERE 子句中条件的顺序会影响查询的执行计划。一般来说，应该先过滤掉最多的记录，然后再应用其他的过滤条件。 - 示例：调整 WHERE 子句中条件的顺序： ```sql SELECT * FROM zl_yhjbqk ...

    oracle时间管理命令

    通过使用 `JOIN` 或者直接在 `WHERE` 子句中定义连接条件，可以实现跨表的数据检索。 **示例：** ```sql FROM MODULE.FWLOTL L, MODULE.HYMEMMDBH H, OCM.BS_CM_MOD_BOMB B WHERE H.LOTID = L.APPID AND L....

    sql性能优化

    SQL性能优化是数据库管理中的关键任务，它直接影响到应用程序的响应时间和整体效率。本文主要讨论了几种常见的SQL性能优化策略。 首先，对于查询的模糊匹配，应尽量避免使用LIKE '%parm1%'这样的语法，因为百分号在...

    oracle严禁的语句

    - **TRUNC函数**：在日期比较中，避免使用TRUNC，如`WHERE TRUNC(TRANS_DATE) AND TRUNC(SYSDATE);`，应该用BETWEEN操作符。 - **字符串连接符`||`**：使用字符串连接可能导致索引失效，如`WHERE ACCOUNT_NAME || ...

    oracle索引被限制的一些情况

    在 SQL 语句中使用 NULL 会有很多的麻烦。因此，建议开发人员在建表时，把需要索引的列设成 NOT NULL。如果被索引的列在某些行中存在 NULL 值，就不会使用这个索引（除非索引是一个位图索引）。 限制3：使用函数 ...

    oracle不走索引可能原因.docx

    INDEX SKIP SCAN是一种优化策略，允许数据库跳过索引的第一个字段，但这并不总是被触发，特别是在非第一字段未单独出现在WHERE子句中时。 2. **NULL值的影响**: 在含有NULL值的列上建立的索引，在执行`SELECT COUNT...

    oracle关于日期的查询语句

    在Oracle数据库中，日期是数据处理的核心部分，无论是记录事务发生的时间、设定时间窗口进行数据分析，还是设置基于时间的触发器，都离不开对日期的查询和操作。本篇将深入探讨Oracle关于日期的查询语句及其相关知识...

    oracle批量删除

    在Oracle数据库中，当需要处理大量数据，如删除超过50万条记录时，直接使用单个删除语句可能会导致资源占用过多，甚至可能引发锁表问题，影响其他并发操作。为了解决这个问题，通常会采用批量处理的方式，分批进行...

    SQL性能优化

     使用复合索引且第一个索引字段没有出现在 where 中时，建议使用 hint 强制。 1.6 索引使用优化  建立Plan_Table CREATE TABLE PLAN_TABLE ( STATEMENT_ID VARCHAR2(30), TIMESTAMP DATE, REMARKS VARCHAR2...

    MySql性能优化的几种解决方案

    例如，创建一个内存表来存储最近5分钟的服务器日志统计数据，使用`CREATE TEMPORARY TABLE`语句创建内存表，然后通过`INSERT INTO`语句将符合条件的日志数据聚合到内存表中，如`INSERT INTO temp_table(col1, col2, ...

    y2复习题及答案快快抢啊，而且分还低，答案真实！！！

    14. 找出表中重复的记录：可以使用GROUP BY和HAVING子句来找出col1和col2字段中所有重复的记录，例如：SELECT col1, col2 FROM TABLE_NAME GROUP BY col1, col2 HAVING COUNT(*) &gt; 1。 这些知识点涵盖了Oracle...

    数据库性能优化 心得体会 笔记

    - 避免在WHERE子句中使用复杂的计算或函数调用。 - **建议**：熟练掌握这些技巧并在适当的时候应用它们。 11. **使用ORACLE HINTs** - ORACLE HINTs是用于指导Oracle如何执行SQL语句的一种机制。 - 可以通过...

    jdbc操作文档，数据库基本操作文档集合

    update 表名 set 字段名 = 字段值 where 要判断的字段名 = 要判断的字段值; 如果你要进行判断的字段值为null 要写 update 表名 set 字段名 = 字段值 where 要判断的字段名 is null; 4.删除记录 delete from 表名 ...

    Oracle里抽取随机数的多种方法

    注意，dbms_random.value(1,5000) 是取 1 到 5000 间的随机数，会有小数，因此我们需要使用 trunc 函数对随机数字取整，以便和临时表的整数 ID 字段相对应。 如果 tmp_1 记录比较多（10 万条以上），我们也可以找一...

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