代码之家 › 专栏 › 技术社区 › nnmmss

加快记录获取时间

performance-testing query-optimization oracle11g oracle sql

nnmmss · 技术社区 · 6 年前

我有一个SQL语句,大约需要44秒来获取数据。我怎样才能减少时间?

这是sql

Select AVNR,replace(to_char((CAST(DBTM as timestamp)),'hh24:mi'),'00:00','24:00') as ptime,
        Wert
From e_mw_60min_me
Where to_char((CAST ( DBTM as TIMESTAMP)), 'DD-MM-YYYY') = '13-03-1396'  and
      AVNR In (1141,1142,1144,10335,10336,12016,1146,1147,1149,1129,1130,1132,1134,1135,1137,5895,5896,5900,8906,8907,8909,8901,8902,8904,8940,8941,8943,8951,8952,8954,8972,8973,8975,8830,8831,8833,8835,8836,8838,1113,1982,1984,2314,2315,2317,3272,3273,3275,3267,3268,3270,3262,3263,3265,10231,10136,9066,8779,8780,8782,8774,8775,8777,8320,8321,8323,7696,7697,7699,10486,10487,10489,3329,3330,12018,3322,3328,10132,3320,3321,12017,3222,3223,3225,686,687,689,691,692,694,696,697,699,1,2,4,10527,10528,10529,4911,4912,4914,4917,4918,4920,5162,5163,5166,5157,5158,5160,5168,5169,5171,5449,5450,5452,10116,10117,10119,10120,10121,10123,2271,2272,2279,2266,2267,2269,2259,2260,2262,1292,1293,1295,5380,5381,5383,5374,5375,5377,10545,10546,10547,3281,10126,12031,3244,3245,12030,10983,10984,12033,10987,10989,12032,11073,11074,1125,11079,11080,3333,105,106,108,100,101,103,93,94,96,29,30,32,95,102,1197,124,123,126,12085,12086,12087,2520,2521,2523,2525,2526,2528,2515,2516,2518,2510,2511,2513,5444,5445,5447,2201,2202,12025,3336,3337,3339,3002,3003,12029,1643,1644,1646,1609,1610,1612,1596,1597,1599,9717,9718,9720,9722,9723,9725,2146,2147,2149,2141,2142,2144,2136,2137,2139,2131,2132,2134,2121,2122,2124,2126,2127,2129,2635,2636,2638,2641,2642,2644,7499,7500,7502,4610,4611,4613,4605,4606,4608,4600,4601,4603,9074,9075,9077,9079,9080,9082,9235,9236,9238,9240,9241,9243,9245,9246,9248,8468,8469,8471,5785,5786,5788,5790,5791,5793,5691,5692,5694,5685,5686,5688,8455,8456,8458,11312,11313,4588,7654,7655,7657,9376,9377,9379,9371,9372,9374,9382,9383,9385,5918,5919,5922,5934,5935,5938,5912,5913,5916,10963,7860,10964,135,136,138,10658,10659,10664,10660,10661,10662,5173,5458,5460);

 //these AVNRs are completley Dynamic

桌子是这样的:

AVNR(如身份证号码)
DBTM(每个点的每次REOCRD)
Wert(价值)

我想知道任何其他的sql命令,使它更快地获取

2 回复 | 直到 6 年前

Alex Poole 6 年前

如果DBTM是一个日期,则不需要将其转换为时间戳;如果有索引,则不应将其转换为字符串以与另一个字符串进行比较-将其保留为日期并将固定值转换为日期,并与覆盖整天的范围进行比较:

select AVNR,
  replace(to_char(DBTM,'hh24:mi'),'00:00','24:00') as ptime,
  Wert
from e_mw_60min_me
where DBTM >= date '1396-03-13'
and DBTM < date '1396-03-14'
and AVNR In (1141, ...);

我用过日期文字,但你可以用 to_date() 如果从变量字符串开始:

where DBTM >= to_date('13-03-1396', 'DD-MM-YYYY')
and DBTM < to_date('13-03-1396', 'DD-MM-YYYY') + 1
and AVNR In (1141, ...);

您还应该考虑为正在查找和使用的所有AVNR值创建一个集合 member of 相反,如果 IN ,或者分解集合并加入它。但这取决于这些价值观来自何方。

Gordon Linoff 6 年前

可以添加索引:

create index idx_e_mw_60min_me_2 on (to_char((CAST ( DBTM as TIMESTAMP)), 'DD-MM-YYYY'), AVNR)

这和 where 子句,因此它应该加快查询速度。