1. 表连接SELECT pro.id, pro.name, organ.id, organ.name
FROM T_ORGAN organ
LEFT JOIN T_ORGAN pro ON pro.id = substr(organ.id, 0, 1)
WHERE organ.id like "1100%"这条语句是先进行表连接还是先执行where语句?2. 分页
下面的语句是将TABLE T_REPORT_BUSINESS_EXPERIENCE中按地区分组统计各个业务体验的次数,不知道这样的语句效率怎么样?大虾们指点一下!分页语句放在最外面和最里面一样吗?
组织机构分为:省(1)/市(1100)/区(1100100)/厅(1100100100)CREATE TABLE T_REPORT_BUSINESS_EXPERIENCE (
organId varchar2(50),
businessName varchar2(20),
experienceNum number,
createTime date,
constraint PK_REPORT_BUSINESS_EXPERIENCE primary key(organId, businessName, createTime),
constraint FK_RBE_ORGANID foreign key(organId) references T_ORGAN (id)
);SELECT *
FROM(
SELECT b.*, rownum row_id
FROM(
SELECT
pro.id provinceId, pro.name provinceName,
city.id cityId, city.name cityName,
area.id areaId, area.name areaName,
(SELECT sum(experienceNum) FROM T_REPORT_BUSINESS_EXPERIENCE WHERE SUBSTR(organId,0,7)=a.areaId and businessName='爱音乐') musicCount,
(SELECT sum(experienceNum) FROM T_REPORT_BUSINESS_EXPERIENCE WHERE SUBSTR(organId,0,7)=a.areaId and businessName='手机影视') videoCount,
(SELECT sum(experienceNum) FROM T_REPORT_BUSINESS_EXPERIENCE WHERE SUBSTR(organId,0,7)=a.areaId and businessName='互联星空') chinaNetCount,
(SELECT sum(experienceNum) FROM T_REPORT_BUSINESS_EXPERIENCE WHERE SUBSTR(organId,0,7)=a.areaId and businessName='天翼live') liveCount,
(SELECT sum(experienceNum) FROM T_REPORT_BUSINESS_EXPERIENCE WHERE SUBSTR(organId,0,7)=a.areaId and businessName='189邮箱') mailCount,
(SELECT sum(experienceNum) FROM T_REPORT_BUSINESS_EXPERIENCE WHERE SUBSTR(organId,0,7)=a.areaId and businessName='天翼地图') mapCount,
(SELECT sum(experienceNum) FROM T_REPORT_BUSINESS_EXPERIENCE WHERE SUBSTR(organId,0,7)=a.areaId and businessName='手机上网') netCount,
(SELECT sum(experienceNum) FROM T_REPORT_BUSINESS_EXPERIENCE WHERE SUBSTR(organId,0,7)=a.areaId and businessName='手机炒股') stockCount
FROM (
SELECT SUBSTR(rbe.organId,0,7) areaId
FROM T_REPORT_BUSINESS_EXPERIENCE rbe
WHERE rbe.organId like '$organId$%'
and trunc(rbe.createTime,#timeType#) <![CDATA[>= ]]>trunc(#startTime#,#timeType#)
and trunc(rbe.createTime,#timeType#) <![CDATA[<= ]]>trunc(#endTime#,#timeType#)
GROUP BY SUBSTR(rbe.organId,0,7)
) a
LEFT JOIN T_ORGAN pro ON pro.id = SUBSTR(a.areaId,0,1)
LEFT JOIN T_ORGAN city ON city.id = SUBSTR(a.areaId,0,4)
LEFT JOIN T_ORGAN area ON area.id = a.areaId
) b
) t
WHERE t.row_id between #startIndex# and #endIndex#
ORDER BY t.provinceId, t.cityId, t.areaId
FROM T_ORGAN organ
LEFT JOIN T_ORGAN pro ON pro.id = substr(organ.id, 0, 1)
WHERE organ.id like "1100%"这条语句是先进行表连接还是先执行where语句?2. 分页
下面的语句是将TABLE T_REPORT_BUSINESS_EXPERIENCE中按地区分组统计各个业务体验的次数,不知道这样的语句效率怎么样?大虾们指点一下!分页语句放在最外面和最里面一样吗?
组织机构分为:省(1)/市(1100)/区(1100100)/厅(1100100100)CREATE TABLE T_REPORT_BUSINESS_EXPERIENCE (
organId varchar2(50),
businessName varchar2(20),
experienceNum number,
createTime date,
constraint PK_REPORT_BUSINESS_EXPERIENCE primary key(organId, businessName, createTime),
constraint FK_RBE_ORGANID foreign key(organId) references T_ORGAN (id)
);SELECT *
FROM(
SELECT b.*, rownum row_id
FROM(
SELECT
pro.id provinceId, pro.name provinceName,
city.id cityId, city.name cityName,
area.id areaId, area.name areaName,
(SELECT sum(experienceNum) FROM T_REPORT_BUSINESS_EXPERIENCE WHERE SUBSTR(organId,0,7)=a.areaId and businessName='爱音乐') musicCount,
(SELECT sum(experienceNum) FROM T_REPORT_BUSINESS_EXPERIENCE WHERE SUBSTR(organId,0,7)=a.areaId and businessName='手机影视') videoCount,
(SELECT sum(experienceNum) FROM T_REPORT_BUSINESS_EXPERIENCE WHERE SUBSTR(organId,0,7)=a.areaId and businessName='互联星空') chinaNetCount,
(SELECT sum(experienceNum) FROM T_REPORT_BUSINESS_EXPERIENCE WHERE SUBSTR(organId,0,7)=a.areaId and businessName='天翼live') liveCount,
(SELECT sum(experienceNum) FROM T_REPORT_BUSINESS_EXPERIENCE WHERE SUBSTR(organId,0,7)=a.areaId and businessName='189邮箱') mailCount,
(SELECT sum(experienceNum) FROM T_REPORT_BUSINESS_EXPERIENCE WHERE SUBSTR(organId,0,7)=a.areaId and businessName='天翼地图') mapCount,
(SELECT sum(experienceNum) FROM T_REPORT_BUSINESS_EXPERIENCE WHERE SUBSTR(organId,0,7)=a.areaId and businessName='手机上网') netCount,
(SELECT sum(experienceNum) FROM T_REPORT_BUSINESS_EXPERIENCE WHERE SUBSTR(organId,0,7)=a.areaId and businessName='手机炒股') stockCount
FROM (
SELECT SUBSTR(rbe.organId,0,7) areaId
FROM T_REPORT_BUSINESS_EXPERIENCE rbe
WHERE rbe.organId like '$organId$%'
and trunc(rbe.createTime,#timeType#) <![CDATA[>= ]]>trunc(#startTime#,#timeType#)
and trunc(rbe.createTime,#timeType#) <![CDATA[<= ]]>trunc(#endTime#,#timeType#)
GROUP BY SUBSTR(rbe.organId,0,7)
) a
LEFT JOIN T_ORGAN pro ON pro.id = SUBSTR(a.areaId,0,1)
LEFT JOIN T_ORGAN city ON city.id = SUBSTR(a.areaId,0,4)
LEFT JOIN T_ORGAN area ON area.id = a.areaId
) b
) t
WHERE t.row_id between #startIndex# and #endIndex#
ORDER BY t.provinceId, t.cityId, t.areaId
解决方案 »
- oracle数据库,导出某个user的所有数据
- 請教:RHEL5中oracle database 10g express安裝以及怎樣配置
- 关于toad怎么查看 一句插入执行的情况
- 这个SQl语句有点难写,不会写~~呵呵,选择问题
- 有谁见过oracle 中VIEW 里含有UNION ALL时出现的BUG,如何解决?
- row_number()的使用请教, 知道的都请进来指点指点
- nvarchar类型的数据,exp/imp备份数据的时候出错,高手请进
- Oracle9!里写ISQL/Plus语句在哪里查看帮助文件呢?
- 一个初学者的问题
- Oracle中有否十进制转成十六进制与十六进制转成十进制等系列函数否?急
- 急,在线等!数据库导出导入excel的问题。
- linux下oracle开发
2、数据量大肯定没效率了。。可以使用中间表
2.看执行计划,相关字段建索引。另外下面这一堆是否可以用一个sql group by businessName出来,然后再decode放在上面
(SELECT sum(experienceNum) FROM T_REPORT_BUSINESS_EXPERIENCE WHERE SUBSTR(organId,0,7)=a.areaId and businessName='爱音乐') musicCount,
(SELECT sum(experienceNum) FROM T_REPORT_BUSINESS_EXPERIENCE WHERE SUBSTR(organId,0,7)=a.areaId and businessName='手机影视') videoCount,
(SELECT sum(experienceNum) FROM T_REPORT_BUSINESS_EXPERIENCE WHERE SUBSTR(organId,0,7)=a.areaId and businessName='互联星空') chinaNetCount,
(SELECT sum(experienceNum) FROM T_REPORT_BUSINESS_EXPERIENCE WHERE SUBSTR(organId,0,7)=a.areaId and businessName='天翼live') liveCount,
(SELECT sum(experienceNum) FROM T_REPORT_BUSINESS_EXPERIENCE WHERE SUBSTR(organId,0,7)=a.areaId and businessName='189邮箱') mailCount,
(SELECT sum(experienceNum) FROM T_REPORT_BUSINESS_EXPERIENCE WHERE SUBSTR(organId,0,7)=a.areaId and businessName='天翼地图') mapCount,
(SELECT sum(experienceNum) FROM T_REPORT_BUSINESS_EXPERIENCE WHERE SUBSTR(organId,0,7)=a.areaId and businessName='手机上网') netCount,
(SELECT sum(experienceNum) FROM T_REPORT_BUSINESS_EXPERIENCE WHERE SUBSTR(organId,0,7)=a.areaId and businessName='手机炒股') stockCount
我对oracle不熟悉,那看执行计划是什么意思啊?随机吗?
decode 函数我去查查,谢谢
like "1100%"
可以用SUBSTR来代替吧,这样可以使用索引
从右到左原则....会先执行WHERE字句,或者查看执行计划就清楚.
thank you,你说的是个提高效率的方法。
你们说的执行计划是个啥啊?
2. 优化SQL一定先 程序设计,先看看你的语句之中是否含有不需要的。一般先这样了。
因为oracle数据库现在都是基于代价的优化,基于代价的SQL优化,oracle数据库会根据你执行analyse,收集到 表、索引的信息来生成自认为效率最快的执行计划,所以你现在看懂执行计划,也优化不了。收集到的表的信息是动态的。所以SQL优化,一般是到了程序慢了,再去优化的。一般用规则来优化。