想问下这段sql有能调优的地方吗？必须关联两个表一起查！

kjl1225 2011-08-22 05:36:26
select * from (select *
from (select TRANS_ID,
txn_id,
(select name
from t_type a
where a.code = t.txn_id
and category = 'TXN') txn_name,
(case
when (TRAN_STATUS = 1) then
'有效'
when (TRAN_STATUS = 2) then
'撤销'
when (TRAN_STATUS = 3) then
'被冲正'
else
'暂挂'
end) TRAN_STATUS,
'卡' status,
txn_date,
pan,
txn_amt,
shop_id,
(select name from t_shop a where a.code = t.shop_id) shop_name,
cust_no,
(select name
from t_client_info a
where a.code = t.cust_no) cust_name,
g_id,
(select name from t_goods s where s.code = t.g_id) g_name,
g_price,
g_qty,
old_pan
from t_transaction_int t
order by txn_date desc)
where rownum <= 10000
union
select *
from (select TRANS_ID,
txn_id,
(select name
from t_type a
where a.code = t.txn_id
and category = 'TXN') txn_name,
'有效' TRAN_STATUS,
'卡' status,
txn_date,
pan,
txn_amt,
shop_id,
(select name from t_shop a where a.code = t.shop_id) shop_name,
'' cust_no,
(case
when (t.issu_bank is null) then
'卡'
else
(select name from t_bank a where a.code = t.issu_bank)
end) cust_name,
g_id,
(select name from t_goods s where s.code = t.g_id) g_name,
g_price,
g_qty,
'' old_pan
from t_transaction_ext t
order by txn_date desc)
where rownum <= 10000)
order by txn_date desc
...全文
116 16 打赏 收藏 转发到动态 举报
写回复
用AI写文章
16 条回复
切换为时间正序
请发表友善的回复…
发表回复
kjl1225 2011-08-24
  • 打赏
  • 举报
 回复
那几个关联表都有主键约束。

[Quote=引用 14 楼 zengjc 的回复:]

引用 12 楼 kjl1225 的回复:
第一个不是很明白，这三个表里面都有自己的主键索引。
第三个/*+parallel(t,8)*/ 应该增加在哪个地方？

引用 11 楼 zengjc 的回复:

1.中间查询的临时表T_TYPE、T_SHOP、T_CLIENT_INFO都需要在关联字段上建立主键或索引；
2.可能的话，尽量减少T_TRANSACTION_INT、T_TRAN……
[/Quote]
zengjc 2011-08-24
  • 打赏
  • 举报
 回复
[Quote=引用 12 楼 kjl1225 的回复:]
第一个不是很明白，这三个表里面都有自己的主键索引。
第三个/*+parallel(t,8)*/ 应该增加在哪个地方？

引用 11 楼 zengjc 的回复:

