果然视图的效率永远是跟不上表的啊

工作上的原因开始接触Oracle了...虽然Oracle在几年前就玩过了,但是基本上都是最基本得操作了,简单的存储过程,简单的触发器.....

现在到了新的公司,碰到了诡异的问题是一个针对地区的统计问题.

一个省下面有许多的市,需要统计这些市出现的次数,以及外省的数字.

本来这个是一个非常容易搞定的问题,但是现在却碰到了麻烦,那就是这个地址字段里面存放的并不是地区的代码而是地区的文本....orz

于是现在如果想要统计这种要么通过不断的LIKE来做Count(ID),然后UNION ALL形成最终结果了...但是仅仅面对20万左右的数据量,这条语句的执行时间居然长达3分钟以上.

然后想了一个办法,首先将所有符合条件的数据全部用视图的方式UNION ALL起来,添加一个虚拟的地区代码字段,然后再对这个视图做Group BY操作.

可惜,就算是这样做了,也仅仅只是把时间提升到了2分钟10秒左右.

没办法,只有想办法改动表结构了...我是一直不怎么想改动这个表结构的,因为不是我设计的,而且本身设计的很糟糕... 程序也是刚刚接手,都没办法整理,所以如果变动了表结构,就一定要通过触发器来解决后续的问题了...

不过抱着尝试的心态,添加了一个字段,然后简单的用LIKE来更新这个字段.

然后再用Group By来生成报表.这时惊人的效率提升终于出现了..只用了5秒.

但是只有20万的数据量的话,用5秒似乎也显得太多了一点 ,估计是没有使用到索引...

于是添加索引,以Where语句的字段和Group By后面的字段添加联合索引.再做测试...

速度终于提升到0.15秒左右了...正常范围内了(虽然赶不上MySQL的效率了,当然,这也是必然的)...

接下来就是触发器了...以前只是写过一个自增ID字段的触发器而已,而且还是网上直接抄下来的...

不过现在这个触发器也很简单就是了...所需要实现的就是在Insert字段的时候根据既有的字段中的某一个值(所在地区)得取值来确定我添加的那个字段的取值.并且将更新了的数据插入进去就可以了.

但是不得不说,我根本就忘了触发器怎么写了....orz

不过最后还是搞定了就是了...之前忘掉了使用for each row,导致一直都报告ORA-04082错误,添加那个字段后终于搞定了...这个字段的含义是,缺省的触发器是语句级别的,如果添加了这个字段就会变成行级别,可以对语句所涉及到的每一行都做操作,对于Insert而言,这自然就是对刚刚Insert进来的这一行了...

最终的样子如下:
create OR REPLACE trigger BI_ACCEPT
       before Insert
              on WX_ACCEPT
for each row
Begin
  if :new.APPEAL_ADDRESS LIKE '%武汉%' then
    :new.LOCATION_NAME := 1;
  elsif :new.APPEAL_ADDRESS LIKE '%黄石%' then
    :new.LOCATION_NAME := 2;
  elsif :new.APPEAL_ADDRESS LIKE '%十堰%' then
    :new.LOCATION_NAME := 3;
  elsif :new.APPEAL_ADDRESS LIKE '%宜昌%' then
    :new.LOCATION_NAME := 4;
  elsif :new.APPEAL_ADDRESS LIKE '%襄樊%' OR :new.APPEAL_ADDRESS LIKE '%襄阳%' then
    :new.LOCATION_NAME := 5;
  elsif :new.APPEAL_ADDRESS LIKE '%鄂州%' then
    :new.LOCATION_NAME := 6;
  elsif :new.APPEAL_ADDRESS LIKE '%荆门%' then
    :new.LOCATION_NAME := 7;
  elsif :new.APPEAL_ADDRESS LIKE '%孝感%' then
    :new.LOCATION_NAME := 8;
  elsif :new.APPEAL_ADDRESS LIKE '%荆州%' then
    :new.LOCATION_NAME := 9;
  elsif :new.APPEAL_ADDRESS LIKE '%黄冈%' then
    :new.LOCATION_NAME := 10;
  elsif :new.APPEAL_ADDRESS LIKE '%咸宁%' then
    :new.LOCATION_NAME := 11;
  elsif :new.APPEAL_ADDRESS LIKE '%随州%' then
    :new.LOCATION_NAME := 12;
  elsif :new.APPEAL_ADDRESS LIKE '%恩施%' then
    :new.LOCATION_NAME := 13;
  elsif :new.APPEAL_ADDRESS LIKE '%仙桃%' then
    :new.LOCATION_NAME := 14;
  elsif :new.APPEAL_ADDRESS LIKE '%潜江%' then
    :new.LOCATION_NAME := 15;
  elsif :new.APPEAL_ADDRESS LIKE '%天门%' then
    :new.LOCATION_NAME := 16;
  elsif :new.APPEAL_ADDRESS LIKE '%神农架%' then
    :new.LOCATION_NAME := 17;
  end if;
end;

评论

发表评论

此博客中的热门博文

转一下关于Fuck的用法

