几种oracle操作
Oracle分页查询select *from( select row_number() over(order by pri_id) as row_no, rbac_privilege.* from rbac_privilege where pri_type=1) xwhere x.row_no>=2 and x.row_no<=10
第二种 :明显比第一种快
SELECT * FROM(SELECT A.*, ROWNUM RNFROM (select info.* from DATA_INFO info where info.case_id = 1 order by info.INFO_ID asc) AWHERE ROWNUM <= 10)WHERE RN >= 1
还是第二种
SELECT * FROM (SELECT result.*, ROWNUM row_no FROM (select info.*from DATA_INFO info, TD_CLUE clwhere info.CLUE_ID = cl.CLUE_IDand info.case_id = 1and cl.crtuser = 1order by info.INFO_ID asc) result WHERE ROWNUM <= 1000) WHERE row_no >= 990
建立自动更新物理视图，影响基表插入速度
CREATE MATERIALIZED VIEW DATA_INFO_STATISTICSREFRESH FORCE ON COMMITASselect info.case_id, info.case_name, info.object_id, info.object_name, info.clue_id, info.CLUENAME, info.info_type_id, info.INFOTYPENAME_CH, info.info_type_class_id, count(info.info_id) as INFOCOUNT, sum(info.data_isread) as READCOUNT, sum(DECODE(info.data_isread, 0, 1, 0)) as UNREADCOUNT, sum(info.DATA_ISMANAGEED) as FILED, sum(DECODE(info.DATA_ISMANAGEED, 0, 1, 0)) as UNFILEDfrom DATA_INFO info group by info.case_id, info.case_name, info.object_id, info.object_name, info.clue_id, info.CLUENAME, info.info_type_id, info.INFOTYPENAME_CH, info.info_type_class_id; insert select
insert into TD_CLUE_TYPE_PARAMETER(param_id, clue_type_id, clue_type_class_id, param_name, param_name_ch, param_name_en, param_type, param_use, param_default, param_is_advanced, seq,status) select TD_CLUE_TYPE_PARAMETER_SEQ.NEXTVAL,de.clue_type_id,de.clue_type_class_id,'clueparameter1','Email地址','EMAIL','string','1','@163.com','0','1','0'from TD_CLUE_TYPE_DETAIL de where de.cluetypename_en='CLUE_EMAIL';
update select
update (select cl.clue_endtime, cl.updttimefrom TD_CLUE cl, Td_Object ob where ob.object_id = cl.object_id and ob.case_id = #caseId# and cl.object_id = #objectId# and cl.clue_endtime > #endTime#)set clue_endtime = #endTime#, updttime = to_char(sysdate, 'yyyy-mm-dd') 暂停/启动触发器
在自动任务的最前面加一个：ALTER TRIGGER trigger_name DISABLE;自动任务的最后面加一个：ALTER TRIGGER trigger_name ENABLE;
页:
[1]