时间字段可否考虑用时间戳存储, 然后建立索引, 查询时若是一个规整的范围可以用between, 应该可以利用到索引;
看下执行计划
alter system flush buffer_cache; 一定不要在生产环境运行这个命令!
你可以考虑把sec_transaction放在WITH里面,然后再跟其他表关联
这种数量大的业务表,可以考虑分区
单独用transittime 条件来查询SEC_TRANSACTION表,需要多少时间? 从计划来看,成本都用在这张表上了
本来准备试一下先通过where查sec_transaction,然后再连接其他表查询。结果发现现在3秒就查出了,是因为缓存? select a.emplid, b.cname, a.cardid, b.bldeptid, c.descr, d.doorcode, d.doorname, a.transittime, a.transitstate, d.doortype from sec_transaction a, v_all_user b, EMP_ORG c, SEC_BD_DOORINFO d where a.emplid = b.emplid and b.bldeptid = c.deptid and a.doorno = d.doorno and a.transittime between to_date('2018/04/01', 'yyyy/mm/dd') and to_date('2018/07/01', 'yyyy/mm/dd'); --0:03 select a.emplid, b.cname, a.cardid, b.bldeptid, c.descr, d.doorcode, d.doorname, a.transittime, a.transitstate, d.doortype from (select t.emplid, t.cardid, t.transittime, t.transitstate, t.doorno from sec_transaction t where t.transittime between to_date('2018/04/01', 'yyyy/mm/dd') and to_date('2018/07/01', 'yyyy/mm/dd')) a, v_all_user b, EMP_ORG c, SEC_BD_DOORINFO d where a.emplid = b.emplid and b.bldeptid = c.deptid and a.doorno = d.doorno --2:20 第一次查2:20秒,第二次三次....都是3秒,上面的查也是3秒
select a.emplid, b.cname, a.cardid, b.bldeptid, c.descr, d.doorcode, d.doorname, a.transittime, a.transitstate, d.doortype from sec_transaction a, v_all_user b, EMP_ORG c, SEC_BD_DOORINFO d where a.emplid = b.emplid and b.bldeptid = c.deptid and a.doorno = d.doorno and a.transittime between to_date('2018/04/01', 'yyyy/mm/dd') and to_date('2018/07/01', 'yyyy/mm/dd'); --0:03 select a.emplid, b.cname, a.cardid, b.bldeptid, c.descr, d.doorcode, d.doorname, a.transittime, a.transitstate, d.doortype from (select t.emplid, t.cardid, t.transittime, t.transitstate, t.doorno from sec_transaction t where t.transittime between to_date('2018/04/01', 'yyyy/mm/dd') and to_date('2018/07/01', 'yyyy/mm/dd')) a, v_all_user b, EMP_ORG c, SEC_BD_DOORINFO d where a.emplid = b.emplid and b.bldeptid = c.deptid and a.doorno = d.doorno --2:20 第一次查2:20秒,第二次三次....都是3秒,上面的查也是3秒
17,377
社区成员
95,128
社区内容
加载中
试试用AI创作助手写篇文章吧