不得不说,不会国骂人的人那一定不是土生土长中国人,相对的,不会用这英文四字真经的绝对也不是土生土长美国人. “Fuck” 是现代英语中最强烈，最有争议的粗语之一，这个词可能也是世界上最著名的亵渎语。但何时这个词从一个友好用法变成亵渎用法却仍然无法解释。一些证明显示，在一些英语为母语的地区，这个词直到17世纪才有一种攻击和亵渎的含义; 而另外一些证明却表明，早在16世纪的英国，这个词已经变成粗语。因此一些诸如牛津大英字典的权威资料仍然无法确定这个粗语的来源，但倾向于相信撒克逊人(英国人)起源说，然后再流传到英国殖民地，再到全世界。 　　 　　在现代英语中，”Fuck”是一种高度冒犯性的用词，是最为严厉的4字母词。”Fuck” 原意就是性交(操，干)，但它往往被用作一个咒骂语和语气加强词。一些”Fuck”原意的例子有： 　　 　　”Let‘ s fuck.” (让我们操吧) 　　”That was a good fuck.” (那真是一次美妙的性交) 　　”I can‘t believe she‘s fucking him!” (我不能相信她正在操他！) 　　”I fucked my teacher!” (我干了我老师) 　　 　　其他则是一些粗直语用法，但这些用法往往牵涉到性，譬如强奸(fuck you)或者自慰(fuck yourself). 　　 　　”Fuck you!” or “Go fuck yourself!” (我不喜欢你，快滚开) 　　”He”s a dumb fuck.” (他是个白痴) 　　”Sorry, I fucked up your computer.” (不好意思，我弄坏了你的电脑) 　　”He‘ s pretty fucked up.” (他心理或者心情不稳定) 　　”I fucked up on this test.” (我这次测验做的很糟) 　　”Let‘s fuck around for a couple hours.” (让我们浪费2个小时好了) 　　”I‘m fucked.” (结果) 　　”What the fuck!” (到底刚才发生了什么?) 　　”Shut the fuck up!” (闭嘴) 　　”I‘m so fucked up right now.” (我已经烂醉或者被药物搞的晕晕乎乎了。) 　　其他一些粗直用法则...
阅读全文

远程记录OpenWRT日志

默认的OpenWRT日志放在/tmp/log下，这样有个问题就是每次重启就会丢失全部的日志，于是公司的路由器每次重启之后连为什么重启都不知道 于是就想办法把日志迁移出来，好在OpenWRT本身就附带了远程日志打印功能，于是直接在内网中开一个rsyslog服务器就可以了 在/etc/rsyslog.d/下新建一个router.conf 内容如下： module(load="imtcp") module(load="imudp") input(type="imudp"      port="514"      ruleset="routerSet") input(type="imtcp"      port="514"      ruleset="routerSet") # rsyslog RuleSets ruleset(name="routerSet") {     action(type="omfile"         File="/var/log/router.log") } （这个配置文件可以在 http://www.rsyslog.com/rsyslog-configuration-builder/ 生成） 接下来启动一下rsyslog服务就可以了 systemctl restart rsyslog 在OpenWRT端需要做的工作： vim /etc/config/system 在config system下增加这么几行         option log_remote '1'         option log_ip '192.168.0.13'  # 你的日志i服务器内网IP         option log_port '514'         option log_size '0'  # 如果不加这个可能会丢失部...
阅读全文

用OpenWRT打造自动翻墙路由器（详解篇）

最初先调整一下bash ，让它符合自己的工作习惯： 到vim /etc/profile，可以在里面设置自己想要的alias 比如我加了一行： alias ll='ls -al' 然后就装图形显示界面再说 首先update一下 opkg update 安装luci opkg install luci 设置luci自动启动 /etc/init.d/uhttpd enable 结果告诉我 /etc/rc.common: line 1: procd_add_reload_trigger: not found 坑爹，搜索了一下发现是因为我的固件版本与软件仓库的版本不符，导致软件与系统组件不搭配所致，后来发现是 新版的OpenWrt更新了而已，于是我升级了一下系统就好了 ，如果你无法升级系统，那么可以按照下述方式解决： 先把安装的那些个用不了的一个个卸载掉 opkg remove luci opkg remove uhttpd-mod-ubus opkg remove uhttpd 修改opkg的配置，修改到以前的仓库 vim /etc/opkg.conf 注释掉原有的仓库地址，改用旧的 #src/gz barrier_breaker http://downloads.openwrt.org/snapshots/trunk/ar71xx/packages src/gz barrier_breaker http://downloads.openwrt.org/attitude_adjustment/12.09/ar71xx/generic/packages 然后在opkg update一下，继续 opkg install luci opkg install luci-i18n-chinese 这次在/etc/init.d/uhttpd start 的时候就不会报错了 然后稳妥的进入luci的Web界面，修改一下时区、界面语言之类的 接下来的工作是 修改防火墙的设置，让远端的HTTP和SSH能够通过 vim /etc/config/firewall config rule                ...
阅读全文