由于開發(fā)經(jīng)驗(yàn)較少，數(shù)據(jù)量較大（千萬級(jí)，日有40萬條數(shù)據(jù)左右），導(dǎo)致這個(gè)分頁jsp頁面做的效率非常低，通過一系列的優(yōu)化后，效果明顯得到了提高，記錄一下筆記：
1、分頁獲取的list對象集合由于沒有預(yù)料到數(shù)據(jù)量的大小，是直接一次性讀取然后展示到前臺(tái)的，導(dǎo)致查詢展示效率非常低：
更改SQL語句,改為按照當(dāng)前需要展示的數(shù)據(jù)行數(shù)，通過SQL的ROWNUM來進(jìn)行控制查詢的數(shù)據(jù)量大小，（插入數(shù)據(jù)時(shí)，打開一個(gè)數(shù)據(jù)庫連接即一次性插入100條數(shù)據(jù)（可以配置的模式））：
SELECT BRAS_ADDRESS, APPLYTIMES, ALLFAILTIMES,SUCCESSRATE, RN
FROM
(SELECT BRAS_ADDRESS, APPLYTIMES, ALLFAILTIMES,SUCCESSRATE, ROWNUM AS RN
FROM
(SELECT BRAS_ADDRESS, APPLYTIMES, ALLFAILTIMES,1-ALLFAILTIMES/DECODE(APPLYTIMES,0,1,APPLYTIMES) AS SUCCESSRATE
FROM
(SELECT BRAS_ADDRESS,
SUM(DECODE(AUTNCOUNTTYPE,'REQ_CHALLENGE',APPLYTIMES,0)) AS APPLYTIMES,
SUM(DECODE(AUTNCOUNTTYPE,'ACK_CHALLENGE',APPLYTIMES,'ACK_AUTH',APPLYTIMES,'REQ_LOGOUT',APPLYTIMES,0)) AS ALLFAILTIMES
FROM T_BRAS_XXXX
WHERE READTIME between TO_DATE('2012-06-01 00:00:00','yyyy-MM-dd HH24:mi:ss')
and TO_DATE('2012-06-12 23:59:59','yyyy-MM-dd HH24:mi:ss')
GROUP BY BRAS_ADDRESS
)
ORDER BY SUCCESSRATE
)
WHERE ROWNUM <= 180
)
WHERE RN > 165 2、建立索引，建立索引的字段不能使用函數(shù)避免索引失效；
3、建立分區(qū)表，將間隔5天的數(shù)據(jù)放在一個(gè)表中：
----------------------刪除原表，新建分區(qū)表
--刪除表
drop table T_BRAS_XXXX;
commit;
--創(chuàng)建分區(qū)表：利用Oracle11g INTERVAL進(jìn)行分區(qū)的方法
create table T_BRAS_XXXX
(
BRAS_XXXX_ID integer NOT NULL PRIMARY KEY,
BRAS_XXXX VARCHAR2(64),
AUTNCOUNTTYPE VARCHAR2(50),
SUCCESSTIMES NUMBER,
APPLYTIMES NUMBER,
INTERVALTIME NUMBER,
UPDATETIME DATE DEFAULT SYSDATE,
READTIME DATE
)
PARTITION BY RANGE (READTIME)
INTERVAL (NUMTODSINTERVAL(5,'day'))
(
PARTITION T_BRAS_XXXX_PART01 VALUES LESS THAN (TO_DATE('2012-07-01 00:00:00','yyyy-MM-dd HH24:mi:ss'))
)
--保留原來創(chuàng)建的BRAS_XXXX_ID自增序列
--創(chuàng)建索引
create index idx_t_bras_XXXX on t_bras_XXXX(readtime,bras_address);
commit;
--查看分區(qū)是否成功：
select table_name,partition_name from user_tab_partitions where table_name='T_BRAS_XXXX';