1.中间查询的临时表T_TYPE、T_SHOP、T_CLIENT_INFO都需要在关联字段上建立主键或索引；
2.可能的话，尽量减少T_TRANSACTION_INT、T_TRANSACTION_EXT查询出来的数据量，如增加……
[/Quote]
1.例如这个，t_shop 的shop_id字段应该建立主键约束，一是提升性能，二是避免查询出多条导致异常(select name from t_shop a where a.code = t.shop_id)
3.
select * from (select *

  from (select/*+parallel(t,8)*/  TRANS_ID,

  txn_id,
caoleione 2011-08-24
  • 打赏
  • 举报
 回复
缩小执行范围 先查询出10000条数据 在执行与其它表关联
BenChiM888 2011-08-23
  • 打赏
  • 举报
 回复
union需要排序剃重。
union all 不需要排序剃重。
因此 union all的速度要比union 快，并且重复数据会被显示，不会出现你说的

【当时写sql的时候用union all的就少了2条，而用union就没少，我也没深究原因，就直接用union了。】

[Quote=引用 7 楼 kjl1225 的回复:]
引用 6 楼 benchim888 的回复:

增加查询条件（where）自然就会走索引，你where条件没有自然就走全表扫描，而且还有排序速度自然会慢。

根据你sql所表现出来的业务，只能把 union 改为 union all 提高一点速度了。
其余的没办法。


where条件我是有的，在程序上我会加上，但就算sql加上时间条件，在explain分析是还是提示全表扫描的。
……
[/Quote]
BenChiM888 2011-08-23
  • 打赏
  • 举报
 回复
从你的执行计划看，下面这样的过滤起不到任何作用。

5 - filter(ROWNUM<=10000)
7 - filter(ROWNUM<=10000)
10 - filter(ROWNUM<=10000)
12 - filter(ROWNUM<=10000)


[Quote=引用 8 楼 kjl1225 的回复:]
请问执行计划如何查询到？我还是菜鸟！


引用 2 楼 njlywy 的回复:

执行计划贴出来看下…
[/Quote]
kjl1225 2011-08-23
  • 打赏
  • 举报
 回复
请问执行计划如何查询到？我还是菜鸟！

[Quote=引用 2 楼 njlywy 的回复:]

执行计划贴出来看下…
[/Quote]
kjl1225 2011-08-23
  • 打赏
  • 举报
 回复
[Quote=引用 6 楼 benchim888 的回复:]

增加查询条件（where）自然就会走索引，你where条件没有自然就走全表扫描，而且还有排序速度自然会慢。

根据你sql所表现出来的业务，只能把 union 改为 union all 提高一点速度了。
其余的没办法。
[/Quote]

where条件我是有的，在程序上我会加上，但就算sql加上时间条件，在explain分析是还是提示全表扫描的。
虽然有主键索引，但当时写sql的时候用union all的就少了2条，而用union就没少，我也没深究原因，就直接用union了。
BenChiM888 2011-08-23
  • 打赏
  • 举报
 回复
增加查询条件（where）自然就会走索引，你where条件没有自然就走全表扫描，而且还有排序速度自然会慢。

根据你sql所表现出来的业务，只能把 union 改为 union all 提高一点速度了。
其余的没办法。
kjl1225 2011-08-23
  • 打赏
  • 举报
 回复
分析得知是全表扫描，但两个表本身是有主键的索引的。集合到一起来就变全表扫描了，用hint好像也不起作用，不能如何在这基础上增加个索引呢？还有其他什么优化方案吗？
kjl1225 2011-08-23
  • 打赏
  • 举报
 回复
PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------------------------------

Plan hash value: 2819955526

---------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 20000 | 6582K| | 474K (1)| 01:34:52 |
| 1 | VIEW | | 20000 | 6582K| | 474K (1)| 01:34:52 |
| 2 | SORT UNIQUE | | 20000 | 2871K| 6944K| 474K (54)| 01:34:52 |
| 3 | UNION-ALL | | | | | | |
| 4 | VIEW | | 10000 | 1503K| | 221K (1)| 00:44:19 |
|* 5 | COUNT STOPKEY | | | | | | |

PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------------------------------

| 6 | VIEW | | 3445K| 506M| | 221K (1)| 00:44:19 |
|* 7 | SORT ORDER BY STOPKEY| | 3445K| 828M| 2243M| 221K (1)| 00:44:19 |
| 8 | TABLE ACCESS FULL | T_TRANSACTION_INT | 3445K| 828M| | 33812 (1)| 00:06:46 |
| 9 | VIEW | | 10000 | 1367K| | 252K (1)| 00:50:25 |
|* 10 | COUNT STOPKEY | | | | | | |
| 11 | VIEW | | 4493K| 599M| | 252K (1)| 00:50:25 |
|* 12 | SORT ORDER BY STOPKEY| | 4493K| 938M| 2340M| 252K (1)| 00:50:25 |
| 13 | TABLE ACCESS FULL | T_TRANSACTION_EXT | 4493K| 938M| | 38670 (1)| 00:07:45 |
---------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):

PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------------------------------

---------------------------------------------------

5 - filter(ROWNUM<=10000)
7 - filter(ROWNUM<=10000)
10 - filter(ROWNUM<=10000)
12 - filter(ROWNUM<=10000)


用explain分析得出一下结果，但解决方案不知道怎么弄。
kjl1225 2011-08-23
  • 打赏
  • 举报
 回复
[Quote=引用 1 楼 caoleione 的回复:]

select *
from (select TRANS_ID,
txn_id,
(select name
from t_type a
where a.code = t.txn_id
and category = '……
[/Quote]


但书上写着9i之后引入了case 效率是要比decode来的快，用plsql实测case快了 0.01秒
kjl1225 2011-08-23
  • 打赏
  • 举报
 回复
我加这个条件就是为了控制数据量，还有一个日期条件我没加进去。

[Quote=引用 9 楼 benchim888 的回复:]

从你的执行计划看，下面这样的过滤起不到任何作用。

5 - filter(ROWNUM<=10000)
7 - filter(ROWNUM<=10000)
10 - filter(ROWNUM<=10000)
12 - filter(ROWNUM<=10000)


引用 8 楼 kjl1225 的回复:
请问执行计划如何查询到？我还是菜鸟！


引用 2……
[/Quote]
kjl1225 2011-08-23
  • 打赏
  • 举报
 回复
第一个不是很明白，这三个表里面都有自己的主键索引。
第三个/*+parallel(t,8)*/ 应该增加在哪个地方？
[Quote=引用 11 楼 zengjc 的回复:]

1.中间查询的临时表T_TYPE、T_SHOP、T_CLIENT_INFO都需要在关联字段上建立主键或索引；
2.可能的话，尽量减少T_TRANSACTION_INT、T_TRANSACTION_EXT查询出来的数据量，如增加TXN_DATE条件等；
3.如果资源允许，增加sql并发数/*+parallel(t,8)*/
[/Quote]
zengjc 2011-08-23
  • 打赏
  • 举报
 回复
1.中间查询的临时表T_TYPE、T_SHOP、T_CLIENT_INFO都需要在关联字段上建立主键或索引；
2.可能的话，尽量减少T_TRANSACTION_INT、T_TRANSACTION_EXT查询出来的数据量，如增加TXN_DATE条件等；
3.如果资源允许，增加sql并发数/*+parallel(t,8)*/
njlywy 2011-08-22
  • 打赏
  • 举报
 回复
执行计划贴出来看下…
caoleione 2011-08-22
  • 打赏
  • 举报
 回复
select *
from (select TRANS_ID,
txn_id,
(select name
from t_type a
where a.code = t.txn_id
and category = 'TXN') txn_name,
/*(case
when (TRAN_STATUS = 1) then
'有效'
when (TRAN_STATUS = 2) then
'撤销'
when (TRAN_STATUS = 3) then
'被冲正'
else
'暂挂'
end) TRAN_STATUS,*/
decode(TRAN_STATUS, 1, '有效', 2, '撤销', 3, '被冲正', '暂挂') as TRAN_STATUS '卡' status,
txn_date,
pan,
txn_amt,
shop_id,
(select name from t_shop a where a.code = t.shop_id) shop_name,
cust_no,
(select name from t_client_info a where a.code = t.cust_no) cust_name,
g_id,
(select name from t_goods s where s.code = t.g_id) g_name,
g_price,
g_qty,
old_pan
from (select *
from (select * from t_transaction_int order by txn_date desc)
where rownum <= 10000) t
union
select TRANS_ID,
txn_id,
(select name
from t_type a
where a.code = t.txn_id
and category = 'TXN') txn_name,
'有效' TRAN_STATUS,
'卡' status,
txn_date,
pan,
txn_amt,
shop_id,
(select name from t_shop a where a.code = t.shop_id) shop_name,
'' cust_no,
(case
when (t.issu_bank is null) then
'卡'
else
(select name from t_bank a where a.code = t.issu_bank)
end) cust_name,
g_id,
(select name from t_goods s where s.code = t.g_id) g_name,
g_price,
g_qty,
'' old_pan
from (select *
from (select * from t_transaction_ext order by txn_date desc)
where rownum <= 10000) t)
order by txn_date desc
oracle sql以及性能调优
目 录 1.选用适合的ORACLE优化器 2 2.访问Table的方式 3 3.共享SQL语句 3 4.选择最有效率的表名顺序(只在基于规则的优化器中有效) 5 5.WHERE子句中的连接顺序． 6 6.SELECT子句中避免使用 ‘ * ‘ 7 7.减少访问数据库的次数 7 8.使用DECODE函数来减少处理时间 8 9.整合简单,无关联的数据库访问 9 10.删除重复记录 10 11.用TRUNCATE替代DELETE 10 12.尽量多使用COMMIT 11 13.计算记录条数 11 14.
SQL优化实战经典案例分析
前言 大家好，我是黎杜，今天和大家聊聊SQL优化的场景。SQL调优这块呢，大厂面试必问的。最近金九银十嘛，所以整理了SQL的调优思路，并且附几个经典案例分析。1.慢SQL优化思路。 慢查询日志记录慢SQLexplain分析SQL的执行计划profile 分析执行耗时Optimizer Trace分析详情确定问题并采用相应的措施1.1 慢查询日志记录慢SQL如何定位慢SQL呢、我们可以通过慢查询日志...
面试官：给你一段有问题的SQL，如何优化？(手把手带你优化一遍)
在面试的时候很多面试官喜欢问候选人这样一个问题：这里不能说完全错误，因为这些技巧确实可以提高SQL运行效率；但是也不能说完全正确，毕竟面试官是想问他具体怎么是做SQL优化的。如果接下来面试官接着问:到这一步的时候就有很多候选人做不好打了退堂鼓。他们有很扎实的理论知识，但是动手能力却差点火候。今天这篇文章就从实战的角度出发，带大家走一遍SQL优化的真实流程。
【MySQL】祖传SQL脚本调优
看到这个题目你敢相信自己的眼睛吗？居然有人敢动祖传代码？没错，那个人就是我，而且这次不仅要动而且要调优。
MySQL调优之SQL语句：如何写出高性能SQL语句？
MySQL调优之SQL语句：如何写出高性能SQL语句？
Oracle 高级技术

3,490

社区成员

18,714

社区内容

发帖
与我相关
我的任务
社区描述
Oracle 高级技术相关讨论专区
社区管理员
  • 高级技术社区
加入社区
  • 近7日
  • 近30日
  • 至今

加载中

查看更多榜单
社区公告
暂无公告

试试用AI创作助手写篇文章吧

+ 用AI写文章