oracle9i like查询优化

henanxiaomai 2011-05-11 02:12:03
select a.nbxh,a.zch,a.qymc,a.hy,a.djjg,a.gxdw,a.glph,a.qylxdl,b.zwwz zwwz from hz_qyhznr a,wl_jd b
where (a.zt='K' or a.zt='B') and a.nbxh=b.nbxh(+) and (b.nbxh is null or b.zwwz='Y')
and a.qymc like '%企业名称%';

//现在用PL/sql developer查询的时候卡在那里,结果一直出不来。如果没有加最后q.qymc的查询条件,结果可以出来。
表“hz_qyhznr”46万条记录,表“wl_jd”18万条,想要知道怎么去优化该查询,求高手赐教,谢谢
...全文
49 2 打赏 收藏 转发到动态 举报
AI 作业
写回复
用AI写文章
2 条回复
切换为时间正序
请发表友善的回复…
发表回复
henanxiaomai 2011-05-11
  • 打赏
  • 举报
回复
呵呵,按照你说的添加了索引,查询速度果然提升了很多……,多谢了!
luoyoumou 2011-05-11
  • 打赏
  • 举报
回复
-- 贴子发一遍就够啦!

-- 可以先在表hz_qyhznr的zt字段创建索引:
CREATE index hz_qyhznr_inx1 on hz_qyhznr(zt);

-- 可以先在表wl_jd的nbxh、zwwz字段创建复合索引(最好按字段定义时的顺序去指定索引中的字段顺序)
CREATE index wl_jd_inx1 on wl_jd(nbxh,zwwz);

with t as(SELECT nbxh, zch, qymc, hy, djjg, gxdw, glph, qylxdl from hz_qyhznr WHERE zt='K' or zt='B'),
b as(SELECT zwwz from wl_jd where zwwz='Y' or nbxh is null),
a as(select nbxh, zch, qymc, hy, djjg, gxdw, glph, qylxdl from t WHERE qymc like '%企业名称%')
select a.nbxh,a.zch,a.qymc,a.hy,a.djjg,a.gxdw,a.glph,a.qylxdl,b.zwwz zwwz from a, b
where a.nbxh=b.nbxh(+);
One of the problems with having plenty of development options is that it’s sometimes hard to figure out which one might be the best choice for your particular needs. Everyone wants as much flexibility as possible (as many choices as they can possibly have), but they also want things to be very cut and dried—in other words, easy. Oracle presents developers with almost unlimited choice. No one ever says, “You can’t do that in Oracle”; rather, they say, “How many different ways would you like to do that in Oracle?” I hope that this book will help you make the correct choice. This book is aimed at those people who appreciate the choice but would also like some guidelines and practical implementation details on Oracle features and functions. For example, Oracle has a really neat feature called parallel execution. The Oracle documentation tells you how to use this feature and what it does. Oracle documentation does not, however, tell you when you should use this feature and, perhaps even more important, when you should not use this feature. It doesn’t always tell you the implementation details of this feature, and if you’re not aware of them, this can come back to haunt you (I’m not referring to bugs, but the way the feature is supposed to work and what it was really designed to do). In this book I strove to not only describe how things work, but also explain when and why you would consider using a particular feature or implementation. I feel it is important to understand not only the “how” behind things, but also the “when” and “why”—as well as the “when not” and “why not”!

3,494

社区成员

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

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