17,377
社区成员
发帖
与我相关
我的任务
分享关于索引影响查询效率的问题？
一个比较复杂的视图：V_View.里面还有几个表的连接.
执行查询
1.select * from V_View 耗时0.95s
2.select count(*) from V_View 耗时420s 返回数7万余条
想要实现的是：先执行查询2返回总数，再用查询1返回结果列表。
尝试了一些优化方法，没太大用.
后来发现V_View中一个大表TB_CEP的非唯一索引IDX_CEP(就是用来作连接的那个字段上的)可以大幅度
的影响执行效率。
我做了试验
删除IDX_CEP索引
执行select * from V_View 耗时16s
执行select count(*) from V_View 耗时16s
相对来说，效率还可以。但是一旦再次建立了索引就又恢复到以前的效率了。
请问，怎么才能让查询能够保持无索引IDX_CEP状态的下查询计划呢？（我不可能删除索引IDX_CEP）
执行查询
1.select * from V_View 耗时0.95s
2.select count(*) from V_View 耗时420s 返回数7万余条
想要实现的是：先执行查询2返回总数，再用查询1返回结果列表。
尝试了一些优化方法，没太大用.
后来发现V_View中一个大表TB_CEP的非唯一索引IDX_CEP(就是用来作连接的那个字段上的)可以大幅度
的影响执行效率。
我做了试验
删除IDX_CEP索引
执行select * from V_View 耗时16s
执行select count(*) from V_View 耗时16s
相对来说，效率还可以。但是一旦再次建立了索引就又恢复到以前的效率了。
请问，怎么才能让查询能够保持无索引IDX_CEP状态的下查询计划呢？（我不可能删除索引IDX_CEP）
...全文
请发表友善的回复…
发表回复
dingfuhai 2004-11-26
- 打赏
- 举报
如果分析查询计划，并且根据查询计划优化SQL有什么技巧？
tomboy0 2004-11-26
- 打赏
- 举报
set autotrace on explain select ....
观察语句有没有用到索引
观察语句有没有用到索引
hollyzp 2004-11-26
- 打赏
- 举报
补充一下关于你这个问题。从现象来看，你的数据库采用的是似乎是基于规则的优化方法，因为根据索引访问数据的等级高于全表扫描。如果用的是8i以上的数据库，选用Choose优化模式，并分析统计下SQL语句涉及到的表和索引对你的速度有帮助。
hollyzp 2004-11-26
- 打赏
- 举报
分析查询计划,可以看出你的语句毛病所在。查询计划可以看出SQL语句中各个数据行源对数据的访问方法，行源之间的连接方法与连接顺序，把这三点弄清楚，基本上就可以满足日常开发优化调整需求
tomboy0 2004-11-25
- 打赏
- 举报
怎么避免使用特定索引
[A]在很多时候，Oracle会错误的使用索引而导致效率的明显下降，我们可以使用一点点技巧而避免使用不该使用的索引，如：
表test,有字段a,b,c,d，在a,b,c上建立联合索引inx_a(a,b,c)，在b上单独建立了一个索引Inx_b(b)。
在正常情况下，where a=? and b=? and c=?会用到索引inx_a，
where b=?会用到索引inx_b
但是，where a=? and b=? and c=? group by b会用到哪个索引呢？在分析数据不正确（很长时间没有分析）或根本没有分析数据的情况下，oracle往往会使用索引inx_b。通过执行计划的分析，这个索引的使用，将大大耗费查询时间。
当然，我们可以通过如下的技巧避免使用inx_b，而使用inx_a。
where a=? and b=? and c=? group by b||'' --如果b是字符
where a=? and b=? and c=? group by b+0 --如果b是数字
通过这样简单的改变，往往可以是查询时间提交很多倍
当然，我们也可以使用no_index提示，相信很多人没有用过，也是一个不错的方法：
select /*+ no_index(t,inx_b) */ * from test t
where a=? and b=? and c=? group by b
[A]在很多时候，Oracle会错误的使用索引而导致效率的明显下降，我们可以使用一点点技巧而避免使用不该使用的索引，如：
表test,有字段a,b,c,d，在a,b,c上建立联合索引inx_a(a,b,c)，在b上单独建立了一个索引Inx_b(b)。
在正常情况下，where a=? and b=? and c=?会用到索引inx_a，
where b=?会用到索引inx_b
但是，where a=? and b=? and c=? group by b会用到哪个索引呢？在分析数据不正确（很长时间没有分析）或根本没有分析数据的情况下，oracle往往会使用索引inx_b。通过执行计划的分析，这个索引的使用，将大大耗费查询时间。
当然，我们可以通过如下的技巧避免使用inx_b，而使用inx_a。
where a=? and b=? and c=? group by b||'' --如果b是字符
where a=? and b=? and c=? group by b+0 --如果b是数字
通过这样简单的改变，往往可以是查询时间提交很多倍
当然，我们也可以使用no_index提示，相信很多人没有用过，也是一个不错的方法：
select /*+ no_index(t,inx_b) */ * from test t
where a=? and b=? and c=? group by b
tomboy0 2004-11-25
- 打赏
- 举报
如何使用Hint提示
[A] 在select/delete/update后写/*+ hint */
如 select /*+ index(TABLE_NAME INDEX_NAME) */ col1...
注意/*和+之间不能有空格
如何用hint指定使用某个索引
select /*+ index(cbotab) */ col1 from cbotab;
select /*+ index(cbotab cbotab1) */ col1 from cbotab;
select /*+ index(a cbotab1) */ col1 from cbotab a;
其中
TABLE_NAME是必须要写的，且如果在查询中使用了表的别名，在hint也要用表的别名来代替表名;
INDEX_NAME可以不必写，Oracle会根据统计值选一个索引;
如果索引名或表名写错了，那这个hint就会被忽略;
[A] 在select/delete/update后写/*+ hint */
如 select /*+ index(TABLE_NAME INDEX_NAME) */ col1...
注意/*和+之间不能有空格
如何用hint指定使用某个索引
select /*+ index(cbotab) */ col1 from cbotab;
select /*+ index(cbotab cbotab1) */ col1 from cbotab;
select /*+ index(a cbotab1) */ col1 from cbotab a;
其中
TABLE_NAME是必须要写的，且如果在查询中使用了表的别名，在hint也要用表的别名来代替表名;
INDEX_NAME可以不必写，Oracle会根据统计值选一个索引;
如果索引名或表名写错了，那这个hint就会被忽略;
dingfuhai 2004-11-25
- 打赏
- 举报
to xbm2008(大道至简):
那个索引我在其他地方要用到。
那个索引我在其他地方要用到。
xbm2008 2004-11-25
- 打赏
- 举报
你为什么非要建一个自己不愿意用的索引在那里
dingfuhai 2004-11-25
- 打赏
- 举报
加一个什么Hint可以使查询计划中不使用某一个索引？
hqskoala 2004-11-25
- 打赏
- 举报
用explain plan for select count(*) from V_View 看看他都做了什么样的操作，参照着想办法，效率问题只能通过这种办法，具体问题具体分析。对于特定的数据，同样的sql可能效率都不一样的。
dingfuhai 2004-11-25
- 打赏
- 举报
自己Up
