oracle查询200W数据需要1S,64W数据表，查询数据执行时间为139s，优化为1s-Oracle

64W数据表，查询数据执行时间为139s，优化为1s

AFC_TXN.TXN_DATA_FUNC.fetchbatch包中的SQL查询语句过慢

cut_batch表大约有64W数据，其中执行参数为substr(t.BATCH_ID,1,2)=’01’的语句为139s

给cut_batch表添加索引create index idx_cut_batch2 on cut_batch(substr(batch_id,1,2))后，执行时间为54s

修改语句为以下写法：

SELECT

os.AFC_DEVICE_ID      AS AFC_DEVICE_ID,

os.BATCH_ID           AS BATCH_ID,

os.DATA_DIRECTORY     AS DATA_DIRECTORY,

os.RECORD_COUNT       AS RECORD_COUNT

FROM (

SELECT

t.AFC_DEVICE_ID      AS AFC_DEVICE_ID,

t.BATCH_ID           AS BATCH_ID,

t.DATA_DIRECTORY     AS DATA_DIRECTORY,

t.RECORD_COUNT       AS RECORD_COUNT,

SUM(t.RECORD_COUNT) OVER (ORDER BY BATCH_ID ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS TOTAL_TXN

FROM

(SELECT

t.AFC_DEVICE_ID      AS AFC_DEVICE_ID,

t.BATCH_ID           AS BATCH_ID,

t.DATA_DIRECTORY     AS DATA_DIRECTORY,

t.RECORD_COUNT       AS RECORD_COUNT,

t.SC_BATCH_ID AS SC_BATCH_ID

FROM cut_batch_test t

WHERE substr(t.BATCH_ID,1,2) = :”SYS_B_0″) t

WHERE  t.SC_BATCH_ID IS NULL

) os

WHERE os.TOTAL_TXN < 300;

其执行速度达到45s

修改以下oracle系统参数

alter system set db_cache_size=200M scope=both;

alter system set large_pool_size=50M scope=both