熱點推薦:
您现在的位置: 電腦知識網 >> 編程 >> Oracle >> 正文

Oracle大數據量查詢實際分析

2022-06-13   來源: Oracle 

  Oracle大數據量查詢實際分析

  Oracle數據庫

  剛做一張萬條數據的數據抽取當前表同時還在繼續insert操作每分鐘幾百條數據

  該表按照時間以月份為單位做的表分區沒有任何索引當前共有個字段平均每個字段個字節當前表分區從每月一個分區

  測試服務器xeno 核cpuwin操作系統物理內存G;測試工具plsql

  最開始的查詢

  stringFormat(@select * from (select ridrcarcoderlongtituderlatitudervelocityrgpstimerisonline from t_gps_record r where id in( select min(id) from t_gps_record r where carcode={} group by to_char(gpstimeyyyyMMdd HH:mi)) and carcode={} and gpstime>(select nvl((select max(gpstime) from t_gps_carposition where carcode={})(select min(gpstime) from t_gps_record where carcode={})) from dual) order by gpstime asc ) where rownum<= row[carcode]ToString());

  一開始以條數據為段進行查詢查詢一次分鐘秒;

  後來查分鐘秒;基本跟條數無關

  後來把最小時間寫成固定的

  stringFormat(@select * from (select ridrcarcoderlongtituderlatitudervelocityrgpstimerisonline from t_gps_record r where id in( select min(id) from t_gps_record r where carcode={} group by to_char(gpstimeyyyyMMdd HH:mi)) and carcode={} and gpstime>to_date( ::yyyymmdd HH:mi:ss) order by gpstime asc ) where rownum<= row[carcode]ToString());

  查詢時間

  不加分區查詢

  select ridrcarcoderlongtituderlatitudervelocityrgpstimerisonline from t_gps_record r where id in( select min(id) from t_gps_record r group by carcode to_char(gpstimeyyyyMMdd HH:mi)) and gpstime>=to_date( ::yyyymmdd HH:mi:ss) and gpstime<=to_date( ::yyyymmdd HH:mi:ss) order by gpstime asc

  查詢時間

  添加分區查詢

   select ridrcarcoderlongtituderlatitudervelocityrgpstimerisonline from t_gps_record r where id in( select min(id) from t_gps_record partition(GPSHISTORY) r group by carcode to_char(gpstimeyyyyMMdd HH:mi)) and gpstime>=to_date( ::yyyymmdd HH:mi:ss) and gpstime<=to_date( ::yyyymmdd HH:mi:ss) order by gpstime asc

  添加分區後查詢s

  所以加分區後的查詢效率提高十幾倍所以大數據量建立分區表是相當重要的


From:http://tw.wingwit.com/Article/program/Oracle/201311/16819.html
    Copyright © 2005-2022 電腦知識網 Computer Knowledge   All rights reserved.