几种oracle操作

select * from SYS_USER t where rownum <= 1

 Oracle分页查询

select *
from
(
       select
        row_number() over(order by pri_id) as row_no,
        rbac_privilege.*
        from rbac_privilege
        where pri_type=1
) x
where x.row_no>=2 and x.row_no<=10

 

 

第二种 :明显比第一种快

 

   SELECT * FROM
(
SELECT A.*, ROWNUM RN
FROM (select info.*
                  from DATA_INFO info
                 where
                   info.case_id = 1
                 
                 order by info.INFO_ID asc) A
WHERE ROWNUM <= 10
)
WHERE RN >= 1

 

 

还是第二种

SELECT * FROM (SELECT result.*, ROWNUM row_no FROM (
select info.*
from DATA_INFO info, TD_CLUE cl
where info.CLUE_ID = cl.CLUE_ID
and info.case_id = 1
and cl.crtuser = 1
order by info.INFO_ID asc
) result WHERE ROWNUM <= 1000) WHERE row_no >= 990

 

建立自动更新物理视图，影响基表插入速度

CREATE MATERIALIZED VIEW DATA_INFO_STATISTICS
REFRESH FORCE ON COMMIT
AS
select 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 UNFILED
  from 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.updttime
  from 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 [I]trigger_name[/I] DISABLE;

自动任务的最后面加一个：
ALTER TRIGGER [I]trigger_name[/I] ENABLE;

 

只查询一行